none
Script database recovery RRS feed

  • Question

  • Hello our SQL Server DBA is missing
    so as the Oracle DBA I got asked to the following.

    Please note I am aware that things like having backups and
    data,log files on the same disk are not ideal


    We want to move ALL databases to different storage
    detach and attach / copying files is too slow across network for large USER databases
    the backups are already on a drive where we need to move databases to
    so we want to use back an restore to do a MOVE for all user Databases


    -- Step 1 table Full Backup

    BACKUP DATABASE Test
    TO DISK = 'C:\SQLSERVER_BACKUPS\Test\Test.bak'
    WITH NOFORMAT,
    MEDIANAME = 'TestBackUp'
    NAME = 'Full Backup Of Test'
    GO

    -- there could be a number of LOG back ups and diff backups occur here
    -- between Step 1 and 2


    -- Step2 About to do restore and database is online

    BACKUP LOG Test
    TO DISK = 'C:\SQLSERVER_BACKUPS\Test\Test.trn'
    WITH NOFORMAT,
    MEDIANAME = 'TestLogBackUp'
    NAME = 'Backup Of Test Log'
    GO


    -- Step3
    RESTORE filelistonly
    FROM DISK = 'C:\SQLSERVER_BACKUPS\Test\Test.bak'
    RESTORE DATABASE Test
    FROM DISK = 'C:\SQLSERVER_BACKUPS\Test\Test.bak'
    WITH NORECOVERY
    MOVE 'Test'  TO 'C:\SQLDATA\Test.mdf'
    MOVE 'Test_log' TO 'C:\SQLDATA\Test_log.ldf'
    GO

    -- Step 4
    RESTORE LOG [Test] from disk = 'C:\SQLSERVER_BACKUPS\Test\Test.trn'
    WITH RECOVERY
    GO


    Okay my question is
    Is there anyway I can write a T-SQL script to
    do the following

    For Each Database (excluding system databases) LOOP
        Determine last full backup
        Determine all log backups between full backup and now
        Generate script to perform LOG Backup Now
        Generate script to preform Restore using FULL backup and all LOG BAckUps
    END LOOP

     

    Tuesday, January 10, 2012 1:17 AM

Answers

  • Yes you can go for sp_MSforeachdb as well. but again it is said to be undocumented in MSDN, try searching on http://msdn.microsoft.com/en-us/library/ms123401.aspx, I didnot find it there.

     

    Yes but in SSMS you can get the details about it 

    sp_helptext 'sp_msforeachdb '
    

    I suggest refer this http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c45a7f66-1bef-4b96-87cc-62e556386478

    its mentioned "These system stored procedures are not meant for general consumption so it will not work for all scenarios. It is intended for internal usage only.'


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
    • Marked as answer by Stephanie Lv Wednesday, January 11, 2012 8:36 AM
    Tuesday, January 10, 2012 8:32 AM
  • There's really no need for a temp table. You can use a cursor that loop sys.databases and in there you construct the backup (or whatever) command in a string variable which you then EXEC(@sql). So, the tamp table approach is just an alternative to use a cursor. Sp_MDforeachdb is just a (undocumented) system stored procedure that uses a cursor and dynamic SQL. I wouldn't lose sleep over whcih approach to use. In some cases, SQL Server don't accept a variable on a command (as you see variable name is used in the backup command in the temp table example), so a cursor/dynamic SQL approach is more widealy usable. Dynamic SQL has many drawbacks, so we don't recommend it for general application usage, but for system programming like this is it often a good choice.
    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by Stephanie Lv Wednesday, January 11, 2012 8:36 AM
    Tuesday, January 10, 2012 10:16 AM
    Moderator

All replies

  • To be more specific what system tables could I look at to script the above requirements

    Thankyou

    Tuesday, January 10, 2012 1:20 AM
  • Hi,

    Just a thought. Since you state that you need to move the files and copying across the network is too slow does this mean that you are in fact moving the databases to a new server and SQL instance?

    If so why not restore the next full backup or the database to the new (secondary server) and then enable log shipping between the current (primary server) to the new server so that the log shipping feature keeps the new server up to date until you are ready to switch?

    http://msdn.microsoft.com/en-us/library/ms188708.aspx


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog or Twitter.
    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, January 10, 2012 1:53 AM
  • Hi Sean

    Thanks for your suggestion.

    Same server, same instance just different disks.

    I will go and have a read ofthe link

    Cheers

    Tuesday, January 10, 2012 3:10 AM
  • If everything is staying within the same server you should not have any delays in moving the files due to the network. What size are your MDF, NDF and LDF files for the databases you need to migrate? I can move large files such as ~50GB in seconds between disks on the same server via RDP sessions.

    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog or Twitter.
    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, January 10, 2012 3:21 AM
  • Okay my question is 

    Is there anyway I can write a T-SQL script to
    do the following

    For Each Database (excluding system databases) LOOP
        Determine last full backup
        Determine all log backups between full backup and now
        Generate script to perform LOG Backup Now
        Generate script to preform Restore using FULL backup and all LOG BAckUps
    END LOOP

     

    Yes you can write a TSQL script to do above tasks

    Also I would like to tell you that the following system table holds many information about the backups

    select * from msdb.dbo.backupset
    

    and the following is the example I posted some years back @ http://www.mssqltips.com/tipcomments.asp?tip=1070 to take the backup of all the databases in a server, this is just for your references, so that you can edit as per your requirements

     

    DECLARE @name VARCHAR(50) -- database name 
    DECLARE @path VARCHAR(256) -- path for backup files 
    DECLARE @fileName NVARCHAR(256) -- filename for backup 
    DECLARE @fileDate VARCHAR(20) -- used for file name
     
    SET @path = 'd:\Backup\' 
    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
    SELECT name,flag=0 into #tempbackup FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')
    set rowcount 1
    WHILE (exists(SELECT * FROM #tempbackup WHERE flag=0))
     BEGIN 
           Select @name=name from #tempbackup WHERE flag=0
           SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
           BACKUP DATABASE @name TO DISK = @fileName        
           Update #tempbackup set flag=1 WHERE flag=0 AND name=@name
     END  
    set rowcount 0
    drop table #tempbackup
    



    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
    • Proposed as answer by CK Shinde Tuesday, January 10, 2012 8:49 AM
    Tuesday, January 10, 2012 3:24 AM
  • I agree it should not take long, to copy the files across but it does and that is another issue.

    A  5Gb file copy took 4 hours.... there are some very large files.

    I just inheirited this thing and while networking is gettign sorted I need to move these files and copying them isn't a feasible option.

     

    Tuesday, January 10, 2012 3:37 AM
  • hey thanks Manish,

    I appreciate your example thankyou. I ddi manage to get the loop scripted similar to your

    I was more after which the system tables were to find out info about the backups.

    I have just found these

    Tables in the msdb Database

    These tables store information used by database backup and restore operations.

    backupfile restorefile
    backupmediafamily restorefilegroup
    backupmediaset restorehistory
    backupset  
    Tuesday, January 10, 2012 3:40 AM
  • These tables store information used by database backup and restore operations.

    backupfile restorefile
    backupmediafamily restorefilegroup
    backupmediaset restorehistory
    backupset  

    You are welcome and Yes you are right Rockdeejay, about the above tables of MSDB,

    Let us know if you need any thing else.


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
    Tuesday, January 10, 2012 3:46 AM
  • manish,

    I don't know if you like either of these  better then using a temp table

    exec

    sp_MSforeachdb

    'USE [?]IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''

    BEGIN SELECT ''?''

    END'

     

    -- more cryptic

    exec sp_MSforeachdb

    'IF DB_ID(''?'') > 4  

        DO_SOMETHING

    END'

     

     

     

    Tuesday, January 10, 2012 6:09 AM
  • Yes you can go for sp_MSforeachdb as well. but again it is said to be undocumented in MSDN, try searching on http://msdn.microsoft.com/en-us/library/ms123401.aspx, I didnot find it there.

     

    Yes but in SSMS you can get the details about it 

    sp_helptext 'sp_msforeachdb '
    

    I suggest refer this http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c45a7f66-1bef-4b96-87cc-62e556386478

    its mentioned "These system stored procedures are not meant for general consumption so it will not work for all scenarios. It is intended for internal usage only.'


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
    • Marked as answer by Stephanie Lv Wednesday, January 11, 2012 8:36 AM
    Tuesday, January 10, 2012 8:32 AM
  • There's really no need for a temp table. You can use a cursor that loop sys.databases and in there you construct the backup (or whatever) command in a string variable which you then EXEC(@sql). So, the tamp table approach is just an alternative to use a cursor. Sp_MDforeachdb is just a (undocumented) system stored procedure that uses a cursor and dynamic SQL. I wouldn't lose sleep over whcih approach to use. In some cases, SQL Server don't accept a variable on a command (as you see variable name is used in the backup command in the temp table example), so a cursor/dynamic SQL approach is more widealy usable. Dynamic SQL has many drawbacks, so we don't recommend it for general application usage, but for system programming like this is it often a good choice.
    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by Stephanie Lv Wednesday, January 11, 2012 8:36 AM
    Tuesday, January 10, 2012 10:16 AM
    Moderator
  • Cool , Thanks everyone!.

    Wednesday, January 11, 2012 12:16 AM
  • The move using full backup and tLogs worked really well I "moved"  400Gb in less then 3 hours.

    Trying to detach, copy and attach would not have worked... last time I copied a 4Gb files across the network it took 4 hours!

    The 3 hours it took was total time, I acutally "moved" via recovery a 200Gb in 36minutes

     

    Friday, January 20, 2012 4:02 AM