Restore using SQLDMO

I am using SQLDMO for restoring database from back up file . If the back up file version(SQL2000) is differenent than database version(SQL Express) I am trying to restore, does SQLDMO performs upgrade if the backup files are an earlier version. if no how can i handle that situation?It should restore the database on the server, but set it to compatability level 80, so the SQL 2000 functionality will still be there.

Restore Terminating

I have come across an issue trying to restore a backup of my SQL7 to SQL2000. I migrated to SBS2003 and have restored our database to SQL 2000 many times for testing. Durning my final migration is when I started encountering this error when I execute:
restore database fisc
from disk = 'C:\JEP\fiscbackup
with recovery,
move 'fisc_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\fisc_Data.MDF',
move 'fisc_Log' to 'C:\Program Files\Microsoft SQL Ser4ver\MSSQL\Data\fisc_Log.MDF'
The script has worked before but is now returning error:
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I have run sp_who and it shows 17 loginname entries of sa with a status of either sleeping or background. SPID 51 shows administrator with a status of runnable. I have tried using kill but it will not kill the administrator since that is the login I am
using to run the script.
Any ideas of what I can do to get the database to restore?
Thanks,
Jason Pintok
Hi,
Execute the below script from master to restore the FISC database (Execute
all the lines in a together from query analyzer)
use master
go
alter database fisc set single_user with rollback immediate
go
restore database fisc
from disk = 'C:\JEP\fiscbackup
with recovery,
move 'fisc_Data' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\fisc_Data.MDF',
move 'fisc_Log' to 'C:\Program Files\Microsoft SQL
Ser4ver\MSSQL\Data\fisc_Log.MDF'
Thanks
Hari
MCDBA
"Jason Pintok" <anonymous@.discussions.microsoft.com> wrote in message
news:7301F6E9-1E57-4B94-9591-6C984B05D6ED@.microsoft.com...
> I have come across an issue trying to restore a backup of my SQL7 to
SQL2000. I migrated to SBS2003 and have restored our database to SQL 2000
many times for testing. Durning my final migration is when I started
encountering this error when I execute:
> restore database fisc
> from disk = 'C:\JEP\fiscbackup
> with recovery,
> move 'fisc_Data' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\fisc_Data.MDF',
> move 'fisc_Log' to 'C:\Program Files\Microsoft SQL
Ser4ver\MSSQL\Data\fisc_Log.MDF'
> The script has worked before but is now returning error:
> Server: Msg 3101, Level 16, State 1, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I have run sp_who and it shows 17 loginname entries of sa with a status of
either sleeping or background. SPID 51 shows administrator with a status of
runnable. I have tried using kill but it will not kill the administrator
since that is the login I am using to run the script.
> Any ideas of what I can do to get the database to restore?
> Thanks,
> Jason Pintok
|||Thanks Hari, that nailed it. I appreciate the help.
Jason Pintok

Restore Terminating

I have come across an issue trying to restore a backup of my SQL7 to SQL2000
. I migrated to SBS2003 and have restored our database to SQL 2000 many ti
mes for testing. Durning my final migration is when I started encountering
this error when I execute:
restore database fisc
from disk = 'C:\JEP\fiscbackup
with recovery,
move 'fisc_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\fisc_D
ata.MDF',
move 'fisc_Log' to 'C:\Program Files\Microsoft SQL Ser4ver\MSSQL\Data\fisc_L
og.MDF'
The script has worked before but is now returning error:
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I have run sp_who and it shows 17 loginname entries of sa with a status of e
ither sleeping or background. SPID 51 shows administrator with a status of
runnable. I have tried using kill but it will not kill the administrator si
nce that is the login I am
using to run the script.
Any ideas of what I can do to get the database to restore?
Thanks,
Jason PintokHi,
Execute the below script from master to restore the FISC database (Execute
all the lines in a together from query analyzer)
use master
go
alter database fisc set single_user with rollback immediate
go
restore database fisc
from disk = 'C:\JEP\fiscbackup
with recovery,
move 'fisc_Data' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\fisc_Data.MDF',
move 'fisc_Log' to 'C:\Program Files\Microsoft SQL
Ser4ver\MSSQL\Data\fisc_Log.MDF'
Thanks
Hari
MCDBA
"Jason Pintok" <anonymous@.discussions.microsoft.com> wrote in message
news:7301F6E9-1E57-4B94-9591-6C984B05D6ED@.microsoft.com...
> I have come across an issue trying to restore a backup of my SQL7 to
SQL2000. I migrated to SBS2003 and have restored our database to SQL 2000
many times for testing. Durning my final migration is when I started
encountering this error when I execute:
> restore database fisc
> from disk = 'C:\JEP\fiscbackup
> with recovery,
> move 'fisc_Data' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\fisc_Data.MDF',
> move 'fisc_Log' to 'C:\Program Files\Microsoft SQL
Ser4ver\MSSQL\Data\fisc_Log.MDF'
> The script has worked before but is now returning error:
> Server: Msg 3101, Level 16, State 1, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I have run sp_who and it shows 17 loginname entries of sa with a status of
either sleeping or background. SPID 51 shows administrator with a status of
runnable. I have tried using kill but it will not kill the administrator
since that is the login I am using to run the script.
> Any ideas of what I can do to get the database to restore?
> Thanks,
> Jason Pintok|||Thanks Hari, that nailed it. I appreciate the help.
Jason Pintok

