SQL Server Restore Database Script: Automated T-SQL Restore

Updated: 12 October 2026 · Applies to: SQL Server 2017 and later on Windows and Linux; script tested on SQL Server 2025 CU9 (17.0.5005.3, Enterprise Developer edition) on Linux

The T-SQL statement that restores a database is RESTORE DATABASE name FROM DISK = N'file.bak' WITH MOVE ..., RECOVERY. The script below automates it: it reads the backup history in msdb, finds the newest full backup, the newest differential and the log backups taken after it, writes the RESTORE commands with the right MOVE paths and runs them in order. Use it to refresh a test or reporting copy every night. To restore a single .bak file by hand or in SSMS, see How to Restore a SQL Server Database from a .bak File (SSMS, T-SQL and Linux).

The T-SQL restore commands in short

List the logical file names, restore the full backup, the newest differential and each log backup in order, then bring the database online:

RESTORE FILELISTONLY FROM DISK = N'D:\Backup\ShopDB_full.bak';
RESTORE DATABASE ShopDB FROM DISK = N'D:\Backup\ShopDB_full.bak'
  WITH MOVE N'ShopDB' TO N'D:\SQLData\ShopDB.mdf',
       MOVE N'ShopDB_log' TO N'E:\SQLLogs\ShopDB_log.ldf',
       REPLACE, NORECOVERY, STATS = 10;
RESTORE DATABASE ShopDB FROM DISK = N'D:\Backup\ShopDB_diff.bak' WITH NORECOVERY;
RESTORE LOG ShopDB FROM DISK = N'D:\Backup\ShopDB_logs.trn' WITH FILE = 1, NORECOVERY;
RESTORE LOG ShopDB FROM DISK = N'D:\Backup\ShopDB_logs.trn' WITH FILE = 2, NORECOVERY;
RESTORE DATABASE ShopDB WITH RECOVERY;

• NORECOVERY keeps the database restoring so the next backup can be applied; RECOVERY (the default) brings it online. With only a full backup, restore it WITH RECOVERY at once.
• MOVE puts each file (logical name from RESTORE FILELISTONLY) in a folder that exists on this server. FILE = n picks one backup when several were appended to the same file.
• REPLACE overwrites an existing database. Microsoft says to use it rarely, as it skips the checks that stop you overwriting the wrong database or losing log records that were never backed up.

The automated restore script

Save it as restore-latest.sql and change the three settings at the top.

/* restore-latest.sql: restore the newest backup chain (full, newest
   differential, later log backups) of one database, using the backup
   history in msdb. SQL Server 2017 or later. */
SET NOCOUNT ON;
DECLARE @SourceDb sysname = N'ShopDB';       -- database that was backed up
DECLARE @TargetDb sysname = N'ShopDB_Test';  -- name to restore it as
DECLARE @Execute  bit     = 0;               -- 0 = only print, 1 = run them
DECLARE @DataPath nvarchar(512) = CONVERT(nvarchar(512), SERVERPROPERTY('InstanceDefaultDataPath'));
DECLARE @LogPath  nvarchar(512) = CONVERT(nvarchar(512), SERVERPROPERTY('InstanceDefaultLogPath'));

DECLARE @FullId int, @FullCkpt numeric(25,0), @DiffId int, @BaseLsn numeric(25,0), @Move nvarchar(max);
DECLARE @Plan TABLE (Step int IDENTITY(1,1), Cmd nvarchar(max));

IF @TargetDb = @SourceDb AND @Execute = 1
BEGIN RAISERROR(N'Target and source are the same database; refusing to overwrite it.', 16, 1); RETURN; END;

-- 1. Newest full backup (copy-only backups are skipped)
SELECT TOP (1) @FullId = backup_set_id, @FullCkpt = checkpoint_lsn, @BaseLsn = last_lsn
FROM msdb.dbo.backupset
WHERE database_name = @SourceDb AND type = 'D' AND is_copy_only = 0
ORDER BY backup_finish_date DESC;
IF @FullId IS NULL
BEGIN RAISERROR(N'No full backup of %s in msdb.', 16, 1, @SourceDb); RETURN; END;

-- 2. Newest differential based on that full backup (if any)
SELECT TOP (1) @DiffId = backup_set_id, @BaseLsn = last_lsn
FROM msdb.dbo.backupset
WHERE database_name = @SourceDb AND type = 'I' AND database_backup_lsn = @FullCkpt
ORDER BY backup_finish_date DESC;

-- 3. MOVE every file of the full backup to this server's default folders
SELECT @Move = STRING_AGG(CONVERT(nvarchar(max),
         N'MOVE N''' + REPLACE(logical_name, N'''', N'''''') + N''' TO N'''
       + REPLACE(CASE file_type WHEN 'L' THEN @LogPath ELSE @DataPath END + @TargetDb + N'_' + logical_name
       + RIGHT(physical_name, CHARINDEX(N'.', REVERSE(physical_name))), N'''', N'''''') + N''''), N', ')
FROM msdb.dbo.backupfile
WHERE backup_set_id = @FullId AND is_present = 1;

-- 4. Build the restore plan: full, differential, then the log backups in order
IF DATABASEPROPERTYEX(@TargetDb, 'Status') = 'ONLINE'
    INSERT @Plan (Cmd) VALUES (N'ALTER DATABASE ' + QUOTENAME(@TargetDb) + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;');

INSERT @Plan (Cmd)
SELECT CASE b.type WHEN 'L' THEN N'RESTORE LOG ' ELSE N'RESTORE DATABASE ' END + QUOTENAME(@TargetDb)
     + N' FROM ' + d.Devices + N' WITH FILE = ' + CONVERT(nvarchar(10), b.position)
     + CASE WHEN b.backup_set_id = @FullId THEN N', ' + @Move + N', REPLACE' ELSE N'' END
     + N', NORECOVERY, STATS = 50;'
FROM msdb.dbo.backupset AS b
CROSS APPLY (SELECT STRING_AGG(CONVERT(nvarchar(max), N'DISK = N''' + REPLACE(physical_device_name, N'''', N'''''') + N''''), N', ')
                    WITHIN GROUP (ORDER BY family_sequence_number) AS Devices
             FROM msdb.dbo.backupmediafamily
             WHERE media_set_id = b.media_set_id) AS d
WHERE b.backup_set_id IN (@FullId, @DiffId)
   OR (b.database_name = @SourceDb AND b.type = 'L'
       AND b.database_backup_lsn = @FullCkpt AND b.last_lsn > @BaseLsn)
ORDER BY CASE WHEN b.backup_set_id = @FullId THEN 0 WHEN b.backup_set_id = @DiffId THEN 1 ELSE 2 END, b.first_lsn;

INSERT @Plan (Cmd) VALUES (N'RESTORE DATABASE ' + QUOTENAME(@TargetDb) + N' WITH RECOVERY;');

-- 5. Print each step and run it; stop at the first error
DECLARE @Step int = 1, @Cmd nvarchar(max), @rc int;
WHILE 1 = 1
BEGIN
    SELECT @Cmd = Cmd FROM @Plan WHERE Step = @Step;
    IF @@ROWCOUNT = 0 BREAK;
    PRINT @Cmd;
    IF @Execute = 1
    BEGIN
        EXEC @rc = sys.sp_executesql @Cmd;
        IF @rc <> 0 BEGIN RAISERROR(N'Step %d failed; restore stopped.', 16, 1, @Step); RETURN; END;
    END;
    SET @Step += 1;
END;

It uses the newest full backup that is not copy-only, the newest differential based on it, and the later log backups of that chain in LSN order. Each file goes to the server's default data or log folder with the target name in front (ShopDB_Test_ShopDB.mdf). Striped backups and backups appended to one file are handled.

Run it

1. Run it as it is: with @Execute = 0 it only prints the plan. Read it.
2. Set @Execute = 1 and run it with sqlcmd (or paste it into an SSMS query window):

   sqlcmd -S localhost -U sa -C -b -i restore-latest.sql

3. Check the result: SELECT name, state_desc FROM sys.databases; should show the new database as ONLINE.

The plan the script printed on our lab server (SQL Server 2025 in a test container):

ALTER DATABASE [ShopDB_Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [ShopDB_Test] FROM DISK = N'/var/opt/mssql/backup/ShopDB_full.bak' WITH FILE = 1, MOVE N'ShopDB' TO N'/var/opt/mssql/data/ShopDB_Test_ShopDB.mdf', MOVE N'ShopDB_log' TO N'/var/opt/mssql/data/ShopDB_Test_ShopDB_log.ldf', REPLACE, NORECOVERY, STATS = 50;
RESTORE DATABASE [ShopDB_Test] FROM DISK = N'/var/opt/mssql/backup/ShopDB_diff.bak' WITH FILE = 1, NORECOVERY, STATS = 50;
RESTORE LOG [ShopDB_Test] FROM DISK = N'/var/opt/mssql/backup/ShopDB_logs.trn' WITH FILE = 1, NORECOVERY, STATS = 50;
RESTORE LOG [ShopDB_Test] FROM DISK = N'/var/opt/mssql/backup/ShopDB_logs.trn' WITH FILE = 2, NORECOVERY, STATS = 50;
RESTORE DATABASE [ShopDB_Test] WITH RECOVERY;

With @Execute = 1 it ran these steps, each followed by the usual Processed ... pages messages, and all five rows of the test table (from before the full backup, the differential and each log backup) were back in ShopDB_Test.

Run it again to refresh the copy: an online target is switched to single-user mode first (open transactions are rolled back) and then replaced. If the target name equals the source name, the script refuses to run.

Restore on another server

The backup history lives in msdb on the server that made the backups. Run the script there with @Execute = 0, set @DataPath and @LogPath to the test server's folders (keep the closing \ or /), and run the printed commands on the test server. Its SQL Server service account must be able to read the backup files at those paths, so copy them to the same place or back up to a network share. A backup restores on the same or a newer SQL Server version, never an older one.

Schedule it

• SQL Server Agent: in SSMS, expand SQL Server Agent, create a job, select New on the Steps page, choose Transact-SQL Script (TSQL), paste the script into Command and add a schedule. On Linux the Agent is off by default: run sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true and sudo systemctl restart mssql-server.
• cron or Task Scheduler: run the sqlcmd line above with the password in the SQLCMDPASSWORD environment variable, not on the command line, and -o restore-latest.log to keep the output. With -b, sqlcmd exits with code 1 when the script fails, so the scheduler can alert you.

Permissions, according to Microsoft: restoring a new database needs CREATE DATABASE; restoring over an existing one is allowed for sysadmin, dbcreator and the database owner. The login must also be able to read the backup tables in msdb.

Restore to a point in time

To stop just before a mistake such as a wrong DELETE, restore the chain by hand and add STOPAT to the last log backup:

RESTORE DATABASE ShopDB_PIT FROM DISK = N'/var/opt/mssql/backup/ShopDB_full.bak'
  WITH MOVE N'ShopDB' TO N'/var/opt/mssql/data/ShopDB_PIT.mdf',
       MOVE N'ShopDB_log' TO N'/var/opt/mssql/data/ShopDB_PIT_log.ldf', NORECOVERY;
RESTORE DATABASE ShopDB_PIT FROM DISK = N'/var/opt/mssql/backup/ShopDB_diff.bak' WITH NORECOVERY;
RESTORE LOG ShopDB_PIT FROM DISK = N'/var/opt/mssql/backup/ShopDB_logs.trn' WITH FILE = 1, NORECOVERY;
RESTORE LOG ShopDB_PIT FROM DISK = N'/var/opt/mssql/backup/ShopDB_logs.trn'
  WITH FILE = 2, STOPAT = '2026-10-11T18:24:29.629', RECOVERY;

Our copy had four rows instead of five: the row inserted after the STOPAT time was left out. This needs the full recovery model and an unbroken log chain. To check a backup without restoring it, use RESTORE VERIFYONLY (The backup set on file 1 is valid.).

When a step fails

The script stops at the first failed step. Here we had moved the differential backup away:

Msg 3201, Level 16, State 2, Server SQLLAB, Line 1
Cannot open backup device '/var/opt/mssql/backup/ShopDB_diff.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Server SQLLAB, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 50000, Level 16, State 1, Server SQLLAB, Line 70
Step 3 failed; restore stopped.

Operating system error 2 means the file is not at that path; error 5 means the SQL Server service account may not read it. The target stays RESTORING until you fix the cause and run the script again.

Note: the earlier script on this page read file names with INSERT ... EXEC into a fixed table of RESTORE FILELISTONLY columns. That table no longer matches: on SQL Server 2025 it stopped with Msg 213 ... Column name or number of supplied values does not match table definition. The script above reads msdb instead.

Related: SQL Server Commands: sqlcmd and T-SQL Basics with Examples (MS SQL) and How to Reset the SQL Server sa Password (Windows and Linux). To run SQL Server you need full administrator access, for example on a Windows VPS.

Official documentation: RESTORE statements, backupset and SQL Server Agent on Linux.