Database Restore Error

I have a question about restoring a database using the GUI. If we log in as
the db_owner using an SQL account, click on databases and then restore we
get the message:
"RESTORE cannot process database 'XXX' because it is in use by this session.
It is recommended that the master database be used when performing this
operation."
We did not have any active connections and for the id we set the default
database to master database.
If I try doing the restore using transact-SQL it works but I am curious on
how to get it working using the GUI so our developers can do their own
restores.
Seems you have a bug in the GUI so it doesn't put the connection in the master database before
executing the RESTORE command. What GUI are you using? EM, SSMS, 3:rd party? Also, is it service
packed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:uTnOt3W7GHA.1012@.TK2MSFTNGP05.phx.gbl...
>I have a question about restoring a database using the GUI. If we log in as the db_owner using an
>SQL account, click on databases and then restore we get the message:
> "RESTORE cannot process database 'XXX' because it is in use by this session. It is recommended
> that the master database be used when performing this operation."
> We did not have any active connections and for the id we set the default database to master
> database.
> If I try doing the restore using transact-SQL it works but I am curious on how to get it working
> using the GUI so our developers can do their own restores.
>
|||We are using SSMS and it is patched with SQL Server 2005 SP1. The same
problem occurs on other servers here as well.
Thanks,
Loren Z
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e3Mn9SX7GHA.1560@.TK2MSFTNGP04.phx.gbl...
> Seems you have a bug in the GUI so it doesn't put the connection in the
> master database before executing the RESTORE command. What GUI are you
> using? EM, SSMS, 3:rd party? Also, is it service packed?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
> news:uTnOt3W7GHA.1012@.TK2MSFTNGP05.phx.gbl...
>
|||I just tired a couple of restores using SSMS on SP 1 and
didn't have any problems with the restore. The only time it
failed is if I had a connection in the database. Are you
sure you don't have any connections in the database you are
trying to restore?
Check all connections and make sure none are in the database
you want to restore. Open up SSMS. Right click on the
database, select Tasks, Restore, Database and restore from
there.
-Sue
On Wed, 11 Oct 2006 14:58:05 -0600, "Loren Z"
<anonymous@.discussions.microsoft.com> wrote:

>We are using SSMS and it is patched with SQL Server 2005 SP1. The same
>problem occurs on other servers here as well.
>Thanks,
>Loren Z
>
>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:e3Mn9SX7GHA.1560@.TK2MSFTNGP04.phx.gbl...
>
|||Don't know if it relates to this, but we use a medical database program
called Misys. It uses a service. If I want to restore the database from
a backup, I first have to stop the Misys Homecare Server service.
Otherwise I get an "in-use" message...
Regards,
Hank Arnold
Loren Z wrote:
> I have a question about restoring a database using the GUI. If we log in as
> the db_owner using an SQL account, click on databases and then restore we
> get the message:
> "RESTORE cannot process database 'XXX' because it is in use by this session.
> It is recommended that the master database be used when performing this
> operation."
> We did not have any active connections and for the id we set the default
> database to master database.
> If I try doing the restore using transact-SQL it works but I am curious on
> how to get it working using the GUI so our developers can do their own
> restores.
>
|||I checked the properties of the SQL ID and the default database is the
database which this ID owns. As soon as I open the restore window a
connection to this database is established. I changed the default database
to master and then tried opening the restore window and the connection was
not there. A restore was then performed successfully.
Is this the way SQL should function by design? That when you open the
restore window a connection is automatically established to the default
database of the SQL ID? In order for our users to perform simple backups
should I set the default database to master?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:fv9ri25n8b361pkvmsap0aaga96ujmgjug@.4ax.com...
>I just tired a couple of restores using SSMS on SP 1 and
> didn't have any problems with the restore. The only time it
> failed is if I had a connection in the database. Are you
> sure you don't have any connections in the database you are
> trying to restore?
> Check all connections and make sure none are in the database
> you want to restore. Open up SSMS. Right click on the
> database, select Tasks, Restore, Database and restore from
> there.
> -Sue
> On Wed, 11 Oct 2006 14:58:05 -0600, "Loren Z"
> <anonymous@.discussions.microsoft.com> wrote:
>
|||> Is this the way SQL should function by design?
Seems like an oversight in the tool (SSMS) you are using. Consider reporting it to
http://connect.microsoft.com/site/si...spx?SiteID=68.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:ecN6VRh7GHA.728@.TK2MSFTNGP04.phx.gbl...
>I checked the properties of the SQL ID and the default database is the database which this ID owns.
>As soon as I open the restore window a connection to this database is established. I changed the
>default database to master and then tried opening the restore window and the connection was not
>there. A restore was then performed successfully.
> Is this the way SQL should function by design? That when you open the restore window a connection
> is automatically established to the default database of the SQL ID? In order for our users to
> perform simple backups should I set the default database to master?
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:fv9ri25n8b361pkvmsap0aaga96ujmgjug@.4ax.com...
>
|||Lines: 100
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
NNTP-Posting-Host: n175en1.energy.gov.ab.ca 199.214.175.1
Xref: leafnode.mcse.ms microsoft.public.sqlserver.tools:1109
Thanks Tibor, have you confirmed this problem as well? If not would you be
able to replicate this behaviour?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e%23mW5jh7GHA.4996@.TK2MSFTNGP03.phx.gbl...
> Seems like an oversight in the tool (SSMS) you are using. Consider
> reporting it to http://connect.microsoft.com/site/si...spx?SiteID=68.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
> news:ecN6VRh7GHA.728@.TK2MSFTNGP04.phx.gbl...
>
|||I just did.
So if a users default database is the same as the database
they are going to restore, they will get this error when
using SSMS.
-Sue
On Thu, 12 Oct 2006 10:29:30 -0600, "Loren Z"
<anonymous@.discussions.microsoft.com> wrote:

>Thanks Tibor, have you confirmed this problem as well? If not would you be
>able to replicate this behaviour?
>
>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:e%23mW5jh7GHA.4996@.TK2MSFTNGP03.phx.gbl...
>
|||Chris Wood (anonymous@.discussions.microsoft.com) writes:
> Does anyone look at the Connect Site? I recorded the bug on October 12th
> and nobody appears to have even checked it out.
Patience, my dear friend!
The bugs you file there are sent to the internal bug database where the
developers deal with them. You may get a reply the next day, and it
may take several months. I can testify, as I have submitted quite a
few bugs.
It's a good idea to register a notification address so that you get
mail when the bug is changed. Not the least, because sometimes the
bug changes status without any comment. (Usually when this happens it
is due to that the developer forgot to fill in a crucial field in the
tool the devs are using. That is, they don't work directly against the
Connect site.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx