September 3, 2013 at 4:30 pm
I found a very good script to generate script for database Restore for native sql server.
http://sequelserver.blogspot.com/2009/04/generate-restore-scripts-from-backup.html
Can you please let me know how we can modify the same to work with Litespeed?
CREATE
PROCEDURE
[dbo].[usp_GenerateRestoreScripts] @DBname VARCHAR(100)
AS
SET
NOCOUNT
ON-- required because we're going to print T-SQL for the restores in the messages 'tab' of SSMS
/*
Script creates the T-SQL to restore a database with info from MSDB
It helps by creating RESTORE command constructed from the last FULL backup, the last DIFFERENTIAL backup
and all the required TRANSACTION LOG backups after this.
Neat when you have a high frequency of differential or log backups
The variable @DBName should be set to the name of the database you want to restore.
!!! BE AWARE: include MSDB in your backup plan for this T-SQL script to work in all circumstances !!!
I usually include MSDB in the log backup schedule (set the db to full recovery mode)
*/
DECLARE @lastFullBackup INT, @lastFullBackupPath VARCHAR(2000), @lastDifferentialBackup INT, @lastDifferentialBackupPath VARCHAR(2000)
DECLARE @i INT, @logBackupPath VARCHAR(1000)
-- remove temp object that might exist
IF
OBJECT_ID('tempdb..#MSDBBackupHistory')
IS
NOT
NULL
DROP
TABLE #MSDBBackupHistory
CREATE
TABLE #MSDBBackupHistory (
id INT
IDENTITY(1,1),
backup_start_date DATETIME,
backup_type CHAR(1),
physical_device_name VARCHAR(1000))
INSERT
INTO #MSDBBackupHistory (backup_start_date, backup_type, physical_device_name)
SELECT BS.backup_start_date, BS.type, RTRIM(BMF.physical_device_name)
FROM msdb..backupset BS JOIN msdb..backupmediafamily BMF ON BMF.media_set_id=BS.media_set_id
WHERE BS.database_name
= @DBName
ORDER
BY BS.backup_start_date
-- dump the last backup first in table
-- get the last Full backup info.
SET @lastFullBackup =
(SELECT
MAX(id)
FROM #MSDBBackupHistory WHERE backup_type='D')
SET @lastFullBackupPath =
(SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@lastFullBackup)
-- Restore the Full backup
'RESTORE DATABASE '
+ @DBName
'FROM DISK='''
+ @lastFullBackupPath +
''''
-- IF it's there's no backup (differential or log) after it, we set to 'with recovery'
IF
(@lastFullBackup =
(SELECT
MAX(id)
FROM #MSDBBackupHistory))
'WITH RECOVERY'
ELSE
'WITH NORECOVERY'
'GO'
''
-- get the last Differential backup (it must be done after the last Full backup)
SET @lastDifferentialBackup =
(SELECT
MAX(id)
FROM #MSDBBackupHistory WHERE backup_type='I'
AND id > @lastFullBackup)
SET @lastDifferentialBackupPath =
(SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@lastDifferentialBackup)
-- when there's a differential backup after the last full backup create the restore T-SQL commands
IF
(@lastDifferentialBackup IS
NOT
NULL)
BEGIN
-- Restore last diff. backup
'RESTORE DATABASE '
+ @DBName
'FROM DISK='''
+ @lastDifferentialBackupPath +
''''
-- If no backup made (differential or log) after it, set to 'with recovery'
IF
(@lastDifferentialBackup =
(SELECT
MAX(id)
FROM #MSDBBackupHistory))
'WITH RECOVERY'
ELSE
'WITH NORECOVERY'
'GO'
''
-- new line for readability
END
-- construct the required TRANSACTION LOGs restores
IF
(@lastDifferentialBackup IS
NULL)
-- no diff backup made?
SET @i = @lastFullBackup +
1 -- search for log dumps after the last full
ELSE
SET @i = @lastDifferentialBackup +
1
-- search for log dumps after the last diff
-- script T-SQL restore commands from the log backup history
WHILE
(@i <=
(SELECT
MAX(id)
FROM #MSDBBackupHistory))
BEGIN
SET @logBackupPath =
(SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@i)
'RESTORE LOG '
+ @DBName
'FROM DISK='''
+ @logBackupPath +
''''
-- it's the last transaction log, set to 'with recovery'
IF
(@i =
(SELECT
MAX(id)
FROM #MSDBBackupHistory))
'WITH RECOVERY'
ELSE
'WITH NORECOVERY'
'GO'
''
-- new line for readability
SET @i = @i +
1
-- try to find the next log entry
END
-- remove temp objects that exist
IF
OBJECT_ID('tempdb..#MSDBBackupHistory')
IS
NOT
NULL
DROP
TABLE #MSDBBackupHistory
September 3, 2013 at 11:54 pm
I'm not familiar with LiteSpeed, so I don't know how much a LiteSpeed restore script differs from a native SQL restore script.
This is the approach you need to follow: Compare the output (native SQL restore command) of the script with a restore command you need for LieSpeed. Notice all the differences between them. Analyse the code in the script to see where the differnces of the restore script are generated. Change the script to accommodate a LiteSpeed restore.
September 4, 2013 at 4:12 am
gary1 (9/3/2013)
I found a very good script to generate script for database Restore for native sql server.http://sequelserver.blogspot.com/2009/04/generate-restore-scripts-from-backup.html
Can you please let me know how we can modify the same to work with Litespeed?
The Litespeed extended stored procedures are not that dissimilar from the native commands. The Litespeed product has 4 main xps which are
master.dbo.xp_backup_database
master.dbo.xp_backup_log
master.dbo.xp_restore_database
master.dbo.xp_restore_log
So your code should look something like this, obviously consult the Litespeed documentation and test thoroughly
CREATE
PROCEDURE
[dbo].[usp_GenerateRestoreScripts] @DBname VARCHAR(100)
AS
SET
NOCOUNT
ON-- required because we're going to print T-SQL for the restores in the messages 'tab' of SSMS
/*
Script creates the T-SQL to restore a database with info from MSDB
It helps by creating RESTORE command constructed from the last FULL backup, the last DIFFERENTIAL backup
and all the required TRANSACTION LOG backups after this.
Neat when you have a high frequency of differential or log backups
The variable @DBName should be set to the name of the database you want to restore.
!!! BE AWARE: include MSDB in your backup plan for this T-SQL script to work in all circumstances !!!
I usually include MSDB in the log backup schedule (set the db to full recovery mode)
*/
DECLARE @lastFullBackup INT, @lastFullBackupPath VARCHAR(2000), @lastDifferentialBackup INT, @lastDifferentialBackupPath VARCHAR(2000)
DECLARE @i INT, @logBackupPath VARCHAR(1000)
-- remove temp object that might exist
IF
OBJECT_ID('tempdb..#MSDBBackupHistory')
IS
NOT
NULL
DROP
TABLE #MSDBBackupHistory
CREATE
TABLE #MSDBBackupHistory (
id INT
IDENTITY(1,1),
backup_start_date DATETIME,
backup_type CHAR(1),
physical_device_name VARCHAR(1000))
INSERT
INTO #MSDBBackupHistory (backup_start_date, backup_type, physical_device_name)
SELECT BS.backup_start_date, BS.type, RTRIM(BMF.physical_device_name)
FROM msdb..backupset BS JOIN msdb..backupmediafamily BMF ON BMF.media_set_id=BS.media_set_id
WHERE BS.database_name
= @DBName
ORDER
BY BS.backup_start_date
-- dump the last backup first in table
-- get the last Full backup info.
SET @lastFullBackup =
(SELECT
MAX(id)
FROM #MSDBBackupHistory WHERE backup_type='D')
SET @lastFullBackupPath =
(SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@lastFullBackup)
-- Restore the Full backup
'EXEC master.dbo.xp_restore_database @database=N''' + @DBName + ''''
', @filename=N''' + @lastFullBackupPath + ''''
-- IF it's there's no backup (differential or log) after it, we set to 'with recovery'
IF
(@lastFullBackup =
(SELECT
MAX(id)
FROM #MSDBBackupHistory))
', @WITH=''RECOVERY'''
ELSE
', @WITH=''NORECOVERY'''
'GO'
''
-- get the last Differential backup (it must be done after the last Full backup)
SET @lastDifferentialBackup =
(SELECT
MAX(id)
FROM #MSDBBackupHistory WHERE backup_type='I'
AND id > @lastFullBackup)
SET @lastDifferentialBackupPath =
(SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@lastDifferentialBackup)
-- when there's a differential backup after the last full backup create the restore T-SQL commands
IF
(@lastDifferentialBackup IS
NOT
NULL)
BEGIN
-- Restore last diff. backup
EXEC master.dbo.xp_restore_database @database=N''' + @DBName + ''''
', @filename=N''' + @lastDifferentialBackupPath + ''''
-- If no backup made (differential or log) after it, set to 'with recovery'
IF
(@lastDifferentialBackup =
(SELECT
MAX(id)
FROM #MSDBBackupHistory))
', @WITH=''RECOVERY'''
ELSE
', @WITH=''NORECOVERY'''
'GO'
''
-- new line for readability
END
-- construct the required TRANSACTION LOGs restores
IF
(@lastDifferentialBackup IS
NULL)
-- no diff backup made?
SET @i = @lastFullBackup +
1 -- search for log dumps after the last full
ELSE
SET @i = @lastDifferentialBackup +
1
-- search for log dumps after the last diff
-- script T-SQL restore commands from the log backup history
WHILE
(@i <=
(SELECT
MAX(id)
FROM #MSDBBackupHistory))
BEGIN
SET @logBackupPath =
(SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@i)
'RESTORE LOG '
+ @DBName
'FROM DISK='''
+ @logBackupPath +
''''
-- it's the last transaction log, set to 'with recovery'
IF
(@i =
(SELECT
MAX(id)
FROM #MSDBBackupHistory))
'WITH RECOVERY'
ELSE
'WITH NORECOVERY'
'GO'
''
-- new line for readability
SET @i = @i +
1
-- try to find the next log entry
END
-- remove temp objects that exist
IF
OBJECT_ID('tempdb..#MSDBBackupHistory')
IS
NOT
NULL
DROP
TABLE #MSDBBackupHistory
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply