Database restore error in C#

Question

Possible Duplicate:
Exclusive access could not be obtained because the database is in use

I use C#, .net4 and SQL Server 2008 R2 in project and use this code to restore a database:

_comm = new SqlCommand("use master; RESTORE DATABASE [DB1] FROM  DISK = @Address WITH  RESTRICTED_USER,  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10; use DB1;", _conn);
        _comm.CommandType = System.Data.CommandType.Text;
        _comm.Parameters.AddRange(new SqlParameter[] 
                {
                new SqlParameter("@Address", _path)
                });

            _conn.Open();
            _comm.ExecuteNonQuery();

The following error is displayed:

Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally. Changed database context to 'master'. Changed database context to 'DB1'.

The message is pretty clear: the `DB1` database is still being used by someone else - either you have query windows in SQL Server Management Studio open, or some other application is still connected to that database. You **cannot** restore a database while it's being used. — marc_s, Dec 19 '12 at 12:21
Also: you should not use `use master` in your SQL statement inside C# - if you want to execute something on the `master` database - you need to have an appropriate connection string: `server=YourServer;database=master; ......` — marc_s, Dec 19 '12 at 12:23

S3ddi9 · Accepted Answer · 2012-12-19T17:04:05.730

its better to use SMO (cause no need for master) once you are connected to the server you do whatever you want

using Microsoft.SqlServer.Management.Smo;

...

        Server myServer = new Server(@".\SQLExpress");

        Database mydb = myServer.Databases["DB1"];
        if(mydb!=null)
           myServer.KillAllProcesses(mydb.Name);//detach

        Restore restoreDB = new Restore();
        restoreDB.Database = mydb.Name;

        restoreDB.Action = RestoreActionType.Database;
        restoreDB.Devices.AddDevice(_path, DeviceType.File);


        restoreDB.ReplaceDatabase = true;

        restoreDB.NoRecovery = false;

        restoreDB.SqlRestore(myServer);

BACKUP

        Server myServer = new Server(@".\SQLExpress");

        Database mydb = myServer.Databases["DB1"];

        Backup bkp = new Backup();

        bkp.Action = BackupActionType.Database;

        bkp.Database = mydb.Name;

        bkp.Devices.AddDevice(_path, DeviceType.File);

        bkp.BackupSetName = "DB1 backup";//optional
        bkp.BackupSetDescription = "mybackup dated " + DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss");//optional

        bkp.Initialize = true;
        bkp.Incremental = false;

        bkp.SqlBackup(myServer);

References :

C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll

C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.Sfc.dll

C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll

C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.SmoExtended.dll

I can not use Server & Database & Restore Class. please give me reference for class — Ahmad Kazemi, Dec 19 '12 at 12:51
beleive me, using SMO will help you a lot (its like using SqlServer Management Studio but in C#), you can Backup , restore, Transfert DB from a server to another, fetch all information in a strongly typed classes — S3ddi9, Dec 19 '12 at 12:57
@S3ddi9 can you give me the complete code where i can put the database name, path, or can you comment your code please thanks — r.hamd, Apr 27 '15 at 15:11
@S3ddi9 can you give me the complete code where i can put the database name, path, or can you comment your code. or if you could put both restore and backup in two different functions with parameters. please thanks — r.hamd, Apr 27 '15 at 15:21

score 0 · Answer 2 · answered Dec 19 '12 at 12:26

0

Insert the line below before calling the SqlCommand:

_conn.ChangeDatabase("master");

answered Dec 19 '12 at 12:26

Alex Filipovici

31,789
6
54
78

Database restore error in C#

2 Answers2