5

Every night I create a backup (to disk) of a particular database. Subsequently that backup has to be restored to 2 different servers. These restores now happen sequentially and take about half an hour per restore.

Would it be possible/wise/faster to execute the 2 restores in parallel?

Improve this question
6
  • are you trying to replicate a database by backing up and restoring it, why don't you use mirroring or replication
    – AmmarR
    Jun 27, 2012 at 9:18
  • I know that there are better solutions available, but for now I should get this to work.
    – Stijn
    Jun 27, 2012 at 9:34
  • restoring the same file to 2 different databases on same server parallel is possible, but if you want to restore it to 2 different servers it wont be parallel, because the file has to be available on the server physically, so you will need to have a copy of the file on the 2 servers. and yeah possible as well... or if you have the file on a mapped drive and both servers can see the same file on the same mapped network location, again its possible.
    – AmmarR
    Jun 27, 2012 at 9:53
  • The backup file is located in a network folder, so it is accessible to both servers. What about performance? Will it be faster than restoring sequential?
    – Stijn
    Jun 27, 2012 at 10:34
  • hi tino, i added a Post below check it
    – AmmarR
    Jun 27, 2012 at 11:32

2 Answers 2

Reset to default
3

I am pretty sure that I have done this, though I didn't think twice about it at the time.

You definitely can restore a bak file using a UNC path. Mapping drive letters is to be avoided because the mapping must be done in the context of the SQL service account. Most people map the drive under their own account and are frustrated when the restore command doesn't work as they expect.

Naturally, there will be a little more load on the NIC on the server that holds the BAK file and there could be some contention if the database mdf and ldf files are on the same SAN, but these are probably minor problems, if you notice them at all.

Improve this answer
2

Usually its suggested that Using multiple backup devices for backup and restore operations enables SQL Server to use parallel I/O to increase the speed of backup and restore operations because each backup device can be written to or read from at the same time as other backup devices. For enterprises with large databases, using many backup devices can greatly reduce the time taken for backup and restore operations. SQL Server supports a maximum of 64 backup devices for a single backup operation.

this is the opposite of the way you are doing it, so yeah restoring 2 database from the same media device will effect performance because IO operations are happening on the same disk at the same time.

Copying the data and transaction log from the backup devices to the database and transaction log files is performed by reader/writer threads; one thread is assigned to each backup device.

performance increases with the number of backup devices and in your case its the opposite, so Performance decreases if you are using 1 backup device to restore 2 different databases

for more on Backup and Restore Performance in SQL Server check section Using Multiple Media or Devices

So i guess the answer to your question, is Its possible to restore 2 databases from same file but not Wise

Improve this answer
1
  • 2
    To add to this - It would be interesting to observe the performance restoring two databases on different servers from a single backup file simultaneously vs make a copy of the backup file on the same drive, restore two databases from backup.bak and backup-copy.bak and see which method is better. I can't test this at work, for we dont need such a process and can't test it at home too - dont have necessary setup :(
    – RK Kuppala
    Jun 27, 2012 at 11:43

Your Answer

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

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