May 27, 2011 at 9:20 am
Does anyone have a script to build the necessary restore statements for a full db backup and trans logs statements for a specific database without using the Management Studio? The reason I ask is that in a disaster we’d like to restore specific databases from one prod SQL Server to a disaster recovery SQL Server without wiping out the existing databases on the disaster recovery SQL Server. In other words I cannot restore the prod SQL Server msdb into the disaster recovery SQL Server. What I was thinking of doing was restoring msdb from the prod SQL Server to a different db name so I can script out the restores. This is SQL 2008
May 27, 2011 at 9:28 am
Here's how I do it. PS I don't use diff backups so I don't consider those but it'd be an easy change to implement.
SET IMPLICIT_TRANSACTIONS ON
SET DATEFORMAT YMD
SET STATISTICS IO OFF
SET NOCOUNT ON
--------Instructions----------
-- SET datetime of restore
-- You can exclude full backup file if they are corrupted at the line "Backup exclude list". Uncomment the line and enter the full filename to exclude
-- set query output to text
-- execute the script
-- copy / paste the result of the query to a new ssms window
-- The script is generated so that you can't accidentally erase the prod DB
-- To erase the prod DB : Erase or comment the line that starts with : , MOVE N'PROD-FOR
-- Then do a search / replace ([PROD-FORDIA_test_restore] >>>> [PROD-FORDIA]
-- Execute the script
--------Instructions----------
--What date / time to restore the db to?
DECLARE @pit AS DATETIME
SET @pit = '2011-05-27 10:14:15.000'
IF OBJECT_ID('tempdb..#out') > 0 DROP TABLE #out
IF OBJECT_ID('tempdb..#cmds') > 0 DROP TABLE #cmds
CREATE TABLE #out (t VARCHAR(500) NULL, dt DATETIME NULL, TYPE CHAR(1))
INSERT INTO #out (t)
EXEC xp_cmdshell 'dir / B /OD Q:\*FORDIA*.bak'
DELETE t FROM #out t WHERE t.t NOT LIKE '%.bak' OR t IS NULL
--Full Backup exclude list (can't ignore log backups without another full or diff'
--OR t IN ('PROD-FORDIA-FULL 2011-05-03 05H00.bak', 'PROD-FORDIA-FULL 2011-05-04 05H00.bak')
UPDATE #out SET t = 'Q:\' + t, dt = REPLACE(LEFT(RIGHT(t, 20), 16), 'H', ':'), TYPE = CASE WHEN LOWER(t) LIKE '%full%' THEN 'D' ELSE 'L' END
DECLARE @Exec VARCHAR(8000)
SET @Exec = NULL
;
WITH CTE_BK (t, dt, TYPE)
AS
(
SELECT TOP 1 t, dt, TYPE FROM #out WHERE [TYPE] = 'D' AND dt <= @pit
ORDER BY dt DESC
)
SELECT --t, dt, TYPE ,
CASE dtRestore_seq.[TYPE]
WHEN 'D' THEN 'PRINT ''
Restoring...' + t + '''
RESTORE DATABASE [PROD-FORDIA_test_restore] FROM DISK = N''' + t + ''' WITH FILE = 1
, MOVE N''PROD-FORDIA_Data'' TO N''Q:\Test restore\PROD-FORDIA_test_restore.mdf'', MOVE N''PROD-FORDIA_Log'' TO N''Q:\Test restore\PROD-FORDIA_test_restore_1.ldf'', MOVE N''PROD-FORDIA_1_Data'' TO N''Q:\Test restore\PROD-FORDIA_test_restore_2.ndf''
, NORECOVERY, STATS = 10'
WHEN 'L' THEN 'PRINT ''
Restoring...' + t + '''
RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N''' + t + ''' WITH FILE = 1, NORECOVERY, STATS = 25
'
WHEN 'STOP_AT' THEN 'PRINT ''
Restoring...' + t + '''
RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N''' + t + ''' WITH FILE = 1, STATS = 10, STOPAT = N''' + CONVERT(VARCHAR(100), @pit, 126) + ''''
END AS [--cmd] --self commenting line in copy/paste
FROM (
SELECT t, dt, TYPE FROM CTE_BK
UNION ALL
SELECT t, dt, TYPE FROM #out WHERE TYPE = 'L' AND dt > (SELECT dt FROM CTE_BK) AND dt <= @pit
UNION ALL
SELECT TOP 1 t, dt, 'STOP_AT' AS TYPE FROM #out WHERE TYPE = 'L' AND dt > @pit AND EXISTS (SELECT * FROM CTE_BK) ORDER BY dt
) dtRestore_seq
ORDER BY dt
IF OBJECT_ID('tempdb..#out') > 0 DROP TABLE #out
IF OBJECT_ID('tempdb..#cmds') > 0 DROP TABLE #cmds
ROLLBACK
May 27, 2011 at 9:33 am
That is helpful. However, I was hoping to run some type of build script against a copy of the msdb database so it will read the backup history and build the restore script much like the Mananagement Studio does.
May 27, 2011 at 9:39 am
Markus (5/27/2011)
That is helpful. However, I was hoping to run some type of build script against a copy of the msdb database so it will read the backup history and build the restore script much like the Mananagement Studio does.
Start profiler and see what code ssms uses to generate the code while doing a PIT restore.
May 27, 2011 at 9:57 am
I've used the attached proc in the past.
May 27, 2011 at 10:51 am
Charles, awesome... awesome... awesome. That is EXACTLY what I was looking for.
I restored the msdb to another db name, modified the SP you posted to point to that db and presto... restore statements generated. I tested it out fully and it does exactly what I was looking for.
Thanks a million.
😛 😛 😛
May 27, 2011 at 11:07 am
I originally found it here, so you can thank him. I modified it to work with Litespeed and SQL Backup. I'm glad it helped.
http://www.sqlservercentral.com/articles/Disaster+Recovery+(DR)/streamliningtherecoveryprocess/837/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply