Automatic Restore

,

this code scan the backup file for the databases and choose one randomly as the condition needed like the backup size between 1 and 1000 MG to restore it on a predefined path after cheking that there is enough disk on that path.

this code needs to be inhanced to use and kind of backups not only full backup, and some more verifacation needs to be done on the restord database, and it also could use mail notification.

am using this script to make sure that the backups am taking could be restred in case of disaster, at least on time every tow days or so.

DECLARE @database_name VARCHAR(40),@backup_name VARCHAR(200),@Divice_name VARCHAR(200),@size BIGINT,
@destenation_siz int

SELECT TOP (1) newid() AS dynamic_id,
CONVERT(VARCHAR(40), K.backup_finish_date, 103) AS B_date,
 K.database_name,
 K.name,
 K.type,
 M.physical_device_name,
 CONVERT(bigINT, K.[backup_size]) / 1024 / 1024 AS BackUP_Size_MB
 INTO #temp 
FROM msdb..backupset K
 jOIN msdb..backupmediafamily M ON M.media_set_id = K.media_set_id
 AND K.backup_finish_date = ( SELECT MAX(backup_finish_date)
 from msdb..backupset
 WHERE database_name = K.database_name
 AND type = K.type
 )
WHERE K.name <> 'tempdb' 
AND TYPE='D'
AND CONVERT(bigINT, K.[backup_size]) / 1024 / 1024 BETWEEN 1 AND 1000
ORDER BY 1
SELECT @database_name=database_name,@backup_name=[name],@Divice_name=physical_device_name,@size=BackUP_Size_MB FROM #temp
CREATE TABLE #temp_2 (drive VARCHAR(10),GB int)

INSERT INTO #temp_2 EXEC xp_fixeddrives
SELECT @destenation_siz=GB FROM #temp_2
WHERE DRIVE='D'
IF (@destenation_siz>@size)
BEGIN
    
    DECLARE @s1 VARCHAR(2000)
    
        SET @s1='DECLARE @dfile VARCHAR(60)
                 DECLARE @s1 VARCHAR(300)
            SELECT @dfile=[name] FROM '+@database_name+'.sys.database_files
            WHERE TYPE=0 
            DECLARE @lfile VARCHAR(60)
                 DECLARE @s2 VARCHAR(300)
                 SELECT @lfile=[name] FROM '+@database_name+'.sys.database_files
        WHERE TYPE=1
            create table ##temp_3 (ldf_file varchar(60),mdf_file varchar(60))
            insert into ##temp_3 values (@lfile,@dfile)
            '
    exec (@s1)
    DECLARE @dfile VARCHAR(60)
    DECLARE @lfile VARCHAR(60)
    SELECT @dfile=mdf_file,@lfile=ldf_file FROM ##temp_3
    
    DECLARE @string VARCHAR(600)
    SET @string='
    RESTORE DATABASE '+@database_name+' FROM 
    DISK = N'''+@Divice_name+''' WITH REPLACE, 
    MOVE N'''+@dfile+''' TO N''D:\ProcedureTest\'+@dfile+'test.mdf'', 
    MOVE N'''+@lfile+''' TO N''D:\ProcedureTest\'+@lfile+'test.ldf'', NOUNLOAD, STATS = 10'
    print (@string)
    
END


DROP table #temp
DROP TABLE #temp_2
DROP table ##temp_3

Rate

2.67 (3)

Share

Share

Rate

2.67 (3)