SQL Server backup restore issues?

Question

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?

Answer 1

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...