@.rem Uncomment the following line for MSDE
@.rem set DBNAME=(local)\NETSDK
set DBNAME=(local)\NETSDK
Thanks,
Bob HIt would appear that NETSDK is the "instance name" of the MSDE installation.
With SQL Server 2000 (and MSDE), multiple instances of SQL Server can be installed. The first uses the "default instance"; that is, to access it, simply reference the name of the server in a connection string. To reference a non-default instance, follow the server with \<instance name>sql
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,
JonJon 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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>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|||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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Thanks!
-GarySQL Express can only subscribe to a replication, it can't publish I'm afraid.
Can I replicate a database onto a seperate instance of SQL? I need to
replicate a database to a stand-alone machine with no connectivety to
anything. Does anyone know where I can find info on this?
Thanks for any help.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200509/1Perhaps in Books Online, under "Replication"? :-) And I guess you
don't really mean "no connectivity to anything", otherwise it would be
somewhat difficult to implement. Depending on what your goal is
(availability, disaster recovery, offline reporting etc.) you could
also look at log shipping, clustering, and of course backup/restore.
Replication is quite a specialized area, so if you have specific
questions about it, you'll probably get a better response in
microsoft.public.sqlserver.replication.
Simon|||Thanks Simon. I did check the Books Online ;-). All I need is our online
database on another stand-alone computer
that is not connected to any network (due to stupid security). I need this
for a whole bunch of testing on one of our apps. I think that the
backup/restore option would be better?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200509/1|||If you have no network connection, you'll need to copy the database to
a physical medium (DVD, tape, USB drive) anyway, so backup/restore
would be the only real option (you could detach/attach, but that would
mean taking the source offline).
Also see this article:
http://support.microsoft.com/defaul...kb;en-us;314546
Simon
hello all
i am develope a travel website, in this site there is an 14 database tables
in my local machine all the work is done properly like (insert, update, delete, adminlogin , client login, etc...)
but in the internet or main server it not work properly
what is this prob.
plz help me
ashwani
what happens when you run it on the internet?
Does it give you an error Message or just not save changes to the database?
|||it could be a connection string problem, the application cannot connect to the database, or ASP.NET account doesn't have the proper rights.
