Results 1 to 11 of 11
  1. #1
    Join Date
    May 2010
    Location
    Carlisle, UK
    Posts
    3

    Unanswered: Automate a restore with latest backup

    Hi

    I've got a small (non-urgent) problem which my limited T-SQL cannot fix! I can just about get away with SELECT statements and simple joins!

    We've got 2 databases running on SQL Server 2005 SP3 one of which is the live data and one of which is the test system.

    The department using the databases currently call us when they would like a backup of the live database restored onto the test one for various reasons. They have asked if it is possible for us to automate the restore with the a copy of the latest backup each Saturday night (a backup taken each evening). I thought this may be possible with a maintenance plan but I can't see how.

    I've tried scripting it (see below) but the script SQL Server generates autopopulates with the filename of the latest backup taken.

    Code:
    RESTORE DATABASE [QTTest] FROM  DISK = N'\\mightymouse\d$\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\QTLive_backup_201005192030.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
    GO
    This code works fine but having to recreate/alter the script each time it's due to run kind of defeats the perpose of automating it! I've tried subsituting 'QTLive_backup_201005192030.bak' with '*.bak' but it errors out.

    There is only 1 .bak file in the backup folder as the previous one gets deleted out after the backup tape picks it up, so there shouldn't be a problem with the wildcard seeing more than 1 file to restore from.

    Therefore, is there a way to get the '*.bak' wildcard working or perhaps a way to get the backup job (code below) to stop appending the date/time stamp to the file name?

    Code:
    BACKUP DATABASE [QTLive] TO  DISK = N'D:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\QTLive_backup_201005201646.bak' WITH NOFORMAT, NOINIT,  NAME = N'QTLive_backup_20100520164639', SKIP, REWIND, NOUNLOAD,  STATS = 10
    GO
    declare @backupSetId as int
    select @backupSetId = position from msdb..backupset where database_name=N'QTLive' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'QTLive' )
    if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''QTLive'' not found.', 16, 1) end
    RESTORE VERIFYONLY FROM  DISK = N'D:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\QTLive_backup_201005201646.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
    Any help much appreciated, tia

    Cheers

    Andy

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    should be easy (if incredibly dangerous).

    Code:
    alter database [test database] set offline with rollback immediate
    
    declare @devicename nvarchar(512)
    declare @dbname sysname
    set @dbname = 'live database'
    
    select @devicename = f.physical_device_name
    from backupmediafamily f join
    	backupset s on f.media_set_id = s.media_set_id
    where s.database_name = @dbname
      and type = 'D' -- Full dump
      and backup_start_date = (select max(backup_start_date) from backupset where database_name = @dbname and type = 'D')
    
    restore database [test database] from disk = @devicename with replace
    You should do some testing of this before you try this. I highly recommend commenting out the actual restore line, and just printing out what you would get for the restore command, evaluate, then test.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    EDIT: Oh, this should also be run from the msdb database, where all the backup catalog is kept.

  4. #4
    Join Date
    May 2010
    Location
    Carlisle, UK
    Posts
    3
    Many thanks MCrowley.

    Comment about danger noted, could you explain a bit more about why it is dangerous? If the risks outweight the benefits we'll continue to do it manually.

    Cheers
    Andy

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This isn't as easy as you might think. Just spent my lunch cracking it, and there is more work to be done (especially WRT error handling).
    A lot of the difficulty is that the live and test DBs on are on the same server. Of course, it is best practice to have these on two separate servers (and a third server for dev).

    Code:
    IF EXISTS (SELECT NULL FROM sys.databases WHERE name = 'restore_test_live')
    BEGIN
        EXEC ('DROP DATABASE restore_test_live')
    END
    
    IF EXISTS (SELECT NULL FROM sys.databases WHERE name = 'restore_test_dev')
    BEGIN
        EXEC ('DROP DATABASE restore_test_dev')
    END
    
    CREATE DATABASE restore_test_live
    GO
    
    CREATE DATABASE restore_test_dev
    GO
    Code:
    DECLARE    @physical_path_data     AS VARCHAR(200)
          , @physical_path_log      AS VARCHAR(200)
          , @logical_name_data      AS VARCHAR(200)
          , @logical_name_log       AS VARCHAR(200)
          , @logical_name_live_data AS VARCHAR(200)
          , @logical_name_live_log  AS VARCHAR(200)
          
    BEGIN TRY
        
        SELECT  @physical_path_data     = MAX(CASE WHEN type_desc = 'ROWS'  THEN physical_name  ELSE NULL END)
              , @physical_path_log      = MAX(CASE WHEN type_desc = 'LOG'   THEN physical_name  ELSE NULL END)
              , @logical_name_data      = MAX(CASE WHEN type_desc = 'ROWS'  THEN name           ELSE NULL END)
              , @logical_name_log       = MAX(CASE WHEN type_desc = 'LOG'   THEN name           ELSE NULL END)
        FROM    restore_test_dev.sys.database_files
        
        SELECT  @logical_name_live_data  = MAX(CASE WHEN type_desc = 'ROWS'  THEN name           ELSE NULL END)
              , @logical_name_live_log   = MAX(CASE WHEN type_desc = 'LOG'   THEN name           ELSE NULL END)
        FROM    restore_test_live.sys.database_files
        
        SELECT  @physical_path_data
              , @physical_path_log
              , @logical_name_data
              , @logical_name_log
              , @logical_name_live_data
              , @logical_name_live_log
        
        BACKUP DATABASE restore_test_live TO DISK = 'C:\sql_server\backups\restore_test_live.bak'
        
        PRINT   'Backed up'
        
        ALTER DATABASE restore_test_dev SET OFFLINE WITH ROLLBACK IMMEDIATE
    
        PRINT   'Offline'
        
        RESTORE DATABASE restore_test_dev
            FROM DISK = 'C:\sql_server\backups\restore_test_live.bak'
            WITH REPLACE, 
            MOVE @logical_name_live_data    TO @physical_path_data, 
            MOVE @logical_name_live_log     TO @physical_path_log
    
        PRINT   'Restored'
            
        EXEC    ('ALTER DATABASE restore_test_dev
                MODIFY FILE
                (NAME = ''' + @logical_name_live_data + ''', NEWNAME = ''' + @logical_name_data + ''')')
        
        EXEC    ('ALTER DATABASE restore_test_dev
                MODIFY FILE
                (NAME = ''' + @logical_name_live_log + ''', NEWNAME = ''' + @logical_name_log+ ''')')
    
        SELECT  physical_name, name
        FROM    restore_test_dev.sys.database_files
        
    END TRY
    BEGIN CATCH
        
        PRINT   '>>>>>>>>>>>>>>>Ooooh - error'
        PRINT   ERROR_MESSAGE()
        ALTER DATABASE restore_test_dev SET ONLINE
    
    END CATCH

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Baseline View Post
    Many thanks MCrowley.

    Comment about danger noted, could you explain a bit more about why it is dangerous? If the risks outweight the benefits we'll continue to do it manually.

    Cheers
    Andy
    I think one of the issues MCrowley is alluding to is that these sort of operations are pretty heavy duty to leave to an automated process and could potentially leave things pretty screwed up if you aren't careful. You know the saying - to cock up you just need a human but to destroy something completely takes automation.

    Anyway, to give you an idea if you run MCrowley's code it will fail and your test db is in standby. Panic and (for example) drop it and you have orphaned the database files.

  7. #7
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Concerning "dangerous."

    Someone configures a test scenario on your test system.

    it takes four days to set up the test scenario.

    They plan on running the final test on Friday.

    They get sick and don't come in on Friday.

    Over the weekend, the automated backup and restore wipe-out the configured test scenario.

    If you are backing-up your test database, no big problem. If not, dangerous.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  8. #8
    Join Date
    May 2010
    Location
    Carlisle, UK
    Posts
    3
    Many thanks. I'll think it over at the weekend and run tests on an old server I've got handy, but I think in light of comments maybe leaving it as a manual job would be best to avoid any problems

    Cheers

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Quote Originally Posted by pootle flump
    Anyway, to give you an idea if you run MCrowley's code it will fail and your test db is in standby. Panic and (for example) drop it and you have orphaned the database files.
    Your confidence in my code is underwhelming. I admit, I missed the part about moving the physical files, but they are not likely to be changing very often, and as such could be hard-coded. On the plus side, your code is very "enterprisey" ;-)

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It had nothing to do with confidence - I ran it and the test database was left in standby. Naturally I panicked. The rest I put down to experience

    WTF is enterprisey code? Should I be demanding a pay rise?

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by pootle flump View Post
    Should I be demanding a pay rise?
    Certainly. Let us know how that works for you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •