SQL Server Performance Forum – Threads Archive
Backup Restore
I am trying to restore backup from SQL Server 2000 to SQL Server 2005. I am successful in restoring the backup with one data file and one log file. I had trouble restoring the backups with multiple data files. I have done exactly the same but it gives me the following error message: Msg 3154, Level 16, State 4, Line 1The backup set holds a backup of a database other than the existing ‘TEST’ database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally. I tried restoring the database with "Move" option. I would appreciate, if anyone can help me on this. Thanks,
Gopal
According error, I suppose you are restoring one database over TEST database allready in SQL.
That’s what you want to do? Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
That’s correct. I am doing the exactly the same. I have the used the following command for restore restore database TEST
from disk = ‘\server_nameX$TEST.BAK’
with norecovery,
move …. I just added ‘replace’ command after norecovery and it started working. Thanks for your time,
Gopal
Gopal,
I’m getting the same problem and same error message. Could you please send me the complete restore statement with REPLACE command? Thanks in advance. Dennis
Have you looked at the contents of that backup file using RESTORE FILELISTONLY, to resolve this run restore database MyDatabase from disk = \pathdbbackup-file’ with replace Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
It worked. Thanks, Sayta. Dennis
After I run restore command, it says restore successfully. However, it still says "Restoring" in the Management Studio and I cannot access the database. What should I do now? Stop and Start the server? Dennis
Did u try refreshing it? Do not restart server for such small things, see if the database is accessible i.e. if you are able to run any queries or open a different Management Studio Satya
Am I seeing the truth or is it a mistake? I see that there are 3500 views for this thread?
Run…
RESTORE DATABASE <DB NAME> WITH RECOVERY use DATABASEPROPERTYEX function get the status of the database…
See BOL for syntax…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
There you go Gopal I didnt look at your syntax. You have restored it with norecovery option, use recovery to have the database accessible after your restore. Use norecovey only when you plan somehting like pending log restores and stuff. Satya
Yes, everything is fine after I run RESTORE DATABASE <DB NAME> WITH RECOVERY. Thanks, folks. Dennis
Read SQL Server 2005 Books Online topic "RESTORE (Transact-SQL)" to understand more about this… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
]]>