2

We have a 2gb live database that takes forever restore whenever I need some live data to test something. If I use idera it takes 5 mins at most. When I use the restore database command it can take almost an hour. How is idera able to do so much faster?

I use the following to restore:

RESTORE DATABASE dbname
  FROM DISK = 'path'
  WITH 
  MOVE 'CMTS_dat' TO 'C:\db\MARTIN_Latest.mdf',     
  MOVE 'CMTS_log' TO 'C:\db\MARTIN_Latest_log.ldf'
  ,REPLACE

If it has any data partitions then I'll use MOVE to do the partitions as well.

Improve this question
5
  • 3
    I doubt there's any magic here. Did you intercept what RESTORE command Idera is actually sending to your server, and compare it with the one you wrote? Did you check what your long-running restore was waiting on while it was running?
    – Aaron Bertrand
    Commented Apr 30, 2015 at 12:50
  • ok aaron how do I do all that. I'm not a dba. I'm very new to databases. Its not usually a junior dev job to restore databases
    – Ageis
    Commented Apr 30, 2015 at 12:53
  • So it seems like Idera is not actually performing a full restore before making the database at least partially available to you. You can do a similar thing yourself with a little more planning using piecemeal restores.
    – Aaron Bertrand
    Commented Apr 30, 2015 at 13:18
  • I'd be curious though, how big is your database and how slow is your disk? Would be interesting to see where the restore is taking time such that it takes an hour. Do you have instant file initialization enabled? You can turn on trace flag 3004 to get information about this in the error log. Also with 3604 and 3014 enabled you can watch the output (no timestamps are printed) - this can quickly show you if, for example, writing the history to MSDB is slowing down the process.
    – Aaron Bertrand
    Commented Apr 30, 2015 at 13:22
  • 1
    As a side note, I would recommend you to enable Instant File Initialization to speed up your restore. Also, check if this database has unusually large number of VLFs.
    – Kin Shah
    Commented Apr 30, 2015 at 13:22

2 Answers 2

Reset to default
2

From Idera's website, it appears SQL Safe inserts a "shim" (a piece of code, be it CLR or native code) into SQL Server to support what they refer to as "Instant Restore":

Instant Restore

Patented Instant Restore technology brings your database online immediately—virtually eliminating application downtime. This can save you huge amounts of time when getting back online in a disaster or searching old backups for a piece of lost data. Once online, your database acts as a fully functional database, supporting all read and write operations. SQL Safe streams data from the backup file “on demand” to support applications and user requests while completing the restore operation in the background.

This indicates, to me, that the database is not actually fully restored prior to the database becoming accessible to client queries.

I'd be just a little concerned about this style of recovery for production since anything that has the possibility of reducing ACID compliance is a risk. Using this restore capability for development or support seems like a great idea since you no longer need to wait for recovery to complete.

Improve this answer
0

The 3rd party agents(Like Idera in you're case) will often have "brick level" backups and restores which allow you to restore parts of a database (tables, etc.) rather than only the entire database. They also do not require you to stage the output (doubling your disk requirement) by backing up the database directly.

They will also provide you additional data level compression and encryption options unavailable through SQL Server's native backup mechanism. This is much favourable to options at the NTFS level.

But not to forget, SQL 2005 and below benefit from many third-party backup compression capabilities and from SQL 2008 and onwards you get somewhat the same output ( like less space utilized backups and instant restore).

So, if you are using SQL 2008 and above you should be good with faster restore, making sure the feature is ON

Improve this answer
1
  • I did notice the databse I was restoring had it's compatibility level set as 2005. I presume this means I don't get any of the benefits sql server 2008 offers?
    – Ageis
    Commented May 4, 2015 at 6:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.