CODEWITHSUNDEEP
sqlsql-server-2005
Kulahan
Kulahan

Reputation: 522

SQL Server backup restore issues?

Ok, so I'm having a bit of an issue - I executed some code on my SQL Server and didn't realize that I didn't have the WHERE bit selected. Of course, when I saw the "608 rows affected" rather than "1 row affected", I freaked a bit.

Luckily, I have a backup saved, but for some reason, I'm getting a couple issues. Now, I took the server down, so I know that it's not being used by anyone, but it's giving me the following error

"Restore failed for Server 'myserver'.

System.Data.sqlclient.sqlerror: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)"

I saw something that stated I should be using 

Alter Database Databases
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE PRODUCT
FROM DISK = ''

but I'm having three reservations about this code. First, I'm completely unsure of how to turn multi_user back on. Second, I don't know where the program stores its backups. Third, this SQL is a bit above my head - I'm relatively new to the language, honestly, so I'm not sure how this will affect things.

Anyone have any answers to my troubles?

Upvotes: 8

Views: 28159

Answers (5)

user2145577
user2145577

Reputation: 61

right on the database click tasks->takeoffline , when its succeed do the same thing but put it Bring Online then try to restore your database

Upvotes: 6

Rajesh Panicker
Rajesh Panicker

Reputation: 11

Go to the activity Monitor and see if users are still logged in then kill the process for that user using the respective db. Then go ahead restore the backup

Upvotes: 1

Michael Fredrickson
Michael Fredrickson

Reputation: 37378

I might suggest instead of overwriting the existing database from the backup, that you instead recover the backup with a different database name... this way you can have the current and previous databases side-by-side.

Then, you can simply write an update statement to recover the data from just that one specific table instead of resetting the whole database.

EDIT: The specifics would depend on your environment, but the restore would look something like this:

restore database PRODUCT_OLD
from disk='C:\PRODUCT.bak'
with
    move 'PRODUCT_Data' to 'C:\PRODUCT_OLD_Data.MDF',
    move 'PRODUCT_Log' to 'C:\PRODUCT_OLD_Log.LDF'

And then the update statement would also be based on your specific table...

Upvotes: 8

user596075
user596075

Reputation:

Set the database to single user is correct. When you are complete with your restoration you'll execute this

alter database YourDb
set multi_user

As for where your .bak file resides, you'll have to find it prior to restoring.

Upvotes: 4

n8wrl
n8wrl

Reputation: 19765

Scary.

Ok, some things to check:

  1. Make sure you are the only person connected to the server.
  2. Make sure no other applications, web servers, app servers, etc. hold connections to your DB.
  3. Make sure your SQL manager has no open windows to tables or other objects in your database.

THEN you should be able to do the restore w/o single user stuff.

Upvotes: 3

Related Questions