Sql-server – Can a database restore fail after it has begun

restoresql serversql-server-2008

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?

Best 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 !

Related Question