Thursday, August 4, 2011

Problem in restoring database in User Instance , "Exclusive Access could not be obtained because the database is in use","Restore a db when the db is in use"

I have made backup of database using SMO very well but when I tried to restore that
 database from that created backup file.  The error I kept getting was:
Restore failed for Server 'localhost\SQLExpress'.
Additional Information that I got in inner exception:
 {"System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the
 database is in use."}

The above line should look familiar to anyone who has done a Database Restore and is
easily solved if you are using Management single user mode command and the restore
command as one query.  Something like:

ALTER DATABASE  [your_db_name] SET SINGLE_USER WITH ROLLBACK
IMMEDIATE

RESTORE DATABASE [your_db_name] FROM  DISK =
N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\your_db_name.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

Syntax is something like that:
use master
go

alter database <dbname> set single_user with rollback immediate
alter database <dbname> set multi_user
you can obtain this using SMO, May be management studio do the restore operation
in single user mode internally, but SMO(server management objects) failed to restore
when database in use. Here is some lines that help to do
database backup successfully. To achieve this just kill the database associated process
before restore operation as:
    Restore restore = new Restore();
    restore.Database = databaseName;
    restore.Devices.AddDevice(backupFileName, DeviceType.File);
    restore.ReplaceDatabase = true;
    _server.KillAllProcesses(databaseName);
    restore.Wait();
    restore.SqlRestore(_server);
..
It may be the solution. try this before the restore statement if you do not want to another database
connected process. It works in my case.
database = new Microsoft.SqlServer.Management.Smo.Database(server, databaseName);
database.Refresh();
databaseOptions = database.DatabaseOptions;
databaseOptions.UserAccess =
                            Microsoft.SqlServer.Management.Smo.DatabaseUserAccess.Restricted;
databaseOptions.Alter(
 Microsoft.SqlServer.Management.Smo.TerminationClause.RollbackTransactionsImmediately);
server.ConnectionContext.SqlConnectionObject.ChangeDatabase("Master");



No comments :

Post a Comment