Restrict user from default tables (access given by public role)

I am newbie.. probably this is a silly question..
I added few users with only SELECT permissions to 2 User Tables. But
when these users login,they could also see multiple system tables
(syscontraints, syssegments , dtproperties) . From reading this forum
and other articles, I understand these are visible due to the user
being part of the default "public" role.
My Question is, is there any way to remove/hide these system tables
from the user?.. The team manager doesnt like these users seeing
anything other than the 2 User Tables they supposed to see..
Thanks in advance for all tips/advices/suggestionsTell your team manager he's a turd. No...actually don't say that, it'll
probably get you fired.
I wouldn't go messing around with the permissions on the system tables,
you're likely to stuff up some functionality of SQL Server in that DB
for those users. The system tables are a very necessary part of
day-to-day data & schema manipulation...for every user of the DB.
Things like optimising query plans, for example, rely on data stored in
the system tables. I'm not positive if things will start breaking if
you start revoking or denying permissions on those system tables but I
wouldn't risk it.
There's an option in SSMS (Tools | Options | Environment | General |
Hide system objects in Object Explorer), not sure about SQLEM or QA,
that will hide the system tables in the GUI but not change their
underlying permissions. That may placate the pointy-haired manager
<http://www.dilbert.com/> asking for this change, perhaps.
What tool are the users using to "see" these system tables?
*mike hodgson*
http://sqlnerd.blogspot.com
sreejith.ram@.gmail.com wrote:

>I am newbie.. probably this is a silly question..
>I added few users with only SELECT permissions to 2 User Tables. But
>when these users login,they could also see multiple system tables
>(syscontraints, syssegments , dtproperties) . From reading this forum
>and other articles, I understand these are visible due to the user
>being part of the default "public" role.
>My Question is, is there any way to remove/hide these system tables
>from the user?.. The team manager doesnt like these users seeing
>anything other than the 2 User Tables they supposed to see..
>Thanks in advance for all tips/advices/suggestions
>
>|||Thanks Mike... This convinced me I should spend my time convincing the
manager to leave it alone than trying to hide the tables
The users are using SQL Server Enterprise Manager.|||> The users are using SQL Server Enterprise Manager.
EM is more of a DBA/Developer tool than an end-user tool. In any case, you
can hide most of the system object noise by unchecking the 'show system
databases and system objects' option under the EM server registration
properties.
Hope this helps.
Dan Guzman
SQL Server MVP
<sreejith.ram@.gmail.com> wrote in message
news:1138983030.862156.116660@.f14g2000cwb.googlegroups.com...
> Thanks Mike... This convinced me I should spend my time convincing the
> manager to leave it alone than trying to hide the tables
> The users are using SQL Server Enterprise Manager.
>|||I thought there was that "hide" option in SQLEM. I just couldn't
remember where it was (been using SSMS too long <g> ).
*mike hodgson*
http://sqlnerd.blogspot.com
Dan Guzman wrote:

>EM is more of a DBA/Developer tool than an end-user tool. In any case, you
>can hide most of the system object noise by unchecking the 'show system
>databases and system objects' option under the EM server registration
>properties.
>
>

Restrict User Access in MSSQL 2000

Hi,

I have few SQL user who has permissions on different databases. When they are accessing MSSQL server from Enterprise Manager they can see all Databases but can not access them and they can change file size allocated to them. I want to restrict this and they can only see database belongs to respective user and restrict user to change allocated size. Please help me with this as I am not able to find the solution.

Thanks & Regards,

Gaurav

You cannot restrict the users in SQL Server 2000 from seeing the available databases (this can be done in SQL Server 2005 by revoking VIEW ANY DATABASE to the public server role).

Also, a database owner will be able to change the file size for the databases he owns; you cannot prevent this.

Thanks
Laurentiu

restrict user access

how can i set the user access level for sql 2000 server?
i want to restrict the user only can query/add table/ drop table/ modify
data/import & export data
but don't allow them to view other services such as "Management,
Replication, Security...etc"
which standard role i can choose or need to create a custom role for this?
btw, Although user can view the content of other user, but they still can
see the name of DB? how can avoid this?
Thanks in advanced.
Hi
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
Thats all what you want ? :-))))
Well first of all you need to create a login to SQL Server for this person.
Then grant an access to the database
As I see your requirements you are going to put them into db_owner database
role.
For more details please refer to the BOL.
"beachboy" <stanley@.javacatz.com> wrote in message
news:%23SgC77JIGHA.740@.TK2MSFTNGP12.phx.gbl...
> how can i set the user access level for sql 2000 server?
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
> but don't allow them to view other services such as "Management,
> Replication, Security...etc"
> which standard role i can choose or need to create a custom role for this?
> btw, Although user can view the content of other user, but they still can
> see the name of DB? how can avoid this?
> Thanks in advanced.
>

restrict user access

how can i set the user access level for sql 2000 server?
i want to restrict the user only can query/add table/ drop table/ modify
data/import & export data
but don't allow them to view other services such as "Management,
Replication, Security...etc"
which standard role i can choose or need to create a custom role for this?
btw, Although user can view the content of other user, but they still can
see the name of DB? how can avoid this?
Thanks in advanced.Hi
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
Thats all what you want ? :-))))
Well first of all you need to create a login to SQL Server for this person.
Then grant an access to the database
As I see your requirements you are going to put them into db_owner database
role.
For more details please refer to the BOL.
"beachboy" <stanley@.javacatz.com> wrote in message
news:%23SgC77JIGHA.740@.TK2MSFTNGP12.phx.gbl...
> how can i set the user access level for sql 2000 server?
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
> but don't allow them to view other services such as "Management,
> Replication, Security...etc"
> which standard role i can choose or need to create a custom role for this?
> btw, Although user can view the content of other user, but they still can
> see the name of DB? how can avoid this?
> Thanks in advanced.
>

restrict user access

how can i set the user access level for sql 2000 server?
i want to restrict the user only can query/add table/ drop table/ modify
data/import & export data
but don't allow them to view other services such as "Management,
Replication, Security...etc"
which standard role i can choose or need to create a custom role for this?
btw, Although user can view the content of other user, but they still can
see the name of DB? how can avoid this?
Thanks in advanced.Hi
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
Thats all what you want ? :-))))
Well first of all you need to create a login to SQL Server for this person.
Then grant an access to the database
As I see your requirements you are going to put them into db_owner database
role.
For more details please refer to the BOL.
"beachboy" <stanley@.javacatz.com> wrote in message
news:%23SgC77JIGHA.740@.TK2MSFTNGP12.phx.gbl...
> how can i set the user access level for sql 2000 server?
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
> but don't allow them to view other services such as "Management,
> Replication, Security...etc"
> which standard role i can choose or need to create a custom role for this?
> btw, Although user can view the content of other user, but they still can
> see the name of DB? how can avoid this?
> Thanks in advanced.
>sql

restrict update trigger to updated rows

How do I restrict an update trigger to just the rows updated?
Would like to have two audit fields with user and time of modification for
updates.
The following trigger would attempt to update all rows in the table:
ALTER trigger tm_stmp_admit_proc
on dbo.tblAdmissions_procedures
for update
as
begin
update tblAdmissions_procedures
set time_stamp = current_timestamp,user_modify = system_user
end
Couldn't find any description in BOL analogous to the FOR EACH ROW parameter
in
PostgreSQL:
CREATE OR REPLACE FUNCTION public.timestamper()
RETURNS trigger AS
'
BEGIN
NEW.time_stamp := \'now\';
NEW.user_modify := current_user;
RETURN NEW;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER timestamper
BEFORE INSERT OR UPDATE
ON public.admit_procedures
FOR EACH ROW
EXECUTE PROCEDURE public.timestamper();
Thanks,
David P. Lurie
If you have a primary key column in the table
use INSERTED table in the trigger.
like
update tblAdmissions_procedures
set time_stamp = current_timestamp,user_modify = system_user
where exists ( select keycolumn from inserted
where tblAdmissions_procedures.keycolumn = inserted.keycolumn )
but, then u may also have to set this parameter
SET RECURSIVE_TRIGGERS OFF
HTH
Faris
"David P. Lurie" <abc@.def.net> schrieb im Newsbeitrag
news:%23Di0ArVbEHA.2944@.TK2MSFTNGP11.phx.gbl...
> How do I restrict an update trigger to just the rows updated?
> Would like to have two audit fields with user and time of modification for
> updates.
> The following trigger would attempt to update all rows in the table:
> ALTER trigger tm_stmp_admit_proc
> on dbo.tblAdmissions_procedures
> for update
> as
> begin
> update tblAdmissions_procedures
> set time_stamp = current_timestamp,user_modify = system_user
> end
> Couldn't find any description in BOL analogous to the FOR EACH ROW
parameter
> in
> PostgreSQL:
> CREATE OR REPLACE FUNCTION public.timestamper()
> RETURNS trigger AS
> '
> BEGIN
> NEW.time_stamp := \'now\';
> NEW.user_modify := current_user;
> RETURN NEW;
> END;
> '
> LANGUAGE 'plpgsql' VOLATILE;
> CREATE TRIGGER timestamper
> BEFORE INSERT OR UPDATE
> ON public.admit_procedures
> FOR EACH ROW
> EXECUTE PROCEDURE public.timestamper();
> Thanks,
> David P. Lurie
>
|||Thanks a lot -
I tested this on two of the tables, and seems to work. Didn't turn off
recursive triggers to see whether it was necessary, and no problems thus far
with a few updates on each table. Assume that infinite loop would have
occurred if that setting was needed.
David P. Lurie
"Faris" <faris@.bss-india.com> wrote in message
news:eTBHKhWbEHA.3944@.tk2msftngp13.phx.gbl...
> If you have a primary key column in the table
> use INSERTED table in the trigger.
> like
> update tblAdmissions_procedures
> set time_stamp = current_timestamp,user_modify = system_user
> where exists ( select keycolumn from inserted
> where tblAdmissions_procedures.keycolumn = inserted.keycolumn )
> but, then u may also have to set this parameter
> SET RECURSIVE_TRIGGERS OFF
> HTH
> Faris

Restrict Printing based on User?

