RESTORE with RECOVERY and REPLACE?

Just to be clear, I am not restoring to a different machine. Just trying to
create a new database (which is not hanging from my first attempt) from an
existing database.
TahnksActually, it seems that RECOVERY is the default, so maybe I don't need to
specify it.
A better command might be
RESTORE DATABASE stuckdb
FROM c:\mybackups\template.bak
WITH REPLACE|||Well, that did not work. It says no entry in sysdevices for
'c:\mybackups\template.bak'|||Tried adding DISK and putting a single quote around the path. Seems to have
worked.
Thanks!

> RESTORE DATABASE stuckdb
> FROM DISK = 'c:\mybackups\template.bak'
> WITH REPLACE|||"mike" <mike@.commmcasssttt.com> wrote in message
news:12a7td2rqcpoo7d@.corp.supernews.com...
> Well, that did not work. It says no entry in sysdevices for
> 'c:\mybackups\template.bak'
>
below is a script that you can adapt for your own purposes. You really need
to read BOL for the commands involved to make sure you understand exactly
what happens. BOL also has many useful examples. To restore to a new
database from a backup of an existing database (the template in your
description), just use a new database name in the restore command ("test_db"
in this example) and be sure to specify the files you want to use for the
database (the move options). The 2nd command is useful to identify the
logical names (used by the database in the backup) that need to be moved.
use master
go
exec xp_cmdshell 'dir C:\sql2k\MSSQL\BACKUP\ /o-d'
go
RESTORE FILELISTONLY
FROM DISK='C:\sql2k\MSSQL\BACKUP\save_me.BAK'
GO
RESTORE DATABASE test_db
FROM DISK='C:\sql2k\MSSQL\BACKUP\save_me.BAK'
WITH RECOVERY, STATS, REPLACE,
MOVE 'main_Data' TO 'C:\sql2k\MSSQL\DATA\test_db_DATA.mdf',
MOVE 'main_Log' TO 'C:\sql2k\MSSQL\DATA\test_db_Log.ldf'
GO|||Thanks to those who responded earlier. If someone would check my commands
below, I would appreciate it.
Again, what I tried to do is create a new database from an existing template
database. To do this, I tried (in Enterprise Manager) to restore from the
template to a new database name. However, something went wrong and at the
end of the restore process I got an error about "log begins at 30000 and is
too late to apply to database." The new database is stuck with a (loading)
next to it.
So I am going to try to do it in Query AnalyzeR with the RECOVERY option.
After trying to decipher the books online, this is what I came up with:
RESTORE DATABASE stuckdb
FROM c:\mybackups\template.bak
WITH RECOVERY, REPLACE
Thank you|||Just to be clear, I am not restoring to a different machine. Just trying to
create a new database (which is not hanging from my first attempt) from an
existing database.
Tahnks|||Actually, it seems that RECOVERY is the default, so maybe I don't need to
specify it.
A better command might be
RESTORE DATABASE stuckdb
FROM c:\mybackups\template.bak
WITH REPLACE|||Well, that did not work. It says no entry in sysdevices for
'c:\mybackups\template.bak'|||Tried adding DISK and putting a single quote around the path. Seems to have
worked.
Thanks!

> RESTORE DATABASE stuckdb
> FROM DISK = 'c:\mybackups\template.bak'
> WITH REPLACE

Restore using Microsoft SQL Server Management Studio?

