Generate Restore Database Commands
Restoring a database backup from one server to another, or from one database to another on the same machine, often requires MOVE ... TO ... commands. For databases with a lot of files, this can be tedious. Use the script below to automatically generate restore database commands with correctly generated MOVE clauses for all files included in the backup.
“Please, no more generating database
restore statements by hand!”
Just point the script at an existing SQL Server Backup File, and give the new database a name, along with a target folder for the data and log files, and press F5. This script is compatible with SQL Server 2005 and higher, and has been tested on a case-sensitive-collation server.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 |
/* This script will generate a "RESTORE DATABASE" command with the correct "MOVE" clause, etc. By: Max Vernon */ SET NOCOUNT ON; DECLARE @FileListCmd nvarchar(max); DECLARE @RestoreCmd nvarchar(max); DECLARE @cmd nvarchar(max); DECLARE @BackupFile nvarchar(max); DECLARE @DBName sysname; DECLARE @DataPath nvarchar(260); DECLARE @LogPath nvarchar(260); DECLARE @Version decimal(10,2); DECLARE @MaxLogicalNameLength int; DECLARE @MoveFiles nvarchar(max); SET @BackupFile = N'D:\SQLServer\MyDatabaseBackup.bak'; --source backup file SET @DBName = N'MyDB'; --target database name SET @DataPath = N'C:\Database\Data'; --target data path SET @LogPath = N'C:\Database\Log'; --target log path /* ************************************ modify nothing below this point. ************************************ */ IF RIGHT(@DataPath, 1) <> '\' SET @DataPath = @DataPath + N'\'; IF RIGHT(@LogPath, 1) <> '\' SET @LogPath = @LogPath + N'\'; SET @cmd = N''; SET @Version = CONVERT(decimal(10,2), CONVERT(varchar(10), SERVERPROPERTY('ProductMajorVersion')) + '.' + CONVERT(varchar(10), SERVERPROPERTY('ProductMinorVersion')) ); IF @Version IS NULL --use ProductVersion instead BEGIN DECLARE @sv varchar(10); SET @sv = CONVERT(varchar(10), SERVERPROPERTY('ProductVersion')); SET @Version = CONVERT(decimal(10,2), LEFT(@sv, CHARINDEX(N'.', @sv) + 1)); END IF OBJECT_ID(N'tempdb..#FileList', N'U') IS NOT NULL BEGIN DROP TABLE #FileList; END CREATE TABLE #FileList ( LogicalName sysname NOT NULL , PhysicalName varchar(255) NOT NULL , [Type] char(1) NOT NULL , FileGroupName sysname NULL , Size numeric(20,0) NOT NULL , MaxSize numeric(20,0) NOT NULL , FileId bigint NOT NULL , CreateLSN numeric(25,0) NOT NULL , DropLSN numeric(25,0) NULL , UniqueId uniqueidentifier NOT NULL , ReadOnlyLSN numeric(25,0) NULL , ReadWriteLSN numeric(25,0) NULL , BackupSizeInBytes bigint NOT NULL , SourceBlockSize int NOT NULL , FileGroupId int NULL , LogGroupGUID uniqueidentifier NULL , DifferentialBaseLSN numeric(25,0) NULL , DifferentialBaseGUID uniqueidentifier NOT NULL , IsReadOnly bit NOT NULL , IsPresent bit NOT NULL ); IF @Version >= 10.5 ALTER TABLE #FileList ADD TDEThumbprint varbinary(32) NULL; IF @Version >= 12 ALTER TABLE #FileList ADD SnapshotURL nvarchar(360) NULL; SET @FileListCmd = N'RESTORE FILELISTONLY FROM DISK = N''' + @BackupFile + N''';'; INSERT INTO #FileList EXEC (@FileListCmd); SET @MaxLogicalNameLength = COALESCE((SELECT MAX(LEN(fl.LogicalName)) FROM #FileList fl), 0); SELECT @MoveFiles = (SELECT N', MOVE N''' + fl.LogicalName + N''' ' + REPLICATE(N' ', @MaxLogicalNameLength - LEN(fl.LogicalName)) + N'TO N''' + CASE WHEN fl.Type = 'L' THEN @LogPath ELSE @DataPath END + @DBName + N'\' + CASE WHEN fl.FileGroupName = N'PRIMARY' THEN N'System' WHEN fl.FileGroupName IS NULL THEN N'Log' ELSE fl.FileGroupName END + N'\' + fl.LogicalName + CASE WHEN fl.Type = 'L' THEN N'.log' ELSE CASE WHEN fl.FileGroupName = N'PRIMARY' THEN N'.mdf' ELSE N'.ndf' END END + N''' ' FROM #FileList fl FOR XML PATH('')); SET @MoveFiles = REPLACE(@MoveFiles, N'
', N''); SET @MoveFiles = REPLACE(@MoveFiles, char(10), char(13) + char(10)); SET @MoveFiles = LEFT(@MoveFiles, LEN(@MoveFiles) - 2); SET @RestoreCmd = N'RESTORE DATABASE ' + @DBName + N' FROM DISK = N''' + @BackupFile + N''' WITH REPLACE , RECOVERY , STATS = 5 ' + @MoveFiles + N'; GO;'; IF LEN(@RestoreCmd) > 4000 BEGIN DECLARE @CurrentLen int; SET @CurrentLen = 1; WHILE @CurrentLen <= LEN(@RestoreCmd) BEGIN PRINT SUBSTRING(@RestoreCmd, @CurrentLen, 4000); SET @CurrentLen = @CurrentLen + 4000; END RAISERROR (N'Output is chunked into 4,000 char pieces - look for errant line endings!', 14, 1); END ELSE BEGIN PRINT @RestoreCmd; END |
That code will generate a statement similar to this:
|
1 2 3 4 5 6 7 8 9 |
RESTORE DATABASE MyDB FROM DISK = N'D:\SQLServer\MyDatabaseBackup.bak' WITH REPLACE , RECOVERY , STATS = 5 , MOVE N'MyDatabase_system_01' TO N'C:\Database\Data\MyDB\system\MyDatabase_system_01' , MOVE N'MyDatabase_fgdata01_01' TO N'C:\Database\Data\MyDB\fgdata01\MyDatabase_fgdata01_01' , MOVE N'MyDatabase_log_01' TO N'C:\Database\Log\MyDB\Log\MyDatabase_log_01'; GO; |
Have fun using this to generate restore database commands!
This post is part of our series on Database Recovery. Check out the rest of our handy SQL Server Tools!
Click here for Microsoft documentation for the RESTORE DATABASE command.
[…] Hannah Vernon helps us out with a query to generate a database restore command: […]
[…] Log is one of the most important aspects of database management. The first being always take-and-test your backups! If you don’t have a backup, nothing else matters. However, once you’ve […]