Is there any way to restrict certain users from printing a report (or
even exporting it)?
If so, what is the difficulty level on a scale of 1-10? I received a
request from a user to prohibit some users from printing, but I would
argue that if you don't want them to print it, they probably shouldn't
even see it.
Still, I'm curious as to the feasibility.
Thanks,
MikeNot with Report Manager. You can remove certain export types but it is for
everybody. Same thing with the new printing control, everybody or nobody.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bassist695" <Michael.EJ.Reynolds@.gmail.com> wrote in message
news:1122382697.120430.226760@.g47g2000cwa.googlegroups.com...
> Is there any way to restrict certain users from printing a report (or
> even exporting it)?
> If so, what is the difficulty level on a scale of 1-10? I received a
> request from a user to prohibit some users from printing, but I would
> argue that if you don't want them to print it, they probably shouldn't
> even see it.
> Still, I'm curious as to the feasibility.
> Thanks,
> Mike
>|||If you allow users to view your reports, you can't prevent them from
printing them.. Even if you find a way to do that, they'll still be
able to use "Print Screen" and then print out the screenshot...

Restrict login to one instance

Hello All,
I'm using SQL Server 2000 and wish to restrict the user login to one
instance only. Is this possible? Is this wise?"Fred Payne" <fpayne@.ryerson.ca> wrote in message
news:bnohcm$u46$1@.news.ryerson.ca...
> Hello All,
> I'm using SQL Server 2000 and wish to restrict the user login to one
> instance only. Is this possible? Is this wise?

Not sure what you mean. You can sit a database to single user, but that's
generally only for maintenance purposes.

Without knowing more, I can only say it's probably NOT wise.
|||"Fred Payne" <fpayne@.ryerson.ca> wrote in message
news:bnohcm$u46$1@.news.ryerson.ca...
> Hello All,
> I'm using SQL Server 2000 and wish to restrict the user login to one
> instance only. Is this possible? Is this wise?

Not sure what you mean. You can sit a database to single user, but that's
generally only for maintenance purposes.

Without knowing more, I can only say it's probably NOT wise.
|||"Fred Payne" <fpayne@.ryerson.ca> wrote in message news:<bnohcm$u46$1@.news.ryerson.ca>...
> Hello All,
> I'm using SQL Server 2000 and wish to restrict the user login to one
> instance only. Is this possible? Is this wise?

Assuming that you mean 'instance' as in 'named instance' (see Books
Online), then each instance is a separate installation, so you manage
security separately for each one. If a user doesn't need access to one
of the instances, then you simply don't grant access to that one. If
this doesn't help, perhaps you could clarify exactly what you want to
achieve?

Simon

restrict delete function on odbc connection

i have the odbc connection connected to our SQL databasse via sql user
account through MS access Frontend and having problem controling the delete
function of the account.

In SQL i am only allow the account to read, insert,update and denied delete
on the database and tables. However, the account still be able delete the
records.

Can someone let me know what am i missing in the permission granting.

Check individual permissions, groups permissions and what is important the permissions granted to the public role where every database user will be added to.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Restrict delete from a table

Hi

I need to restrict delete from one table by any user of SQL. How can we do this? This is our master table and we dont want any one to delete data from this table.

Thanks
BalaTry Cascade ON DELETE NO ACTION, you can use either code or set it on the table properties. Run a search for Cascade Delete in the BOL(books online) for more examples. NO ACTION is an ANSI SQL DRI(Declarative Referential Integrity) rule that prevents Deletes from tables. Hope this helps

CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION,
qty_ordered int)
GO

Kind regards,
Gift Peddie

Restoring to get a single table

A user has managed to ruin a table on our production system, and I'd like to
offer them an older copy online so they can compare them. I think the best
way would be to restore the last backup (only a day old, phew) as a new
database, and then copy the data over.
Can anyone guide me on how to do the restore to a NEW database, leaving the
original intact and online?
MaurySee the Restore Database command, and simply use MyDatabase_New instead of
MyDatabase. Same thing in Enterprise Manager if you prefer to do it
there...just give it a new name
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:27178864-F2FE-44B9-8D10-7B3CB35B1303@.microsoft.com...
>A user has managed to ruin a table on our production system, and I'd like
>to
> offer them an older copy online so they can compare them. I think the best
> way would be to restore the last backup (only a day old, phew) as a new
> database, and then copy the data over.
> Can anyone guide me on how to do the restore to a NEW database, leaving
> the
> original intact and online?
> Maury|||"Kevin3NF" wrote:
> See the Restore Database command, and simply use MyDatabase_New instead of
> MyDatabase. Same thing in Enterprise Manager if you prefer to do it
> there...just give it a new name
Ok, thanks. Here goes nothing...
Maury|||www.red-gate.com makes a data Compare utility that may be useful in helping
you identify the ruined data, as well as writing scripts to resolve it.
"Maury Markowitz" wrote:
> "Kevin3NF" wrote:
> > See the Restore Database command, and simply use MyDatabase_New instead of
> > MyDatabase. Same thing in Enterprise Manager if you prefer to do it
> > there...just give it a new name
> Ok, thanks. Here goes nothing...
> Maurysql

Restoring to get a single table

A user has managed to ruin a table on our production system, and I'd like to
offer them an older copy online so they can compare them. I think the best
way would be to restore the last backup (only a day old, phew) as a new
database, and then copy the data over.
Can anyone guide me on how to do the restore to a NEW database, leaving the
original intact and online?
Maury
See the Restore Database command, and simply use MyDatabase_New instead of
MyDatabase. Same thing in Enterprise Manager if you prefer to do it
there...just give it a new name
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:27178864-F2FE-44B9-8D10-7B3CB35B1303@.microsoft.com...
>A user has managed to ruin a table on our production system, and I'd like
>to
> offer them an older copy online so they can compare them. I think the best
> way would be to restore the last backup (only a day old, phew) as a new
> database, and then copy the data over.
> Can anyone guide me on how to do the restore to a NEW database, leaving
> the
> original intact and online?
> Maury
|||"Kevin3NF" wrote:

> See the Restore Database command, and simply use MyDatabase_New instead of
> MyDatabase. Same thing in Enterprise Manager if you prefer to do it
> there...just give it a new name
Ok, thanks. Here goes nothing...
Maury
|||www.red-gate.com makes a data Compare utility that may be useful in helping
you identify the ruined data, as well as writing scripts to resolve it.
"Maury Markowitz" wrote:

> "Kevin3NF" wrote:
>
> Ok, thanks. Here goes nothing...
> Maury

Restoring to get a single table

A user has managed to ruin a table on our production system, and I'd like to
offer them an older copy online so they can compare them. I think the best
way would be to restore the last backup (only a day old, phew) as a new
database, and then copy the data over.
Can anyone guide me on how to do the restore to a NEW database, leaving the
original intact and online?
MaurySee the Restore Database command, and simply use MyDatabase_New instead of
MyDatabase. Same thing in Enterprise Manager if you prefer to do it
there...just give it a new name
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:27178864-F2FE-44B9-8D10-7B3CB35B1303@.microsoft.com...
>A user has managed to ruin a table on our production system, and I'd like
>to
> offer them an older copy online so they can compare them. I think the best
> way would be to restore the last backup (only a day old, phew) as a new
> database, and then copy the data over.
> Can anyone guide me on how to do the restore to a NEW database, leaving
> the
> original intact and online?
> Maury|||"Kevin3NF" wrote:

> See the Restore Database command, and simply use MyDatabase_New instead of
> MyDatabase. Same thing in Enterprise Manager if you prefer to do it
> there...just give it a new name
Ok, thanks. Here goes nothing...
Maury|||www.red-gate.com makes a data Compare utility that may be useful in helping
you identify the ruined data, as well as writing scripts to resolve it.
"Maury Markowitz" wrote:

> "Kevin3NF" wrote:
>
> Ok, thanks. Here goes nothing...
> Maury

Restoring the Master Database

