Fully automated SQL Server Restore

Question

I'm not very fluent with SQL Server commands.

I need a script to restore a database from a .bak file and move the logical_data and logical_log files to a specific path.

I can do:

restore filelistonly from disk='D:\backups\my_backup.bak'

This will give me a result set with a column LogicalName, next I need to use the logical names from the result set in the restore command:

restore database my_db_name from disk='d:\backups\my_backups.bak' with file=1,
move 'logical_data_file' to 'd:\data\mydb.mdf',
move 'logical_log_file' to 'd:\data\mylog.ldf'

How do I capture the logical names from the first result set into variables that can be supplied to the "move" command?

I think the solution might be trivial, but I'm pretty new to SQL Server.

Answer 1

Here's the fully automated restore T-SQL stored proc. Accepts three(3) parameters.

1. Target Database
2. Source Database
3. Fully Qualified backup file name location
   (\\yourserver\yourshare\backupfile.bak or simply c:\backup.bak)

CREATE PROC [dbo].[restoreDB]
    @p_strDBNameTo SYSNAME,
    @p_strDBNameFrom SYSNAME,
    @p_strFQNRestoreFileName VARCHAR(255)
AS 
    DECLARE 
        @v_strDBFilename VARCHAR(100),
        @v_strDBLogFilename VARCHAR(100),
        @v_strDBDataFile VARCHAR(100),
        @v_strDBLogFile VARCHAR(100),
        @v_strExecSQL NVARCHAR(1000),
        @v_strExecSQL1 NVARCHAR(1000),
        @v_strMoveSQL NVARCHAR(4000),
        @v_strREPLACE NVARCHAR(50),
        @v_strTEMP NVARCHAR(1000),
        @v_strListSQL NVARCHAR(4000),
        @v_strServerVersion NVARCHAR(20)

    SET @v_strREPLACE = ''   
    IF exists (select name from sys.databases where name = @p_strDBNameTo)
        SET @v_strREPLACE = ', REPLACE'

    SET @v_strListSQL = ''
    SET @v_strListSQL = @v_strListSQL + 'IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''##FILE_LIST''))'
    SET @v_strListSQL = @v_strListSQL + 'BEGIN'
    SET @v_strListSQL = @v_strListSQL + '   DROP TABLE ##FILE_LIST '
    SET @v_strListSQL = @v_strListSQL + 'END '

    SET @v_strListSQL = @v_strListSQL + 'CREATE TABLE ##FILE_LIST ('
    SET @v_strListSQL = @v_strListSQL + '   LogicalName VARCHAR(64),'
    SET @v_strListSQL = @v_strListSQL + '   PhysicalName VARCHAR(130),'
    SET @v_strListSQL = @v_strListSQL + '   [Type] VARCHAR(1),'
    SET @v_strListSQL = @v_strListSQL + '   FileGroupName VARCHAR(64),'
    SET @v_strListSQL = @v_strListSQL + '   Size DECIMAL(20, 0),'
    SET @v_strListSQL = @v_strListSQL + '   MaxSize DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '   FileID bigint,'
    SET @v_strListSQL = @v_strListSQL + '   CreateLSN DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '   DropLSN DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '   UniqueID UNIQUEIDENTIFIER,'
    SET @v_strListSQL = @v_strListSQL + '   ReadOnlyLSN DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '   ReadWriteLSN DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '   BackupSizeInBytes DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '   SourceBlockSize INT,'
    SET @v_strListSQL = @v_strListSQL + '   filegroupid INT,'
    SET @v_strListSQL = @v_strListSQL + '   loggroupguid UNIQUEIDENTIFIER,'
    SET @v_strListSQL = @v_strListSQL + '   differentialbaseLSN DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '   differentialbaseGUID UNIQUEIDENTIFIER,'
    SET @v_strListSQL = @v_strListSQL + '   isreadonly BIT,'
    SET @v_strListSQL = @v_strListSQL + '   ispresent BIT'

    SELECT @v_strServerVersion = CAST(SERVERPROPERTY ('PRODUCTVERSION') AS NVARCHAR)

    IF @v_strServerVersion LIKE '10.%' 
        BEGIN
            SET @v_strListSQL = @v_strListSQL + ', TDEThumbpr DECIMAL'
            --PRINT @v_strServerVersion
        END

    SET @v_strListSQL = @v_strListSQL + ')'

    EXEC (@v_strListSQL)


    INSERT INTO ##FILE_LIST EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @p_strFQNRestoreFileName + '''')

    DECLARE curFileLIst CURSOR FOR 
        SELECT 'MOVE N''' + LogicalName + ''' TO N''' + replace(PhysicalName, @p_strDBNameFrom, @p_strDBNameTo) + ''''
          FROM ##FILE_LIST

    SET @v_strMoveSQL = ''

    OPEN curFileList 
    FETCH NEXT FROM curFileList into @v_strTEMP
    WHILE @@Fetch_Status = 0
    BEGIN
        SET @v_strMoveSQL = @v_strMoveSQL + @v_strTEMP + ', '
        FETCH NEXT FROM curFileList into @v_strTEMP
    END

    CLOSE curFileList
    DEALLOCATE curFileList

    PRINT 'Killing active connections to the "' + @p_strDBNameTo + '" database'

    -- Create the sql to kill the active database connections
    SET @v_strExecSQL = ''
    SELECT   @v_strExecSQL = @v_strExecSQL + 'kill ' + CONVERT(CHAR(10), spid) + ' '
    FROM     master.dbo.sysprocesses
    WHERE    DB_NAME(dbid) = @p_strDBNameTo AND DBID <> 0 AND spid <> @@spid

    EXEC (@v_strExecSQL)

    PRINT 'Restoring "' + @p_strDBNameTo + '" database from "' + @p_strFQNRestoreFileName + '" with '
    PRINT '  data file "' + @v_strDBDataFile + '" located at "' + @v_strDBFilename + '"'
    PRINT '  log file "' + @v_strDBLogFile + '" located at "' + @v_strDBLogFilename + '"'

    SET @v_strExecSQL = 'RESTORE DATABASE [' + @p_strDBNameTo + ']'
    SET @v_strExecSQL = @v_strExecSQL + ' FROM DISK = ''' + @p_strFQNRestoreFileName + ''''
    SET @v_strExecSQL = @v_strExecSQL + ' WITH FILE = 1,'
    SET @v_strExecSQL = @v_strExecSQL + @v_strMoveSQL
    SET @v_strExecSQL = @v_strExecSQL + ' NOREWIND, '
    SET @v_strExecSQL = @v_strExecSQL + ' NOUNLOAD '
    SET @v_strExecSQL = @v_strExecSQL + @v_strREPLACE


    --PRINT '---------------------------'
    --PRINT @v_strExecSQL
    --PRINT '---------------------------'


    EXEC sp_executesql @v_strExecSQL

Answer 2

RESTORE FILELISTONLY produces a result set that is documented in the MSDN. You then need to iterate this result set and build an appropriate RESTORE ... MOVE... How you capture and iterate the result set depends on your environment. In a C# application you would use a SqlDataReader. In pure T-SQL you would use INSERT ... EXEC.

The skeleton of a pure SQL solution would be:

declare @filelist table (LogicalName nvarchar(128), PhysicalName nvarchar(260), Type char(1), FilegroupName varchar(10), size int, MaxSize bigint, field int, createlsn bit, droplsn bit, uniqueid uniqueidentifier, readonlylsn bit, readwritelsn bit, backupsizeinbytes bigint, sourceblocksize int, filegroupid int, loggroupguid uniqueidentifier, differentialbaselsn bit, differentialbaseguid uniqueidentifier, isreadonly bit, ispresent bit, tdethumbprint varchar(5));
insert into @filelist exec sp_executesql N'restore filelistonly from disk=''D:\backups\my_backup.bak''';

set @sql = N'RESTORE database my_database from disk ''D:\backups\my_backup.bak'' with ';
select @sql = @sql + N' move ' + LogicalName + N' to ' udf_localFilePath(PhysicalName) + N','
from @filelist;

set @sql = substring(@sql, 1, len(@sql)-1); -- remove last ','
exec sp_executesql @sql;

This is not actual working code, but just to get you the idea. You can also use a cursor instead of the non-standard assignment-inside-query construction of @sql

Be aware that the list of columns in the result set of RESTORE FILELISTONLY differs between SQL Server versions. Refer to the target version specifications for the correct list.

Answer 3

Using

• http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp
• http://weblogs.sqlteam.com/dang/archive/2009/06/13/Restore-Database-Stored-Procedure.aspx

as references, I came up with this .. and I think it works (not tested for backups with multiple files)

DECLARE @FileList TABLE
      (
      LogicalName nvarchar(128) NOT NULL,
      PhysicalName nvarchar(260) NOT NULL,
      Type char(1) NOT NULL,
      FileGroupName nvarchar(120) NULL,
      Size numeric(20, 0) NOT NULL,
      MaxSize numeric(20, 0) NOT NULL,
      FileID bigint NULL,
      CreateLSN numeric(25,0) NULL,
      DropLSN numeric(25,0) NULL,
      UniqueID uniqueidentifier NULL,
      ReadOnlyLSN numeric(25,0) NULL ,
      ReadWriteLSN numeric(25,0) NULL,
      BackupSizeInBytes bigint NULL,
      SourceBlockSize int NULL,
      FileGroupID int NULL,
      LogGroupGUID uniqueidentifier NULL,
      DifferentialBaseLSN numeric(25,0)NULL,
      DifferentialBaseGUID uniqueidentifier NULL,
      IsReadOnly bit NULL,
      IsPresent bit NULL,
      TDEThumbprint varbinary(32) NULL
 );

 declare @RestoreStatement nvarchar(max), @BackupFile nvarchar(max);

 set @BackupFile = 'D:\mybackup.bak'

 SET @RestoreStatement =  N'RESTORE FILELISTONLY
      FROM DISK=N''' + @BackupFile + ''''

INSERT INTO @FileList
      EXEC(@RestoreStatement);

declare @logical_data nvarchar(max), @logical_log nvarchar(max);

set @logical_data = (select LogicalName from @FileList where Type = 'D' and FileID = 1)
set @logical_log = (select LogicalName from @FileList where Type = 'L' and FileID = 2)