patternsqlMinor
What can I add to a server to make SQL restores faster?
Viewed 0 times
canrestoreswhatsqlmakefasterserveradd
Problem
I have a 2.8TB SQL database (mostly data files, some 400GB of log files) that currently takes around 9 hours to restore. This database is used for testing purposes and must be deleted and restored from a backup between each run, to make sure we're always starting from the same point.
My question is, the server currently has 12 cores and 92GB of RAM, with a RAID 5 disk subsystem that the database is on. What areas usually cause bottlenecks for SQL restore processes? Is it the disk, memory, or CPU?
My question is, the server currently has 12 cores and 92GB of RAM, with a RAID 5 disk subsystem that the database is on. What areas usually cause bottlenecks for SQL restore processes? Is it the disk, memory, or CPU?
Solution
Don't do a backup and restore; use SQL Server Snapshots. It takes a lot of disk space to store a sparse file the same size as the files you've snapshotted, but rolling back is hundreds of times faster.
They are available in SQL Server Enterprise and SQL Server Developer editions.
They are available in SQL Server Enterprise and SQL Server Developer editions.
Context
StackExchange Database Administrators Q#44796, answer score: 7
Revisions (0)
No revisions yet.