Restore stays in load status after restore norecovery

windows 2003, sql2000, sp3a - load 100gb database from a full backup (in EM)
and remains in load status (I was sure I had it set to recover). note: No t
ranslogs backups ever are produced for this db. No errors I believe when
it completed, although I di
d not look at the msg box too carefully, and clicked ok.
Although I later did a restore with norecovery anyway, it says the following
, any thoughts ?
thanks
Kw
file 'JDE_PRODUCTION_Data' was only partially restored by a database or file
restore. The entire file must be successfully restored before applying the
log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.sounds a bad backup. Can you reproduce the error by doing another restore
through Query analyzer?
"kw" <anonymous@.discussions.microsoft.com> wrote in message
news:03E527B2-999A-4DE8-8D2E-6EA3387C2E67@.microsoft.com...

quote:

---

> windows 2003, sql2000, sp3a - load 100gb database from a full backup (in

---

EM) and remains in load status (I was sure I had it set to recover). note:
No translogs backups ever are produced for this db. No errors I believe
when it completed, although I did not look at the msg box too carefully, and
clicked ok.

quote:

---

> Although I later did a restore with norecovery anyway, it says the

---

following, any thoughts ?

quote:

---

> thanks
> Kw
> file 'JDE_PRODUCTION_Data' was only partially restored by a database or

---

file restore. The entire file must be successfully restored before applying
the log.

quote:

---

> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>

---

Restore stays in load status after restore norecovery

windows 2003, sql2000, sp3a - load 100gb database from a full backup (in EM) and remains in load status (I was sure I had it set to recover). note: No translogs backups ever are produced for this db. No errors I believe when it completed, although I did not look at the msg box too carefully, and clicked ok.
Although I later did a restore with norecovery anyway, it says the following, any thoughts ?
thanks
Kw
file 'JDE_PRODUCTION_Data' was only partially restored by a database or file restore. The entire file must be successfully restored before applying the log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.sounds a bad backup. Can you reproduce the error by doing another restore
through Query analyzer?
"kw" <anonymous@.discussions.microsoft.com> wrote in message
news:03E527B2-999A-4DE8-8D2E-6EA3387C2E67@.microsoft.com...
> windows 2003, sql2000, sp3a - load 100gb database from a full backup (in
EM) and remains in load status (I was sure I had it set to recover). note:
No translogs backups ever are produced for this db. No errors I believe
when it completed, although I did not look at the msg box too carefully, and
clicked ok.
> Although I later did a restore with norecovery anyway, it says the
following, any thoughts ?
> thanks
> Kw
> file 'JDE_PRODUCTION_Data' was only partially restored by a database or
file restore. The entire file must be successfully restored before applying
the log.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>

Restore SQL2000 database using vbscript

Hello,
I am trying to use a VBscript thru installshield to restore a sql2000 database. the script (below) worked fine for default instance of database. but with a specific named instance I get error 3201. any ideas? thx - Prasanna
-----------
code:

dim sql
dim sqlRest

on error resume next
Set sql = CreateObject("SQLDMO.SQLServer")
sql.LoginSecure = True
sql.Connect ".\TEST2000"

Set sqlRest = CreateObject("SQLDMO.Restore")
sqlRest.Files = "c:\testdb.bak"
sqlRest.Database = "TESTDB"
sqlRest.Action = SQLDMORestore_Database
sqlRest.ReplaceDatabase = True
sqlRest.SQLRestore sql
msgbox err.numberFound a solution:

The backup was created using default instance of SQL2000 and once I used a backup created using the 'TEST2000' instance the script worked. I think you can use the RelocateFiles property of the SQLDMO Restore object to set the location of the data and log files but due to time constraints I could not try that solution.

-Prasanna

restore sql 7db to sql2000

Hi there, we tried to restore a sql 7 DB to sql2000, somehow some of
the varchar filed become nvarchar, why is this?

thanks,

-sarah(sarahlin@.yahoo.com) writes:
> Hi there, we tried to restore a sql 7 DB to sql2000, somehow some of
> the varchar filed become nvarchar, why is this?

Sounds like you are victim to a mirage. That does simply just not happen.
You may be mistaken about the source database. Or you are looking into
the wrong database on the target server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||YES I am!! I was indeed looking into a different database.
One quick follow up question, is there any tool that I can use to make
sure that the restored database is "completely" identicial? say,
premission, or data.

Thanks!!

-sarah|||(sarahlin@.yahoo.com) writes:
> YES I am!! I was indeed looking into a different database.
> One quick follow up question, is there any tool that I can use to make
> sure that the restored database is "completely" identicial? say,
> premission, or data.

I guess Red Gate's SQL Compare could do the job. But I have not used
it myself, so I don't know how far it goes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I use SQL Compare frequently. It is awesome. I do not know, however, if
you can compare SQL7 to SQL2000. That seems reasonable enough ;).|||Hi Sarah Lin,

I would bet dollars to donuts that your data is identical. Permissions could
be complicated since they are stored by and id number not by name
(permissions to dchristo on Server1 are not going to translate to dchristo
on Server2).

I would be most concerned that the stored procedures and SQL Code used to
access your database continue to work as advertised. Particularly if any
column or table names in the SQL 7 database are now reserved words in SQL
2000. (I seem to recall having trouble with a column called "Level")

--
-Dick Christoph
<sarahlin@.yahoo.com> wrote in message
news:1141166493.530739.45340@.u72g2000cwu.googlegro ups.com...
> YES I am!! I was indeed looking into a different database.
> One quick follow up question, is there any tool that I can use to make
> sure that the restored database is "completely" identicial? say,
> premission, or data.
> Thanks!!
> -sarah

Restore SQL 2000 different domain?

Hello. Would anyone know of the most advisable method in moving our SQL
2000 on ServerA to another SQL 2000 machine ServerB where we plan on
retiring ServerA and ServerB will take on the same name and IP address as
ServerA? I'm mainly wondering since we have some windows authentication
logins on our SQL Server and moving it to a different domain/machine would
affect anything.
Thanks in advance.
J
Hi
"J" wrote:

> Hello. Would anyone know of the most advisable method in moving our SQL
> 2000 on ServerA to another SQL 2000 machine ServerB where we plan on
> retiring ServerA and ServerB will take on the same name and IP address as
> ServerA? I'm mainly wondering since we have some windows authentication
> logins on our SQL Server and moving it to a different domain/machine would
> affect anything.
>
See http://support.microsoft.com/kb/246133/ on how to transfer logins from
one server to another.
If you rename the server you will need to drop it and re-add it. See
http://support.microsoft.com/kb/303774/
General information about moving databases see
http://support.microsoft.com/kb/314546

> Thanks in advance.
> J
John
|||Thanks for the info. Much appreciated.
Take care.
J
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FC96D7E6-AEC9-4CC7-8602-75B61F1F571E@.microsoft.com...
> Hi
> "J" wrote:
> See http://support.microsoft.com/kb/246133/ on how to transfer logins from
> one server to another.
> If you rename the server you will need to drop it and re-add it. See
> http://support.microsoft.com/kb/303774/
> General information about moving databases see
> http://support.microsoft.com/kb/314546
>
> John

Restore SQL 2000 different domain?

Hello. Would anyone know of the most advisable method in moving our SQL
2000 on ServerA to another SQL 2000 machine ServerB where we plan on
retiring ServerA and ServerB will take on the same name and IP address as
ServerA? I'm mainly wondering since we have some windows authentication
logins on our SQL Server and moving it to a different domain/machine would
affect anything.
Thanks in advance.
JHi
"J" wrote:

> Hello. Would anyone know of the most advisable method in moving our SQL
> 2000 on ServerA to another SQL 2000 machine ServerB where we plan on
> retiring ServerA and ServerB will take on the same name and IP address as
> ServerA? I'm mainly wondering since we have some windows authentication
> logins on our SQL Server and moving it to a different domain/machine would
> affect anything.
>
See http://support.microsoft.com/kb/246133/ on how to transfer logins from
one server to another.
If you rename the server you will need to drop it and re-add it. See
http://support.microsoft.com/kb/303774/
General information about moving databases see
http://support.microsoft.com/kb/314546

> Thanks in advance.
> J
John|||Thanks for the info. Much appreciated.
Take care.
J
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FC96D7E6-AEC9-4CC7-8602-75B61F1F571E@.microsoft.com...
> Hi
> "J" wrote:
>
> See http://support.microsoft.com/kb/246133/ on how to transfer logins from
> one server to another.
> If you rename the server you will need to drop it and re-add it. See
> http://support.microsoft.com/kb/303774/
> General information about moving databases see
> http://support.microsoft.com/kb/314546
>
> Johnsql

