473,573 Members | 3,290 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL Server 2000 Backup and Restore, Need help!

6 New Member
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??? =(

May 8 '07 #1
5 7089
218 Recognized Expert New Member
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?

May 8 '07 #2
6 New Member
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?


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?

May 9 '07 #3
218 Recognized Expert New Member
Nope, it doesn't matter where the backup is taken or restored from
May 9 '07 #4
6 New Member
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???
May 10 '07 #5
6 New Member
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???
May 10 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Michael Orlando | last post by:
Any help would be greatly appreciated. My problem is that I need to set up a backup SQL Server 2000 machine which can be used in case of a failure to my primary. All databases (30 as of now) must be an up to the minute exact copy of production and include most recent changes in data as well as any structure changes (Tables, Views, SP's,...
by: Robert Kruk | last post by:
Hello, Recently I've tried to restore backup made on SQL Server 6.5. Unfortunately, the only version of SQL Server I currently possess is SQL Server 2000. I have access only to that backup, because I've made it a few years ago, and the main db file doesn't exist any more ;( I would be grateful if you could help me a little ;) Regards...
by: dschneiderman | last post by:
Hello All, I am not a SQL Server expert, but I have been put in a pretty precarious situtation and I am close to panicking :( I have need to restore a SQL database from backup (through Enterprise Manager). The database got corrupt and I detached it, then was unable to attach it again. I created a new database with the same name and file...
by: anguo | last post by:
Hello, I need to set up a development platform at home with a copy of a DB from a SQL server at a friend's company. So far, all my attempts at backup/restore or export/import have failed despite my reading all the documentation I could find (I am a newbie). Using DTS/Export wizard, I tried to export the DB first to a plain text file,...
by: John Rajendran | last post by:
Hi there, I need to take a backup with the following scanerio in mind: At T1 I take the FULL BACK UP of the database called BACK_1_FULL.BACK in c:\ Then at T2 I would like to take only the differential BACKUP of the Transaction log called BACK_1_TRANSLOG.BACK and at T3 I would like to take only the differential BACKUP of the Transaction log...
by: Andy J | last post by:
Hi Everyone NEW USER Q. I am trying to restore a database backup image to another machine. Both machines are installed with same DB2 7.2 installation package. Version: 7, Release: 2, Modification: 5, Service Level: WR21294 Querying the system... Windows 2000 Version
by: HSP | last post by:
hi. i need to restore an old database. The db was backed up using a DLT drive, using 2 volumes. The content for the tapes was copied to file onto Solaris machine using rsh and dd (for backup purposes). Now, the drive is defective and can't read the tapes anymore. Server is AIX 4.3.2 and database is IBM DB2 Server (DB2 for AIX Version...
by: l0b0 | last post by:
I'm working on a restore procedure for the case where all MDF files are missing, but the LDF files are all intact. A full backup is done every 24 hours, and a log backup is done every 3 hours. After restoring the last full + log backups, is it at all possible to use the LDF files to recover data from that point up to a newer point in time? ...
by: Seguros Catatumbo | last post by:
Hi, i am using sql server 2000, and i make daily backups. I want to restore my backup to a test msde database i have. I don't know how i did it last time. -The database name is the same: "web" on both places -The database is located on different hard drives and the file name are different on both places. I tried to do it from enterprise...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.