can't get the restore to work 1

[phil22046]

I successfully added a backup device with the sp_addumpdevice command.

Next I tried to restore the S030 Database with this command:

restore database S030 from [S030diff] with replace

S030Diff is a backup of the S030 database from the production server. I am trying to restore to another server which is not available to anyone but me. And I get these errors:

Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

 

[ptheriault]

You have to use the "with move" option to restore to another database.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Check out this link. It should help.

http://msdn2.microsoft.com/en-us/library/ms186858.aspx

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

I followed the link and came up with the following command:

restore database S030 from [S030diff] with move 'S030Diff' TO 'S030' ,replace

I logged in as administrator and ran the above

Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'S030Diff' is not part of database 'S030'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

 

[phil22046]

I just tried this restore command after creating a fresh new database: Restore Database S030_B FROM S030Diff WITH MOVE 'S030diff' TO 'S030_B'

And I am getting this error which does not make sense, since I just created the S030_B database.

Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Why does the database in use error occur?

 

[SQLBill]

If the database exists, then you can't just restore to it. You need to use the WITH REPLACE command if the database already exists.

-SQLBill

Posting advice: FAQ481-4875

 

[phil22046]

I get the same results using the REPLACE

Restore Database S030_B FROM S030Diff WITH MOVE 'S030diff' TO 'S030_B', REPLACE

Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

 

[ptheriault]

Phil,
I believe your syntax is incorrect. you need to specify the full path the .mdf and .ldf when you use the with move option.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

After correcting the Syntax, I tried the following:

Restore Database S030_B FROM S030Diff WITH MOVE 'S030diff' TO 'D:\MSSQL\MSSQL\data\S030_B_data.mdf', REPLACE

and I still get these errors:

Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

S030_B is an empty database that I created to use for testing my restore commands. This is really frustrating.
Nobody is using the S030_B database.

 

[ptheriault]

Try this.

IN SSMS right click on the S030_B and select tasks -> restore -> database.

select "From Device" and navigate to add your device.
One the options page
Select "Overwrite the existing database"
Change the restore as path to the path of the S030_B .mdf and .ldf files.

You can then run this or select the script drop down and script out your backup. This will give you the correct syntax.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

OK I tried that. I went into Enterprise manager(Didn't know what SMSS was) and I put in all the commands and changed the restore path. I could not figure out how to generate the SQL code from the setup I made. Anyway I ran it and got this message:

Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally.

 

[ptheriault]

SSMS is the 2005 version of EM.

Kill all the users in the database.

from QA in the master db run this.

sp_who2

look for users in the database you are trying to restore.

Run the kill command.

Kill <spid>

Let me know if there is a lot of them. I have a script that will kill them all.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

Better progress but still didn't work correctly.

After running for several minutes and display a progress bar that never ever got any psrt filled in, the restore ended with a strange little window that said in the caption "Microsoft SQL-DMO(ODBC SQLSTATE: HY008)" and an OK button to click on in the window.

Files were created of the correct size for the MDF and LDF and in the Enterprise manager the database displays as S030_B(Loading)

 

[ptheriault]

Restore the db again and this time check to make sure you have with recovery enabled.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[katbear]

THE PROBLEM IS THAT YOU HAVE USERS CONNECTED TO THE DATABASE YOU'RE TRYING TO RESTORE. YOU NEED TO RUN AN SP_WHO TO SEE WHO'S STILL CONNNECTED. THEN KILL THE CONNNECTIONS. THAT WILL SOLVE YOUR PROBLEM.

AS FOR YOUR SECOND PROBLEM, LOG ON TO EM AND DELETE THE "LOADING" DATABASE, S030_B, AND TRY YOUR RESTORE AGAIN. SOMETHING SOUNDS MESSED UP. UNLESS THIS IS A REALLY LARGE DATABASE, THEN IT COULD TAKE A WHILE TO RESTORE.

NOT THE MOST SCIENTIFIC ANSWER, BUT THIS IS WHAT HAS WORKED FOR ME IN THE PAST.

 

[SQLBill]

If you haven't dropped the database yet, try this:

RESTORE DATABASE dbname WITH RECOVERY

If you restored the database WITH NORECOVERY, the above command will 'reset' it to being recovered.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Also, something to consider when restoring a database that already exists. If you open Enterprise Manager and Query Analyzer - that is two connections. You have to have one or the other open, not both.

-Bill

Posting advice: FAQ481-4875

 

[phil22046]

KatBear the Database S030_B is test database I created yesterday. Nobody but me know it exists and nobody is connected to it.

Ptheriault When I restore through enterprise manager I don't see the option to recovery enabled, so I chose the option to Read only with Additional transaction logs allowed.

After waiting an hour and 20 minutes this restore operation ended with the following message Window.

Microsoft SQL-DMO(ODBC SQLState: 42000)
Cannot Associate files with different databases.
and an OK button

So I clicked on the OK button and Another restore operation in progress window was under that and appears to be running.
So now I am letting that run.

And in Enterprise Manager the Database appears as
S030_B(Read Only)

 

[ptheriault]

When I restore through enterprise manager I don't see the option to recovery enabled, so I chose the option to Read only with Additional transaction logs allowed.

Only select that option if you have more tlogs or differentials to apply. If will not be applying more backup sets select leave database ready to use. It's the same as saying with RECOVERY. WITH NORECOVERY is to apply more logs.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

The second restore that was going on completed successfully.

I noticed that when I restore from my differential backup I get a choice of File 1, file 2 ...

I do the differential once every 24 hours and transaction logs every 1/2 hour.

I assume the File 1 is the Original full backup, File 2 would be the database as it was after the first Differential backup.