Automated dB Restore

dstergodstergo Posts: 4
edited May 13, 2005 12:56AM in SQL Backup Previous Versions
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
I''ll have your very best pan galactic gargle blaster please

Comments

  • peteypetey Posts: 2,358 New member
    The SQL Backup (SQB) backup history is stored in the msdb tables, just like regular SQL Server backups.

    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Here the stored proc I use. It was originall posted in www.sqlservercentral.com by Greg Larsen. I modified to work with SQL Backup. Take the output and paste it an new QA window to do the restore.


    -- 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
    Francis
  • Thanks Guys. This is all good. I will post back if I have any probs.
    I''ll have your very best pan galactic gargle blaster please
  • Petey

    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
    I''ll have your very best pan galactic gargle blaster please
  • peteypetey Posts: 2,358 New member
    How about using the physical device name? I'm sure the naming convention for the tape backups are different enough from the SQB ones to differentiate between the two.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.