Restoring to remove standy/read-only mode.

Hi all,
I have a database that is restored with the following command:
RESTORE DATABASE [<databasename>] FROM DISK= '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
So, the database is in standy/read-only mode. Now, I want to make sure that
I can change it over so that it's not longer in standy/read-only mode. Is
the following synatx/script correct?
RESTORE DATABASE [<databasename>] WITH RECOVERY
Is that enough? It seems to work, but I'm not sure if I'm missing an
important element.
Thanks!
WadeThat is all you need. Note that once it is recovered you cannot apply
subsequent transaction log or differential backups.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have a database that is restored with the following command:
> RESTORE DATABASE [<databasename>] FROM DISK=> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
> So, the database is in standy/read-only mode. Now, I want to make sure
> that I can change it over so that it's not longer in standy/read-only
> mode. Is the following synatx/script correct?
> RESTORE DATABASE [<databasename>] WITH RECOVERY
> Is that enough? It seems to work, but I'm not sure if I'm missing an
> important element.
> Thanks!
> Wade
>|||Yes, excellent -- thanks! :)
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>|||Another question ... is there a query I can use to determine if the database
is in standby/read-only mode? This way I can turn off my diff and trans
restores.
Thanks!
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>|||You continue to do diff and/or log restores while the database is in
standby. You stop the restores when it is recovered.
The information you seek is in the master.dbo.sysdatabases table,
specifically the status column. Look up sysdatabases in BOL for what the
status bit flags mean.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
> Another question ... is there a query I can use to determine if the
> database is in standby/read-only mode? This way I can turn off my diff
> and trans restores.
> Thanks!
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>> That is all you need. Note that once it is recovered you cannot apply
>> subsequent transaction log or differential backups.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>>
>|||Thanks, I already found it:
use [master]
select name, DATABASEPROPERTY(name, N'IsReadOnly') from
master.dbo.sysdatabases
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
> You continue to do diff and/or log restores while the database is in
> standby. You stop the restores when it is recovered.
> The information you seek is in the master.dbo.sysdatabases table,
> specifically the status column. Look up sysdatabases in BOL for what the
> status bit flags mean.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
>> Another question ... is there a query I can use to determine if the
>> database is in standby/read-only mode? This way I can turn off my diff
>> and trans restores.
>> Thanks!
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>> That is all you need. Note that once it is recovered you cannot apply
>> subsequent transaction log or differential backups.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>>
>>
>|||FYI, DATABASEPROPERTYEX() is a newer version of DATABASEPROPERTY()
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message news:OySXXXZrFHA.3424@.TK2MSFTNGP14.phx.gbl...
> Thanks, I already found it:
> use [master]
> select name, DATABASEPROPERTY(name, N'IsReadOnly') from
> master.dbo.sysdatabases
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
>> You continue to do diff and/or log restores while the database is in
>> standby. You stop the restores when it is recovered.
>> The information you seek is in the master.dbo.sysdatabases table,
>> specifically the status column. Look up sysdatabases in BOL for what the
>> status bit flags mean.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
>> Another question ... is there a query I can use to determine if the
>> database is in standby/read-only mode? This way I can turn off my diff
>> and trans restores.
>> Thanks!
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>> That is all you need. Note that once it is recovered you cannot apply
>> subsequent transaction log or differential backups.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>>
>>
>>
>

restoring to a mirrored database

