Wednesday, November 12, 2014

script to generate restore script


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 (


Post a Comment

Featured Post

SQL Server AWS Migration BCP

stored procedure to generate BCP scritps to migrate the SQL Server database. Developed this stored procedure on my labs to simulate the Mi...

Contributors