SQL Server 2000 Backup and Restore, Need help!

[smoi]

Hi all,

My manager ask me to do backup for 3 database and restore them in a new server.

I did the backup for the 3 database into BAK file. Then in the new server, when I did the restore in SQL Server Enterprise Manager by using their restore function and selecting device(s) for the BAK file, I encounter the different messages such as the following:

1. Microsoft SQL-DMO (ODBC SQLState: 4200) The media family on device 'C:\Backup.BAK' is incorrectly formed. SQL server cannot process this media family. RESTORE DATABASE is terminating abnormally.



Sometimes I got the error message:
'The file on device 'C:\Backup.BAK' is not a valid Microsoft Tape Format backup set. RESTORE DATABASE is terminating abnormally.'


Then I try to do backup for the 3 databases and I uncheck 'Media set name and backup set expiration' under Options for Backup. When I restore them into new server I got the following message:
"Collation ID 49 is invalid. RESTORE DATABASE is terminating abnormally".


I don't understand... I just plainly take the latest BAK file from the server, and I FTP to my own pc to get the BAK files. From my pc I FTP again to the new server to put the copies into the new server directory and I did restore from there. I've read many articles and follow their guides...... But I couldn't get it work...


I really need help for this coz my deadline is tomorrow...... Please help me with this... I don't understand why I cant restore from BAK file... And is it important about media type??? Or anything wrong from my backup? Or restore part??? =(


Thanks!
Smoi

[cyberdwarf]

When restoring, are you setting restore from device to File? From your post, it sounds like you are trying to restore from a tape device

What versions of SQL Server are you using?

Steve

[smoi]

When restoring, are you setting restore from device to File? From your post, it sounds like you are trying to restore from a tape device

What versions of SQL Server are you using?

Steve

Hi Steve,

I'm using MS SQL Server 2000, when I backup, I choose to backup in a file as BAK file, not onto the tape. Same like when I do restore, I select From Device and choose the filename.

I think I got some idea about the media set error message... Please correct me if I'm wrong... Does that mean during the time when I do backup, the backup BAK file take note of the harddisk as well??? Since I restore by copying the file into the new server, that may not work... Correct?

Thanks
Smoi

[cyberdwarf]

Nope, it doesn't matter where the backup is taken or restored from

[smoi]

If it doesn't matter where the backup is taken from but now it still doesn't work... I still don't understand what happen???

[smoi]

If it doesn't matter where the backup is taken from but now it still doesn't work... I still don't understand what happen???

Now I got a new error message saying "Server: Msg 2774, Level 16, State 1, Line 2 Collation ID 49 is not valid."

Can anyone please let me know how to proceed the restoration without having problem for collation ID and media type or media set???