restore question on sql2000

Hi
I have a 60 GB sql 2000 STD edition database backup.
I take backup on a remote machine A where I can restore it.
But I have to restore the same database on another machine B where it can
not restore it.
I have already copied the file over a very slow network and when I say
restore and select the backup file I do not see any datafiles listed. I clic
k
on "VIEW CONTENTS" and there is nothing.
What could cause this as same backup seen on the Machine A is fine but when
moved to machine B I can not "view the contents" and restore it.
All the version across all these machines are same.
Tks
MangeshHi
I found the reason. The copy command is still running on the Machine A for
12 hrs.
I did not know that copy command creates the file size as that of source
when it starts copy unlike ftp.
Thanks
Mangesh
"Mangesh Deshpande" wrote:

> Hi
> I have a 60 GB sql 2000 STD edition database backup.
> I take backup on a remote machine A where I can restore it.
> But I have to restore the same database on another machine B where it can
> not restore it.
> I have already copied the file over a very slow network and when I say
> restore and select the backup file I do not see any datafiles listed. I cl
ick
> on "VIEW CONTENTS" and there is nothing.
> What could cause this as same backup seen on the Machine A is fine but whe
n
> moved to machine B I can not "view the contents" and restore it.
> All the version across all these machines are same.
> Tks
> Mangesh|||Hi Mangesh,
You can try using Query Analyzer from ServerB to restore DB's from remote
locations. It is possible the backup gets corrupt during the copy. At leas
t
you can try listing the contents remotely; try this:
--
-- i.e. EXEC xp_cmdshell 'command' <-- remove any <ENTER> bewteen quotes (''
)
-- ServerA is the remote DB Server
-- Share the remote backup folder, in this example is ShareDriveA
-- account is a domain account with access to the shared folder
-- accountpassword (account password)
-- ACCOUNTDOMAIN is the domain where 'account' belongs to
EXEC xp_cmdshell 'net use \\ServerA\ShareDriveA accountpassword
/USER:ACCOUNTDOMAIN\account'
GO
-- to test if the account you used has access to the remote folder
EXEC xp_cmdshell 'dir \\ServerA\ShareFolderA\BackupFolder'
GO
-- to list the backup contents
RESTORE FILELISTONLY
FROM DISK = '\\ServerA\ShareFolder\BackupFolder\Back
upFile.BAK'
-- to finally restore the backup
RESTORE DATABASE DatabaseName
FROM DISK = '\\ServerA\ShareFolder\BackupFolder\Back
upFile.BAK'
WITH REPLACE,
MOVE 'LogicalFileName_Data' TO 'C:\SQLdata\DatabaseName.MDF',
MOVE 'LogicalFileName_Log' TO 'C:\SQLdata\DatabaseName.LDF'
--
"Mangesh Deshpande" wrote:

> Hi
> I have a 60 GB sql 2000 STD edition database backup.
> I take backup on a remote machine A where I can restore it.
> But I have to restore the same database on another machine B where it can
> not restore it.
> I have already copied the file over a very slow network and when I say
> restore and select the backup file I do not see any datafiles listed. I cl
ick
> on "VIEW CONTENTS" and there is nothing.
> What could cause this as same backup seen on the Machine A is fine but whe
n
> moved to machine B I can not "view the contents" and restore it.
> All the version across all these machines are same.
> Tks
> Mangesh

restore question on sql2000

Hi
I have a 60 GB sql 2000 STD edition database backup.
I take backup on a remote machine A where I can restore it.
But I have to restore the same database on another machine B where it can
not restore it.
I have already copied the file over a very slow network and when I say
restore and select the backup file I do not see any datafiles listed. I click
on "VIEW CONTENTS" and there is nothing.
What could cause this as same backup seen on the Machine A is fine but when
moved to machine B I can not "view the contents" and restore it.
All the version across all these machines are same.
Tks
Mangesh
Hi
I found the reason. The copy command is still running on the Machine A for
12 hrs.
I did not know that copy command creates the file size as that of source
when it starts copy unlike ftp.
Thanks
Mangesh
"Mangesh Deshpande" wrote:

