I am only having a problem on a Windows 2003 machine (without SP1). Even if we close all the applications, some connected users (mostly sleeping state) remain in the server. This prevents me from restoring the operation whereas in Windows 2000, Windows XP and Windows 2003 with SP1, as soon as we close the connected applications, the number of connected users in SQL Sever are automatically removed and we are able to perform the restore.
ALTER DATABASE DatabaseName WITH ROLLBACK IMMEDIATE RESTORE DATABASE DatabaseName FROM DISK = 'C:\DatabaseBackup.bak'
The ALTER DATABASE command will automatically kill all connections to the database being restored. This command prevents the need to query master.dbo.sysprocesses to kill existing connections in order to complete the restore. For additional information reference the following articles:
In addition to these commands it is imperative to properly configure the databases involved in the backup and restore process. If you intend on performing log shipping, it is necessary to ensure your production database is in full recovery mode. The same would be true for the backup database.
It is also important to be aware of the STANDBY option for the RESTORE DATABASE command which enables SELECT statements to be issued against the database when a restore is not being issued, with the ability to restore subsequent databases. Two other options corresponding to the RESTORE DATABASE command are RECOVERY and NORECOVERY. By specifying NORECOVERY subsequent transaction logs can be restored and in order to have the database come online the last transaction log should have RECOVERY specified.
Do you have comments on this Ask the Expert Q&A? Let us know.
This was first published in June 2005