SQL Server backup and restore: .bak files without Agent

A SQL Server backup is one statement: BACKUP DATABASE [appdb] TO DISK = N'/path/appdb.bak' WITH CHECKSUM, INIT;. It writes a consistent copy of the database to a single .bak file while the database keeps serving traffic, and RESTORE DATABASE puts it back, on the same server or any other SQL Server of the same or a newer version. The parts that trip people up are around that statement, not in it: Express has no SQL Server Agent to schedule it, the file lands on the server's disk rather than yours, a restore to a different server needs WITH MOVE, and restored logins arrive disconnected from their users.

This guide covers all of it for SQL Server 2022, with the Express edition's limits called out where they matter. Most of it applies unchanged to 2016 through 2019 and to Windows as well as Linux.

What a native backup contains#

BACKUP DATABASE is not a file copy. SQL Server reads every allocated data page, then appends enough of the transaction log to make the copy consistent as of the moment the backup finished. When it is restored, the engine replays that log portion and rolls back anything that was uncommitted. The result is a database that existed at one instant, with every foreign key intact, which is exactly what a copy of the .mdf file taken while the server runs cannot promise.

There are three backup types, and Express supports all of them:

A differential is cumulative, not incremental: Wednesday's differential contains everything changed since Sunday's full, including what Monday's and Tuesday's already held. Restoring needs the full plus the single most recent differential. Log backups are the opposite - each one holds only its own stretch of time, and restoring to a point needs the unbroken chain. Whether you need them at all depends on the recovery model, which SQL Server recovery models and log growth covers in depth. For a database under 10 GB with a nightly full backup and an hour of acceptable loss, full backups alone are a perfectly respectable plan.

What a backup does not contain is anything outside the database. Logins live in master, jobs live in msdb, and server settings live in the instance. A .bak of appdb restored elsewhere brings its database users with it but not the logins they map to. That is the single most common surprise after a restore, and it has its own section below.

Taking a full backup#

Connect as sa or any login with db_backupoperator on the database, and run:

BACKUP DATABASE [appdb]TO DISK = N'/var/opt/mssql/data/appdb-2026-10-08.bak'WITH CHECKSUM, INIT, STATS = 10,     NAME = N'appdb full 2026-10-08';

What each option does:

• CHECKSUM - verifies the page checksums as it reads and writes a checksum over the whole backup. If a page is already corrupt, the backup fails instead of quietly preserving the damage. There is no good reason to leave it off.
• INIT - overwrites any backup sets already in the file. Without it, SQL Server appends to the file, and a .bak that has been appended to every night for a year is a 300 GB surprise holding 365 backups. Use a new file name per backup and INIT, or one file name and INIT, never append by accident.
• STATS = 10 - prints progress every 10 percent, which is how you tell a slow backup from a hung one.
• COPY_ONLY - add this to an ad hoc backup taken outside your normal schedule. It does not reset the differential base, so the next scheduled differential still pairs with the scheduled full rather than with your one-off copy.

The path is on the server. That is worth repeating because it confuses everyone who first runs a backup from SSMS on their laptop: TO DISK is resolved by the SQL Server process, on the machine SQL Server runs on. A path like C:\Backups\appdb.bak on a Linux server is not your C: drive. To find where the server keeps backups by default:

SELECT SERVERPROPERTY('InstanceDefaultBackupPath') AS backup_dir,       SERVERPROPERTY('InstanceDefaultDataPath')   AS data_dir;

InstanceDefaultBackupPath exists from SQL Server 2019. On a standard Linux install both usually point at /var/opt/mssql/data/, unless someone changed them with mssql-conf. The SQL Server process runs as the mssql user on Linux and needs write permission on whatever directory you name.

Verifying a backup before you need it#

A backup file that has never been read is a hypothesis. SQL Server gives you three cheap checks, none of which touch the live database:

-- Is the file readable and complete? Checks the checksums too.RESTORE VERIFYONLYFROM DISK = N'/var/opt/mssql/data/appdb-2026-10-08.bak'WITH CHECKSUM;-- What is in the file: database name, type, dates, version.RESTORE HEADERONLYFROM DISK = N'/var/opt/mssql/data/appdb-2026-10-08.bak';-- Which data and log files the database had, by logical name.RESTORE FILELISTONLYFROM DISK = N'/var/opt/mssql/data/appdb-2026-10-08.bak';

VERIFYONLY proves the file is intact. It does not prove the database inside is healthy - a database that was logically damaged before the backup is backed up faithfully. The only real test is restoring the file to a new name and running DBCC CHECKDB against the copy:

DBCC CHECKDB (N'appdb_verify') WITH NO_INFOMSGS, ALL_ERRORMSGS;

No output means no errors. Then drop the copy. On a 10 GB Express database this takes minutes, and doing it once a month is the difference between having backups and believing you do. Testing a restore before you need it makes the general case.

