Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
1  
So, the hardware WILL fail. Murphies law, you know. –  TomTom Jul 3 '13 at 5:28
1  
"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
add comment

1 Answer

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]
GO

ALTER DATABASE nameofDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

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'

EXEC(@Kill)
GO


ALTER DATABASE nameofDB SET MULTI_USER;
GO

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. –  Justicator Jul 4 '13 at 13:45
    
Oh I know it's overkill.. :) –  Paul Jul 4 '13 at 13:50
add comment

Your Answer

 
discard

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.