> Hi
> I have a 60 GB sql 2000 STD edition database backup.
> I take backup on a remote machine A where I can restore it.
> But I have to restore the same database on another machine B where it can
> not restore it.
> I have already copied the file over a very slow network and when I say
> restore and select the backup file I do not see any datafiles listed. I click
> on "VIEW CONTENTS" and there is nothing.
> What could cause this as same backup seen on the Machine A is fine but when
> moved to machine B I can not "view the contents" and restore it.
> All the version across all these machines are same.
> Tks
> Mangesh
|||Hi Mangesh,
You can try using Query Analyzer from ServerB to restore DB's from remote
locations. It is possible the backup gets corrupt during the copy. At least
you can try listing the contents remotely; try this:
-- i.e. EXEC xp_cmdshell 'command' <-- remove any <ENTER> bewteen quotes ('')
-- ServerA is the remote DB Server
-- Share the remote backup folder, in this example is ShareDriveA
-- account is a domain account with access to the shared folder
-- accountpassword (account password)
-- ACCOUNTDOMAIN is the domain where 'account' belongs to
EXEC xp_cmdshell 'net use \\ServerA\ShareDriveA accountpassword
/USER:ACCOUNTDOMAIN\account'
GO
-- to test if the account you used has access to the remote folder
EXEC xp_cmdshell 'dir \\ServerA\ShareFolderA\BackupFolder'
GO
-- to list the backup contents
RESTORE FILELISTONLY
FROM DISK = '\\ServerA\ShareFolder\BackupFolder\BackupFile.BAK '
-- to finally restore the backup
RESTORE DATABASE DatabaseName
FROM DISK = '\\ServerA\ShareFolder\BackupFolder\BackupFile.BAK '
WITH REPLACE,
MOVE 'LogicalFileName_Data' TO 'C:\SQLdata\DatabaseName.MDF',
MOVE 'LogicalFileName_Log' TO 'C:\SQLdata\DatabaseName.LDF'
"Mangesh Deshpande" wrote:

> Hi
> I have a 60 GB sql 2000 STD edition database backup.
> I take backup on a remote machine A where I can restore it.
> But I have to restore the same database on another machine B where it can
> not restore it.
> I have already copied the file over a very slow network and when I say
> restore and select the backup file I do not see any datafiles listed. I click
> on "VIEW CONTENTS" and there is nothing.
> What could cause this as same backup seen on the Machine A is fine but when
> moved to machine B I can not "view the contents" and restore it.
> All the version across all these machines are same.
> Tks
> Mangesh

restore question on sql2000

Hi
I have a 60 GB sql 2000 STD edition database backup.
I take backup on a remote machine A where I can restore it.
But I have to restore the same database on another machine B where it can
not restore it.
I have already copied the file over a very slow network and when I say
restore and select the backup file I do not see any datafiles listed. I click
on "VIEW CONTENTS" and there is nothing.
What could cause this as same backup seen on the Machine A is fine but when
moved to machine B I can not "view the contents" and restore it.
All the version across all these machines are same.
Tks
MangeshHi
I found the reason. The copy command is still running on the Machine A for
12 hrs.
I did not know that copy command creates the file size as that of source
when it starts copy unlike ftp.
Thanks
Mangesh
"Mangesh Deshpande" wrote:
> Hi
> I have a 60 GB sql 2000 STD edition database backup.
> I take backup on a remote machine A where I can restore it.
> But I have to restore the same database on another machine B where it can
> not restore it.
> I have already copied the file over a very slow network and when I say
> restore and select the backup file I do not see any datafiles listed. I click
> on "VIEW CONTENTS" and there is nothing.
> What could cause this as same backup seen on the Machine A is fine but when
> moved to machine B I can not "view the contents" and restore it.
> All the version across all these machines are same.
> Tks
> Mangesh|||Hi Mangesh,
You can try using Query Analyzer from ServerB to restore DB's from remote
locations. It is possible the backup gets corrupt during the copy. At least
you can try listing the contents remotely; try this:
--
-- i.e. EXEC xp_cmdshell 'command' <-- remove any <ENTER> bewteen quotes ('')
-- ServerA is the remote DB Server
-- Share the remote backup folder, in this example is ShareDriveA
-- account is a domain account with access to the shared folder
-- accountpassword (account password)
-- ACCOUNTDOMAIN is the domain where 'account' belongs to
EXEC xp_cmdshell 'net use \\ServerA\ShareDriveA accountpassword
/USER:ACCOUNTDOMAIN\account'
GO
-- to test if the account you used has access to the remote folder
EXEC xp_cmdshell 'dir \\ServerA\ShareFolderA\BackupFolder'
GO
-- to list the backup contents
RESTORE FILELISTONLY
FROM DISK = '\\ServerA\ShareFolder\BackupFolder\BackupFile.BAK'
-- to finally restore the backup
RESTORE DATABASE DatabaseName
FROM DISK = '\\ServerA\ShareFolder\BackupFolder\BackupFile.BAK'
WITH REPLACE,
MOVE 'LogicalFileName_Data' TO 'C:\SQLdata\DatabaseName.MDF',
MOVE 'LogicalFileName_Log' TO 'C:\SQLdata\DatabaseName.LDF'
--
"Mangesh Deshpande" wrote:
> Hi
> I have a 60 GB sql 2000 STD edition database backup.
> I take backup on a remote machine A where I can restore it.
> But I have to restore the same database on another machine B where it can
> not restore it.
> I have already copied the file over a very slow network and when I say
> restore and select the backup file I do not see any datafiles listed. I click
> on "VIEW CONTENTS" and there is nothing.
> What could cause this as same backup seen on the Machine A is fine but when
> moved to machine B I can not "view the contents" and restore it.
> All the version across all these machines are same.
> Tks
> Mangesh

restore question

I have SQL2000 server with 300 databases. I have a maintenance plan that
performs a full backup of all databases every night.
I have performed test restores of some of those databases but now I'm
wondering in case that something catastrophic happens and I have to restore
entire SQL server, how can I do that?
Would I have to restore every database separately or is there a way to
restore all of them in one step (some transact SQL script ...)?
Tom
Hi,
Best option is to restore the system databases first and then restore the
User databases one by one. You could write the RESTORE DATABASE
Script and execute it from Query Analyzer in a single execution.
See the below URLS for the step by step approach with different
methodologies:-
http://support.microsoft.com/default...b;en-us;304692
http://support.microsoft.com/default...b;en-us;224071
http://support.microsoft.com/default...b;en-us;314546
Thanks
Hari
SQL Server MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:%23XIEZ1VjFHA.2904@.tk2msftngp13.phx.gbl...
>I have SQL2000 server with 300 databases. I have a maintenance plan that
>performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to
> restore entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>
|||Hi Tom,
This gives you an opportunity to move some of the databases to different
servers, to mitigate the risk of running all the databases on the same server.
Also make sure that you are running the server on disk array's, to avoid any
disk failures.
For avoid dependency on OS, you can look at fail-over clusterting also.
- - - - - - - - -
Thanks
Yogish
"Tom" wrote:

> I have SQL2000 server with 300 databases. I have a maintenance plan that
> performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to restore
> entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>
>

restore question

I have SQL2000 server with 300 databases. I have a maintenance plan that
performs a full backup of all databases every night.
I have performed test restores of some of those databases but now I'm
wondering in case that something catastrophic happens and I have to restore
entire SQL server, how can I do that?
Would I have to restore every database separately or is there a way to
restore all of them in one step (some transact SQL script ...)?
TomHi,
Best option is to restore the system databases first and then restore the
User databases one by one. You could write the RESTORE DATABASE
Script and execute it from Query Analyzer in a single execution.
See the below URLS for the step by step approach with different
methodologies:-
http://support.microsoft.com/default.aspx?scid=kb;en-us;304692
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
Thanks
Hari
SQL Server MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:%23XIEZ1VjFHA.2904@.tk2msftngp13.phx.gbl...
>I have SQL2000 server with 300 databases. I have a maintenance plan that
>performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to
> restore entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>|||Hi Tom,
This gives you an opportunity to move some of the databases to different
servers, to mitigate the risk of running all the databases on the same server.
Also make sure that you are running the server on disk array's, to avoid any
disk failures.
For avoid dependency on OS, you can look at fail-over clusterting also.
--
- - - - - - - - -
Thanks
Yogish
"Tom" wrote:
> I have SQL2000 server with 300 databases. I have a maintenance plan that
> performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to restore
> entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>
>

restore question

I use sql2000/sp3 in w2k/sp3,if i can
restore a database from a net device direct(not copy the
bak file to local disk)?I think you want to do something like this
RESTORE DATABASE FROM DISK = '\\ServerName\ShareName\FileName'
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"peng" <zhoupeng1971@.yahoo.com> wrote in message
news:022d01c35cbd$68fde570$a001280a@.phx.gbl...
> I use sql2000/sp3 in w2k/sp3,if i can
> restore a database from a net device direct(not copy the
> bak file to local disk)?|||Hi Peng,
Make sure that the SQL Server Startup service account has the permission to
access the network for this to work.
Sincerely,
Yih-Yoon Lee [Microsoft]
Microsoft SQL Server Support
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

restore question

I have SQL2000 server with 300 databases. I have a maintenance plan that
performs a full backup of all databases every night.
I have performed test restores of some of those databases but now I'm
wondering in case that something catastrophic happens and I have to restore
entire SQL server, how can I do that?
Would I have to restore every database separately or is there a way to
restore all of them in one step (some transact SQL script ...)?
TomHi,
Best option is to restore the system databases first and then restore the
User databases one by one. You could write the RESTORE DATABASE
Script and execute it from Query Analyzer in a single execution.
See the below URLS for the step by step approach with different
methodologies:-
http://support.microsoft.com/defaul...kb;en-us;304692
http://support.microsoft.com/defaul...kb;en-us;224071
http://support.microsoft.com/defaul...kb;en-us;314546
Thanks
Hari
SQL Server MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:%23XIEZ1VjFHA.2904@.tk2msftngp13.phx.gbl...
>I have SQL2000 server with 300 databases. I have a maintenance plan that
>performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to
> restore entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>|||Hi Tom,
This gives you an opportunity to move some of the databases to different
servers, to mitigate the risk of running all the databases on the same serve
r.
Also make sure that you are running the server on disk array's, to avoid any
disk failures.
For avoid dependency on OS, you can look at fail-over clusterting also.
--
- - - - - - - - -
Thanks
Yogish
"Tom" wrote:

> I have SQL2000 server with 300 databases. I have a maintenance plan that
> performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to restor
e
> entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>
>

Restore problem - anyone seen this error before?

I am having a problem restoring a database (SQL2000).
from a .bak file device. When I attempt a restore I get
the following message
"The media set for database xxx has 3 family members but
only 1 are provided. All memebers must be provided.
Restore database is terminating abnormally"
The .bak file has been zipped and unzipped with winzip9.
Could this have corrupted the file?
Regards
Neil Gibson
ngibson(revovethis)@.mclgroup.co.ukI think you may have made the database backup to multiple files. You need
to find the other 2 backup files to be able to restore the database.
Look in the msdb..backupmediafamily table on the server you made the backup
to find the names of the other 2 files if you don't know them.
<plug> If you need small backups, give MiniSQLBackup a spin. </plug>
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Neil Gibson" <anonymous@.discussions.microsoft.com> wrote in message
news:17b8a01c4494d$a81ed980$a001280a@.phx
.gbl...
> I am having a problem restoring a database (SQL2000).
> from a .bak file device. When I attempt a restore I get
> the following message
> "The media set for database xxx has 3 family members but
> only 1 are provided. All memebers must be provided.
> Restore database is terminating abnormally"
> The .bak file has been zipped and unzipped with winzip9.
> Could this have corrupted the file?
> Regards
> Neil Gibson
> ngibson(revovethis)@.mclgroup.co.uk

Restore problem - anyone seen this error before?

I am having a problem restoring a database (SQL2000).
from a .bak file device. When I attempt a restore I get
the following message
"The media set for database xxx has 3 family members but
only 1 are provided. All memebers must be provided.
Restore database is terminating abnormally"
The .bak file has been zipped and unzipped with winzip9.
Could this have corrupted the file?
Regards
Neil Gibson
ngibson(revovethis)@.mclgroup.co.uk
I think you may have made the database backup to multiple files. You need
to find the other 2 backup files to be able to restore the database.
Look in the msdb..backupmediafamily table on the server you made the backup
to find the names of the other 2 files if you don't know them.
<plug> If you need small backups, give MiniSQLBackup a spin. </plug>
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Neil Gibson" <anonymous@.discussions.microsoft.com> wrote in message
news:17b8a01c4494d$a81ed980$a001280a@.phx.gbl...
> I am having a problem restoring a database (SQL2000).
> from a .bak file device. When I attempt a restore I get
> the following message
> "The media set for database xxx has 3 family members but
> only 1 are provided. All memebers must be provided.
> Restore database is terminating abnormally"
> The .bak file has been zipped and unzipped with winzip9.
> Could this have corrupted the file?
> Regards
> Neil Gibson
> ngibson(revovethis)@.mclgroup.co.uk

Restore problem - anyone seen this error before?

I am having a problem restoring a database (SQL2000).
from a .bak file device. When I attempt a restore I get
the following message
"The media set for database xxx has 3 family members but
only 1 are provided. All memebers must be provided.
Restore database is terminating abnormally"
The .bak file has been zipped and unzipped with winzip9.
Could this have corrupted the file?
Regards
Neil Gibson
ngibson(revovethis)@.mclgroup.co.ukI think you may have made the database backup to multiple files. You need
to find the other 2 backup files to be able to restore the database.
Look in the msdb..backupmediafamily table on the server you made the backup
to find the names of the other 2 files if you don't know them.
<plug> If you need small backups, give MiniSQLBackup a spin. </plug>
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Neil Gibson" <anonymous@.discussions.microsoft.com> wrote in message
news:17b8a01c4494d$a81ed980$a001280a@.phx.gbl...
> I am having a problem restoring a database (SQL2000).
> from a .bak file device. When I attempt a restore I get
> the following message
> "The media set for database xxx has 3 family members but
> only 1 are provided. All memebers must be provided.
> Restore database is terminating abnormally"
> The .bak file has been zipped and unzipped with winzip9.
> Could this have corrupted the file?
> Regards
> Neil Gibson
> ngibson(revovethis)@.mclgroup.co.uk

Restore of database fails

Hi,
I am trying to move an SQL2000 database (abt 10 GB) to a Win2000 server by
using the restore option with the UNC name (\\servername\G$\path\file), since
the restore is to be performed over the network (Novell) to a disk on the
remote computer. This however fails with OS error=5(access is denied). The
user who performs this operation is administrator on the computer. Could you
please inform why this is not possible and if there is a better way to
perform this operation.
Thanks!
Susanne
Hi Susanne
It's the account that runs the SQLServer service that needs to have access
to the UNC path. You can test the access by logging on to the server as the
user that runs the SQL server service and then verify that you now can get
to the file.
Regards
Steen
Susanne wrote:
> Hi,
> I am trying to move an SQL2000 database (abt 10 GB) to a Win2000
> server by using the restore option with the UNC name
> (\\servername\G$\path\file), since the restore is to be performed
> over the network (Novell) to a disk on the remote computer. This
> however fails with OS error=5(access is denied). The user who
> performs this operation is administrator on the computer. Could you
> please inform why this is not possible and if there is a better way
> to perform this operation.
> Thanks!
> Susanne
|||That's exactly the point. I can access the file using Windows Exporer on the
server, however not when trying to restore the database (using SQL Enterprise
Manager).
"Steen Persson" wrote:

> Hi Susanne
> It's the account that runs the SQLServer service that needs to have access
> to the UNC path. You can test the access by logging on to the server as the
> user that runs the SQL server service and then verify that you now can get
> to the file.
> Regards
> Steen
> Susanne wrote:
>
>
|||Suzanne
We do this a lot and on the target SQL Server Machine set the logon in the
services startup properties to a domain user with access to the unc path (I
just used <domain>\Administrator)
evan
"Susanne" <Susanne@.discussions.microsoft.com> wrote in message
news:08EC523C-8FCF-401A-B8FA-4BE58FD8F269@.microsoft.com...
> Hi,
> I am trying to move an SQL2000 database (abt 10 GB) to a Win2000 server by
> using the restore option with the UNC name (\\servername\G$\path\file),
since
> the restore is to be performed over the network (Novell) to a disk on the
> remote computer. This however fails with OS error=5(access is denied).
The
> user who performs this operation is administrator on the computer. Could
you
> please inform why this is not possible and if there is a better way to
> perform this operation.
> Thanks!
> Susanne
|||But then you have to change the account that runs the SQLServer srvice to an
account that has the access or grant the existing account the required
access. If you can't do that, you'll have to copy the files to the server
and then do the retstore from a local file.
Regards
Steen
Susanne wrote:[vbcol=seagreen]
> That's exactly the point. I can access the file using Windows Exporer
> on the server, however not when trying to restore the database (using
> SQL Enterprise Manager).
>
>
> "Steen Persson" wrote:
|||It is a comfort to read that this can be done. I have tried to restore, after
changing the logon for the SQL Server Manager to Administrator and restarting
the service, but it still doesn't work. I get the same error. Unfortunately,
there is not enough of space on the target server so I can run the restore
from a local disk.
"evan b" wrote:
> Suzanne
> We do this a lot and on the target SQL Server Machine set the logon in the
> services startup properties to a domain user with access to the unc path (I
> just used <domain>\Administrator)
> evan
> "Susanne" <Susanne@.discussions.microsoft.com> wrote in message
> news:08EC523C-8FCF-401A-B8FA-4BE58FD8F269@.microsoft.com...
> since
> The
> you
>
>
|||Hi Susanne
I have to admit that I don't know if there can be amy issues when you are
using a Novell network, but I doubt.
You say that you have changed the logon for SQL Server Manager, but what do
you mean with that? It's the account that runs the SQL Server Service that
you need to set to an account that has access to the share. When you have
done that, then log on to the server using tihs account and then verify that
you can access the share with that account. If it still doesn't work, then
try to post the code you are using - maybe there are something in you code
that isn't correct.
Regards
Steen
Susanne wrote:[vbcol=seagreen]
> It is a comfort to read that this can be done. I have tried to
> restore, after changing the logon for the SQL Server Manager to
> Administrator and restarting the service, but it still doesn't work.
> I get the same error. Unfortunately, there is not enough of space on
> the target server so I can run the restore from a local disk.
> "evan b" wrote: