Automated SQL Server script to generate restore database script
If you are migrating your databases to a different server or wants to keep the restore script every time you backup your databases for recovery purpose then below procedure generates the restore script. You need to manually execute this script.
By default it restores the database with NO recovery , to avoid the problems where you might want to restore the tail log manually.
This procedure generates restore script only. It wont execute the backup script.
| CREATE FUNCTION dbo.fn_file_exists(@filename VARCHAR(300)) RETURNS INT AS BEGIN DECLARE @file_exists AS INT EXEC master..xp_fileexist @filename, @file_exists OUTPUT RETURN @file_exists END GO CREATE proc DBA_GEN_RESTORE_SCRIPT (@dbname varchar(90) ,@REPORT BIT = NULL,@Latency_nd_Checks BIT = NULL) AS SET NOCOUNT ON declare @T1 table (iid int identity(1,1),backup_set_id int ,database_name varchar(90),physical_device_name varchar(500),type varchar(90),position int,BKP_TS DATETIME) --- Get the recent full insert into @T1 SELECT --bkset.backup_set_id,database_name,' DISK = N'''+physical_device_name+''',' as physical_device_name,type,position bkset.backup_set_id,database_name,physical_device_name as physical_device_name,type,position ,backup_finish_date --backup_start_date,TYPE, user_name , backup_finish_date,(compressed_backup_size/1024)/1024 as size FROM msdb..backupset bkset INNER JOIN msdb..backupmediafamily bkfmly ON bkset.media_set_id = bkfmly.media_set_id where database_name = @dbname and type = 'D' AND bkset.backup_set_id >= (select MAX(backup_set_id ) from MSDB.DBO.backupset where type = 'D' and database_name = @dbname) order by bkset.backup_set_id desc --- Get the recent diff after recent full insert into @T1 SELECT --bkset.backup_set_id,database_name,' DISK = N'''+physical_device_name+''',' as physical_device_name,type,position bkset.backup_set_id,database_name,physical_device_name as physical_device_name,type,position ,backup_finish_date --backup_start_date,TYPE, user_name , backup_finish_date,(compressed_backup_size/1024)/1024 as size FROM msdb..backupset bkset INNER JOIN msdb..backupmediafamily bkfmly ON bkset.media_set_id = bkfmly.media_set_id where database_name = @dbname and type = 'I' and bkset.backup_set_id > (select MAX(backup_set_id ) from @T1 where type = 'D') AND bkset.backup_set_id >= (select MAX(backup_set_id ) from msdb..backupset where type = 'I' and database_name = @dbname) order by bkset.backup_set_id desc --- Get the all the logs after recent diff insert into @T1 SELECT --bkset.backup_set_id,database_name,' DISK = N'''+physical_device_name+'''' as physical_device_name,type,position -- comma seperator is not required for T-Logs to apply all the tlogs bkset.backup_set_id,database_name,physical_device_name as physical_device_name,type,position -- comma seperator is not required for T-Logs to apply all the tlogs ,backup_finish_date --backup_start_date,TYPE, user_name , backup_finish_date,(compressed_backup_size/1024)/1024 as size FROM msdb..backupset bkset INNER JOIN msdb..backupmediafamily bkfmly ON bkset.media_set_id = bkfmly.media_set_id where database_name = @dbname and type = 'L' and bkset.backup_set_id > -- If the Diff backup does NOT exist then take the max bkp set id of Full COALESCE((select MAX(backup_set_id ) from @T1 where type = 'I'),(select MAX(backup_set_id ) from @T1 where type = 'D')) order by bkset.backup_set_id ASC IF @REPORT IS NOT NULL BEGIN select backup_set_id,database_name,physical_device_name,TYPE,position,BKP_TS,DATEDIFF(MINUTE,BKP_TS,GETDATE()) AS LATENCY ,dbo.fn_file_exists( physical_device_name) as file_exists from @T1 RETURN END ------------------------------------------------------------------------------------------------------------------------- -- remove the last comma in last row to build syntax -- except log files ------------------------------------------------------------------------------------------------------------------------- --update @T1 --SET physical_device_name = STUFF(physical_device_name ,LEN(physical_device_name),1,'') --WHERE iid IN (SELECT IID FROM (SELECT MAX(iid) AS IID ,type FROM @T1 group by type )X) --and type NOT in --('L') -- except log files ----SELECT * FROM @T1 ------------------------------------------------------------------------------------------------------------------------- -- add comma for split backups except for the last row in each splitted backup set ------------------------------------------------------------------------------------------------------------------------- update @T1 SET physical_device_name = 'DISK = ''' + physical_device_name +'''' WHERE iid IN (SELECT IID FROM (SELECT MAX(iid) AS IID ,type FROM @T1 group by type )X) and type NOT in ('L') -- except log files update @T1 SET physical_device_name = 'DISK = ''' + physical_device_name +''',' WHERE iid NOT IN (SELECT IID FROM (SELECT MAX(iid) AS IID ,type FROM @T1 group by type )X) and type NOT in ('L') -- except log files --SELECT * FROM @T1 ------------------------------------------------------------------------------------------------------------------------- declare @restore varchar(max) ='' declare @with varchar(max) = ' WITH NORECOVERY , NOUNLOAD, STATS = 10 ' declare @with_file varchar(9) if exists (select * from @T1 where type = 'D') BEGIN SELECT 'RESTORE DATABASE '+@dbname+' FROM ' AS '--RECENT FULL ' SELECT physical_device_name AS ' ' FROM @T1 WHERE TYPE ='D' ORDER BY backup_set_id ,physical_device_name SELECT @with_file = position FROM @T1 WHERE TYPE ='D' select @with+' ,file =' + @with_file SELECT ', MOVE ''' + name +''' TO N'''+ physical_name +'''' FROM sys.master_files where DB_NAME(database_id) = @dbname ORDER BY name END if exists (select * from @T1 where type = 'I') BEGIN SELECT 'RESTORE DATABASE '+@dbname+' FROM ' AS '--RECENT DIFFERENTIAL ' SELECT physical_device_name AS ' ' FROM @T1 WHERE TYPE ='I' ORDER BY backup_set_id ,physical_device_name SELECT @with_file = position FROM @T1 WHERE TYPE ='I' select @with+' ,file =' + @with_file --SELECT ', MOVE ''' + name +''' TO N'''+ physical_name +'''' --FROM sys.master_files where DB_NAME(database_id) = @dbname --ORDER BY name END --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- if exists (select * from @T1 where type = 'L') BEGIN select count(backup_set_id)AS CNT_BKPSTID,min(iid)as min_iid,max(iid) as max_iid ,backup_set_id into #t from @t1 where type = 'L' group by backup_set_id having count(backup_set_id) > 1 set @with = ' NORECOVERY , NOUNLOAD, STATS = 10 ' -- FOR INDIVIDUAL FILES SELECT 'RESTORE LOG '+@dbname+' FROM DISK ='''+physical_device_name +''' with file ='+cast(position as varchar(9)) + ','+@with AS '-- RECENT LOGS' FROM @T1 WHERE TYPE ='L' and backup_set_id not in (select backup_set_id from #t ) ORDER BY backup_set_id -- FOR SPLITTED FILES if exists (select * from #t) begin --select * from #t a --join @T1 b on a.backup_set_id = b.backup_set_id where b.type = 'L' update @T1 SET physical_device_name = 'RESTORE LOG '+@dbname+' FROM DISK = '''+physical_device_name + '''' WHERE iid IN (SELECT min_iid from #t) update @T1 SET physical_device_name = ', DISK = '''+physical_device_name + ''' with file ='+cast(position as varchar(9))+ ','+@with WHERE iid IN (SELECT max_iid from #t) SELECT physical_device_name AS '--splt logs' FROM @T1 WHERE TYPE ='L' and backup_set_id in (select backup_set_id from #t )ORDER BY iid ,physical_device_name end END |
-- To validate Database backups for all the databases in a server
create table #v (backup_set_id int,database_name varchar(500),path varchar(500) , type varchar(5)
,position int, bkp_ts datetime,latency int , file_exists int
)
DECLARE @DB VARCHAR(256)
DECLARE CUR CURSOR FOR SELECT NAME FROM SYS.DATABASES WHERE DATABASE_ID > 5
OPEN CUR
FETCH NEXT FROM CUR INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM CUR INTO @DB
insert into #v exec DBA_GEN_RESTORE_SCRIPT @DB,1,NULL
END
CLOSE CUR
DEALLOCATE CUR
GO
SELECT * FROM #v (
No comments:
Post a Comment