473,686 Members | 3,247 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Database Restore Woes

I attempt to back up a database on one server and restore it on my local machine.

This is what the query text looks like:

restore database model from
Disk='c:\JQJ\my dump\model.bak'
with replace

I get this error:

The backup of the system database on device c:\JQJ\mydump\m odel.bak cannot be restored because it was created by a
different version of the server
(134217922) than this server (134218488).

The other server product version is: 8.00.194(RTM)
The server on my machine is 8.00.760(SP3)

1.How can I make this restore work? Seems to me I have the later version, so I should be able to handle it. Mine is a
10-user, and the other is enterprise.

2.Also I need to be able to force a different path and even database name: The source server where I create the backup file
may have a different location than I have on my machine, so what parameters can I give the command so that it will use the
location I specifiy? The backup file will have the location information relative to the source server, which I can not
always use. This means I will need to programatically extract that location information on my machine. (I can't hard code
it, since other people with different installation setups will use my program on their machines).

3.The database name on the source server may be ABC, but I may want to restore over XYZ. How can I force that? Perhaps it
would be the same parameter I need for 2.

Thanks,

Jon
Jul 23 '05 #1
3 6035
Jon Jacobs (JonJacobsAtcom cast.net) writes:
I attempt to back up a database on one server and restore it on my local
machine.

This is what the query text looks like:

restore database model from
Disk='c:\JQJ\my dump\model.bak'
with replace

I get this error:

The backup of the system database on device c:\JQJ\mydump\m odel.bak
cannot be restored because it was created by a different version of the
server (134217922) than this server (134218488).

The other server product version is: 8.00.194(RTM)
The server on my machine is 8.00.760(SP3)

1.How can I make this restore work? Seems to me I have the later
version, so I should be able to handle it. Mine is a 10-user, and the
other is enterprise.
I would expect that it is possible to restore a backup from SQL 2000
RTM on SQL 2000 SP3, but it is very clear that it is not possible in
this case. But then again, you are not restoring any database - you
are restoring model. That's a system database, so I assume it is special.

Question: are you restoring model, because you really need a copy of model
from the other box on your machine? Or did you just pick model as a test
case? In the former case, the easiest may be to script any user objects
you have in model, and run the script. I would not really expect that
you have any data in model. If you are just testing, try Northwind or
pubs instead.
2.Also I need to be able to force a different path and even database
name: The source server where I create the backup file may have a
different location than I have on my machine, so what parameters can I
give the command so that it will use the location I specifiy?
You use MOVE:

RESTORE DATABASE mydatase FROM disk = 'C:\temp\mydump .bak'
WITH MOVE 'mydatabase_dat a' TO 'e:\mylocation\ mydatabase.mdf' ,
MOVE 'mydatabase_log ' TO 'f:\myloglocati on\mydatabase.l df',
REPLACE

It does not seem that you can use variable in place of the device
and paths, but you could build the BACKUP command dynamically and
execute with EXEC(@sql).
3.The database name on the source server may be ABC, but I may want to
restore over XYZ. How can I force that? Perhaps it would be the same
parameter I need for 2.


RESTORE DATABASE XYZ ...

There is no law that says that the database you restore to must have
the same name as the source database.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
>Question: are you restoring model, because you really need a copy of model
from the other box on your machine? Or did you just pick model as a test
case? In the former case, the easiest may be to script any user objects
you have in model, and run the script. I would not really expect that
you have any data in model. If you are just testing, try Northwind or
pubs instead.
Yes, Model was just for testing. I will try another database.

You use MOVE:

RESTORE DATABASE mydatase FROM disk = 'C:\temp\mydump .bak'
WITH MOVE 'mydatabase_dat a' TO 'e:\mylocation\ mydatabase.mdf' ,
MOVE 'mydatabase_log ' TO 'f:\myloglocati on\mydatabase.l df',
REPLACE
Excellent. I will put that to use.

It does not seem that you can use variable in place of the device
and paths, but you could build the BACKUP command dynamically and
execute with EXEC(@sql).
I will need to build the command dynamically as you say. But first I need to extract the destination path. How?

RESTORE DATABASE XYZ ...

There is no law that says that the database you restore to must have
the same name as the source database.


I've been trying that to no avail. Well, maybe it will work after I get the other issues resolved, but so far, the error
messages complain about the original database name.

