Automated dB Restore
dstergo
Posts: 4
Hi Everyone. I currently use a stored procedure that uses SQL Native Backup to restore a recently backed up database to a test database on the same server.
Here is the SP:
CREATE procedure ala_RestoreTestDB as
declare @filename as char(200)
declare filename_cursor cursor for
select top 1 substring(message, 22, len(message) - 22) as filename
from msdb.dbo.sysdbmaintplan_history
where database_name = 'ALAGP'
and activity = 'Backup database'
and succeeded = 1
order by sequence_id desc
open filename_cursor
fetch next from filename_cursor into @filename
RESTORE DATABASE TEST
FROM DISK = @filename
WITH REPLACE,
MOVE 'GPSALAGPDat.mdf' TO 'E:\GPSQLData1\SQLData\GPSTESTDat.mdf',
MOVE 'GPSALAGPLog.ldf' TO 'E:\GPSQLLogs1\SQLLogs\GPSTESTLog.ldf'
GO
As you can see, it takes the last good backup from the sysdbmaintplan_history table in msdb and restores it to a database named test. I would like to use this sp to do the same thing with SQL Backup. It is backup history stored in a table somewhere? If not, any other suggestions?
Thanks
Here is the SP:
CREATE procedure ala_RestoreTestDB as
declare @filename as char(200)
declare filename_cursor cursor for
select top 1 substring(message, 22, len(message) - 22) as filename
from msdb.dbo.sysdbmaintplan_history
where database_name = 'ALAGP'
and activity = 'Backup database'
and succeeded = 1
order by sequence_id desc
open filename_cursor
fetch next from filename_cursor into @filename
RESTORE DATABASE TEST
FROM DISK = @filename
WITH REPLACE,
MOVE 'GPSALAGPDat.mdf' TO 'E:\GPSQLData1\SQLData\GPSTESTDat.mdf',
MOVE 'GPSALAGPLog.ldf' TO 'E:\GPSQLLogs1\SQLLogs\GPSTESTLog.ldf'
GO
As you can see, it takes the last good backup from the sysdbmaintplan_history table in msdb and restores it to a database named test. I would like to use this sp to do the same thing with SQL Backup. It is backup history stored in a table somewhere? If not, any other suggestions?
Thanks
I''ll have your very best pan galactic gargle blaster please
Comments
E.g. to retrieve the last backup file made by SQB for the pubs database, you could use something like this:
SELECT TOP 1 a.physical_device_name
FROM msdb..backupmediafamily a
INNER JOIN msdb..backupset b ON a.media_set_id = b.media_set_id
WHERE b.database_name = 'pubs'
AND a.device_type = 7
ORDER BY a.media_set_id DESC
Look in the BOL for details on the backup tables (backupmediafamily, backupset, backupfile). To tell a native backup from a SQB backup, look at the device_type value in the backupmediafamily table. A value of 7 indicates a virtual device backup. This indicates a SQB backup, unless you are using another specialized database backup software e.g. database agents for tape backup software.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
-- build_restore_script 'MDSHC'
create procedure build_restore_script
@dbname sysname = '' --restrict restore statements to 1 database
as
--
-- This stored procedure was written by Greg Larsen for Washington State Department of Health.
-- Date: 12/16/2001
--
-- Description:
-- This stored procedure generates TSQL script that will restore all the databases
-- on the current SQL Server. This stored procedure takes into account when the last
-- full and differential backups where taken, and how many transaction log backups
-- have been taken since the last database backup, based on the information in
-- the msdb database.
--
-- Modified:
-- Date Who Description
-- 3 Jan 2005 fhanlon Added @dbname parameter
-- 8 Mar 2005 fhanlon Added support for compressed backups
--
-- Declare variables used in SP
declare @cmd nvarchar (1000)
declare @cmd1 nvarchar (1000)
declare @db nvarchar(128)
declare @filename nvarchar(128)
declare @cnt int
declare @num_processed int
declare @name nvarchar(128)
declare @physical_device_name nvarchar(128)
declare @backup_start_date datetime
declare @type char(1)
-- Turn off the row number message
set nocount on
-- SECTION 1
-- Define cursor to hold all the different databases for the restore script will be built
IF @dbname = ''
declare db cursor for
select name from master.dbo.sysdatabases
where name not in ('tempdb', 'model')
ELSE
declare db cursor for
select name from master.dbo.sysdatabases
where name = @dbname
-- Create a global temporary table that will hold the name of the backup, the database name, and the type of database backup.
create table ##backupnames (
name nvarchar(100),
database_name nvarchar(100),
type char(1) )
-- Open cursor containing list of database names.
open db
fetch next from db into @db
-- Process until no more databases are left
@FETCH_STATUS = 0
BEGIN
-- Subsection 1A
-- initialize the physical device name
set @physical_device_name = ''
-- get the name of the last full database backup
select @physical_device_name = physical_device_name , @backup_start_date = backup_start_date
from msdb..backupset a join msdb..backupmediaset b on a.media_set_id = b.media_set_id
join msdb..backupmediafamily c on a.media_set_id = c.media_set_id
where type='D' and backup_start_date =
(select top 1 backup_start_date from msdb..backupset
where @db = database_name and type = 'D'
order by backup_start_date desc)
-- Did a full database backup name get found
if @physical_device_name <> ''
begin
-- Build command to place a record in table that holds backup names
select @cmd = 'insert into ##backupnames values (' + char(39) +
@physical_device_name + char(39) + ',' + char(39) + @db + char(39) + ',' +
char(39) + 'D' + char(39)+ ')'
-- Execute command to place a record in table that holds backup names
exec sp_executesql @cmd
end
-- Subsection 1B
-- Reset the physical device name
set @physical_device_name = ''
-- Find the last differential database backup
select @physical_device_name = physical_device_name, @backup_start_date = backup_start_date
from msdb..backupset a join msdb..backupmediaset b on a.media_set_id = b.media_set_id
join msdb..backupmediafamily c on a.media_set_id = c.media_set_id
where type='I' and backup_start_date =
(select top 1 backup_start_date from msdb..backupset
where @db = database_name and type = 'I' and backup_start_date > @backup_start_date
order by backup_start_date desc)
-- Did a differential backup name get found
if @physical_device_name <> ''
begin
-- Build command to place a record in table that holds backup names
select @cmd = 'insert into ##backupnames values (' + char(39) +
@physical_device_name + char(39) + ',' + char(39) + @db + char(39) + ',' +
char(39) + 'I' + char(39)+ ')'
-- Execute command to place a record in table that holds backup names
exec sp_executesql @cmd
end
-- Subsection 1C
-- Build command to place records in table to hold backup names for all
-- transaction log backups from the last database backup
set @cmd = 'insert into ##backupnames select physical_device_name,' + char(39) + @db + char(39) +
',' + char(39) + 'l' + char(39) +
'from msdb..backupset a join msdb..backupmediaset b on a.media_set_id = b.media_set_id join ' +
'msdb..backupmediafamily c on a.media_set_id = c.media_set_id ' +
'where type=' + char(39) + 'L' + char(39) + 'and backup_start_date > @backup_start_dat and' +
char(39) + @db + char(39) + ' = database_name'
-- Execute command to place records in table to hold backup names
-- for all transaction log backups from the last database backup
exec sp_executesql @cmd,@params=N'@backup_start_dat datetime', @backup_start_dat = @backup_start_date
-- get next database to process
fetch next from db into @db
end
-- close
close db
-- Section B
open db
-- Get first recod from database list cursor
fetch next from db into @db
-- Generate Heading in Restore script
print '-- Restore All databases'
-- Process all databases
@FETCH_STATUS = 0
BEGIN
-- define cursor for all database and log backups for specific database being processed
declare backup_name cursor for
select name,type from ##backupnames where database_name = @db
-- Open cursor containing list of database backups for specific database being processed
open backup_name
-- Determine the number of different backups available for specific database being processed
select @cnt = count(*) from ##backupnames where database_name = @db
-- Get first database backup for specific database being processed
fetch next from backup_name into @physical_device_name, @type
-- Set counter to track the number of backups processed
set @num_processed = 0
-- Process until no more database backups exist for specific database being processed
@FETCH_STATUS = 0
BEGIN
-- Increment the counter to track the number of backups processed
set @num_processed = @num_processed + 1
-- Is the number of database backup processed the same as the number of different backups
-- available for specific database being processed?
-- If so, is the type of backup currently being processed a transaction log backup?
if UPPER(@type) = 'L'
-- build restore command to restore the last transaction log
-- If extension is 'sqb' assume backup is compressed
if LOWER(RIGHT(@physical_device_name,3)) ='sqb'
select @cmd = 'EXEC master.dbo.sqlbackup ' + char(39)
+ '-SQL "RESTORE LOG ' + rtrim(@db) + char(13) +
+ ' FROM DISK = ' + char(39) + char(39) +
RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) +
char(39) +char(39) + char(13) + ' WITH RECOVERY"' + char(39)
else
select @cmd = 'RESTORE LOG ' + rtrim(@db) + char(13) +
' FROM DISK = ' + char(39) +
RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) +
char(39) + char(13) + ' WITH RECOVERY'
else
-- Last backup was not a transaction log backup
-- Build restore command to restore the last database backup
-- If extension is 'sqb' assume backup is compressed
if LOWER(RIGHT(@physical_device_name,3)) ='sqb'
select @cmd = 'EXEC master.dbo.sqlbackup ' + char(39)
+ '-SQL "RESTORE DATABASE ' + rtrim(@db) + char(13) +
+ ' FROM DISK = ' + char(39) + char(39) +
RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) +
char(39) +char(39) + char(13) + ' WITH REPLACE, RECOVERY"' + char(39)
else
select @cmd = 'RESTORE DATABASE ' + rtrim(@db) + char(13) +
' from disk = ' + char(39) +
RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) +
char(39) + char(13) + ' WITH REPLACE, RECOVERY'
if @cnt <> @num_processed -- add norecovery clause if not last statement
select @cmd = REPLACE(@cmd, 'RECOVERY', 'NORECOVERY')
-- if it is master comment line out
if @db = 'master'
set @cmd = '/* ' + char(13) + @cmd + char(13) + '*/'
-- Generate the restore command and other commands for restore script
print @cmd
print 'go'
print ' '
-- Get next database backup to process
fetch next from backup_name into @physical_device_name, @type
end
-- Close and deallocate database backup name cursor for current database being processed
close backup_name
deallocate backup_name
-- Get next database to process
fetch next from db into @db
end
-- Close and deallocate cursor containing list of databases to process
close db
deallocate db
-- Drop global temporary table
drop table ##backupnames
GO
The bad news is that I do use backup exec which creates a device_type 7. Is there any other way to differentiate SQL Backup Jobs?
Cheers
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8