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.

Andriy M
  • 23,261
  • 6
  • 60
  • 103
Ageis
  • 199
  • 6

2 Answers2

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.

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
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

KASQLDBA
  • 7,203
  • 6
  • 30
  • 53