Thank you very, very much.

Jon
Jul 23 '05 #3
Jon Jacobs (JonJacobsAtcom cast.net) writes:
I will need to build the command dynamically as you say. But first I
need to extract the destination path. How?


Well, I silently passed over that question, since I can't really tell
from a distance where you want to have your databases.

But assuming that you want the database in the default location of the
server.

This information is stored in the registry of the server, and you can
retrieve it with xp_regread. However, this is a undocumented and
unsupported function. (Note also that with SQL 2000 SP4, the function
will be restricted to access to SQL Server own values.)

A somewhat more roundabout way of doing it, is this:

CREATE DATABASE temp
SELECT @path = filename FROM sysdatabases WHERE name = 'temp'
SELECT @path = replace(@path, '//', '/')
SELECT @path = substring(@path , 1, len(@path) - len('temp.mdf') )
DROP DATABASE temp
RESTORE DATABASE XYZ ...

There is no law that says that the database you restore to must have
the same name as the source database.


I've been trying that to no avail. Well, maybe it will work after I get
the other issues resolved, but so far, the error messages complain about
the original database name.


You to use WITH REPLACE in this case. Since you already had this in
your example, I did not mention this.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
10604
by: Moti.Ba | last post by:
Hello, I need to restore the Master DB in my SQL 2k server (with sp3). The problem is that i don't have SQL backups of this file I only have general backup of the entire c:\ drive i made using NTBackup. So i tried all I know and succesfully run the rebuildm utility. Now when I try to restore it from the general backup (not SQL backup) using "restore database master from gen_backup" i got error: "The file on device 'gen_backup' is not a...
5
14707
by: Hassan Naqvi | last post by:
Hi, Basically, I am Java developer. In past I have played with Oracle using Java (JDBC). But this is the time to play with IBM DB2 using Java (JDBC). So kindly help this DB2 newbie. I have a file of 32 MB, named as "fdbk5.0". This is actually a database created in DB2 and containing records. I am interested in viewing the *structure of this database, i.e. table names, column names and finally all the records. How I can do this? Which...
4
8380
by: Knokmans | last post by:
Hi, All of this is still a test envinonment, but has to go on production.... I restore a database a database the follwing way db2 restore db <database> from <directory. taken at <timestamp> replace existing Then i only rollforward the (minumum) logs which are needed for recovery db2 rollforward db <database> to end of logs and complete The database in NOT in any pending state anymore
2
4139
by: anna_cheng11 | last post by:
We do not have a DBA available, and this is a development environment, hence I was asked to do the work. I am not a DBA. I need some help to clarify my understanding of DB2 recovery and I am reading the following as well. http://publib.boulder.ibm.com/infocenter/db2help/index.jsp I have a database say DB_INV on machine A and the database is backed up to tape, I am going to create a second system, say machine B with a database called...
2
3261
by: Stefan Schneider | last post by:
Hi, I have to copy an existing database from one Windows-Server to another one. The source database is a 8.1.0 database on a Windows 2000 Server, the destination database will be a newer 8.x database on a Windows 2003 Server. What is the preferred way to copy the old data to the new server? Should I use the backup/restore tools or is there a better way like copying the tablespace files?
1
6335
by: Mohammed Abdel-Razzak | last post by:
Dear sirs I`ve used SQLDMO to make a backup to my database How can I use it to restore database? thanks Mohammed
1
2291
by: ad | last post by:
I want to backup and restore a database in asp.net. The backup process is successful and fail when restore with message: User does not have permission to RESTORE database 'Health'. RESTORE DATABASE is terminating abnormally the restore SQL is : restore database Health from disk = 'D:\Data\ASPNET\Health8\Backup\myback.bck' and the backup SQL is:
5
3231
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)
5
4319
by: chow.justy | last post by:
Dear all, I'm a new beginner of DB2. I face 2 question during restore the data. I have 2 DB2 servers on my company. Server A is running on V7.2 and Server B is running on v8.2 (Enterprise Server). I backup a database from Server A and restore it on Server B. Question 1) "SQL2542N database name is not match on the source file" error
1
8766
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,...
0
8778
tracyyun
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6439
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
5795
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
4307
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...
0
4528
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2944
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
2
2205
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1933
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.