Introduction

Restoring the latest backups of Production databases on a various test servers is one of the frequent activity I need to perform on a regular basis, various application development teams often requests me to restore latest backup on new or existing test or quality servers, many a times I receive this kind of requests in the eleventh hour when I am packing up to go back home.

Problem Definition

If I have to restore latest backups I needed to find fully qualified backup file path, then I need to find its logical names then data & log file locations all these activities need to be carried out manually. 

Solution

I needed a better  process which will do the same thing with minimal intervention.

Here’s the fully automated T-SQL script, this script you can schedule on the server on which you wish to restore databases, this script queries remote server��s(where databases backed up)   msdb database,  backupset table is used to find latest full backup dates and backupmediafamily table to locate fully qualified backup file path.

Query accepts 5 following parameters.

  1. Linked Server – SQL Server from where you have taken backups and need to be restored.
  2. DataFileLocation – Optional, Specifies the folder for database data files. If not specified, data files are restored to the default data file location.
  3. LogFileLocation – Optional, Specifies the folder for all database log files. If not specified, log files are restored to the default log file location.
  4. ExcludeDbs – Can mention Databases which you want to exclude from restore list, by default master,mode,msdb will be excluded from restore list.
  5. RestoreDbs – Optional, by default restore all databases other than those mentioned in ExcludeDbs parameter, can mention any particular database needs to be restored.
T-SQL Script
SET NOCOUNT ON
DECLARE
        @LinkedServer Varchar(255) = '' ,
--SQL Server from where you have taken backups and need to be restored.
        @DataFileLocation Varchar(255) = '' ,
-- Optional, Specifies the folder for database data files. If not specified, data files are restored to the default data file location
        @LogFileLocation Varchar(255) = '' ,
-- Optional, Specifies the folder for all database log files. If not specified, log files are restored to the default log file location
        @ExcludeDbs Varchar(255) =  '(''master'' , ''model'' , ''msdb'')' ,
-- Databases do not wish to restore
        @RestoreDbs Varchar(255) = ''   
 --  Optional, by default restores all databases other than those mentioned in ExcludeDbs list, can mention any particular database needs to be restored like '(''Database1'', ''Database2'')'
          
DECLARE @dbname Varchar(100) ,
        @physicalpath Varchar(500) ,
        @BackupDate Date ,
        @cmd nvarchar(max) ,
        @logicalname Varchar(255) ,
        @PhysicalFileName Varchar(max) ,
        @type Varchar(5)
          
--Checks linked server exists, if not then linked server is added, requires ALTER ANY LINKED SERVER permission.   
IF NOT EXISTS (SELECT * FROM SYS.servers Where name = @LinkedServer)
        EXEC sp_addlinkedserver @LinkedServer             
  
-- If data file location is not specified then data files will be restored to default data file location.     
IF @DataFileLocation IS NULL
    SELECT @DataFileLocation = SUBSTRING(physical_name, 1,CHARINDEX(N'master.mdf',LOWER(physical_name)) - 2)  FROM master.sys.master_files WHERE database_id = 1 AND FILE_ID = 1
-- If log file location is not specified then log files will be restored to default log file location.    
IF @LogFileLocation IS NULL
    SELECT @LogFileLocation =  SUBSTRING(physical_name, 1,CHARINDEX(N'mastlog.ldf',LOWER(physical_name)) - 2)  FROM master.sys.master_files WHERE database_id = 1 AND FILE_ID = 2
  
CREATE TABLE #filelist (
   LogicalName VARCHAR(255),
   PhysicalName VARCHAR(500),
   [Type] VARCHAR(1),
   FileGroupName VARCHAR(64),
   Size DECIMAL(20, 0),
   MaxSize DECIMAL(25,0),
   FileID bigint,
   CreateLSN DECIMAL(25,0),
   DropLSN DECIMAL(25,0),
   UniqueID UNIQUEIDENTIFIER,
   ReadOnlyLSN DECIMAL(25,0),
   ReadWriteLSN DECIMAL(25,0),
   BackupSizeInBytes DECIMAL(25,0),
   SourceBlockSize INT,
   filegroupid INT,
   loggroupguid UNIQUEIDENTIFIER,
   differentialbaseLSN DECIMAL(25,0),
   differentialbaseGUID UNIQUEIDENTIFIER,
   isreadonly BIT,
   ispresent BIT ,
   TDEThumbprint Varchar(255))
  
  
--Queries backupset and backupmediafamily tables on remote msdb database to get latest full backup.
SET @cmd =   'DECLARE restore_db Cursor For SELECT a.database_name , BackupDate , physical_device_name FROM ['+ @LinkedServer+'].msdb.dbo.backupset A ' +
             ' INNER JOIN (SELECT database_name , BackupDate = MAX(backup_finish_date) '
             ' FROM ['+@LinkedServer+'].msdb.dbo.backupset ' +
             ' WHERE type = ''D'' '
IF @RestoreDbs IS NULL
            SET @cmd = @cmd + ' AND database_name NOT IN '+ @ExcludeDbs  +' And backup_finish_date >= DATEADD(MONTH , -1 , GETDATE()) '
ELSE          
            SET @cmd = @cmd + ' AND database_name  IN '+ @RestoreDbs
            SET @cmd = @cmd + ' GROUP BY database_name  ) as b ' +
             ' ON A.database_name = b.database_name and a.backup_finish_date = BackupDate ' +
             ' INNER JOIN ['+ @LinkedServer +'].msdb.dbo.backupmediafamily c ON c.media_set_id = a.media_set_id ORDER BY database_name '
  
  
exec sp_executesql @cmd
  
OPEN restore_db 
FETCH NEXT FROM restore_db INTO @dbname , @BackupDate ,  @physicalpath  
WHILE @@FETCH_STATUS = 0 
BEGIN 
    --Check database to be restored is already there in this server, if yes then just restore with replace.
    IF EXISTS (SELECT * FROM sys.databases WHERE name = @dbname)
        BEGIN
            --Get rid of any existing connections, so that our restore process go smoothly.
            DECLARE @kill varchar(8000) = '';
            SELECT @kill=@kill+'kill '+convert(varchar(5),spid)+';'
            FROm master.dbo.sysprocesses
            WHERE dbid=db_id(''+ @dbname + '');
            IF len(@kill) <> 0
              exec sp_executesql @kill;
              
            SET @cmd =  'RESTORE DATABASE [' + @dbname +'] FROM DISK = '''+ @physicalpath +''' WITH STATS = 1 , REPLACE '
            Exec sp_executesql @cmd;
              
        END
    ELSE
        BEGIN
            -- If database is not already there then go through the filelist and move to appropriate locations.
            SET @cmd = 'RESTORE FILELISTONLY FROM  DISK= '''+ @physicalpath +''''
            INSERT INTO #filelist
            EXEC (@cmd)
              
            SET @cmd =  'RESTORE DATABASE ['+ @dbname +']  FROM DISK = '''+ @physicalpath +''' WITH STATS = 1 ,   '
              
            DECLARE file_list cursor for
            SELECT LogicalName, PhysicalName, Type FROM #filelist ORDER BY type
            OPEN file_list
            FETCH NEXT FROM  file_list into @LogicalName, @PhysicalFileName, @type
            WHILE @@fetch_status = 0
                BEGIN
                    -- If it is data file move to data file location.
                    IF @type = 'D'
                        SET @cmd = @cmd + ' MOVE ''' + @LogicalName + '''' + ' TO ''' + @DataFileLocation  +'\'+   Substring(@PhysicalFileName , LEN(@PhysicalFileName)-CHARINDEX('\' , REVERSE(@PhysicalFileName))+2 , CHARINDEX('\' , REVERSE(@PhysicalFileName))) + ''','
                    ELSE
                    -- Log files move to log file location.
                        SET @cmd = @cmd + ' MOVE ''' + @LogicalName + '''' + ' TO ''' + @LogFileLocation  + '\'+  Substring(@PhysicalFileName , LEN(@PhysicalFileName)-CHARINDEX('\' , REVERSE(@PhysicalFileName))+2 , CHARINDEX('\' , REVERSE(@PhysicalFileName))) + ''''
              
            FETCH NEXT FROM  file_list into @LogicalName, @PhysicalFileName, @type    
            END
            CLOSE file_list 
            DEALLOCATE file_list
            truncate table #filelist
            Exec sp_executesql @cmd
        END
      
FETCH NEXT FROM restore_db INTO @dbname , @BackupDate , @physicalpath  
END 
CLOSE restore_db 
DEALLOCATE restore_db
  
drop table #filelist


Permissions
You need to have below mentioned permissions in place to execute this script.

Requires ALTER ANY LINKED SERVER permission refer http://technet.microsoft.com/en-us/library/ms190479.aspx

If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadminand dbcreator fixed server roles and the owner (dbo) of the database refer http://technet.microsoft.com/en-us/library/ms186858.aspx.

Beginning in SQL Server 2008, obtaining information about a backup set or backup device requires CREATE DATABASE permission refer http://msdn.microsoft.com/en-us/library/ms173778.aspx

Conclusion

I have demonstrated here how we can dynamically restore production database backups on different servers, we can enhance this script to accommodate differential backups & log backups. 

References

http://technet.microsoft.com/en-us/library/ms186858(v=sql.105).aspx
sp_addlinkedserver (Transact-SQL)
RESTORE FILELISTONLY (Transact-SQL)
 

Download

Download this T-SQL script from gallery.


This article participates in the TechNet Guru Contributions for March