Q

Connected users preventing restore

I have a backup restore functionality in my application that is used to perform database backup and to restore the database whenever required. I have a problem in an application written in Visual Basic that prevents the restore operation. In order to perform the restore operation successfully, there should not be any active or sleeping connections in the SQL Server. My application will check for any such connections before performing the restore operation.

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.

I am not exactly sure how you are restoring your databases via Visual Basic and the impact of Windows 2003, but below are a few T-SQL commands that should resolve the issue:
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:

Alter database

Restore database

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

Dig deeper on SQL Server Backup and Recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close