Errors installing a database from a backup

Question

Errors installing a database from a backup

eflynn

SSC Enthusiast

Points: 111
More actions
March 21, 2008 at 5:51 am

#379343

Hi,
I am trying to install a database from a backup file of a now deleted database.
What happened was that the data file was at D:\SQLData\DatabasesMyDatabase_data.mdf, instead of D:\SQLData\Databases\MyDatabase_data.mdf so we wanted to move it.
The main DBA here showed me a ‘quick’ way to fix this was by taking a backup of the database, deleting it, recreating it, and then overwriting it with the backup. So I did that but when I try to overwrite the recreated database, I get the following error:
“The backup set holds a backup of a database other than the existing 'MyDatabase' database.”
If I try to create a database using a backup, I get the following error:
"Could not find database ID 65535. Database may not be activated yet or may be in transition"
I do all this in Enterprise Manager by the way, not by running scripts.
Has anyone any ideas of what I could do? This backup holds critical information!!!
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

tosscrosby SSCoach Points: 18045 More actions · Answer 1

This is how I do it. See if this works (it's pretty staright forward). In EM, right-click and create database. On the new database, right-click->All Tasks->Restore. On the restore pane, select the file you want to restore (From Device) on the general tab. On the options tab, select force restore. Be sure your logical and physical locations and names are correct for the NEW database and select OK. Unless you're restoring logs on top of that, be certain the "Leave Database operational" radio button is checked (it's the default).

-- You can't be late until you show up.

eflynn SSC Enthusiast Points: 111 More actions · Answer 2

Yeah, that's what I've been doing and that's what should work, but unfortunately it doesn't and all that happens is me getting aforementioned errors.

Does anyone know a script or some code or something that might give me a few more options?

Steve Jones - SSC Editor SSC Guru Points: 736445 More actions · Answer 3

It's possible you have some sizing differences or something else. Why create the blank database? You can restore the database backup to a new database and give it the correct name you want.

eflynn SSC Enthusiast Points: 111 More actions · Answer 4

Yeah, sorry I didn't outline it properly, but when I try and create the database via the restore procedure, that's when I get the second error.

tosscrosby SSCoach Points: 18045 More actions · Answer 5

This is straight from BOL. I apologize if it looks basic but it should be a very simple task. The assumption with this script is that you have one data file and one log file. One dumb question, do you have enough free disk space for the database on the device you're restoring to (although the error message would/should be totally different)?

RESTORE DATABASE TestDB

FROM DISK = 'c:\Northwind.bak' --your backup file name

WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf', --logical and physical data file name

MOVE 'Northwind_log' TO 'c:\test\testdb.ldf' --logical and physical log file name

-- You can't be late until you show up.

eflynn SSC Enthusiast Points: 111 More actions · Answer 6

Thanks for the reply. Yeah, I've tons of disk space.

Hmmm... I've changed that script accordingly and I keep on getting an error on that script saying:

Server: Msg 170, Level 15, State 1, Line 4

Line 4: Incorrect syntax near 'MOVE'

Here's what I have:

RESTORE DATABASE MyDatabase

FROM DISK = 'E:\MyDatabase.bak'

WITH MOVE 'MyDatabase' TO 'E:\SQLDATA\MSSQL\Data\MyDatabase_Data.mdf'

MOVE 'MyDatabase_Log' TO 'E:\SQLDATA\MSSQL\Data\MyDatabase.ldf'

Steve Jones - SSC Editor SSC Guru Points: 736445 More actions · Answer 7

And all three of these paths are valid? Meaning that there are the subs created and the backup file is correctly named?

Have you tried restore with headeronly to verify the logical names?

eflynn SSC Enthusiast Points: 111 More actions · Answer 8

Yep, the paths are all there alright. I'm not sure what you mean restore header only? So no, I haven't tried that..

george sibbald SSC Guru Points: 104210 More actions · Answer 9

RESTORE DATABASE MyDatabase
FROM DISK = 'E:\MyDatabase.bak'
WITH MOVE 'MyDatabase' TO 'E:\SQLDATA\MSSQL\Data\MyDatabase_Data.mdf'
MOVE 'MyDatabase_Log' TO 'E:\SQLDATA\MSSQL\Data\MyDatabase.ldf'

you are missing a comma at the end of the first with move line i.e.

WITH MOVE 'MyDatabase' TO 'E:\SQLDATA\MSSQL\Data\MyDatabase_Data.mdf',

---------------------------------------------------------------------