Backup fails, "database is in use"

Question

Backup:

private void Button1_Click(object sender, EventArgs e)
{
   SqlCommand cmd = new SqlCommand("backup database emp to disk ='C:\\emp.bak'", con);
    con.Open();
    cmd.ExecuteNonQuery();
    // ExecutenonQuery();
    con.Close();
}

But when I restore:

private void Button2_Click(object sender, EventArgs e)
{
    SqlCommand cmd = new SqlCommand("restore database emp from  disk ='C:\\emp.bak'", con);
    con.Open();
    cmd.ExecuteNonQuery();
    // ExecutenonQuery();
    con.Close();
}

I get the error:

RESTORE cannot process database 'emp' because it is in use by this session. It is recommended that the master database be used when performing this operation. RESTORE DATABASE is terminating abnormally.

Anyone can help me please

What *connectionString* is used to restore? Use SMO (SQL Server Management Objects) Lib. — KV Prajapati, Jul 15 '12 at 11:06
Did you read the error message? Have you tried what it suggests? — GSerg, Jul 15 '12 at 11:07

score 1 · Answer 1 · answered Jul 15 '12 at 12:25

May be you should use the master database as it suggest.

Why don't you use the SMO library ? down is an examples of how to backup and restore database using SMO.

How to do a Backup

Server server = new Server(sqlServer);

var bdi = new BackupDeviceItem(String.Format(@"C:\Opticien\Data\Sauvegardes\{0}", backupName), DeviceType.File);
var backup = new Backup() { Database = databaseName, Initialize = true };
backup.Devices.Add(bdi);

backup.SqlBackup(server);

How to restore :

var server = new Server(sqlServer);
var restore = new Restore()
{
     Database = database,
     Action = RestoreActionType.Database,
     ReplaceDatabase = true,
};
restore.Devices.AddDevice(backupPath, DeviceType.File);
var dt = restore.ReadFileList(server);
var dLogicalName = dt.Rows[0]["LogicalName"].ToString();

restore.RelocateFiles.Add(new RelocateFile(dLogicalName, String.Format(@"C:\Opticien\Data\{0}.mdf", database)));
restore.RelocateFiles.Add(new RelocateFile(dLogicalName + "_Log", String.Format(@"C:\Opticien\Data\{0}.ldf", database)));

restore.SqlRestore(server);

Good luck

You'll find the Dlls in the installation path of you SQL Server, for ex : C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies — SidAhmed, Jul 16 '12 at 07:44

score 0 · Answer 2 · answered Jul 15 '12 at 11:09

0

Normally you really want your SQL Database to be in single user mode when you issue a restore command so that you can guarantee that other processes aren't using the same database when this is happening. If anyone else is using the DB at the time you will get a error like you are currently seeing.

Have a look at the Single User documentation

answered Jul 15 '12 at 11:09

John Mitchell

9,653
9
57
91

SqlConnection con = new SqlConnection("Data Source=.\\SQLExpress;AttachDbFilename=|DataDirectory|\\emp.mdf;Integrated Security=True;User Instance=True;Initial Catalog=emp"); – ainma Jul 15 '12 at 11:10
Can I use a sound method i use this SqlConnection//SqlConnection con = new SqlConnection("Data Source=.\\SQLExpress;AttachDbFilename=|DataDirectory|\\emp.mdf;Integrated Security=True;User Instance=True;Initial Catalog=emp"); – ainma Jul 15 '12 at 11:14

score 0 · Answer 3 · edited May 23 '17 at 12:12

0

You might want to consider kill the current sessions before restoring:

use master
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

--do you stuff here 

ALTER DATABASE YourDatabase SET MULTI_USER

For more details:

How do you kill all current connections to a SQL Server 2005 database?

edited May 23 '17 at 12:12

Community

1
1

answered Jul 15 '12 at 11:12

Jupaol

21,107
8
68
100

score 0 · Answer 4 · 2012-07-15T12:01:10.423

0

When you wanted to restore SQL Databse, you make sure any process doesn't using this database. Its better used another name to restore Databse. I edit my answer:

DECLARE @SPId int
DECLARE @SQL nvarchar(100)

--SET @DatabaseName = N'AdventureWorks2008'
SET @DatabaseName = DB_NAME()
DECLARE my_cursor CURSOR FAST_FORWARD FOR
SELECT SPId FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @SPId

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'KILL ' + CAST(@SPId as nvarchar(10))
print @SQL
EXEC sp_executeSQL @SQL
--KILL @SPId -- Causing Incorrect syntax near '@spid'.

FETCH NEXT FROM my_cursor INTO @SPId
END

CLOSE my_cursor
DEALLOCATE my_cursor

edited Jul 15 '12 at 12:01

answered Jul 15 '12 at 11:14

SqlCommand cmd = new SqlCommand("restore database NewDatabaseName from disk ='C:\\emp.bak'", con); – Jul 15 '12 at 11:26
i want any cod backup and roster is use this SqlConnection SqlConnection con = new SqlConnection("Data Source=.\\SQLExpress;AttachDbFilename=|DataDirectory|\\emp.mdf;Integrated Security=True;User Instance=True;Initial Catalog=emp"); – ainma Jul 15 '12 at 11:29

Backup fails, "database is in use"

4 Answers4