Archive
Generate ALTER Statements for Database Moves
Last week, I posted Identifying Corrupt Rows USING DBCC CHECKDB and DBCC PAGE. I wrote the scripts in that post to identify corrupt rows in a client's database. As we continued to investigate, things got strange. Repeated runs of DBCC CHECKDB were generating different results. Each one showed serious corruption, but on different pages with each run. In an effort to remove the storage system from the list of potential causes, I needed to move to data and log files for the database to a a different storage array. This post from TechNet gave me the steps I needed to run.
In this case, only one database had to be moved. To move more than one database, I wrote the following script to generate the statements specified in the TechNet article. In addition to the ALTER DATABASE statements, the script checks to see if the database is part of an availability group. If it is, a statement is generated to remove it. As adding a database to an availability group is far more complicated than removing it, I will leave that to the user.
As usual, I hope you find this useful. And remember, executing code you find on the internet against your production instance always has the potential to become a resume updating event. Use caution and test.
/*--------------------------------------------------------------- Created By - Frank Gill Created On - 2014-10-13 Script to generate ALTER DATABASE statement to move a database to a different storage location ---------------------------------------------------------------*/ USE master; SET NOCOUNT ON; /* Drop temp table if it exists */ IF OBJECT_ID('tempdb..#dbfiles') IS NOT NULL BEGIN DROP TABLE #dbfiles; END /* Declare local variables and create temp table */ DECLARE @dbname SYSNAME; DECLARE @agname SYSNAME; DECLARE @sqlstr NVARCHAR(4000); DECLARE @physicalname NVARCHAR(260); DECLARE @logicalname SYSNAME; DECLARE @filetype TINYINT; DECLARE @datapath NVARCHAR(1000); DECLARE @logpath NVARCHAR(1000); DECLARE @agdb BIT = 0; DECLARE @loopcount INT = 1; DECLARE @looplimit INT; CREATE TABLE #dbfiles (logicalname SYSNAME ,physicalname NVARCHAR(260) ,filetype TINYINT) /* Initialize variables */ SET @dbname = 'Your_Database'; SET @datapath = 'Your_Data_Path'; SET @logpath = 'Your_Log_Path'; /* If the database is part of an availability group, generate a statement to remove it prior to taking the database offline */ IF(SELECT replica_ID FROM sys.databases WHERE name = @dbname) IS NOT NULL BEGIN PRINT '/* This database is part of an availability group and must be removed prior to taking the database offline You will need to add the database back to the availability group after the database files are moved */' SELECT @agname = g.name FROM sys.databases d INNER JOIN sys.availability_replicas r ON d.replica_id = r.replica_id INNER JOIN sys.availability_groups g ON g.group_id = r.group_id WHERE d.name = @dbname; SET @sqlstr = 'ALTER AVAILABILITY GROUP [' + @agname + '] REMOVE DATABASE [' + @dbname + '];'; PRINT @sqlstr; SET @agdb = 1; END /* Generate a statement to set the database offline */ SET @sqlstr = 'ALTER DATABASE ' + @dbname + ' SET OFFLINE WITH ROLLBACK IMMEDIATE'; PRINT @sqlstr; /* Get a list of database files for your database and insert them into the temp table for use in the loop */ SET @sqlstr = 'INSERT INTO #dbfiles SELECT name, physical_name, type FROM ' + @dbname + '.sys.database_files;' EXEC sp_executesql @sqlstr; /* Set the loop limit to the number of files returned from the insert statement */ SET @looplimit = @@ROWCOUNT; WHILE @loopcount <= @looplimit BEGIN /* Get the first set of physical and logical file name and generate an ALTER DATABASE statement using the file type to determine the new physical location */ SELECT TOP 1 @logicalname = logicalname, @physicalname = REVERSE(SUBSTRING(REVERSE(physicalname),1,(CHARINDEX('\',REVERSE(physicalname))-1))), @filetype = filetype FROM #dbfiles; IF @filetype = 0 BEGIN SET @sqlstr = 'ALTER DATABASE ' + @dbname + ' MODIFY FILE (NAME = ' + @logicalname + ' ,FILENAME = ''' + @datapath + @physicalname + '''' + ');'; END ELSE IF @filetype = 1 BEGIN SET @sqlstr = 'ALTER DATABASE ' + @dbname + ' MODIFY FILE (NAME = ' + @logicalname + ' ,FILENAME = ''' + @logpath + @physicalname + '''' + ');'; END PRINT @sqlstr; SET @loopcount += 1; DELETE TOP (1) FROM #dbfiles END /* Generate a statement to bring the database back online */ SET @sqlstr = 'ALTER DATABASE ' + @dbname + ' SET ONLINE'; PRINT @sqlstr; IF @agdb = 1 BEGIN PRINT '/* Reminder! ' + @dbname + 'is part of an availability group You will need to add the database back to the availability group if necessary */' END
Identifying Corrupt Rows Using DBCC CHECKDB and DBCC PAGE
Four months ago, when I blogged about giving all the presentations, things were starting to ramp up pat my new job. I've really enjoyed the last few months, but the combination of my presenting schedule and the responsibilities of the new gig has left little time for blogging.
This week, I ran into a corruption issue that gave me a chance to make a deeper dive into internals than I have in a while. It also gave me the opportunity to develop a script to dump the results of DBCC PAGE to a table.
This week, we discovered corruption in a client's database we were migrating. Digging deeper into the results of DBCC CHECKDB, I found that six objects in the database were corrupted badly enough to require data loss. One of those objects contained 47 irreparable pages.
In an effort to provide the client as much information as possible about the corrupted data, I set out to identify the rows contained on the corrupt pages. Here are the scripts I used.
First, I needed to create tables to hold the results of DBCC CHECKDB and DBCC PAGE. For the PageResults table, I guessed at the column definitions. For DBCCResults, I made all of the numeric columns BIGINT to prevent conversion errors.
USE [Your_Database] GO /* Create table to hold the results of DBCC CHECKDB */ /****** Object: Table [dbo].[DBCCResults] Script Date: 9/30/2014 11:00:47 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO IF OBJECT_ID('Your_Database..DBCCResults') IS NOT NULL BEGIN DROP TABLE DBCCResults; END IF OBJECT_ID('Your_Database..PageResults') IS NOT NULL BEGIN DROP TABLE PageResults; END CREATE TABLE PageResults (ParentObject VARCHAR(100) ,[Object] VARCHAR(1000) ,[Field] VARCHAR(100) ,[VALUE] VARCHAR(1000)) CREATE TABLE [dbo].[DBCCResults]( [Error] [bigint] NULL, [Level] [bigint] NULL, [State] [bigint] NULL, [MessageText] [varchar](7000) NULL, [RepairLevel] [varchar](7000) NULL, [Status] [bigint] NULL, [DbId] [bigint] NULL, [DbFragID] [bigint] NULL, [ObjId] [bigint] NULL, [IndId] [bigint] NULL, [PartID] [bigint] NULL, [AllocID] [bigint] NULL, [RidDbid] [bigint] NULL, [RidPruid] [bigint] NULL, [File] [bigint] NULL, [Page] [bigint] NULL, [Slot] [bigint] NULL, [RefDbid] [bigint] NULL, [RefPruId] [bigint] NULL, [RefFile] [bigint] NULL, [RefPage] [bigint] NULL, [RefSlot] [bigint] NULL, [Allocation] [bigint] NULL ) ON [PRIMARY] GO
After creating the tables, I needed to generate DBCC CHECKDB statements for each database. The script below uses sys.databases as the driver, and you can modify the results with an appropriate WHERE clause. The script is set to print the DBCC CHECKDB commands. Replacing the PRINT statement with EXEC sp_executesql will execute the statement after generation. Upon execution, the results of DBCC CHECKDB will be inserted into the DBCC CHECKDB table.
USE master; GO /* Drop temp table if it exists */ IF OBJECT_ID('tempdb..#databases') IS NOT NULL BEGIN DROP TABLE #databases END /* Declare local variables and create temp tables */ DECLARE @sqlstr NVARCHAR(2000); DECLARE @dbname SYSNAME; DECLARE @loopcount TINYINT = 1; DECLARE @looplimit TINYINT; CREATE TABLE #databases (dbname SYSNAME); /* This will generate statements for all user databases Change the WHERE clause to limit or expand the results */ INSERT INTO #databases SELECT name FROM master.sys.databases WHERE name > 4; /* Get the loop limit */ SELECT @looplimit = @@ROWCOUNT; /* Build the DBCC CHECKDB statement for each database This code will print each statement Replace the PRINT statement with EXEC sp_executesql @sql str to execute the code */ WHILE @loopcount <= @looplimit BEGIN SELECT TOP 1 @dbname = dbname FROM #databases; SELECT @sqlstr = 'INSERT INTO Your_Database..DBCCResults EXEC(''DBCC CHECKDB(' + @dbname + ') WITH TABLERESULTS, NO_INFOMSGS'')'; PRINT @sqlstr; SELECT @loopcount += 1; DELETE FROM #databases WHERE dbname = @dbname; END
Once, the DBCCResults table was populated, I needed to dump the contents of the irreparable pages. For corrupted pages, DBCC CHECKDB returns two possible RepairLevels -- repair_rebuild indicates the page can be rebuilt to fix the corruption; repair_allow_data_loss means that the page is corrupted so badly it cannot be repaired. The only option is to delete the page and restore consistency to the object structure. This link from Paul Randal provides a much more detailed explanation.
USE Your_Database; /* Drop temp tables if they exist */ SET NOCOUNT ON; IF OBJECT_ID('tempdb..#DBCCPages') IS NOT NULL BEGIN DROP TABLE #DBCCPages; END IF OBJECT_ID('tempdb..#PageResults') IS NOT NULL BEGIN DROP TABLE #PageResults; END /* Create temp tables */ CREATE TABLE #PageResults (ParentObject VARCHAR(100) ,[Object] VARCHAR(1000) ,[Field] VARCHAR(100) ,[VALUE] VARCHAR(1000)) /* Declare local variables */ DECLARE @loopcount INT = 1; DECLARE @looplimit INT; DECLARE @sqlstr NVARCHAR(4000); DECLARE @pagenum BIGINT; /* Select information about the corrupt data This example selects rows with a RepairLevel of repair_allow_data_loss for a single object */ SELECT DISTINCT O.name, T.RepairLevel, T.IndId, T.PartID, T.Page INTO #DBCCPages FROM DBCCResults T INNER JOIN Your_Database.sys.objects O ON t.ObjId = O.object_id WHERE RepairLevel = 'repair_allow_data_loss' AND O.name = 'Your_Table' ORDER BY O.name, T.Page; /* Set the loop limit */ SET @looplimit = @@ROWCOUNT; /* Build a DBCC PAGE statement for each corrupt page and execute it Insert the results into the #PageResults temp table */ WHILE @loopcount <= @looplimit BEGIN SELECT TOP 1 @pagenum = Page FROM #DBCCPages SET @sqlstr = 'DBCC PAGE (Your_Database,1,' + CAST(@pagenum AS NVARCHAR) + ',3) WITH TABLERESULTS' INSERT INTO PageResults EXEC sp_executesql @sqlstr; SET @loopcount += 1; DELETE FROM #DBCCPages WHERE Page = @pagenum; END
Because I needed to provide the client with a list of rows affected by the corruption, I had to dump all pages identified as repair_allow_data_loss. The script below queries DBCC Results to return a list of PageIDs for the irreparable pages. It runs DBCC PAGE...WITH TABLERESULTS for each PageID and writes the results to the PageResults table. Here is a sample of the output:
You can see that the results contain a set of rows for each slot, or table row, contained on the page. I needed to take each set of those rows and pivot them into a single row. The script below does that. You will need to add a MIN(CASE statement for each row you want to pivot.
USE Your_Database; /* Select data from PageResults to return the key value for each row contained on a corrupt page */ SELECT ParentObject, MIN(CASE Field WHEN 'col1' THEN VALUE END) AS col1, MIN(CASE Field WHEN 'col2' THEN VALUE END) AS col2, MIN(CASE Field WHEN 'col3' THEN VALUE END) AS col3, MIN(CASE Field WHEN 'col4' THEN VALUE END) AS col4, MIN(CASE Field WHEN 'col5' THEN VALUE END) AS col5 INTO Your_Database..PageResults FROM #PageResults GROUP BY ParentObject ORDER BY col1, col2, col3, col4, col5;
I hope this post provides more insight into the tools and methods available for analyzing corruption and determining its extent.
Stored Procedure to Automate Restores – Mark V
I have added parameters @inphysdatapath and @inphyslogpath to the procedure. This allows alternate physical file restore paths to be passed in. You must pass in both parameters or the proc will error out.
Here is a sample execution:
USE [master] GO DECLARE @return_value int EXEC @return_value = [dbo].[sp_automateDBRestoresWithFileList] @sourcedbname = YourSourceDatabaseName, @restoredbname = YourRestoreDatabaseName,@droprestoredb = 0, @noexec = 1, @stopat = NULL, @inphysdatapath = 'C:\Restore', @inphyslogpath = 'C:\Restore' SELECT 'Return Value' = @return_value GO
If you have questions or comments about the procedure. leave them in the comments on this page.
</span> <pre> USE [master] GO /****** Object: StoredProcedure [dbo].[sp_automateDBRestoresWithFileList] Script Date: 2/16/2014 8:48:07 PM ******/ IF EXISTS(SELECT 1 FROM master.sys.objects WHERE name = 'sp_automateDBRestoresWithFileList') BEGIN DROP PROCEDURE [dbo].[sp_automateDBRestoresWithFileList] END GO USE [master] GO --If the backupfiletype data type does not exist, create it IF NOT EXISTS (SELECT 1 FROM sys.types WHERE name = 'backupfiletype') BEGIN CREATE TYPE backupfiletype AS TABLE (backupfilename VARCHAR(255) ,backupfiletype VARCHAR(4)); END /****** Object: StoredProcedure [dbo].[sp_automateDBRestoresWithFileList] Script Date: 2/16/2014 8:48:07 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].sp_automateDBRestoresWithFileList @sourcedbname SYSNAME, @restoredbname SYSNAME = NULL, @droprestoredb TINYINT = 1, @noexec TINYINT = 0, @stopat DATETIME = NULL, @TVP backupfiletype READONLY AS SET NOCOUNT ON BEGIN TRY --Drop temp tables if they exist IF OBJECT_ID('tempdb..#backupfiles') IS NOT NULL BEGIN DROP TABLE #backupfiles END IF OBJECT_ID('tempdb..#databasefiles') IS NOT NULL BEGIN DROP TABLE #databasefiles END IF EXISTS(SELECT 1 FROM tempdb.sys.objects WHERE name LIKE '%headeronly%') BEGIN DROP TABLE #headeronly END IF EXISTS(SELECT 1 FROM tempdb.sys.objects WHERE name LIKE '%headeronlyfile%') BEGIN DROP TABLE #headeronlyfile END IF EXISTS(SELECT * FROM tempdb.sys.objects WHERE name LIKE 'FileListOnly%') BEGIN DROP TABLE #FileListOnly END --Create temp tables CREATE TABLE #tvpfiles (backupfilename VARCHAR(2000) ,backupfiletype VARCHAR(4)) CREATE TABLE #backupfiles (backupstartdate DATETIME ,backupfinishdate DATETIME ,physicalfilename VARCHAR(2000) ,backuptype CHAR(4)) CREATE TABLE #databasefiles (DatabaseName SYSNAME ,LogicalName SYSNAME ,PhysicalName VARCHAR(2000) ,filetype TINYINT) CREATE TABLE #headeronly (BackupName NVARCHAR(128) ,BackupDescription NVARCHAR(255) ,BackupType SMALLINT ,ExpirationDate DATETIME ,Compressed BIT ,Position SMALLINT ,DeviceType TINYINT ,UserName NVARCHAR(128) ,ServerName NVARCHAR(128) ,DatabaseName NVARCHAR(128) ,DatabaseVersion INT ,DatabaseCreationDate DATETIME ,BackupSize NUMERIC(20,0) ,FirstLSN NUMERIC(25,0) ,LastLSN NUMERIC(25,0) ,CheckpointLSN NUMERIC(25,0) ,DatabaseBackupLSN NUMERIC(25,0) ,BackupStartDate DATETIME ,BackupFinishDate DATETIME ,SortOrder SMALLINT ,CodePage SMALLINT ,UnicodeLocaleId INT ,UnicodeComparisonStyle INT ,CompatibilityLevel TINYINT ,SoftwareVendorId INT ,SoftwareVersionMajor INT ,SoftwareVersionMinor INT ,SoftwareVersionBuild INT ,MachineName NVARCHAR(128) ,Flags INT ,BindingID UNIQUEIDENTIFIER ,RecoveryForkID UNIQUEIDENTIFIER ,Collation NVARCHAR(128) ,FamilyGUID UNIQUEIDENTIFIER ,HasBulkLoggedData BIT ,IsSnapshot BIT ,IsReadOnly BIT ,IsSingleUser BIT ,HasBackupChecksums BIT ,IsDamaged BIT ,BeginsLogChain BIT ,HasIncompleteMetaData BIT ,IsForceOffline BIT ,IsCopyOnly BIT ,FirstRecoveryForkID UNIQUEIDENTIFIER ,ForkPointLSN NUMERIC(25,0) ,RecoveryModel NVARCHAR(60) ,DifferentialBaseLSN NUMERIC(25,0) ,DifferentialBaseGUID UNIQUEIDENTIFIER ,BackupTypeDescription NVARCHAR(60) ,BackupSetGUID UNIQUEIDENTIFIER ,CompressedBackupSize BIGINT ,Containment BIT) CREATE TABLE #FileListOnly ( LogicalName NVARCHAR(128), PhysicalName NVARCHAR(260), [Type] CHAR(1), FileGroupName NVARCHAR(128), Size NUMERIC(20,0), MaxSize NUMERIC(20,0), FileID BIGINT, CreateLSN NUMERIC(25,0), DropLSN NUMERIC(25,0), UniqueID UNIQUEIDENTIFIER, ReadOnlyLSN NUMERIC(25,0), ReadWriteLSN NUMERIC(25,0), BackupSizeInBytes BIGINT, SourceBlockSize INT, FileGroupID INT, LogGroupGUID UNIQUEIDENTIFIER, DifferentialBaseLSN NUMERIC(25,0), DifferentialBaseGUID UNIQUEIDENTIFIER, IsReadOnly BIT, IsPresent BIT, TDEThumbprint VARBINARY(32) ) --Declare local variables DECLARE @filecount SMALLINT DECLARE @fullfilecount SMALLINT DECLARE @loopcount SMALLINT DECLARE @datafileloopcount SMALLINT DECLARE @backuploopcount SMALLINT DECLARE @backupfile VARCHAR(2000) DECLARE @tvpbackupfile VARCHAR(2000) DECLARE @sqlstr VARCHAR(MAX) DECLARE @backupfiletype CHAR(4) DECLARE @tvpbackupfiletype CHAR(4) DECLARE @datafilecount SMALLINT DECLARE @logfilecount SMALLINT DECLARE @logbackupcount SMALLINT DECLARE @diffbackupcount SMALLINT DECLARE @datafile VARCHAR(2000) DECLARE @moveparm VARCHAR(2000) DECLARE @fromstatement VARCHAR(2000) DECLARE @logfile SYSNAME DECLARE @physlogfile VARCHAR(2000) DECLARE @physdatafile VARCHAR(2000) DECLARE @fullbackupdate DATETIME DECLARE @logbackupdate DATETIME DECLARE @logbackuplimit DATETIME DECLARE @errormessage VARCHAR(1000) DECLARE @tvplimit SMALLINT DECLARE @tvploopcount SMALLINT --Initialize variables SET @moveparm = ' WITH' SET @fromstatement = ' FROM ' SET @loopcount = 1 SET @datafileloopcount = 1 SET @backuploopcount = 1 SET @tvploopcount = 1 --Get backup file name and file type from the table-valued parameter, if they exist INSERT INTO #tvpfiles SELECT backupfilename, backupfiletype FROM @TVP --Get the count of rows from the table-valued parameter SET @tvplimit = @@ROWCOUNT SELECT @tvplimit AS TVPLimit --Drop the restored database if it exists and @droprestoredb = 1 IF EXISTS(SELECT 1 FROM sys.databases WHERE name = @restoredbname) AND @droprestoredb = 1 BEGIN --Drop the existing database if @sourcedbname and @restoredbname are different or if they are the same, but @TVP has been passed in IF (@sourcedbname &lt;&gt; @restoredbname) OR (@sourcedbname = @restoredbname AND @tvplimit &gt; 0) BEGIN SET @sqlstr = 'DROP DATABASE ' + @restoredbname EXEC (@sqlstr) END ELSE --This condition will drop the existing database, making sys.database_files unavailable --An error is raised and the procedure stops. BEGIN RAISERROR ('You are attempting to restore over an existing database and have set @droprestoredb = 1. This will cause the existing database to be dropped, making sys.database_files unavailable. Please change the @restoredbname or set @droprestoredb = 0.', -- Message text. 16, -- Severity. 1 -- State. ); END END --If a table-valued parameter is not passed in, get file information from sys.database_files IF @tvplimit = 0 BEGIN --Build dynamic SQL to get a list of data and log files for the source database SET @sqlstr = 'INSERT INTO #databasefiles SELECT ' + '''' + @sourcedbname + '''' + ' AS DatabaseName, name, physical_name, type FROM ' + @sourcedbname + '.sys.database_files' --Execute dynamic SQL to return data and log files for the source database EXEC (@sqlstr) --Get counts of data files and log files SELECT @datafilecount = COUNT(*) FROM #databasefiles WHERE filetype = 0 SELECT @logfilecount = COUNT(*) FROM #databasefiles WHERE filetype = 1 END --If table-valued parameter is passed in get database file information from RESTORE FILELISTONLY --Insert the backupfilename and backupfiletype into #backupfiles ELSE BEGIN SELECT @tvpbackupfile = backupfilename FROM #tvpfiles WHERE backupfiletype = 'FULL' SET @sqlstr = 'RESTORE FILELISTONLY FROM DISK = ' + '''' + @tvpbackupfile + '''' PRINT @sqlstr INSERT INTO #FileListOnly EXEC(@sqlstr) INSERT INTO #databasefiles SELECT @sourcedbname, LogicalName, PhysicalName, CASE [Type] WHEN 'D' THEN 0 WHEN 'L' THEN 1 END FROM #FileListOnly --Loop through files in #tvpfiles and run RESTORE HEADERONLY to get the BackupStartDate and BackupFinishDate for each file WHILE @tvploopcount &lt;= @tvplimit BEGIN SELECT TOP 1 @tvpbackupfile = backupfilename, @tvpbackupfiletype = backupfiletype FROM #tvpfiles IF @tvpbackupfiletype NOT IN ('FULL', 'DIFF', 'LOG') BEGIN RAISERROR ('You have passed an invalid backup file type. Valid backup file types are FULL, DIFF, and LOG. Please review your data.', -- Message text. 16, -- Severity. 1 -- State. ); END SELECT @sqlstr = 'RESTORE HEADERONLY FROM DISK = ' + '''' + @tvpbackupfile + '''' INSERT INTO #headeronly EXEC(@sqlstr) INSERT INTO #backupfiles SELECT BackupStartDate, BackupFinishDate, @tvpbackupfile, @tvpbackupfiletype FROM #headeronly SELECT @datafilecount = COUNT(*) FROM #databasefiles WHERE filetype = 0 SELECT @logfilecount = COUNT(*) FROM #databasefiles WHERE filetype = 1 SET @tvploopcount += 1 DELETE FROM #tvpfiles WHERE backupfilename = @tvpbackupfile DELETE FROM #headeronly END END --Raise an error if the database contains more than one log file IF @logfilecount &gt; 1 BEGIN RAISERROR ('You have more than one log file, which is a really bad idea. No restore for you. See this link for more information. http://www.sqlskills.com/blogs/paul/multiple-log-files-and-why-theyre-bad/', -- Message text. 16, -- Severity. 1 -- State. ); END --If @droprestoredb = 1 and @restoredbname &lt;&gt; @droprestoredb. replace file extensions IF @sourcedbname &lt;&gt; @restoredbname BEGIN UPDATE #databasefiles SET PhysicalName = REPLACE(PhysicalName,'.mdf','_testrestore.mdf') WHERE PhysicalName LIKE '%mdf%' UPDATE #databasefiles SET PhysicalName = REPLACE(PhysicalName,'.ldf','_testrestore.ldf') WHERE PhysicalName LIKE '%ldf%' END --Get the logical and physical log file names SELECT @logfile = LogicalName, @physlogfile = PhysicalName FROM #databasefiles WHERE filetype = 1 --If the database contains one data file, build the MOVE clause for a single file IF @datafilecount = 1 BEGIN SELECT @datafile = LogicalName, @physdatafile = PhysicalName FROM #databasefiles WHERE filetype = 0 SET @moveparm = @moveparm + ' MOVE ' + '''' + @datafile + '''' + ' TO ' + '''' + @physdatafile + '''' + ',' + ' MOVE ' + '''' + @logfile + '''' + ' TO ' + '''' + @physlogfile + '''' + ', NORECOVERY' END ELSE --If the database contains more than one data file, loop through the list of files and build a multi-file MOVE statement IF @datafilecount &gt; 1 BEGIN --SELECT @datafileloopcount, @datafilecount WHILE @datafileloopcount &lt;= @datafilecount BEGIN --SELECT TOP (1) LogicalName, REPLACE(PhysicalName,@sourcedbname,@restoredbname) FROM #databasefiles WHERE filetype = 0 SET @physdatafile = (SELECT TOP (1) PhysicalName FROM #databasefiles WHERE filetype = 0) SET @datafile = (SELECT TOP (1) LogicalName FROM #databasefiles WHERE filetype = 0) SET @moveparm = @moveparm + ' MOVE ' + '''' + @datafile + '''' + ' TO ' + '''' + @physdatafile + '''' + ',' DELETE TOP (1) FROM #databasefiles WHERE filetype = 0 SET @datafileloopcount += 1 END --Append the MOVE command for the log file to the MOVE clause and set it to NORECOVERY SET @moveparm = @moveparm + ' MOVE ' + '''' + @logfile + '''' + ' TO ' + '''' + @physlogfile + '''' + ', NORECOVERY' END IF @droprestoredb = 0 BEGIN SET @moveparm = @moveparm + ', REPLACE' END --If the table-valued parameter is not passed in, build the #backupfiles table from msdb tables IF @tvplimit = 0 BEGIN SELECT @fullbackupdate = MAX(c.backup_finish_date) FROM msdb.dbo.backupset c INNER JOIN msdb.dbo.backupmediafamily d ON c.media_set_id = d.media_set_id WHERE c.database_name = @sourcedbname AND c.type = 'D' SELECT @logbackupdate = MAX(c.backup_start_date) FROM msdb.dbo.backupset c INNER JOIN msdb.dbo.backupmediafamily d ON c.media_set_id = d.media_set_id WHERE c.database_name = @sourcedbname AND c.type IN ('D', 'I') --Get the most recent full backup file for the source database and insert it into the #backupfiles temp table INSERT INTO #backupfiles SELECT a.backup_start_date, a.backup_finish_date, b.physical_device_name, 'FULL' AS [backuptype] FROM msdb.dbo.backupset a join msdb.dbo.backupmediafamily b ON a.media_set_id = b.media_set_id WHERE a.database_name = @sourcedbname AND backup_finish_date = @fullbackupdate ORDER BY a.backup_finish_date ASC --Get a list of differential backup files older than the most recent full backup INSERT INTO #backupfiles SELECT TOP 1 a.backup_start_date, a.backup_finish_date, b.physical_device_name, 'DIFF' AS [backuptype] FROM msdb.dbo.backupset a join msdb.dbo.backupmediafamily b ON a.media_set_id = b.media_set_id WHERE a.database_name = @sourcedbname AND a.type = 'I' AND backup_finish_date &gt; @fullbackupdate ORDER BY a.backup_finish_date DESC --Get all log backupfiles run after the latest full or differential backup and insert it into the #backupfiles temp table INSERT INTO #backupfiles SELECT a.backup_start_date, a.backup_finish_date, b.physical_device_name,'LOG' AS [backuptype] from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b on a.media_set_id = b.media_set_id where a.database_name = @sourcedbname AND a.type = 'L' AND backup_finish_date &gt; @logbackupdate order by a.backup_finish_date ASC IF @stopat &lt; @fullbackupdate BEGIN SET @errormessage = 'The @stopat values provided ' + CAST(@stopat AS VARCHAR(26)) + 'is older than the most recent full backup date of ' + CAST(@fullbackupdate AS VARCHAR(26)) + '. Please check the value and rerun.' RAISERROR (@errormessage, -- Message text. 16, -- Severity. 1 -- State. ); END END --If a @stopat date has been passed, delete LOG files newer than @stopat IF @stopat IS NOT NULL BEGIN SELECT TOP 1 @logbackuplimit = backupfinishdate FROM #backupfiles WHERE backupfinishdate &gt; @stopat DELETE FROM #backupfiles WHERE backupfinishdate &gt; @logbackuplimit END --Get counts of all backup files, full backup files, differential backup files, and log backup files for use in loop processing below SELECT @filecount = COUNT(*) FROM #backupfiles SELECT @fullfilecount = COUNT(*) FROM #backupfiles WHERE backuptype = 'FULL' SELECT @diffbackupcount = COUNT(*) FROM #backupfiles WHERE backuptype = 'DIFF' SELECT @logbackupcount = COUNT(*) FROM #backupfiles WHERE backuptype = 'LOG' --If no FULL backups exists. error out IF @fullfilecount = 0 BEGIN RAISERROR ('There are no full backup files for your database.', -- Message text. 16, -- Severity. 1 -- State. ); END --While backup files exist, loop through them to generate the restore statement WHILE @loopcount &lt;= @filecount BEGIN --Get the top backup file from the temp table SET @backupfiletype = (SELECT TOP 1 backuptype FROM #backupfiles) --If it is a full backup file, build the restore statement IF @backupfiletype = 'FULL' BEGIN --If there are no log backups to apply, replace NORECOVERY with RECOVERY in the MOVE clause IF (@logbackupcount = 0 AND @diffbackupcount = 0) BEGIN SET @moveparm = REPLACE(@moveparm, 'NORECOVERY', 'RECOVERY') END --If there is a single full backup file, build the RESTORE statement with that file IF @fullfilecount = 1 BEGIN SET @backupfile = (SELECT TOP 1 physicalfilename FROM #backupfiles) SET @sqlstr = 'RESTORE DATABASE ' + @restoredbname + ' FROM DISK = ' + '''' + @backupfile + '''' + @moveparm --PRINT @sqlstr DELETE TOP (1) FROM #backupfiles END --If there is more than one full backup file, build the RESTORE command for all full backup files ELSE IF @fullfilecount &gt; 1 BEGIN WHILE @backuploopcount &lt;= @fullfilecount BEGIN SET @backupfile = (SELECT TOP 1 physicalfilename FROM #backupfiles WHERE backuptype = 'FULL') SET @fromstatement = @fromstatement + 'DISK = ' + '''' + @backupfile + '''' + ',' DELETE TOP (1) FROM #backupfiles WHERE backuptype = 'FULL' SET @backuploopcount += 1 END --Truncate the trailing comma from the FROM clause SET @fromstatement = SUBSTRING(@fromstatement,1,(LEN(@fromstatement) - 1)) SET @sqlstr = 'RESTORE DATABASE ' + @restoredbname + @fromstatement + @moveparm SET @loopcount += 1 --PRINT @sqlstr END END --One RESTORE statement will be built for the most recent differential backup ELSE IF @backupfiletype = 'DIFF' BEGIN IF @diffbackupcount = 1 AND @logbackupcount = 0 BEGIN SET @moveparm = REPLACE(@moveparm, 'NORECOVERY', 'RECOVERY') END SET @backupfile = (SELECT TOP 1 physicalfilename FROM #backupfiles) SET @sqlstr = 'RESTORE DATABASE ' + @restoredbname + ' FROM DISK = ' + '''' + @backupfile + '''' + @moveparm --PRINT @sqlstr DELETE TOP (1) FROM #backupfiles END --If it is a log backup file, build a RESTORE log statement --One RESTORE statement will be built for each log backup file ELSE IF @backupfiletype = 'LOG' BEGIN SET @backupfile = (SELECT TOP 1 physicalfilename FROM #backupfiles) SET @sqlstr = 'RESTORE LOG ' + @restoredbname + ' FROM DISK = ' + '''' + @backupfile + '''' --If there is another log backup file, set the RESTORE to WITH NORECOVERY IF (SELECT COUNT(*) FROM #backupfiles) &gt; 1 BEGIN SET @sqlstr = @sqlstr + ' WITH NORECOVERY' END --If there is not another log backup file, set the RESTORE to WITH RECOVERY ELSE BEGIN IF @stopat IS NOT NULL BEGIN --CAST(@stopat AS VARCHAR(26)) SET @sqlstr = @sqlstr + ' WITH NORECOVERY, STOPAT = ' + '''' + CONVERT(VARCHAR(30),@stopat,121) + '''' END ELSE BEGIN SET @sqlstr = @sqlstr + ' WITH RECOVERY' END END DELETE TOP (1) FROM #backupfiles END --If @noexec = 1, PRINT @sqlstr IF @noexec = 1 BEGIN PRINT @sqlstr END --If @noexec &lt;&gt; 1, execute @sqlstr ELSE BEGIN EXEC (@sqlstr) END --Increment the loop count and continue processing backup files SET @loopcount += 1 END --If @stopat has been passed and a LOG backup exists, restore database with recovery IF @stopat IS NOT NULL AND @logbackupcount &gt; 0 BEGIN SET @sqlstr = 'RESTORE DATABASE ' + @restoredbname + ' WITH RECOVERY' IF @noexec = 1 BEGIN PRINT @sqlstr END ELSE BEGIN EXEC(@sqlstr) END END END TRY BEGIN CATCH SELECT @sourcedbname AS DatabaseName, ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine, ERROR_MESSAGE() as ErrorMessage; END CATCH GO