Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've noticed that a lot of times if a database restore is going to fail, it will fail before it even starts restoring in SQL Server 2008 Management Studio (displaying an error about space or something similar). I need to set up a schedule to restore a database overnight, but am not sure if I'll need to monitor it overnight or not. The hardware is stable enough that I believe this won't be a point of failure.

A better question might be: What checks are done against a backup prior to the restore process beginning?

share|improve this question
One thing to watch out for is that your restore will not work if there are users using the DB. You can kick them out and give exclusive access to the restore job, but that will be a business decision. – StanleyJohns Jul 3 '13 at 2:05
So, the hardware WILL fail. Murphies law, you know. – TomTom Jul 3 '13 at 5:28
"displaying an error about space or something similar" Not very useful. Have you simply run out of disk space? C'mon, help us to help you – gbn Jul 3 '13 at 6:59
I didn't get an error, or else I would have mentioned it specifically. I have plenty of disk space, made sure no one else was on the database, and was wondering what sort of errors could occur after the restore process has begun (rather than towards the beginning, which is when disk space errors occur). – Sean Long Jul 3 '13 at 12:29
There's always a chance for the network connection to drop, if you restore from network storage. – The Confused DBA May 12 '15 at 14:10

Are you restoring to an existing instance of the DB? If so then space issues shouldn't be particularly relevant. At our place we run the following as job steps in restore jobs:

USE [master]


DECLARE @Kill nvarchar(1024) = ''

SELECT @Kill = @Kill+'KILL '+CAST(spid AS varchar(4))+'; '
FROM sys.sysprocesses s 
JOIN sys.databases d ON s.dbid = d.database_id
WHERE name = 'nameofDB'



This will kill all system processes (spids) against your DB. As someone else mentioned ensure there is an agreement with users that this will occur, or that other system processes require it etc.

Hopefully these steps will add some resiliance.

NB: Assumes you run the DB in multi_user !

share|improve this answer
There is no need to KILL processes after setting SINGLE_USER, and You can just set the database OFFLINE instead and then do a RESTORE. – Roi Gavish Jul 4 '13 at 13:45
Oh I know it's overkill.. :) – Paul Jul 4 '13 at 13:50

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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