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.

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top