Can someone help me with restoring a database using sql server
management studio? Every time I attempt to restore a database, I get
the following message:
Restore failed for Server 'localhost\SQLExpress'.
Additional Information:
System.Data.SqlClient.SqlError: Exclusive access could not be
obtained because the database is in use.
I have no problem restarting the db server locally, but in a
production environment where the db is shared, I cannot restart it.
How can I restore a db successfully using the management studio?
Perhaps MS should add a checkbox to the restore dialog that kills all
processes before the restore is issued...
Anyway, thanks for any help
JimHi
It seems that someone is using the database and therefore stopping you
restoring over it. Look at the current activity on the server and check to
see who is connected. You can either kill the process of get the to logoff
properly.
John
"Jim Geurts" wrote:
> Can someone help me with restoring a database using sql server
> management studio? Every time I attempt to restore a database, I get
> the following message:
> Restore failed for Server 'localhost\SQLExpress'.
> Additional Information:
> System.Data.SqlClient.SqlError: Exclusive access could not be
> obtained because the database is in use.
>
> I have no problem restarting the db server locally, but in a
> production environment where the db is shared, I cannot restart it.
> How can I restore a db successfully using the management studio?
> Perhaps MS should add a checkbox to the restore dialog that kills all
> processes before the restore is issued...
> Anyway, thanks for any help
> Jim
>|||On Wed, 31 Oct 2007 07:41:02 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Hi
>It seems that someone is using the database and therefore stopping you
>restoring over it. Look at the current activity on the server and check to
>see who is connected. You can either kill the process of get the to logoff
>properly.
>John
And don't forget to allow for it being yourself! 8-)
Roy Harvey
Beacon Falls, CT|||I would first set the database to single-user mode or restricted user during
restore if you are encountering such errors
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:A29E8900-FD44-4493-84DD-CDCBB0D16F6D@.microsoft.com...
> Hi
> It seems that someone is using the database and therefore stopping you
> restoring over it. Look at the current activity on the server and check to
> see who is connected. You can either kill the process of get the to logoff
> properly.
> John
> "Jim Geurts" wrote:
>> Can someone help me with restoring a database using sql server
>> management studio? Every time I attempt to restore a database, I get
>> the following message:
>> Restore failed for Server 'localhost\SQLExpress'.
>> Additional Information:
>> System.Data.SqlClient.SqlError: Exclusive access could not be
>> obtained because the database is in use.
>>
>> I have no problem restarting the db server locally, but in a
>> production environment where the db is shared, I cannot restart it.
>> How can I restore a db successfully using the management studio?
>> Perhaps MS should add a checkbox to the restore dialog that kills all
>> processes before the restore is issued...
>> Anyway, thanks for any help
>> Jim
>>|||On Oct 31, 10:04 am, "bass_player [SBS-MVP]" <bass_pla...@.mvps.org>
wrote:
> I would first set the database to single-user mode or restricted user during
> restore if you are encountering such errors
> "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
> news:A29E8900-FD44-4493-84DD-CDCBB0D16F6D@.microsoft.com...
> > Hi
> > It seems that someone is using the database and therefore stopping you
> > restoring over it. Look at the current activity on the server and check to
> > see who is connected. You can either kill the process of get the to logoff
> > properly.
> > John
> > "Jim Geurts" wrote:
> >> Can someone help me with restoring a database using sql server
> >> management studio? Every time I attempt to restore a database, I get
> >> the following message:
> >> Restore failed for Server 'localhost\SQLExpress'.
> >> Additional Information:
> >> System.Data.SqlClient.SqlError: Exclusive access could not be
> >> obtained because the database is in use.
> >> I have no problem restarting the db server locally, but in a
> >> production environment where the db is shared, I cannot restart it.
> >> How can I restore a db successfully using the management studio?
> >> Perhaps MS should add a checkbox to the restore dialog that kills all
> >> processes before the restore is issued...
> >> Anyway, thanks for any help
> >> Jim
I tried setting the production db to single user mode in the past, but
then it wouldn't let me connect to restore the db. Honestly, there
has to be a simple way of doing this, no? This seems like a fairly
common task. I'm amazed that it's so difficult to have it work with a
click of a button, using the management studio. Is there a block of
sql that will accomplish everything that is needed (set to single user
mode, restore db, restore back to multi-user mode, etc)?|||On Oct 31, 9:55 am, Jim Geurts <jgeu...@.gmail.com> wrote:
> Can someone help me with restoring a database using sql server
> management studio? Every time I attempt to restore a database, I get
> the following message:
> Restore failed for Server 'localhost\SQLExpress'.
> Additional Information:
> System.Data.SqlClient.SqlError: Exclusive access could not be
> obtained because the database is in use.
> I have no problem restarting the db server locally, but in a
> production environment where the db is shared, I cannot restart it.
> How can I restore a db successfully using the management studio?
> Perhaps MS should add a checkbox to the restore dialog that kills all
> processes before the restore is issued...
> Anyway, thanks for any help
> Jim
Here is another one of the many ways:
Detach the Database. OR
Kill all conections in the Database and Run the Restore T-SQL(Use With
Replace) or thourgh Studio
How critical is this DB ( How many users are connected at one time ),
if not many users, sut them off(Kill) the then restoe the DB|||Hi
You should not be connected to the database to restore, if the database is
the default for your login it may be an issue connecting once you have a
database in single user mode and the "connection" is used. SQL2000 used to
have a disconnect button on the detach dialog which could be used to remove
connections but this is not available on SQL2005.
John
"Jim Geurts" wrote:
> On Oct 31, 10:04 am, "bass_player [SBS-MVP]" <bass_pla...@.mvps.org>
> wrote:
> > I would first set the database to single-user mode or restricted user during
> > restore if you are encountering such errors
> >
> > "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
> >
> > news:A29E8900-FD44-4493-84DD-CDCBB0D16F6D@.microsoft.com...
> >
> > > Hi
> >
> > > It seems that someone is using the database and therefore stopping you
> > > restoring over it. Look at the current activity on the server and check to
> > > see who is connected. You can either kill the process of get the to logoff
> > > properly.
> >
> > > John
> >
> > > "Jim Geurts" wrote:
> >
> > >> Can someone help me with restoring a database using sql server
> > >> management studio? Every time I attempt to restore a database, I get
> > >> the following message:
> >
> > >> Restore failed for Server 'localhost\SQLExpress'.
> >
> > >> Additional Information:
> > >> System.Data.SqlClient.SqlError: Exclusive access could not be
> > >> obtained because the database is in use.
> >
> > >> I have no problem restarting the db server locally, but in a
> > >> production environment where the db is shared, I cannot restart it.
> > >> How can I restore a db successfully using the management studio?
> > >> Perhaps MS should add a checkbox to the restore dialog that kills all
> > >> processes before the restore is issued...
> >
> > >> Anyway, thanks for any help
> >
> > >> Jim
> I tried setting the production db to single user mode in the past, but
> then it wouldn't let me connect to restore the db. Honestly, there
> has to be a simple way of doing this, no? This seems like a fairly
> common task. I'm amazed that it's so difficult to have it work with a
> click of a button, using the management studio. Is there a block of
> sql that will accomplish everything that is needed (set to single user
> mode, restore db, restore back to multi-user mode, etc)?
>|||You can use the toolbar button to script the restore that you are attempting
to do in SSMS. Then add to the top of that script the following command:
ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
After the backup place it back in Multi-user mode.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jim Geurts" <jgeurts@.gmail.com> wrote in message
news:1193844965.038551.326520@.e9g2000prf.googlegroups.com...
> On Oct 31, 10:04 am, "bass_player [SBS-MVP]" <bass_pla...@.mvps.org>
> wrote:
>> I would first set the database to single-user mode or restricted user
>> during
>> restore if you are encountering such errors
>> "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
>> news:A29E8900-FD44-4493-84DD-CDCBB0D16F6D@.microsoft.com...
>> > Hi
>> > It seems that someone is using the database and therefore stopping you
>> > restoring over it. Look at the current activity on the server and check
>> > to
>> > see who is connected. You can either kill the process of get the to
>> > logoff
>> > properly.
>> > John
>> > "Jim Geurts" wrote:
>> >> Can someone help me with restoring a database using sql server
>> >> management studio? Every time I attempt to restore a database, I get
>> >> the following message:
>> >> Restore failed for Server 'localhost\SQLExpress'.
>> >> Additional Information:
>> >> System.Data.SqlClient.SqlError: Exclusive access could not be
>> >> obtained because the database is in use.
>> >> I have no problem restarting the db server locally, but in a
>> >> production environment where the db is shared, I cannot restart it.
>> >> How can I restore a db successfully using the management studio?
>> >> Perhaps MS should add a checkbox to the restore dialog that kills all
>> >> processes before the restore is issued...
>> >> Anyway, thanks for any help
>> >> Jim
> I tried setting the production db to single user mode in the past, but
> then it wouldn't let me connect to restore the db. Honestly, there
> has to be a simple way of doing this, no? This seems like a fairly
> common task. I'm amazed that it's so difficult to have it work with a
> click of a button, using the management studio. Is there a block of
> sql that will accomplish everything that is needed (set to single user
> mode, restore db, restore back to multi-user mode, etc)?
>|||On Oct 31, 12:40 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> You can use the toolbar button to script the restore that you are attempting
> to do in SSMS. Then add to the top of that script the following command:
> ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> After the backup place it back in Multi-user mode.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
> "Jim Geurts" <jgeu...@.gmail.com> wrote in message
> news:1193844965.038551.326520@.e9g2000prf.googlegroups.com...
> > On Oct 31, 10:04 am, "bass_player [SBS-MVP]" <bass_pla...@.mvps.org>
> > wrote:
> >> I would first set the database to single-user mode or restricted user
> >> during
> >> restore if you are encountering such errors
> >> "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
> >>news:A29E8900-FD44-4493-84DD-CDCBB0D16F6D@.microsoft.com...
> >> > Hi
> >> > It seems that someone is using the database and therefore stopping you
> >> > restoring over it. Look at the current activity on the server and check
> >> > to
> >> > see who is connected. You can either kill the process of get the to
> >> > logoff
> >> > properly.
> >> > John
> >> > "Jim Geurts" wrote:
> >> >> Can someone help me with restoring a database using sql server
> >> >> management studio? Every time I attempt to restore a database, I get
> >> >> the following message:
> >> >> Restore failed for Server 'localhost\SQLExpress'.
> >> >> Additional Information:
> >> >> System.Data.SqlClient.SqlError: Exclusive access could not be
> >> >> obtained because the database is in use.
> >> >> I have no problem restarting the db server locally, but in a
> >> >> production environment where the db is shared, I cannot restart it.
> >> >> How can I restore a db successfully using the management studio?
> >> >> Perhaps MS should add a checkbox to the restore dialog that kills all
> >> >> processes before the restore is issued...
> >> >> Anyway, thanks for any help
> >> >> Jim
> > I tried setting the production db to single user mode in the past, but
> > then it wouldn't let me connect to restore the db. Honestly, there
> > has to be a simple way of doing this, no? This seems like a fairly
> > common task. I'm amazed that it's so difficult to have it work with a
> > click of a button, using the management studio. Is there a block of
> > sql that will accomplish everything that is needed (set to single user
> > mode, restore db, restore back to multi-user mode, etc)?
Cool, thanks Andrew. I'll give that a shot.
@.Maninder: The database server is very critical. It's at a hosting
provider and is shared with many other users. Thus, my login defaults
to my database and as John mentioned, that's probably why it always
says someone is connected. As far as my specific db, there are
usually only two or three applications that access it.

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