473,721 Members | 4,008 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??? =(


Thanks!
Smoi
May 8 '07 #1
5 7108
cyberdwarf
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?

Steve
May 8 '07 #2
smoi
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?

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

2
2532
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, Triggers, Users . . etc). When I tried this using Transactional Replication, the replication...
2
1781
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 R.Kruk
1
3736
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 names to restore to. When I go to All Tasks, restore, and restore from a previously run full...
3
2106
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, then to an excells file. After having gone
1
2113
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 called BACK_2_TRANSLOG.BACK. At T4 my system fails and I need to bring the database
4
7784
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
5
3237
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 2.1.2)
2
8274
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? I've found a post which explains how to do this on SQL Server 2000...
5
7324
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 manager, restore database, from device, i chose the transaction file and the database backup itself
0
8852
marktang
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 usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8736
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9373
Oralloy
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. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9227
jinu1996
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9145
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 most users, this new feature is actually very convenient. If you want to control the update process,...
1
6676
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5992
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4497
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
3
2143
bsmnconsultancy
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.