Restore using Microsoft SQL Server Management Studio?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim Geurts - 31 Oct 2007 14:55 GMT

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

Reply to this Message

John Bell - 31 Oct 2007 15:41 GMT

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

> Can someone help me with restoring a database using sql server
> management studio? Every time I attempt to restore a database, I get
[quoted text clipped - 15 lines]
>
> Jim

Reply to this Message

Roy Harvey (SQL Server MVP) - 31 Oct 2007 16:00 GMT

>Hi
>
[quoted text clipped - 4 lines]
>
>John

And don't forget to allow for it being yourself! 8-)

Roy Harvey
Beacon Falls, CT

Reply to this Message

bass_player [SBS-MVP] - 31 Oct 2007 16:04 GMT

I would first set the database to single-user mode or restricted user during
restore if you are encountering such errors

> Hi
>
[quoted text clipped - 24 lines]
>>
>> Jim

Reply to this Message

Jim Geurts - 31 Oct 2007 16:36 GMT

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
[quoted text clipped - 27 lines]
>
> >> 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)?

Reply to this Message

John Bell - 31 Oct 2007 17:53 GMT

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

> On Oct 31, 10:04 am, "bass_player [SBS-MVP]" <bass_pla...@mvps.org>
> wrote:
[quoted text clipped - 37 lines]
> sql that will accomplish everything that is needed (set to single user
> mode, restore db, restore back to multi-user mode, etc)?

Reply to this Message

Andrew J. Kelly - 31 Oct 2007 18:40 GMT

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.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> On Oct 31, 10:04 am, "bass_player [SBS-MVP]" <bass_pla...@mvps.org>
> wrote:
[quoted text clipped - 40 lines]
> sql that will accomplish everything that is needed (set to single user
> mode, restore db, restore back to multi-user mode, etc)?

Reply to this Message

Jim Geurts - 31 Oct 2007 21:45 GMT

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:
[quoted text clipped - 55 lines]
> > 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.

Reply to this Message

Maninder - 31 Oct 2007 17:34 GMT

> Can someone help me with restoring a database using sql server
> management studio? Every time I attempt to restore a database, I get
[quoted text clipped - 15 lines]
>
> 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

Reply to this Message