SQL Server Tip of the Week – Avoiding restore errors

Quick tip for this week as I have not been home much to contemplate writing something epic, although I have a couple lined up for futureā€¦

This week, my quick tip is for avoiding errors when restoring over an existing database.

It is common practice for many enterprise applications to have the concept of a Live/Production environment and a Test/Development environment. Some products will see you have around five or six different ones, especially during implementations of new systems or upgrades. Typically, I work in a world where you have three environments with your data and one with sample/demo data.

I will be writing a more in depth backup/restore guide, but I wanted to cover some generics to start with.

One common task is to take a copy of live data and restore it into a test or development area so that you can try things out without risking critical data. Some companies may even have policies where they do this weekly or monthly to have a “pristine” copy of their data. When this is, a repetitive task it’s sometimes best to script it out, especially where the process is identical each time, the problem with this though is ensuring the destination database is in a state where the restore can happen. And whilst we can script in the Single-User mode and various other options, we cannot 100% safely validate and kill existing sessions, what if a session to that database is from a business critical system you forgot to switch off before the process began?

Therefore, my recommendation is to use the SQL Server Activity Monitor. This tool is also a starting point for identifying performance issues such as long running queries or table locks.

  1. To access the Activity Monitor load up SQL Server Management Studio, log in, and then right click the Server node (top) in the Object Explorer, under the menu you will see Activity Monitor:

  2. To find whether your destination Database has any connections to it you need to expand the processes section, in there you will see a list of every database connection.

  3. A quick way to see if your database has any connections is whether or not it appears in the database column, you can click on the header to filter on DB name, if yours is not there then it doesn’t have any connections, if it is then filter on it so you can see what connections you have:

  4. If there is only 1 or 2 lingering connections you could right click on them row by row and kill the connection, however if, like above, there are many, I would suggest looking at the Hostname column and seeing what is running on that machine (as the user listed in Login column) to try to identify what you forgot to switch off before attempting the db overwrite.

This tip has saved me many times, it give you a quick view of access to a database, and is the first port of call should you get any “can’t overwrite, file in use” style errors.