Database Restore Woes

[Jon Jacobs]

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\mydump\model.bak'
with replace

I get this error:

The backup of the system database on device c:\JQJ\mydump\model.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

[Erland Sommarskog]

Jon Jacobs (JonJacobsAtcomcast.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\mydump\model.bak'
with replace

I get this error:

The backup of the system database on device c:\JQJ\mydump\model.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_data' TO 'e:\mylocation\mydatabase.mdf',
MOVE 'mydatabase_log' TO 'f:\myloglocation\mydatabase.ldf',
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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Jon Jacobs]

>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_data' TO 'e:\mylocation\mydatabase.mdf',
MOVE 'mydatabase_log' TO 'f:\myloglocation\mydatabase.ldf',
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

[Erland Sommarskog]

Jon Jacobs (JonJacobsAtcomcast.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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp