locked
How to reduce DB restore hrs RRS feed

  • Question

  • Hi,

                We are facing some slower DB restoration in one of the production Server. Is there any best method, where we can to reduce DB restoration ?

    Let me give overview :

    1. Our database size is 8 TB;  We are taking a split backup, which is run 2 hrs (Split backup 12 files + Verify restore only) and files store in local drive.
    2. To restore 4 TB backup files in Server B, it runs 14 to 15 hrs, which is too long in terms of recovery / emergency situation.

    Server Overview:

    SQL Server 2008 R2 – Enterprise edition.

    DB : Simple recovery model

    Ram : 1 TB

    Server - DL980 (fast track architecture)

    Thanks,

    Dhanapal

    Monday, April 29, 2013 12:51 PM

Answers

  • Hello,

    It seems like you're facing two separate issues. The first being that the backups/restores are taking what is considered a long time and the second being you can't meet the DR SLA.

    For the speed of backups/restores at the IO level:

    1. Make sure backup compression is enabled if you aren't CPU bound

    2. Split backups across multiple mountpoints/drives as read/write thread will be created for each

    3. Make sure your IO subsystem can handle the load, i.e. isn't the bottleneck

    4. Tune using Buffercount and Maxtransize if needed


    For the speed of backups/restores at the database level:

    1. Make sure instant file initialization is enabled (log files won't use IFI)

    2. Keep the VLF number and size of log file as low and needed and possible

    3. Use replace with restores when possible

    4. Schedule backups when long running changes/maintenance is not running

    5. Have a DR solution in place (this will give the best boost for RTO)

    6. Partial database availability (piecemeal restores)

    I would look into some type of DR architecture(s). For example, if log shipping is setup then the RTO will be however long it takes to apply the last bit of logs... just an example.

    -Sean


    Sean Gallardy | Blog | Twitter

    Monday, April 29, 2013 3:21 PM
    Answerer

All replies

  • Why dont you try using Litespeed tool to backup the database in compressed size?

    Refer this below website helps you.

    http://henkvandervalk.com/how-to-increase-the-sql-database-restore-speed-using-db-compression-and-solid-state-disks


    Regards http:\\sqldbatask.blogspot.com MCTS Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, April 29, 2013 1:11 PM
  • Hi,

    Try adding buffercount and maxtransfersize options to the restore command, i.e. 

    RESTORE DATABASE [MYDB] FROM  DISK = N'D:\MSSQL\MYDB.BAK' 
    WITH  FILE = 1,  
    NOUNLOAD,  
    REPLACE,  
    STATS = 10, 
    BUFFERCOUNT =50, 
    MAXTRANSFERSIZE = 4194304
    GO
    




    Thanks, Andrew

    Monday, April 29, 2013 1:42 PM
  • Hello,

    It seems like you're facing two separate issues. The first being that the backups/restores are taking what is considered a long time and the second being you can't meet the DR SLA.

    For the speed of backups/restores at the IO level:

    1. Make sure backup compression is enabled if you aren't CPU bound

    2. Split backups across multiple mountpoints/drives as read/write thread will be created for each

    3. Make sure your IO subsystem can handle the load, i.e. isn't the bottleneck

    4. Tune using Buffercount and Maxtransize if needed


    For the speed of backups/restores at the database level:

    1. Make sure instant file initialization is enabled (log files won't use IFI)

    2. Keep the VLF number and size of log file as low and needed and possible

    3. Use replace with restores when possible

    4. Schedule backups when long running changes/maintenance is not running

    5. Have a DR solution in place (this will give the best boost for RTO)

    6. Partial database availability (piecemeal restores)

    I would look into some type of DR architecture(s). For example, if log shipping is setup then the RTO will be however long it takes to apply the last bit of logs... just an example.

    -Sean


    Sean Gallardy | Blog | Twitter

    Monday, April 29, 2013 3:21 PM
    Answerer
  • You are dealing with huge backup size restore, in such cases instant file initialization is one of the good option to restore database quickly. Apart from this as suggested use REPLACE clause while restoring database.

    Rajesh


    • Edited by yama_rajesh Tuesday, April 30, 2013 2:49 AM
    Tuesday, April 30, 2013 2:48 AM