The backup history is in msdb, which is useful for checking that a scheduled job actually ran:

SELECT TOP (20) database_name, type, backup_start_date,       backup_finish_date, backup_size / 1048576 AS size_mb,       physical_device_nameFROM msdb.dbo.backupset AS bJOIN msdb.dbo.backupmediafamily AS m ON m.media_set_id = b.media_set_idORDER BY backup_finish_date DESC;

type is D for full, I for differential and L for log.

Restoring: same server, new name, or another server#

Restoring is where the logical file names from FILELISTONLY matter. A database has at least one data file and one log file, each with a logical name (for example appdb and appdb_log) and a physical path. A restore tries to put the files back at their original physical paths unless told otherwise, which fails if those paths are in use or do not exist on this machine.

To restore beside the original under a new name - the safe way to recover a dropped table without touching production:

RESTORE DATABASE [appdb_restore]FROM DISK = N'/var/opt/mssql/data/appdb-2026-10-08.bak'WITH MOVE N'appdb'     TO N'/var/opt/mssql/data/appdb_restore.mdf',     MOVE N'appdb_log' TO N'/var/opt/mssql/data/appdb_restore_log.ldf',     RECOVERY, STATS = 10;

Then copy the rows you need back across with an ordinary INSERT ... SELECT between the two databases, and drop appdb_restore.

To overwrite the original, you need exclusive access. Every connection to the database, including the one your SSMS query window has open, blocks the restore:

USE master;ALTER DATABASE [appdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;RESTORE DATABASE [appdb]FROM DISK = N'/var/opt/mssql/data/appdb-2026-10-08.bak'WITH REPLACE, RECOVERY, STATS = 10;ALTER DATABASE [appdb] SET MULTI_USER;

ROLLBACK IMMEDIATE disconnects everyone and rolls back their open transactions. REPLACE tells SQL Server you really mean to overwrite a database that exists; without it, a restore over a database whose log has not been backed up stops with error 3159 asking for a tail-log backup first.

A backup taken on Windows restores onto Linux and the other way round. The file format is the same; only the paths differ, so MOVE every file to a Linux path. The version rule is strict in one direction: a backup restores to the same version or a newer one, never an older one. A SQL Server 2022 backup will not restore on 2019, whatever you try. To go down a version, you need a BACPAC or scripts, both covered in migrating a database to SQL Server hosting.

The RECOVERY option finishes the restore and opens the database. NORECOVERY leaves it in the "Restoring..." state so further backups can be applied - a differential, then log backups. Forget the final RECOVERY and the database sits there looking stuck; RESTORE DATABASE [appdb] WITH RECOVERY; on its own finishes it.

Logins, users and the orphaned user problem#

Inside a database, a user is linked to a server login by a security identifier (SID). Logins created with SQL authentication get a random SID on each server. Restore a database onto a different server, create a login with the same name, and the user and the login still do not match: the user is orphaned, and the application gets Login failed for user 'app' even though both exist.

Find orphans in the restored database:

SELECT dp.name AS user_name, dp.sidFROM sys.database_principals AS dpLEFT JOIN sys.server_principals AS sp ON sp.sid = dp.sidWHERE dp.type = 'S'  AND sp.sid IS NULL  AND dp.authentication_type_desc = 'INSTANCE';

Fix each one by pointing it at the login:

ALTER USER [app] WITH LOGIN = [app];

The older sp_change_users_login procedure does the same job and is deprecated; use ALTER USER. To avoid the problem entirely on a planned move, create the login on the new server with the original SID (CREATE LOGIN [app] WITH PASSWORD = '...', SID = 0x...), taking the SID from sys.server_principals on the old one. SQL Server logins, users and roles explains the model properly.

Scheduling backups when there is no SQL Server Agent#

On Standard and Enterprise, a SQL Server Agent job or a maintenance plan runs the nightly backup. Express has no SQL Server Agent at all, on Windows or Linux, so the schedule has to come from outside the engine. That is less of a limitation than it sounds: a backup is a single statement, and anything that can run sqlcmd on a timer can take one.

Put the backup in a script that names its file by date:

DECLARE @file nvarchar(400) =    N'/var/opt/mssql/data/appdb-' + CONVERT(char(8), SYSUTCDATETIME(), 112) + N'.bak';BACKUP DATABASE [appdb] TO DISK = @fileWITH CHECKSUM, INIT, STATS = 25;

Then run it from any machine that can reach the server - your own Linux box, a small app server, a CI job:

$ export SQLCMDPASSWORD='the-generated-sa-password'$ sqlcmd -S db.example.net,14330 -U sa -C -b -i backup.sql -o backup.log

-b makes sqlcmd exit with a non-zero code on an error, so cron or the CI runner can tell a failed backup from a good one. -C trusts the server's certificate, needed with the version 18 tools because they encrypt by default; sqlcmd and bcp covers the flags. The crontab line is then ordinary:

15 3 * * * /usr/local/bin/sql-backup.sh >> /var/log/sql-backup.log 2>&1

On Windows, Task Scheduler running the same sqlcmd line does the job. For anything more elaborate - several databases, differentials on weekdays, deleting old files - Ola Hallengren's free maintenance solution is the standard answer on Express: install its stored procedures once, then call dbo.DatabaseBackup from your scheduled sqlcmd instead of a bare BACKUP. It has supported SQL Server on Linux for years; check its documentation for which cleanup options work there.

Two things about this arrangement are easy to miss. The backup file is still written on the database server's disk, not on the machine running sqlcmd, so it needs pruning there and it needs copying off. And a backup that sits on the same disk as the database protects you from a bad DELETE, not from losing the server.

Getting the .bak off the server#

A backup on the same machine as the database is half a backup. You want a copy somewhere else, ideally somewhere that would survive the server being deleted. There are three realistic routes:

1. Download the file. If your host gives you file access to the server, fetch the .bak over SFTP after the backup finishes and delete old ones on the server. This is the simplest and the most portable.
2. Back up straight to object storage. SQL Server 2022 added BACKUP ... TO URL with an s3:// address for S3-compatible storage, authenticated by a credential holding the access key and secret. It needs an HTTPS endpoint whose certificate the server trusts, and it is worth testing on your edition before you rely on it.
3. Export logically. SqlPackage /Action:Export run from your own machine writes a .bacpac locally over an ordinary connection. It is slower than a native backup and not transactionally consistent if writes happen during the export, but it needs no file access to the server at all.

On RE:NODE, the SQL Server line runs SQL Server 2022 Express on Linux with one to four backup slots depending on the tier. Panel backups are taken on demand or on a schedule from the Schedules tab, stored off the machine they protect, downloadable, and restored with a button - but they are a copy of the whole server, and deleting the server deletes them too. A sensible pattern is to run your native BACKUP a few minutes before the scheduled panel backup, so the archive always contains a consistent .bak, and to download one of those regularly to somewhere that is entirely yours. Every server also has SFTP and the file manager for fetching files directly. Backups that actually restore explains why two independent copies fail better than one good one.

Troubleshooting backups and restores#

Operating system error 5 (Access is denied) or error 2 (cannot find the file). The path is resolved by the SQL Server process on the server. On Linux, the mssql user needs write access to the directory, and the directory must exist - BACKUP does not create folders. Paths are case-sensitive on Linux, so /var/opt/mssql/Data is not /var/opt/mssql/data.

Error 3154: the backup set holds a backup of a database other than the existing database. You are restoring a different database's backup over this one. Add REPLACE if that is genuinely what you want, or restore to a new name.

Error 3101: exclusive access could not be obtained because the database is in use. Something is connected - often your own query window. USE master, then set the database to SINGLE_USER WITH ROLLBACK IMMEDIATE as shown above.

Error 3169: the database was backed up on a server running a newer version. The version rule. Nothing will make that .bak restore on the older server; use a BACPAC or scripts.

The restore fails because the database would exceed 10240 MB. Express limits each database's data files to 10 GB. A backup of a larger database from Standard will not restore on Express. Archive or delete data on the source first, or move to an edition without the cap.

The database is stuck in "Restoring...". The last restore step used NORECOVERY. Run RESTORE DATABASE [appdb] WITH RECOVERY; to bring it online.

FAQ#

Can I back up a SQL Server database to my own computer from SSMS?

Not directly. The backup is written by the server process to the server's own disk, even when you click through the SSMS dialog on your laptop. Back up on the server, then download the file, or export a BACPAC, which SqlPackage and SSMS write to the local machine.

How often should I back up a small SQL Server database?

Nightly full backups cover most applications, with an extra COPY_ONLY backup before every deploy or schema change. If losing a day of writes would hurt, add differentials during the day or move to the full recovery model with log backups every 15 to 60 minutes.

Does BACKUP DATABASE lock the tables?

No. Reads and writes carry on during the backup. It costs disk reads and some CPU, so run it when the database is quiet, but users will not see blocking from the backup itself. A few operations, such as shrinking a file, cannot run at the same time as a backup.

Can I restore a backup from SQL Server on Windows to SQL Server on Linux?

Yes. The .bak format is identical on both. Use RESTORE FILELISTONLY to read the logical file names, then WITH MOVE each file to a Linux path such as /var/opt/mssql/data/. The version rule still applies: same or newer only.

Why is my Express backup as big as the database?

Because Express cannot write compressed backups. The file is roughly the size of the used pages, not the allocated file size. Compress it with gzip or zstd after it is written if storage or transfer time matters; expect a large reduction on most application data.

Is a copy of the .mdf and .ldf files a valid backup?

Only if SQL Server was stopped, or the database detached, when you copied them, and even then it is tied to that version and edition. A copy taken while the server runs may not attach. BACKUP DATABASE is the supported way, and it costs nothing extra.