2

I would like to write an automation script that restores a SQL Server DB from its backup file. However, doing this is not a straightforward procedure in SQL because the main query needs additional inputs which can actually be obtained using another query. Can I do this in one query?

There is a question already on SO; but the solution is not very flexible. RESTORE FILELISTONLY's definition changes very frequently. Even otherwise, the solution seems very verbose.

Isn't there a simpler way to store the results of a query into a variable and use them? This is a cakewalk in any programming language.

Get the Logical Name:

RESTORE FILELISTONLY
FROM DISK = 'D:SourceBackUpFile.bak'
GO

Restore the DB:

RESTORE DATABASE DBName
FROM DISK = 'D:SourceBackUpFile.bak'
WITH RECOVERY
MOVE 'SourceMDFLogicalName' TO 'D:TargetMDFFile.mdf',
MOVE 'SourceLDFLogicalName' TO 'D:TargetLDFFile.ldf'
2

I wrote a blog post at SQLServerScience.com that shows how to obtain the details you're after, and is compatible with all versions of SQL Server since 2008+

This is the main code from that blog post:

/*
    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'&#x0D;', 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

The generated RESTORE DATABASE command looks like:

RESTORE DATABASE MyDB
FROM DISK = N'D:\SQLServer\backups\MyDB.bak' 
WITH REPLACE 
    , RECOVERY
    , STATS = 5
    , MOVE N'PRIMARY' TO N'C:\Database\Data\MyDB\system\PRIMARY'
    , MOVE N'LOG'     TO N'C:\Database\Log\MyDB\Log\LOG';
GO

This code has also been tested on the Linux version of SQL Server 2017.

You asked:

Isn't there a simpler way to store the results of a query into a variable and use them? This is a cakewalk in any programming language.

The requirement here is not adding a value to a variable. We need to extract the contents of a set of disparate data into a table. It might be conceptually similar to loading an array from an object. However, in SQL Server the only way you can store the results of command output like the RESTORE HEADERONLY command, is to first insert it into a table, then grab the specific values from the desired table.

3

Taken from this SO question. This seems to work for all versions up to and including 2019.

Just to note, the definition has changed twice since SQL 2000 - I don't judge 2 changes in 19 years as a frequent change personally. In that sense I wouldn't worry about using RESTORE FILELISTONLY as long as you use a generic script like the one below.

CREATE TABLE #FileListHeaders (     
     LogicalName    nvarchar(128)
    ,PhysicalName   nvarchar(260)
    ,[Type] char(1)
    ,FileGroupName  nvarchar(128) NULL
    ,Size   numeric(20,0)
    ,MaxSize    numeric(20,0)
    ,FileID bigint
    ,CreateLSN  numeric(25,0)
    ,DropLSN    numeric(25,0) NULL
    ,UniqueID   uniqueidentifier
    ,ReadOnlyLSN    numeric(25,0) NULL
    ,ReadWriteLSN   numeric(25,0) NULL
    ,BackupSizeInBytes  bigint
    ,SourceBlockSize    int
    ,FileGroupID    int
    ,LogGroupGUID   uniqueidentifier NULL
    ,DifferentialBaseLSN    numeric(25,0) NULL
    ,DifferentialBaseGUID   uniqueidentifier NULL
    ,IsReadOnly bit
    ,IsPresent  bit
)
IF cast(cast(SERVERPROPERTY('ProductVersion') as char(4)) as float) > 9 -- Greater than SQL 2005 
BEGIN
    ALTER TABLE #FileListHeaders ADD TDEThumbprint  varbinary(32) NULL
END
IF cast(cast(SERVERPROPERTY('ProductVersion') as char(2)) as float) > 12 -- Greater than 2014
BEGIN
    ALTER TABLE #FileListHeaders ADD SnapshotURL    nvarchar(360) NULL
END
INSERT INTO #FileListHeaders
EXEC ('RESTORE FILELISTONLY FROM DISK = N''BackupFileName.bak''')

SELECT * FROM #FileListHeaders

DROP TABLE #FileListHeaders
  • But can't we do this in a simpler/generic way? All we need is two variables - used in another query! – Nishant Apr 17 at 7:10
0

Why not use dbatools or sp_restoregene which are available free?

They both can do what you are after.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.