When I tried to run the command
alter database Press_Ganey_Associates_MSCRM set single_user with
rollback after 30
on a mirrored database, I got the following error.
Msg 1468, Level 16, State 1, Line 1
The operation cannot be performed on database "myDB" because it is
involved in a database mirroring session.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
When restoring to a mirrored database, do I need to pause the
mirroring, or stop it? Once restored, do I simply resume or set on?Are you attempting the restore on the principal or on the mirror?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JOH" <JOHolloway@.gmail.com> wrote in message
news:af10aac4-fae7-4a64-baf8-9592cf7571f7@.b64g2000hsa.googlegroups.com...
> When I tried to run the command
> alter database Press_Ganey_Associates_MSCRM set single_user with
> rollback after 30
> on a mirrored database, I got the following error.
> Msg 1468, Level 16, State 1, Line 1
> The operation cannot be performed on database "myDB" because it is
> involved in a database mirroring session.
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> When restoring to a mirrored database, do I need to pause the
> mirroring, or stop it? Once restored, do I simply resume or set on?|||On Apr 26, 2:37 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Are you attempting the restore on the principal or on the mirror?
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "JOH" <JOHollo...@.gmail.com> wrote in message
> news:af10aac4-fae7-4a64-baf8-9592cf7571f7@.b64g2000hsa.googlegroups.com...
> > When I tried to run the command
> > alter database Press_Ganey_Associates_MSCRM set single_user with
> > rollback after 30
> > on a mirrored database, I got the following error.
> > Msg 1468, Level 16, State 1, Line 1
> > The operation cannot be performed on database "myDB" because it is
> > involved in a database mirroring session.
> > Msg 5069, Level 16, State 1, Line 1
> > ALTER DATABASE statement failed.
> > When restoring to a mirrored database, do I need to pause the
> > mirroring, or stop it? Once restored, do I simply resume or set on?
On the principal.|||You can't restore the principal, this would break the mirroring anyway, so you have to stop
mirroring, do the restore and then re do the mirroring setup. See for instance:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f28f0fb8-c18a-4fc6-96ac-2e7a8ff189cd.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JOH" <JOHolloway@.gmail.com> wrote in message
news:952f4616-d7c5-4c60-9ffb-2a334cd4b28e@.27g2000hsf.googlegroups.com...
> On Apr 26, 2:37 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Are you attempting the restore on the principal or on the mirror?
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "JOH" <JOHollo...@.gmail.com> wrote in message
>> news:af10aac4-fae7-4a64-baf8-9592cf7571f7@.b64g2000hsa.googlegroups.com...
>> > When I tried to run the command
>> > alter database Press_Ganey_Associates_MSCRM set single_user with
>> > rollback after 30
>> > on a mirrored database, I got the following error.
>> > Msg 1468, Level 16, State 1, Line 1
>> > The operation cannot be performed on database "myDB" because it is
>> > involved in a database mirroring session.
>> > Msg 5069, Level 16, State 1, Line 1
>> > ALTER DATABASE statement failed.
>> > When restoring to a mirrored database, do I need to pause the
>> > mirroring, or stop it? Once restored, do I simply resume or set on?
> On the principal.|||On Apr 27, 2:09 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> You can't restore the principal, this would break the mirroring anyway, so you have to stop
> mirroring, do the restore and then re do the mirroring setup. See for instance:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f28f0fb8-c18a-4fc6-96ac-2e7a8ff189cd.htm
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "JOH" <JOHollo...@.gmail.com> wrote in message
> news:952f4616-d7c5-4c60-9ffb-2a334cd4b28e@.27g2000hsf.googlegroups.com...
> > On Apr 26, 2:37 am, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> Are you attempting the restore on the principal or on the mirror?
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> "JOH" <JOHollo...@.gmail.com> wrote in message
> >>news:af10aac4-fae7-4a64-baf8-9592cf7571f7@.b64g2000hsa.googlegroups.com...
> >> > When I tried to run the command
> >> > alter database Press_Ganey_Associates_MSCRM set single_user with
> >> > rollback after 30
> >> > on a mirrored database, I got the following error.
> >> > Msg 1468, Level 16, State 1, Line 1
> >> > The operation cannot be performed on database "myDB" because it is
> >> > involved in a database mirroring session.
> >> > Msg 5069, Level 16, State 1, Line 1
> >> > ALTER DATABASE statement failed.
> >> > When restoring to a mirrored database, do I need to pause the
> >> > mirroring, or stop it? Once restored, do I simply resume or set on?
> > On the principal.
Thank you for the link. I've now read through the BOL on mirroring,
and will experiment with test instances.

Restoring Master Database...

Hello All,
I am trying to test my master and MSDB restore actvities.
1. When I try to run SQL Server on single user mode(DOS prompt command
sqlservr.exe -m) it is taking forever... If the SQL Server is started in
single user mode will I get the DOS prompt again? It it normal that
launching SQL Server in single user mode is taking time?
2. After launching the server in single user mode, the next step is
launching EM and restore the master database like any other user database
restore? OR any special sequence is required.
3. To restore the MSDB should the SQL Server be running in single user mode?
SQL 2K.
Thank you very much.
Johnson1. Try using: sqlservr.exe -c -m
It's not normal to take a long time but it depends on your
definition of a long time. The output to the screen should
give you an idea of what is taking how long. You won't get a
second DOS screen.
2. Yes but SQL Server will shut down after you restore the
master database.
You can find the steps in books online under the topic:
Restoring the master Database from a Current Backup
3. No...but you can't restore a database that is being
accessed so make sure the SQL Server Agent isn't running.
Refer to the article in books online:
Restoring the model, msdb, and distribution Databases
-Sue
On Mon, 15 Aug 2005 19:27:50 -0700, "Johnson Smith"
<JJSmith@.hotmail.com> wrote:
>Hello All,
>I am trying to test my master and MSDB restore actvities.
>1. When I try to run SQL Server on single user mode(DOS prompt command
>sqlservr.exe -m) it is taking forever... If the SQL Server is started in
>single user mode will I get the DOS prompt again? It it normal that
>launching SQL Server in single user mode is taking time?
>2. After launching the server in single user mode, the next step is
>launching EM and restore the master database like any other user database
>restore? OR any special sequence is required.
>3. To restore the MSDB should the SQL Server be running in single user mode?
>SQL 2K.
>Thank you very much.
>Johnson
>|||Hi,
(1)From command prompt switch to the approprite directory and use this
sqlservr.exe -c -m
It is normal it take long time
if would be better firt stop the services and start services again
(2)Yes sql server has to be restarted so that new setting should take
place.
(3)To restore the master database just stop all the serveices and
detach the database and rename the database mdf file name and copy the
backed up msdb database in the location and attach the database agin.
Hope this help u
from
killer
Sue Hoegemeier wrote:
> 1. Try using: sqlservr.exe -c -m
> It's not normal to take a long time but it depends on your
> definition of a long time. The output to the screen should
> give you an idea of what is taking how long. You won't get a
> second DOS screen.
> 2. Yes but SQL Server will shut down after you restore the
> master database.
> You can find the steps in books online under the topic:
> Restoring the master Database from a Current Backup
> 3. No...but you can't restore a database that is being
> accessed so make sure the SQL Server Agent isn't running.
> Refer to the article in books online:
> Restoring the model, msdb, and distribution Databases
> -Sue
> On Mon, 15 Aug 2005 19:27:50 -0700, "Johnson Smith"
> <JJSmith@.hotmail.com> wrote:
> >Hello All,
> >I am trying to test my master and MSDB restore actvities.
> >1. When I try to run SQL Server on single user mode(DOS prompt command
> >sqlservr.exe -m) it is taking forever... If the SQL Server is started in
> >single user mode will I get the DOS prompt again? It it normal that
> >launching SQL Server in single user mode is taking time?
> >2. After launching the server in single user mode, the next step is
> >launching EM and restore the master database like any other user database
> >restore? OR any special sequence is required.
> >3. To restore the MSDB should the SQL Server be running in single user mode?
> >SQL 2K.
> >Thank you very much.
> >Johnson
> >|||Thank you very much for the response.
I understand that I would not get the second DOS screen. I think I should
have made my question very clear.
After entering sqlservr.exe -c -m in the DOS prompt how exactly I will come
to know that SQL Server agent is started in the single user mode. It did
display some text after executing the above command. How I will come to know
it finished the job of starting SQL Server in single user mode? Will I get
the DOS prompt again in the same DOS shell where I executed the line
sqlservr.exe -c -m?
Thanks,
Johnson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:nkl2g19i0oreq3k21g44hku5vs0n0rnm43@.4ax.com...
> 1. Try using: sqlservr.exe -c -m
> It's not normal to take a long time but it depends on your
> definition of a long time. The output to the screen should
> give you an idea of what is taking how long. You won't get a
> second DOS screen.
> 2. Yes but SQL Server will shut down after you restore the
> master database.
> You can find the steps in books online under the topic:
> Restoring the master Database from a Current Backup
> 3. No...but you can't restore a database that is being
> accessed so make sure the SQL Server Agent isn't running.
> Refer to the article in books online:
> Restoring the model, msdb, and distribution Databases
> -Sue
> On Mon, 15 Aug 2005 19:27:50 -0700, "Johnson Smith"
> <JJSmith@.hotmail.com> wrote:
>>Hello All,
>>I am trying to test my master and MSDB restore actvities.
>>1. When I try to run SQL Server on single user mode(DOS prompt command
>>sqlservr.exe -m) it is taking forever... If the SQL Server is started in
>>single user mode will I get the DOS prompt again? It it normal that
>>launching SQL Server in single user mode is taking time?
>>2. After launching the server in single user mode, the next step is
>>launching EM and restore the master database like any other user database
>>restore? OR any special sequence is required.
>>3. To restore the MSDB should the SQL Server be running in single user
>>mode?
>>SQL 2K.
>>Thank you very much.
>>Johnson
>|||First, it should be SQL Server not SQ Agent that you are
starting up. If you read the output on the screen, it should
display lines something like:
warning *****
SQL Server started in single user mode
starting up database master
and then other lines for starting up your other databases,
and you will see a line when it is ready for client
connections although other databases may start up after
that. And then it just sits so if you aren't used to doing
this, it may seem like it is hung. You just having to get
used to doing it or practice with a server with few, if any
user databases. Then you can tell by looking at what
databases have started up.
-Sue
On Mon, 15 Aug 2005 21:30:09 -0700, "Johnson Smith"
<JJSmith@.hotmail.com> wrote:
>Thank you very much for the response.
>I understand that I would not get the second DOS screen. I think I should
>have made my question very clear.
>After entering sqlservr.exe -c -m in the DOS prompt how exactly I will come
>to know that SQL Server agent is started in the single user mode. It did
>display some text after executing the above command. How I will come to know
>it finished the job of starting SQL Server in single user mode? Will I get
>the DOS prompt again in the same DOS shell where I executed the line
>sqlservr.exe -c -m?
>Thanks,
>Johnson
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:nkl2g19i0oreq3k21g44hku5vs0n0rnm43@.4ax.com...
>> 1. Try using: sqlservr.exe -c -m
>> It's not normal to take a long time but it depends on your
>> definition of a long time. The output to the screen should
>> give you an idea of what is taking how long. You won't get a
>> second DOS screen.
>> 2. Yes but SQL Server will shut down after you restore the
>> master database.
>> You can find the steps in books online under the topic:
>> Restoring the master Database from a Current Backup
>> 3. No...but you can't restore a database that is being
>> accessed so make sure the SQL Server Agent isn't running.
>> Refer to the article in books online:
>> Restoring the model, msdb, and distribution Databases
>> -Sue
>> On Mon, 15 Aug 2005 19:27:50 -0700, "Johnson Smith"
>> <JJSmith@.hotmail.com> wrote:
>>Hello All,
>>I am trying to test my master and MSDB restore actvities.
>>1. When I try to run SQL Server on single user mode(DOS prompt command
>>sqlservr.exe -m) it is taking forever... If the SQL Server is started in
>>single user mode will I get the DOS prompt again? It it normal that
>>launching SQL Server in single user mode is taking time?
>>2. After launching the server in single user mode, the next step is
>>launching EM and restore the master database like any other user database
>>restore? OR any special sequence is required.
>>3. To restore the MSDB should the SQL Server be running in single user
>>mode?
>>SQL 2K.
>>Thank you very much.
>>Johnson
>>
>|||Oh and look for the last line of Recovery complete.
-Sue
On Mon, 15 Aug 2005 21:30:09 -0700, "Johnson Smith"
<JJSmith@.hotmail.com> wrote:
>Thank you very much for the response.
>I understand that I would not get the second DOS screen. I think I should
>have made my question very clear.
>After entering sqlservr.exe -c -m in the DOS prompt how exactly I will come
>to know that SQL Server agent is started in the single user mode. It did
>display some text after executing the above command. How I will come to know
>it finished the job of starting SQL Server in single user mode? Will I get
>the DOS prompt again in the same DOS shell where I executed the line
>sqlservr.exe -c -m?
>Thanks,
>Johnson
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:nkl2g19i0oreq3k21g44hku5vs0n0rnm43@.4ax.com...
>> 1. Try using: sqlservr.exe -c -m
>> It's not normal to take a long time but it depends on your
>> definition of a long time. The output to the screen should
>> give you an idea of what is taking how long. You won't get a
>> second DOS screen.
>> 2. Yes but SQL Server will shut down after you restore the
>> master database.
>> You can find the steps in books online under the topic:
>> Restoring the master Database from a Current Backup
>> 3. No...but you can't restore a database that is being
>> accessed so make sure the SQL Server Agent isn't running.
>> Refer to the article in books online:
>> Restoring the model, msdb, and distribution Databases
>> -Sue
>> On Mon, 15 Aug 2005 19:27:50 -0700, "Johnson Smith"
>> <JJSmith@.hotmail.com> wrote:
>>Hello All,
>>I am trying to test my master and MSDB restore actvities.
>>1. When I try to run SQL Server on single user mode(DOS prompt command
>>sqlservr.exe -m) it is taking forever... If the SQL Server is started in
>>single user mode will I get the DOS prompt again? It it normal that
>>launching SQL Server in single user mode is taking time?
>>2. After launching the server in single user mode, the next step is
>>launching EM and restore the master database like any other user database
>>restore? OR any special sequence is required.
>>3. To restore the MSDB should the SQL Server be running in single user
>>mode?
>>SQL 2K.
>>Thank you very much.
>>Johnson
>>
>|||That means If I see the message recovery complete I am ready to connect
through the EM and do my master database restore? SQL Server agent will
never start when I issue the sqlservr.exe -c -m? Even though SQL Server
agent is not running I should not have any problem to connect through the
EM?
Thanks for your quick reply.
Johnson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:s5s2g11la0rs694mq27u4qtgavhf192ad8@.4ax.com...
> Oh and look for the last line of Recovery complete.
> -Sue
> On Mon, 15 Aug 2005 21:30:09 -0700, "Johnson Smith"
> <JJSmith@.hotmail.com> wrote:
>>Thank you very much for the response.
>>I understand that I would not get the second DOS screen. I think I should
>>have made my question very clear.
>>After entering sqlservr.exe -c -m in the DOS prompt how exactly I will
>>come
>>to know that SQL Server agent is started in the single user mode. It did
>>display some text after executing the above command. How I will come to
>>know
>>it finished the job of starting SQL Server in single user mode? Will I get
>>the DOS prompt again in the same DOS shell where I executed the line
>>sqlservr.exe -c -m?
>>Thanks,
>>Johnson
>>
>>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>>news:nkl2g19i0oreq3k21g44hku5vs0n0rnm43@.4ax.com...
>> 1. Try using: sqlservr.exe -c -m
>> It's not normal to take a long time but it depends on your
>> definition of a long time. The output to the screen should
>> give you an idea of what is taking how long. You won't get a
>> second DOS screen.
>> 2. Yes but SQL Server will shut down after you restore the
>> master database.
>> You can find the steps in books online under the topic:
>> Restoring the master Database from a Current Backup
>> 3. No...but you can't restore a database that is being
>> accessed so make sure the SQL Server Agent isn't running.
>> Refer to the article in books online:
>> Restoring the model, msdb, and distribution Databases
>> -Sue
>> On Mon, 15 Aug 2005 19:27:50 -0700, "Johnson Smith"
>> <JJSmith@.hotmail.com> wrote:
>>Hello All,
>>I am trying to test my master and MSDB restore actvities.
>>1. When I try to run SQL Server on single user mode(DOS prompt command
>>sqlservr.exe -m) it is taking forever... If the SQL Server is started in
>>single user mode will I get the DOS prompt again? It it normal that
>>launching SQL Server in single user mode is taking time?
>>2. After launching the server in single user mode, the next step is
>>launching EM and restore the master database like any other user
>>database
>>restore? OR any special sequence is required.
>>3. To restore the MSDB should the SQL Server be running in single user
>>mode?
>>SQL 2K.
>>Thank you very much.
>>Johnson
>>
>|||I am not finding this info in BOL.
As Sue said it has to be by experience. Very strange as how one will guess
like this way!!!
Thanks,
Johnson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:s5s2g11la0rs694mq27u4qtgavhf192ad8@.4ax.com...
> Oh and look for the last line of Recovery complete.
> -Sue
> On Mon, 15 Aug 2005 21:30:09 -0700, "Johnson Smith"
> <JJSmith@.hotmail.com> wrote:
>>Thank you very much for the response.
>>I understand that I would not get the second DOS screen. I think I should
>>have made my question very clear.
>>After entering sqlservr.exe -c -m in the DOS prompt how exactly I will
>>come
>>to know that SQL Server agent is started in the single user mode. It did
>>display some text after executing the above command. How I will come to
>>know
>>it finished the job of starting SQL Server in single user mode? Will I get
>>the DOS prompt again in the same DOS shell where I executed the line
>>sqlservr.exe -c -m?
>>Thanks,
>>Johnson
>>
>>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>>news:nkl2g19i0oreq3k21g44hku5vs0n0rnm43@.4ax.com...
>> 1. Try using: sqlservr.exe -c -m
>> It's not normal to take a long time but it depends on your
>> definition of a long time. The output to the screen should
>> give you an idea of what is taking how long. You won't get a
>> second DOS screen.
>> 2. Yes but SQL Server will shut down after you restore the
>> master database.
>> You can find the steps in books online under the topic:
>> Restoring the master Database from a Current Backup
>> 3. No...but you can't restore a database that is being
>> accessed so make sure the SQL Server Agent isn't running.
>> Refer to the article in books online:
>> Restoring the model, msdb, and distribution Databases
>> -Sue
>> On Mon, 15 Aug 2005 19:27:50 -0700, "Johnson Smith"
>> <JJSmith@.hotmail.com> wrote:
>>Hello All,
>>I am trying to test my master and MSDB restore actvities.
>>1. When I try to run SQL Server on single user mode(DOS prompt command
>>sqlservr.exe -m) it is taking forever... If the SQL Server is started in
>>single user mode will I get the DOS prompt again? It it normal that
>>launching SQL Server in single user mode is taking time?
>>2. After launching the server in single user mode, the next step is
>>launching EM and restore the master database like any other user
>>database
>>restore? OR any special sequence is required.
>>3. To restore the MSDB should the SQL Server be running in single user
>>mode?
>>SQL 2K.
>>Thank you very much.
>>Johnson
>>
>|||Yes.
Just disable the Agent service while you do your restores
and it will make life easier.
-Sue
On Mon, 15 Aug 2005 22:11:26 -0700, "Johnson Smith"
<JJSmith@.hotmail.com> wrote:
>That means If I see the message recovery complete I am ready to connect
>through the EM and do my master database restore? SQL Server agent will
>never start when I issue the sqlservr.exe -c -m? Even though SQL Server
>agent is not running I should not have any problem to connect through the
>EM?
>Thanks for your quick reply.
>Johnson
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:s5s2g11la0rs694mq27u4qtgavhf192ad8@.4ax.com...
>> Oh and look for the last line of Recovery complete.
>> -Sue
>> On Mon, 15 Aug 2005 21:30:09 -0700, "Johnson Smith"
>> <JJSmith@.hotmail.com> wrote:
>>Thank you very much for the response.
>>I understand that I would not get the second DOS screen. I think I should
>>have made my question very clear.
>>After entering sqlservr.exe -c -m in the DOS prompt how exactly I will
>>come
>>to know that SQL Server agent is started in the single user mode. It did
>>display some text after executing the above command. How I will come to
>>know
>>it finished the job of starting SQL Server in single user mode? Will I get
>>the DOS prompt again in the same DOS shell where I executed the line
>>sqlservr.exe -c -m?
>>Thanks,
>>Johnson
>>
>>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>>news:nkl2g19i0oreq3k21g44hku5vs0n0rnm43@.4ax.com...
>> 1. Try using: sqlservr.exe -c -m
>> It's not normal to take a long time but it depends on your
>> definition of a long time. The output to the screen should
>> give you an idea of what is taking how long. You won't get a
>> second DOS screen.
>> 2. Yes but SQL Server will shut down after you restore the
>> master database.
>> You can find the steps in books online under the topic:
>> Restoring the master Database from a Current Backup
>> 3. No...but you can't restore a database that is being
>> accessed so make sure the SQL Server Agent isn't running.
>> Refer to the article in books online:
>> Restoring the model, msdb, and distribution Databases
>> -Sue
>> On Mon, 15 Aug 2005 19:27:50 -0700, "Johnson Smith"
>> <JJSmith@.hotmail.com> wrote:
>>Hello All,
>>I am trying to test my master and MSDB restore actvities.
>>1. When I try to run SQL Server on single user mode(DOS prompt command
>>sqlservr.exe -m) it is taking forever... If the SQL Server is started in
>>single user mode will I get the DOS prompt again? It it normal that
>>launching SQL Server in single user mode is taking time?
>>2. After launching the server in single user mode, the next step is
>>launching EM and restore the master database like any other user
>>database
>>restore? OR any special sequence is required.
>>3. To restore the MSDB should the SQL Server be running in single user
>>mode?
>>SQL 2K.
>>Thank you very much.
>>Johnson
>>
>>
>|||Every detail of it isn't in BOL. Scroll down in the DOS
screen and look for recovery complete. What you see on the
screen is just what is written to the SQL log - same thing
you see if you look in the errorlog.
-Sue
On Mon, 15 Aug 2005 23:02:30 -0700, "Johnson Smith"
<JJSmith@.hotmail.com> wrote:
>I am not finding this info in BOL.
>As Sue said it has to be by experience. Very strange as how one will guess
>like this way!!!
>Thanks,
>Johnson
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:s5s2g11la0rs694mq27u4qtgavhf192ad8@.4ax.com...
>> Oh and look for the last line of Recovery complete.
>> -Sue
>> On Mon, 15 Aug 2005 21:30:09 -0700, "Johnson Smith"
>> <JJSmith@.hotmail.com> wrote:
>>Thank you very much for the response.
>>I understand that I would not get the second DOS screen. I think I should
>>have made my question very clear.
>>After entering sqlservr.exe -c -m in the DOS prompt how exactly I will
>>come
>>to know that SQL Server agent is started in the single user mode. It did
>>display some text after executing the above command. How I will come to
>>know
>>it finished the job of starting SQL Server in single user mode? Will I get
>>the DOS prompt again in the same DOS shell where I executed the line
>>sqlservr.exe -c -m?
>>Thanks,
>>Johnson
>>
>>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>>news:nkl2g19i0oreq3k21g44hku5vs0n0rnm43@.4ax.com...
>> 1. Try using: sqlservr.exe -c -m
>> It's not normal to take a long time but it depends on your
>> definition of a long time. The output to the screen should
>> give you an idea of what is taking how long. You won't get a
>> second DOS screen.
>> 2. Yes but SQL Server will shut down after you restore the
>> master database.
>> You can find the steps in books online under the topic:
>> Restoring the master Database from a Current Backup
>> 3. No...but you can't restore a database that is being
>> accessed so make sure the SQL Server Agent isn't running.
>> Refer to the article in books online:
>> Restoring the model, msdb, and distribution Databases
>> -Sue
>> On Mon, 15 Aug 2005 19:27:50 -0700, "Johnson Smith"
>> <JJSmith@.hotmail.com> wrote:
>>Hello All,
>>I am trying to test my master and MSDB restore actvities.
>>1. When I try to run SQL Server on single user mode(DOS prompt command
>>sqlservr.exe -m) it is taking forever... If the SQL Server is started in
>>single user mode will I get the DOS prompt again? It it normal that
>>launching SQL Server in single user mode is taking time?
>>2. After launching the server in single user mode, the next step is
>>launching EM and restore the master database like any other user
>>database
>>restore? OR any special sequence is required.
>>3. To restore the MSDB should the SQL Server be running in single user
>>mode?
>>SQL 2K.
>>Thank you very much.
>>Johnson
>>
>>
>