I am trying to restore the master database and am getting the following erro
r:
RESTORE DATABASE must be used in single user mode when trying to restore the
master database. RESTORE DATABASE is terminating abnormally.
I know a little bit about SQL, but I am no guru, so any help is appreciated.
Thanks.Hi,
(Master database can be restore while SQL server is started in Single
user Mode)
1.. Stop SQL server Service
2.. Start Microsoft SQL Server in single-user mode.
From a command prompt, enter:
sqlservr.exe -c -m
3. Login to Query analyzer as SA
4. Execute the RESTORE DATABASE statement to restore the master
database backup, specifying:
RESTORE database master from disk='c:\backup\master.bak'
Thanks
Hari
MCDBA
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:1DB3B978-8131-4E92-8CFB-137D6582E367@.microsoft.com...
> I am trying to restore the master database and am getting the following
error:
> RESTORE DATABASE must be used in single user mode when trying to restore
the master database. RESTORE DATABASE is terminating abnormally.
> I know a little bit about SQL, but I am no guru, so any help is
appreciated. Thanks.|||You need to start SQL Server in single-user mode in order to restore master.
This is usually done by starting SQL Server in a command window using
sqlservr.exe. For example:
CD C:\Program Files\Microsoft SQL Server\MSSQL\Binn
SQLSERVR -c -m
After you execute the RESTORE (using OSQL or Query Analyzer), SQL Server
will automatically shutdown in the command window. You can then start it
normally.
See the Books Online for more information on the SQLSERVR application.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:1DB3B978-8131-4E92-8CFB-137D6582E367@.microsoft.com...
> I am trying to restore the master database and am getting the following
error:
> RESTORE DATABASE must be used in single user mode when trying to restore
the master database. RESTORE DATABASE is terminating abnormally.
> I know a little bit about SQL, but I am no guru, so any help is
appreciated. Thanks.|||Thank you, the database was restored. Now I have another problem. I restor
ed the master database to a disaster recovery server and the database names
are different. How do I Point the master DB to the new databases?|||"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:9A89A18C-6F95-4066-B08E-3D5657CDF85B@.microsoft.com...
> Thank you, the database was restored. Now I have another problem. I
restored the master database to a disaster recovery server and the database
names are different. How do I Point the master DB to the new databases?
EXEC sp_attach_db or sp_attach_single_file_db
e.g.
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
EXEC sp_attach_single_file_db @.dbname = 'pubs',
@.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
--Outgoing mail is certified Virus Free.Checked by AVG anti-virus system
(http://www.grisoft.com).Version: 6.0.647 / Virus Database: 414 - Release
Date: 29/03/2004|||Hi,
Since the database names and Physical file names are different in the
restored database you may need to do the below steps:-
1. Execute sp_detach_db <dbname> to detach the database
2. Use sp_attach_db <actual_dbname>,'physical mdf file name with
path','physical LDF file name with path'
Note:
Not the above 2 steps for all the problematic databases.
Thanks
Hari
MCDBA
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:9A89A18C-6F95-4066-B08E-3D5657CDF85B@.microsoft.com...
> Thank you, the database was restored. Now I have another problem. I
restored the master database to a disaster recovery server and the database
names are different. How do I Point the master DB to the new databases?

Restoring System and User DBs

We are using SQL Server 2000, all the System and User Databases and Logs are
on the same RAID-5 disks. I know this might not be recommended but this is a
fairly small system the Database is about 5G. And that's the only disk space
we have.
..
We do a complete backup of the System and User databases every night
We also backup the Logs from the User databases every night on tape.
..
If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
then we would have to restore the System and User databases. The User
databases would be a normal DB restore + the Transaction logs backup, we know
that we could not recovery up to the point of failure since the current logs
would also be lost.
..
But what would be the steps to restore the System Databases, ex: Master ?
I was reading some of the notes and they metionned that we would have to do
a startup in single user mode, then restore the Master datafile.
So as a test I shut down the servive, renamed the Master DB and logs, tried
to restart the service, it failed which is expected. Then tried to start
using
sqlservr.exe -c -m, but this also failed with error opening master.mdf
..
So if the master file and logs are gone, do I have to use rebuildm.exe
first,then resotre the master backup, or did I miss a step ?
..
Also as another test I rename the model datafile and it's log,when I use
sqlservr.exe -c -m to start the sqlserver, the log indicated that the master
and other databases were started except for model, but when I tried to
access SQL Analyzer I kept getting an error SQL server does not exist or
access denied
..
Basically I'm trying to test our recover strategy if we loose the RAID,
which would mean that we loose all datafiles and logs and would have to
recover from the backup done the night before.
..
Any feedback on the proper procedure to restore the System Databases are
welcome
..
Thanks in advance
Here's a good starting point for restoring system DBs:
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
Master remembers where all of the databases are, so after restoring it you
may see user databases show up as Suspect. Don't Panic (Thank you Doug
Adams for that lovely phrase)
"SQL Server newbie" wrote:

> We are using SQL Server 2000, all the System and User Databases and Logs are
> on the same RAID-5 disks. I know this might not be recommended but this is a
> fairly small system the Database is about 5G. And that's the only disk space
> we have.
> .
> We do a complete backup of the System and User databases every night
> We also backup the Logs from the User databases every night on tape.
> .
> If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
> then we would have to restore the System and User databases. The User
> databases would be a normal DB restore + the Transaction logs backup, we know
> that we could not recovery up to the point of failure since the current logs
> would also be lost.
> .
> But what would be the steps to restore the System Databases, ex: Master ?
> I was reading some of the notes and they metionned that we would have to do
> a startup in single user mode, then restore the Master datafile.
> So as a test I shut down the servive, renamed the Master DB and logs, tried
> to restart the service, it failed which is expected. Then tried to start
> using
> sqlservr.exe -c -m, but this also failed with error opening master.mdf
> .
> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup, or did I miss a step ?
> .
> Also as another test I rename the model datafile and it's log,when I use
> sqlservr.exe -c -m to start the sqlserver, the log indicated that the master
> and other databases were started except for model, but when I tried to
> access SQL Analyzer I kept getting an error SQL server does not exist or
> access denied
> .
> Basically I'm trying to test our recover strategy if we loose the RAID,
> which would mean that we loose all datafiles and logs and would have to
> recover from the backup done the night before.
> .
> Any feedback on the proper procedure to restore the System Databases are
> welcome
> .
> Thanks in advance
|||Thanks for the info.
..
But my question remains, if the master.mdf and it's log are removed, can we
just restore from a previous backup or we have to rebuilt it first using
rebuilm.exe
and then restore.
Thanks
"James Luetkehoelter" wrote:
[vbcol=seagreen]
> Here's a good starting point for restoring system DBs:
> http://msdn2.microsoft.com/en-us/library/ms190190.aspx
> Master remembers where all of the databases are, so after restoring it you
> may see user databases show up as Suspect. Don't Panic (Thank you Doug
> Adams for that lovely phrase)
> "SQL Server newbie" wrote:
|||> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup,
Correct. If you can't start SQL Server, it is difficult to have SQL Server perform a restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in message
news:94A87069-D833-4C84-8765-DF0D843A5718@.microsoft.com...
> We are using SQL Server 2000, all the System and User Databases and Logs are
> on the same RAID-5 disks. I know this might not be recommended but this is a
> fairly small system the Database is about 5G. And that's the only disk space
> we have.
> .
> We do a complete backup of the System and User databases every night
> We also backup the Logs from the User databases every night on tape.
> .
> If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
> then we would have to restore the System and User databases. The User
> databases would be a normal DB restore + the Transaction logs backup, we know
> that we could not recovery up to the point of failure since the current logs
> would also be lost.
> .
> But what would be the steps to restore the System Databases, ex: Master ?
> I was reading some of the notes and they metionned that we would have to do
> a startup in single user mode, then restore the Master datafile.
> So as a test I shut down the servive, renamed the Master DB and logs, tried
> to restart the service, it failed which is expected. Then tried to start
> using
> sqlservr.exe -c -m, but this also failed with error opening master.mdf
> .
> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup, or did I miss a step ?
> .
> Also as another test I rename the model datafile and it's log,when I use
> sqlservr.exe -c -m to start the sqlserver, the log indicated that the master
> and other databases were started except for model, but when I tried to
> access SQL Analyzer I kept getting an error SQL server does not exist or
> access denied
> .
> Basically I'm trying to test our recover strategy if we loose the RAID,
> which would mean that we loose all datafiles and logs and would have to
> recover from the backup done the night before.
> .
> Any feedback on the proper procedure to restore the System Databases are
> welcome
> .
> Thanks in advance
|||The SQL Server BOL have explicit steps for restoring all system databases,
including after a severe master failure. There are a number of SQL 2000 DBA
books that walk you through it too. I also recommend having an expert
either assist or at least review your disaster recovery plan. Holy Sh-t
time is NOT when you want to find out you don't have something set
up/documented correctly. :-)
TheSQLGuru
President
Indicium Resources, Inc.

Restoring System and User DBs

We are using SQL Server 2000, all the System and User Databases and Logs are
on the same RAID-5 disks. I know this might not be recommended but this is a
fairly small system the Database is about 5G. And that's the only disk space
we have.
.
We do a complete backup of the System and User databases every night
We also backup the Logs from the User databases every night on tape.
.
If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
then we would have to restore the System and User databases. The User
databases would be a normal DB restore + the Transaction logs backup, we know
that we could not recovery up to the point of failure since the current logs
would also be lost.
.
But what would be the steps to restore the System Databases, ex: Master ?
I was reading some of the notes and they metionned that we would have to do
a startup in single user mode, then restore the Master datafile.
So as a test I shut down the servive, renamed the Master DB and logs, tried
to restart the service, it failed which is expected. Then tried to start
using
sqlservr.exe -c -m, but this also failed with error opening master.mdf
.
So if the master file and logs are gone, do I have to use rebuildm.exe
first,then resotre the master backup, or did I miss a step ?
.
Also as another test I rename the model datafile and it's log,when I use
sqlservr.exe -c -m to start the sqlserver, the log indicated that the master
and other databases were started except for model, but when I tried to
access SQL Analyzer I kept getting an error SQL server does not exist or
access denied
.
Basically I'm trying to test our recover strategy if we loose the RAID,
which would mean that we loose all datafiles and logs and would have to
recover from the backup done the night before.
.
Any feedback on the proper procedure to restore the System Databases are
welcome
.
Thanks in advanceHere's a good starting point for restoring system DBs:
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
Master remembers where all of the databases are, so after restoring it you
may see user databases show up as Suspect. Don't Panic :) (Thank you Doug
Adams for that lovely phrase)
"SQL Server newbie" wrote:
> We are using SQL Server 2000, all the System and User Databases and Logs are
> on the same RAID-5 disks. I know this might not be recommended but this is a
> fairly small system the Database is about 5G. And that's the only disk space
> we have.
> .
> We do a complete backup of the System and User databases every night
> We also backup the Logs from the User databases every night on tape.
> .
> If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
> then we would have to restore the System and User databases. The User
> databases would be a normal DB restore + the Transaction logs backup, we know
> that we could not recovery up to the point of failure since the current logs
> would also be lost.
> .
> But what would be the steps to restore the System Databases, ex: Master ?
> I was reading some of the notes and they metionned that we would have to do
> a startup in single user mode, then restore the Master datafile.
> So as a test I shut down the servive, renamed the Master DB and logs, tried
> to restart the service, it failed which is expected. Then tried to start
> using
> sqlservr.exe -c -m, but this also failed with error opening master.mdf
> .
> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup, or did I miss a step ?
> .
> Also as another test I rename the model datafile and it's log,when I use
> sqlservr.exe -c -m to start the sqlserver, the log indicated that the master
> and other databases were started except for model, but when I tried to
> access SQL Analyzer I kept getting an error SQL server does not exist or
> access denied
> .
> Basically I'm trying to test our recover strategy if we loose the RAID,
> which would mean that we loose all datafiles and logs and would have to
> recover from the backup done the night before.
> .
> Any feedback on the proper procedure to restore the System Databases are
> welcome
> .
> Thanks in advance|||Thanks for the info.
.
But my question remains, if the master.mdf and it's log are removed, can we
just restore from a previous backup or we have to rebuilt it first using
rebuilm.exe
and then restore.
Thanks
"James Luetkehoelter" wrote:
> Here's a good starting point for restoring system DBs:
> http://msdn2.microsoft.com/en-us/library/ms190190.aspx
> Master remembers where all of the databases are, so after restoring it you
> may see user databases show up as Suspect. Don't Panic :) (Thank you Doug
> Adams for that lovely phrase)
> "SQL Server newbie" wrote:
> > We are using SQL Server 2000, all the System and User Databases and Logs are
> > on the same RAID-5 disks. I know this might not be recommended but this is a
> > fairly small system the Database is about 5G. And that's the only disk space
> > we have.
> > .
> > We do a complete backup of the System and User databases every night
> > We also backup the Logs from the User databases every night on tape.
> > .
> > If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
> > then we would have to restore the System and User databases. The User
> > databases would be a normal DB restore + the Transaction logs backup, we know
> > that we could not recovery up to the point of failure since the current logs
> > would also be lost.
> > .
> > But what would be the steps to restore the System Databases, ex: Master ?
> > I was reading some of the notes and they metionned that we would have to do
> > a startup in single user mode, then restore the Master datafile.
> > So as a test I shut down the servive, renamed the Master DB and logs, tried
> > to restart the service, it failed which is expected. Then tried to start
> > using
> > sqlservr.exe -c -m, but this also failed with error opening master.mdf
> > .
> > So if the master file and logs are gone, do I have to use rebuildm.exe
> > first,then resotre the master backup, or did I miss a step ?
> > .
> > Also as another test I rename the model datafile and it's log,when I use
> > sqlservr.exe -c -m to start the sqlserver, the log indicated that the master
> > and other databases were started except for model, but when I tried to
> > access SQL Analyzer I kept getting an error SQL server does not exist or
> > access denied
> > .
> > Basically I'm trying to test our recover strategy if we loose the RAID,
> > which would mean that we loose all datafiles and logs and would have to
> > recover from the backup done the night before.
> > .
> > Any feedback on the proper procedure to restore the System Databases are
> > welcome
> > .
> > Thanks in advance|||> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup,
Correct. If you can't start SQL Server, it is difficult to have SQL Server perform a restore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in message
news:94A87069-D833-4C84-8765-DF0D843A5718@.microsoft.com...
> We are using SQL Server 2000, all the System and User Databases and Logs are
> on the same RAID-5 disks. I know this might not be recommended but this is a
> fairly small system the Database is about 5G. And that's the only disk space
> we have.
> .
> We do a complete backup of the System and User databases every night
> We also backup the Logs from the User databases every night on tape.
> .
> If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
> then we would have to restore the System and User databases. The User
> databases would be a normal DB restore + the Transaction logs backup, we know
> that we could not recovery up to the point of failure since the current logs
> would also be lost.
> .
> But what would be the steps to restore the System Databases, ex: Master ?
> I was reading some of the notes and they metionned that we would have to do
> a startup in single user mode, then restore the Master datafile.
> So as a test I shut down the servive, renamed the Master DB and logs, tried
> to restart the service, it failed which is expected. Then tried to start
> using
> sqlservr.exe -c -m, but this also failed with error opening master.mdf
> .
> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup, or did I miss a step ?
> .
> Also as another test I rename the model datafile and it's log,when I use
> sqlservr.exe -c -m to start the sqlserver, the log indicated that the master
> and other databases were started except for model, but when I tried to
> access SQL Analyzer I kept getting an error SQL server does not exist or
> access denied
> .
> Basically I'm trying to test our recover strategy if we loose the RAID,
> which would mean that we loose all datafiles and logs and would have to
> recover from the backup done the night before.
> .
> Any feedback on the proper procedure to restore the System Databases are
> welcome
> .
> Thanks in advance|||The SQL Server BOL have explicit steps for restoring all system databases,
including after a severe master failure. There are a number of SQL 2000 DBA
books that walk you through it too. I also recommend having an expert
either assist or at least review your disaster recovery plan. Holy Sh-t
time is NOT when you want to find out you don't have something set
up/documented correctly. :-)
--
TheSQLGuru
President
Indicium Resources, Inc.

Restoring System and User DBs

We are using SQL Server 2000, all the System and User Databases and Logs are
on the same RAID-5 disks. I know this might not be recommended but this is a
fairly small system the Database is about 5G. And that's the only disk space
we have.
.
We do a complete backup of the System and User databases every night
We also backup the Logs from the User databases every night on tape.
.
If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
then we would have to restore the System and User databases. The User
databases would be a normal DB restore + the Transaction logs backup, we kno
w
that we could not recovery up to the point of failure since the current logs
would also be lost.
.
But what would be the steps to restore the System Databases, ex: Master ?
I was reading some of the notes and they metionned that we would have to do
a startup in single user mode, then restore the Master datafile.
So as a test I shut down the servive, renamed the Master DB and logs, tried
to restart the service, it failed which is expected. Then tried to start
using
sqlservr.exe -c -m, but this also failed with error opening master.mdf
.
So if the master file and logs are gone, do I have to use rebuildm.exe
first,then resotre the master backup, or did I miss a step ?
.
Also as another test I rename the model datafile and it's log,when I use
sqlservr.exe -c -m to start the sqlserver, the log indicated that the master
and other databases were started except for model, but when I tried to
access SQL Analyzer I kept getting an error SQL server does not exist or
access denied
.
Basically I'm trying to test our recover strategy if we loose the RAID,
which would mean that we loose all datafiles and logs and would have to
recover from the backup done the night before.
.
Any feedback on the proper procedure to restore the System Databases are
welcome
.
Thanks in advanceHere's a good starting point for restoring system DBs:
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
Master remembers where all of the databases are, so after restoring it you
may see user databases show up as Suspect. Don't Panic (Thank you Doug
Adams for that lovely phrase)
"SQL Server newbie" wrote:

> We are using SQL Server 2000, all the System and User Databases and Logs a
re
> on the same RAID-5 disks. I know this might not be recommended but this is
a
> fairly small system the Database is about 5G. And that's the only disk spa
ce
> we have.
> .
> We do a complete backup of the System and User databases every night
> We also backup the Logs from the User databases every night on tape.
> .
> If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
> then we would have to restore the System and User databases. The User
> databases would be a normal DB restore + the Transaction logs backup, we k
now
> that we could not recovery up to the point of failure since the current lo
gs
> would also be lost.
> .
> But what would be the steps to restore the System Databases, ex: Master ?
> I was reading some of the notes and they metionned that we would have to d
o
> a startup in single user mode, then restore the Master datafile.
> So as a test I shut down the servive, renamed the Master DB and logs, trie
d
> to restart the service, it failed which is expected. Then tried to start
> using
> sqlservr.exe -c -m, but this also failed with error opening master.mdf
> .
> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup, or did I miss a step ?
> .
> Also as another test I rename the model datafile and it's log,when I use
> sqlservr.exe -c -m to start the sqlserver, the log indicated that the mast
er
> and other databases were started except for model, but when I tried to
> access SQL Analyzer I kept getting an error SQL server does not exist or
> access denied
> .
> Basically I'm trying to test our recover strategy if we loose the RAID,
> which would mean that we loose all datafiles and logs and would have to
> recover from the backup done the night before.
> .
> Any feedback on the proper procedure to restore the System Databases are
> welcome
> .
> Thanks in advance|||Thanks for the info.
.
But my question remains, if the master.mdf and it's log are removed, can we
just restore from a previous backup or we have to rebuilt it first using
rebuilm.exe
and then restore.
Thanks
"James Luetkehoelter" wrote:
[vbcol=seagreen]
> Here's a good starting point for restoring system DBs:
> http://msdn2.microsoft.com/en-us/library/ms190190.aspx
> Master remembers where all of the databases are, so after restoring it you
> may see user databases show up as Suspect. Don't Panic (Thank you Doug
> Adams for that lovely phrase)
> "SQL Server newbie" wrote:
>|||> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup,
Correct. If you can't start SQL Server, it is difficult to have SQL Server p
erform a restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in mes
sage
news:94A87069-D833-4C84-8765-DF0D843A5718@.microsoft.com...
> We are using SQL Server 2000, all the System and User Databases and Logs a
re
> on the same RAID-5 disks. I know this might not be recommended but this is
a
> fairly small system the Database is about 5G. And that's the only disk spa
ce
> we have.
> .
> We do a complete backup of the System and User databases every night
> We also backup the Logs from the User databases every night on tape.
> .
> If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
> then we would have to restore the System and User databases. The User
> databases would be a normal DB restore + the Transaction logs backup, we k
now
> that we could not recovery up to the point of failure since the current lo
gs
> would also be lost.
> .
> But what would be the steps to restore the System Databases, ex: Master ?
> I was reading some of the notes and they metionned that we would have to d
o
> a startup in single user mode, then restore the Master datafile.
> So as a test I shut down the servive, renamed the Master DB and logs, trie
d
> to restart the service, it failed which is expected. Then tried to start
> using
> sqlservr.exe -c -m, but this also failed with error opening master.mdf
> .
> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup, or did I miss a step ?
> .
> Also as another test I rename the model datafile and it's log,when I use
> sqlservr.exe -c -m to start the sqlserver, the log indicated that the mast
er
> and other databases were started except for model, but when I tried to
> access SQL Analyzer I kept getting an error SQL server does not exist or
> access denied
> .
> Basically I'm trying to test our recover strategy if we loose the RAID,
> which would mean that we loose all datafiles and logs and would have to
> recover from the backup done the night before.
> .
> Any feedback on the proper procedure to restore the System Databases are
> welcome
> .
> Thanks in advance|||The SQL Server BOL have explicit steps for restoring all system databases,
including after a severe master failure. There are a number of SQL 2000 DBA
books that walk you through it too. I also recommend having an expert
either assist or at least review your disaster recovery plan. Holy Sh-t
time is NOT when you want to find out you don't have something set
up/documented correctly. :-)
TheSQLGuru
President
Indicium Resources, Inc.sql

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.
Johnson
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
>
|||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:[vbcol=seagreen]
> 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:
|||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:
>
|||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.. .
>
|||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.. .
>
|||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:
>
|||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:
>
|||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.. .
>
|||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.. .
>

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:[vbcol=seagreen]
> 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:
>|||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:
>
>|||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 kno
w
>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...
>|||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 kno
w
>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...
>|||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:
>
>|||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:
>
>|||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...
>|||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...
>

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
>>
>>
>