**self-hosted**
So when I get to the "restore database" part of a Live to TEST copy, sometimes it takes 20 mins to restore but other times it take HOURS! I am not sure what could be behind this and I had never done a database restore before this role so I don't know much about it outside of this process.
Any thought or ideas about with this might be happening? Is is a space issue on my server? Could is be network speed related??
Ashley Elliott
Database Administrator
St. Louis Symphony Orchestra
314-286-4198
ashleye@slso.org
That's definitely not normal, but it sounds like something you'll need to get into the weeds a bit to troubleshoot. I would say follow your instincts and start testing your assumptions and removing variables when possible. Is there any pattern to the long-running restores? How large is the backup file when you begin the restore? Are you using the network at all when you do the restore? Can you copy the backup file to your server first, and then start the restore so you know the network is not being used? How much space is free on your database server's disks? Which drives are being used to house the database data files and log files? What does the activity monitor in SSMS look like during a long-running restore? Is there lock contention or blocking happening?
Just a hunch, but what could be happening is that when you start the restore, there is an existing connection to the database with an open transaction, so nothing happens until the connection closes or the transaction is committed. What I prefer to do when restoring to my TEST database is to first put the database into restricted user mode so all existing connections are forced to close, and I can do the restore (and overwrite necessary T_DEFAULTS and such for the test environment) in peace before I allow connections again. The relevant incantations are:
USE [master];ALTER DATABASE [impresario] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;GO
RESTRICTED_USER allows only administrators to connect to the database. WITH ROLLBACK IMMEDIATE forces all non-admin connections to close (thus rolling-back any open transactions). Then once you're done:
USE [master];ALTER DATABASE [impresario] SET MULTI_USER;GO
MULTI_USER is the typical mode which allows connections from regular users. N.b. this method is similar to, but different from SINGLE_USER mode, which you will often see recommended, but has a couple of ways to shoot yourself in the foot attached to it so I recommend RESTRICTED_USER as a safer alternative.