| Author |
Topic |
|
smemflynn
Starting Member
10 Posts |
 Posted - 2008-03-21 : 07:50:47
|
| 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 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-21 : 09:56:10
|
| You don't have to create database. Just restore the database and specify the path . You should be fine. |
 |
|
|
smemflynn
Starting Member
10 Posts |
Posted - 2008-03-21 : 10:11:17
|
| I have deleted the database so all I have is a backup. So I have no option but to create a database. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-21 : 10:39:00
|
| What?Backup is enough for you to restore database? Right click database- Restore Database |
|
|
|
smemflynn
Starting Member
10 Posts |
Posted - 2008-03-21 : 10:48:06
|
| Yeah, that's what I'm trying to do but I'm getting these errors! If I create a database and try and do a restore from the BAK file, I get the first error... If I try to create the database from the restore console, I get that second error. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-03-21 : 10:59:26
|
| Do the restore using a script with the MOVE option.The script on the following link will generate a restore command from your backup file. All you have to do is change the output file locations to the new location.Create Restore Command from DB Backup Filehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71665CODO ERGO SUM |
|
|
|
smemflynn
Starting Member
10 Posts |
Posted - 2008-03-21 : 11:45:24
|
| Hi Michael,Thanks a million for that, looks hopefull! I'm getting a few errors though:Server: Msg 170, Level 15, State 1, Line 2Line 2: Incorrect syntax near 'E:\APTPension.bak'.Server: Msg 3701, Level 11, State 5, Line 1Cannot drop the table '#header', because it does not exist in the system catalog.Server: Msg 3701, Level 11, State 5, Line 2Cannot drop the table '#filelist', because it does not exist in the system catalog.Here are my first few lines..declare 'E:\APTPension.bak' nvarchar(500)select 'E:\APTPension.bak' =-- Path to Backup file '\\SERVERNAME\SHARE_NAME\MY_DB_BACKUP_FILENAME.BAK'Any ideas? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-21 : 17:16:21
|
| You can just restore db like this:restore database db_name from disk = 'path\file' with replaceAdd move option if necessary. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-03-21 : 17:37:57
|
quote:
Originally posted by smemflynn
Hi Michael,Thanks a million for that, looks hopefull! I'm getting a few errors though:Server: Msg 170, Level 15, State 1, Line 2Line 2: Incorrect syntax near 'E:\APTPension.bak'.Server: Msg 3701, Level 11, State 5, Line 1Cannot drop the table '#header', because it does not exist in the system catalog.Server: Msg 3701, Level 11, State 5, Line 2Cannot drop the table '#filelist', because it does not exist in the system catalog.Here are my first few lines..declare 'E:\APTPension.bak' nvarchar(500)select 'E:\APTPension.bak' =-- Path to Backup file '\\SERVERNAME\SHARE_NAME\MY_DB_BACKUP_FILENAME.BAK'Any ideas?
I'm not trying to be critical here, but it looks like you do not understand the basics of TSQL.CODO ERGO SUM |
|
|
|
smemflynn
Starting Member
10 Posts |
Posted - 2008-03-25 : 05:37:00
|
quote:
Originally posted by Michael Valentine Jones
I'm not trying to be critical here, but it looks like you do not understand the basics of TSQL.
You would be right in that! I've never really used Query Analyzer, and all I use Enterprise Manager for is for a few backup and zip jobs. But I try and figure it out as I go along.The thing is, the main DBA couldn't fix it but he's gone on holidays so I'd like to have it fixed before he comes back! The database has been down for over two weeks now, but as it's not used that often, it's absence went unnoticed until last Thursday. I'm not running the risk of doing any damage here as it's not our main database and also I'm using a test server.-Emily |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2008-03-25 : 15:59:24
|
| Emily, When I have had to do exactly what you are trying, Delete the existing database you created. So no database with the name you need exists. Right click ANOTHER database in enterprise manager. Let me find the walkthrough i did....If this is a database from a different server you have to go through the process of selecting the backup device. From the Restore Database window. Select "From Device" -> Click "Select Devices" -> Click "Add" -> The "File Name" Radio button should be checked. -> Click the "..." -> Browse to the location of the file name Double Click on the correct .bak file. -> Click "ok" twice (back to the restore database initial window -> You will have to choose the name of your database, existing or new.TYPE THE CORRECT DATABASE NAME IN "RESTORE AS DATABASE"Because the database does not exist it should not be prefilled in. If you are not careful, you will delete whatever poor database you right clicked on to start this job.In every case where those instructions do not work, you will need to use query analyzer with the code that rmiao gave you.If you want to use Michaels code, I think it needs to look like this...]declare @backup_path nvarchar(500)select @backup_path = 'c:\oopswedeletedthedatabasebutthisiswhereihavethebackup.bak'(and then the rest of his code, the delare part just says "this is the kind of thing for you to use SQL") |
|
|
|
smemflynn
Starting Member
10 Posts |
Posted - 2008-03-26 : 08:47:07
|
| Hi Tripodal, thanks for your reply!The procedure you outlined in your post is exactly how I've been doing it.. but I get the errors I outlined before.I tried Michael's script (em.. thank you very much for your correction of my very very wrong interpretation of the code!) which gave me this output:restore database MyDatabase from disk = 'E:\MyDatabase.bak' with move 'Standard_Data' to 'D:\SQLData\DatabasesMyDatabase_data.mdf' ,move 'Standard_Log' to 'D:\SQLData\Databases\MyDatabase_log.ldf' , replace, stats = 5 , recoveryWhen I run this output in Query Analyzer after changing the data file and log file names and locations to: restore database MyDatabase from disk = 'e:\MyDatabase.BAK' with move 'Standard_Data' to 'E:\SQLData\MSSQL\Data\MyDatabase_data.mdf' ,move 'Standard_Log' to 'E:\SQLData\MSSQL\Data\MyDatabase_log.ldf' ,replace, stats = 5 , recovery, I get the error ' The backup set holds a backup of a database other than the existing 'MyDatabase' database.'Do you see the naming error in the original data file location? The missing backslash? Could that be screwing it up, as the data file name is completely different to the log name?Also, when I type in the command 'replace' into QA, it stays pink whereas all the other commands are blue... is that right?Thanks a million for all your input!!! |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-26 : 21:59:55
|
| Have to use replace option, and pink is correct color. |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2008-03-28 : 14:27:01
|
| I see replace, stats=5my guess is you need a "with replace"exactly where do we put that? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-03-28 : 14:41:20
|
| The script I posted produces the command in the correct format, including the REPLACE.CODO ERGO SUM |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2008-03-28 : 15:10:09
|
| Emily, is the 'my database' still on the server?try detaching it and moving teh mdb, and ldb files into a different location and rerunning the script,i assume it will recreate the database from the backup then? and create it with the name 'my database' |
|
|
|
|
Errors Creating Database from Backup file