|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Database Restore ErrorI 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? -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Loren Z" <anonym***@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 Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@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" <anonym***@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. >> > 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" <anonym***@discussions.microsoft.com> wrote: Show quote >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_kara***@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" <anonym***@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. >>> >> > 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? Show quote "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" > <anonym***@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_kara***@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" <anonym***@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. >>>> >>> >> > > 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/sitehome.aspx?SiteID=68. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Loren Z" <anonym***@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... >>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" >> <anonym***@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_kara***@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" <anonym***@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. >>>>> >>>> >>> >> > > Thanks Tibor, have you confirmed this problem as well? If not would you be
able to replicate this behaviour? Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:e%23mW5jh7GHA.4996@TK2MSFTNGP03.phx.gbl... >> 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/sitehome.aspx?SiteID=68. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "Loren Z" <anonym***@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... >>>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" >>> <anonym***@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_kara***@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" <anonym***@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. >>>>>> >>>>> >>>> >>> >> >> > 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" <anonym***@discussions.microsoft.com> wrote: Show quote >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_kara***@hotmail.nomail.com> wrote in >message news:e%23mW5jh7GHA.4996@TK2MSFTNGP03.phx.gbl... >>> 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/sitehome.aspx?SiteID=68. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> >> >> "Loren Z" <anonym***@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... >>>>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" >>>> <anonym***@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_kara***@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" <anonym***@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. >>>>>>> >>>>>> >>>>> >>>> >>> >>> >> > Does anyone look at the Connect Site? I recorded the bug on October 12th and
nobody appears to have even checked it out. Chris Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:e%23mW5jh7GHA.4996@TK2MSFTNGP03.phx.gbl... >> 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/sitehome.aspx?SiteID=68. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "Loren Z" <anonym***@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... >>>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" >>> <anonym***@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_kara***@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" <anonym***@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. >>>>>> >>>>> >>>> >>> >> >> > Chris Wood (anonym***@discussions.microsoft.com) writes:
> Does anyone look at the Connect Site? I recorded the bug on October 12th Patience, my dear friend! > and nobody appears to have even checked it out. 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, esq***@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 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: Show quote > 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. > >  |
|||||||||||||||||||||||
Other interesting topics