Database Restoration Problem =(

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Database Restoration Problem =(

Author

Topic

smoi
Starting Member

8 Posts

Posted - 2007-05-10 : 11:14:32

Hi all,

I need to restore the MS SQL Server 2000 database into a new server. I do backup in EM and backup as BAK files in old server. I have 3 BAK files for 3 database. But I have different error message for each of them when I do restore, as listed in the following:

Error Message 1 for 1st database:

Server: Msg 2774, Level 16, State 1, Line 2
Collation ID 49 is invalid.
Server: Msg 3013, level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally

Error Message 2 for 2nd database:

Server: Msg 3241, Level 16, State 13, Line 2
The media family on device 'D:\Microsoft SQL Server\MSSQL\BACKUP\my_backup.BAK' is incorrectly formed. SQL SERVER cannot process this media family. Server: Msg 3013, level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally

Error Message 3 for 3rd database:

Server: Msg 3201, Level 16, State 2, Line 2
Cannot open backup device 'D:Microsoft SQL Server\MSSQL\BACKUP\my_backup.BAK'. Device error or device off-line. See the SQL Server error log for more details.
Msg 3013, level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally

For all the 3 databases, I restore them in Query Analyzer by using the below sql statement:

USE MASTER
RESTORE DATABASE <my db name>
FROM DISK = 'D:\Microsoft SQL Server\MSSQL\BACKUP\<backup>.BAK'
WITH REPLACE

I also did backup in EM and got the same error messages.

On the old server I manually created the BACKUP folder, which is (D:\Microsoft SQL Server\MSSQL\BACKUP\), and I also create same folder manually in the new server and transfer all the BAK files into the new server.

I've read and research a lot and it makes me more confusing now... I've also tried various ways for few days already and I kept on getting problem with Collation ID and Media type or Media set. I don't know how to get rid of these problems when I restore...

I check the Collation name for both server are the same, I don't know why I get the collation ID problem.

I really need help for this, please, anyone... and need your fast response! I have ran out of ideas...

If you need any more information please let me know.

Thanks
Smoi

p/s: pls don't ignore me till my problem is solved... Coz I'm afraid that I may not get any response when my thread is moved below... =(

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-10 : 13:31:56

For first one, source db has different collation setting from target db. They have to be same.
Second one, seems backup file is bad. Double check with 'restore verifyonly'.
Third one, where is backup file? Still on source server? If so, sql service startup account on target server needs permission to access the file.

Kristen
Test

22859 Posts

Posted - 2007-05-10 : 14:02:47

"I restore them in Query Analyzer by using the below sql statement"

You ought to add a MOVE statement which will provide a suitable LOCAL path instead of the Path of the original database on the Source server.

You might like to have a look at
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example
which shows how to interrogate a Backup File to find where its database was originally hosted, and how to change that location.

"pls don't ignore me till my problem is solved... Coz I'm afraid that I may not get any response when my thread is moved below..."

It doesn't work like that here, and I doubt it works like that elsewhere ...

The "regulars" read new posts that are of interest to them. If they have something to contribute they reply. Its unlikely that anyone will reply "later" - so go with whatever advice you have, and don't expect people to be saving your post somewhere and planning to reply some time later; it doesn't happen.

Kristen

smoi
Starting Member

8 Posts

Posted - 2007-05-10 : 20:58:20

Thank you very much Kristen and rmiao... At least now I have a direction of what to check.

I've read somewhere that before backup we need to create backup device. Is this step necessary? If yes, then I'll have to do backup one more time.

Kristen
Test

22859 Posts

Posted - 2007-05-11 : 05:18:42

"I've read somewhere that before backup we need to create backup device. Is this step necessary?"

Not really. Its the old-fashioned-way when backups where on Tapes, etc.

Since SQL7 you can create a backup directly to a named disk file (either pre-existing or not, with pre-delete if desirable). Something like:


BACKUP DATABASE MyDatabaseName
TO DISK = 'x:\MyPath\MyDatabaseName_yyyymmdd_hhmm.BAK'
WITH INIT    -- Use this to OVERWRITE any pre-existing file

Kristen

smoi
Starting Member

8 Posts

Posted - 2007-05-14 : 20:55:03

Hmmmm...... I still have problem with restoration... I think I would try to export data from the live server into my testing server instead of restoring it and got same problem.

My client's live server is updated every 5 minutes, means there's an application running which will collect the information every 5 minutes and store in the MS SQL Server 2000 database.

If I do exporting of data from live server and save into excel files, is it OK to do it without stopping any of the relevant services??? Any impact on the data exported???

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-14 : 23:22:36

That will not affect apps, but you may not be able to keep data integrity.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources