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.
Dig Deeper on SQL Server Backup and Recovery
Related Q&A from Greg Robidoux
Synchronize databases on separate servers without using SANs. Learn options for server failover in SQL Server including log shipping, database ... Continue Reading
Avoid restoring your SQL Server database to solve the SQL Server error message "server out of memory." The problem could be the memory settings. Continue Reading
Restore backups in SQL Server 2000 and SQL Server 2005 with this command. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.