November 2, 2016 at 12:09 pm
Comments posted to this topic are about the item Build restore scripts dynamically
November 14, 2016 at 9:03 am
Nice script, thanks.
April 15, 2017 at 5:24 am
This script would require small modifications. I have modified the script so tht it would display restore script properly in output window. Also we would need to remove query option --> Include column headers in result set --> Under Results Text in SSMS.
/**************************************************************************************************/
/*** Build Restore Scripts Dynamically ***/
/*** ***/
/*** Save this script on each server to be restored, and open in Management Studio ***/
/*** Set the @BackupPath variable to the parent folder where the server's backup files exist ***/
/*** Run the script, and copy the output results to a new query window ***/
/*** Run entire script at once, or restore each database individually ***/
/*** This script works fine only with native sql backups created using Maintenance Plan ***/
/*** Script created by Brian Smith - April 2, 2015 ***/
/*** Script Modified by Mahesh Shinde - April 15, 2017 ***/
/*** ***/
/**************************************************************************************************/
SET NOCOUNT ON
DECLARE @BackupPath varchar(500); SET @BackupPath = 'C:\MSSQL\Backups\' -- e.g. 'C:\MSSQL\Backups\'
DECLARE @DB varchar(500)
DECLARE @AllFilesTable TABLE (
subdirectory varchar(1500),
depth int,
isfile bit
)
DECLARE @BackupsTable TABLE (
BackupFile varchar(1500)
)
DECLARE @BakFile varchar(1500)
DECLARE @TrnFile varchar(1500)
DECLARE @RestoreScript varchar(max)
-- Start building the restore script
SET @RestoreScript = 'USE [master];
'
-- Capture the first database
SELECT @DB = min(name) FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') AND state_desc = 'ONLINE'
--select min(name) from sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') AND state_desc = 'ONLINE'
--set @DB = 'DBTeam'
-- Begin the loop
WHILE @DB IS NOT NULL
BEGIN
-- Capture the list of backup files
--select @DB
INSERT INTO @AllFilesTable (subdirectory, depth, isfile)
EXEC ('xp_dirtree ''' + @BackupPath + '' + @DB + ''', 1, 1')
--select * from @AllFilesTable
INSERT INTO @BackupsTable (BackupFile)
SELECT subdirectory FROM @AllFilesTable WHERE RIGHT(subdirectory,29) >= RIGHT((SELECT max(subdirectory) FROM @AllFilesTable WHERE RIGHT(subdirectory,4) = '.bak'),29)
--select * from @BackupsTable
-- Continue building the script lines
SET @RestoreScript = @RestoreScript + 'ALTER DATABASE [' + @DB + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' + char(13)
-- Add the full backup restore script line
SELECT @BakFile = BackupFile FROM @BackupsTable WHERE RIGHT(BackupFile,4) = '.bak'
SET @RestoreScript = @RestoreScript + 'RESTORE DATABASE [' + @DB + '] FROM DISK = N''' + @BackupPath + @DB + '\' + @BakFile + ''' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5;'
select @RestoreScript
DELETE @BackupsTable WHERE BackupFile = @BakFile
-- Add the transaction log backup restore script lines (one for each .trn file)
SELECT @TrnFile = min(BackupFile) FROM @BackupsTable WHERE RIGHT(BackupFile,4) = '.trn'
WHILE @TrnFile IS NOT NULL
BEGIN
select replace(replace( 'RESTORE LOG [' + @DB + '] FROM DISK = N''' + @BackupPath + @DB + '\' + @TrnFile + ''' WITH NORECOVERY;' , char(13) , ''),char(10),'')
DELETE @BackupsTable WHERE BackupFile = @TrnFile
SELECT @TrnFile = min(BackupFile) FROM @BackupsTable WHERE RIGHT(BackupFile,4) = '.trn'
END
--select @RestoreScript
-- Add the script line to bring the database back online
select 'RESTORE DATABASE [' + @DB + '] WITH RECOVERY;
GO'
SET @RestoreScript = ''
-- Clear the temp tables for the next capture
DELETE @AllFilesTable
-- Cycle through the databases one at a time
SELECT @DB = min(name) FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') AND state_desc = 'ONLINE' AND name > @DB
--select @RestoreScript
END
SET NOCOUNT OFF
April 15, 2017 at 3:44 pm
The script was written and intended to use with the out-of-the-box defaults of SSMS, including the 'results to grid' query option. Yes, if you have changed your option to 'results to text' you would have to un-check 'include column headers' for it to look right. I suspect the other display discrepancies also relate to that setting, as they turn out fine for me in a 'results to grid' run.
Also, when modifying someone else's script, please explain in the script--with each line, preferably--what was modified. This makes it much easier to determine what is relevant to any given environment.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply