Problems with restoring master from a backup - SQL Server 2000

Hello,
I am trying to restore the master DB and I am not getting anywhere.
I stor the server, then restart it in single user mode (sqlservr.exe -c -m
from command prompt)
When I open Enterprise Manager I find that my user DB is in single user
mode, master is not, so when I try to restore I get a message that master
must be in single user mode.
I must be missing something.
Anu help would be appreciated
RagnarIs this a default or a named instance? If it is a named instance, you need to use the I switch as
well (I think it is "I", but you better check with BIL, might be "i").
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:%23Oz%23yXzTFHA.3952@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am trying to restore the master DB and I am not getting anywhere.
> I stor the server, then restart it in single user mode (sqlservr.exe -c -m from command prompt)
> When I open Enterprise Manager I find that my user DB is in single user mode, master is not, so
> when I try to restore I get a message that master must be in single user mode.
> I must be missing something.
> Anu help would be appreciated
> Ragnar
>|||How to start the default instance of SQL Server in single-user mode (Command
Prompt
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_startp_4okl.asp
AMB
"Ragnar Midtskogen" wrote:
> Hello,
> I am trying to restore the master DB and I am not getting anywhere.
> I stor the server, then restart it in single user mode (sqlservr.exe -c -m
> from command prompt)
> When I open Enterprise Manager I find that my user DB is in single user
> mode, master is not, so when I try to restore I get a message that master
> must be in single user mode.
> I must be missing something.
> Anu help would be appreciated
> Ragnar
>
>|||How to start a named instance of SQL Server in single-user mode (Command
Prompt)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_startp_5609.asp
AMB
"Alejandro Mesa" wrote:
> How to start the default instance of SQL Server in single-user mode (Command
> Prompt)
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_startp_4okl.asp
>
> AMB
> "Ragnar Midtskogen" wrote:
> > Hello,
> >
> > I am trying to restore the master DB and I am not getting anywhere.
> > I stor the server, then restart it in single user mode (sqlservr.exe -c -m
> > from command prompt)
> > When I open Enterprise Manager I find that my user DB is in single user
> > mode, master is not, so when I try to restore I get a message that master
> > must be in single user mode.
> >
> > I must be missing something.
> >
> > Anu help would be appreciated
> >
> > Ragnar
> >
> >
> >|||Thank you Tibor,
I did not install it, but as far as I can tell it is a default instance.
For example, the name of the processes are MSSQLServer and SQLServerAgent.
Ragnar|||Thank you Alejandro,
As I wrote in my post:
> I stop the server, then restart it in single user mode (sqlservr.exe -c -m
> from command prompt)
This is what is described in the link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_startp_4okl.asp
Ragnar|||FYI. for a named instance the syntax is:
a.. From a command prompt, enter:
sqlservr.exe -c - m -s {instancename}
Ragnar
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OQVhbdzTFHA.2556@.TK2MSFTNGP12.phx.gbl...
> Is this a default or a named instance? If it is a named instance, you need
> to use the I switch as well (I think it is "I", but you better check with
> BIL, might be "i").
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:%23Oz%23yXzTFHA.3952@.TK2MSFTNGP15.phx.gbl...
>> Hello,
>> I am trying to restore the master DB and I am not getting anywhere.
>> I stor the server, then restart it in single user mode
>> (sqlservr.exe -c -m from command prompt)
>> When I open Enterprise Manager I find that my user DB is in single user
>> mode, master is not, so when I try to restore I get a message that master
>> must be in single user mode.
>> I must be missing something.
>> Anu help would be appreciated
>> Ragnar
>|||Try using Query Analyzer instead of EM to do the restore. post the RESTORE DATABASE command you
execute and the exact error message from SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:OsCB76$TFHA.3436@.TK2MSFTNGP09.phx.gbl...
> Thank you Tibor,
> I did not install it, but as far as I can tell it is a default instance.
> For example, the name of the processes are MSSQLServer and SQLServerAgent.
> Ragnar
>|||Thanks Tibor,
The error message I get no matter which way I do the restore is that the
master DB must be in single user mode.
The problem here is that even though I follow the recommended procedure for
starting the server in single user mode just puts my user DB in single user
mode.
I did a test and confirmed that my user DB was in single user moed and it
was.
However, I found a post on a NG where someone had the same problem and he
solved it by setting a property in SQL Server to start in single user mode
and it worked for him.
Problem is I can't find the property, and this was post from a couple of
years ago so there is no way to get in touch with the guy.
I will do some more searching when I get a chance.
Ragnar
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eV1vXHAUFHA.612@.TK2MSFTNGP12.phx.gbl...
> Try using Query Analyzer instead of EM to do the restore. post the RESTORE
> DATABASE command you execute and the exact error message from SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:OsCB76$TFHA.3436@.TK2MSFTNGP09.phx.gbl...
>> Thank you Tibor,
>> I did not install it, but as far as I can tell it is a default instance.
>> For example, the name of the processes are MSSQLServer and
>> SQLServerAgent.
>> Ragnar
>|||To start the SQL Server in single user mode for master or any other system
database recovery
From the command prompt
sqlservr.exe -m
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:uX0RF6aUFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Thanks Tibor,
> The error message I get no matter which way I do the restore is that the
> master DB must be in single user mode.
> The problem here is that even though I follow the recommended procedure
> for starting the server in single user mode just puts my user DB in single
> user mode.
> I did a test and confirmed that my user DB was in single user moed and it
> was.
> However, I found a post on a NG where someone had the same problem and he
> solved it by setting a property in SQL Server to start in single user mode
> and it worked for him.
> Problem is I can't find the property, and this was post from a couple of
> years ago so there is no way to get in touch with the guy.
> I will do some more searching when I get a chance.
> Ragnar
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:eV1vXHAUFHA.612@.TK2MSFTNGP12.phx.gbl...
>> Try using Query Analyzer instead of EM to do the restore. post the
>> RESTORE DATABASE command you execute and the exact error message from SQL
>> Server.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
>> news:OsCB76$TFHA.3436@.TK2MSFTNGP09.phx.gbl...
>> Thank you Tibor,
>> I did not install it, but as far as I can tell it is a default instance.
>> For example, the name of the processes are MSSQLServer and
>> SQLServerAgent.
>> Ragnar
>>
>|||Thanks Wayne,
I guess you have not read my the original post, the problem here is that the
command does not work for me, it puts my user DB in single user mode, but
not the master DB.
Ragnar|||How do you determine that the user db is in single user mode? Also, can you describe the exact steps
you take when you start SQL Server in single user mode (like "open DOS prompt, type sqlservr, ")?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:O0nt%23VnUFHA.928@.TK2MSFTNGP15.phx.gbl...
> Thanks Wayne,
> I guess you have not read my the original post, the problem here is that the command does not work
> for me, it puts my user DB in single user mode, but not the master DB.
> Ragnar
>|||First of all, I KNOW the documentation says SINGLE USER mode is sufficient;
however, there can be problems and it only works some times. But there is
an alternative.
The problem is that in order to restore a database, any database, you need
to take it offline. Unfortunately, you can't do that for the master, model,
or msdb system databases. So, an alternative is to not recover them on
startup. There are two trace flags that you can do: 3608 skips autorecovery
for all but the master database, 3607 skips recovery for all databases. As
an aside, 3609 skips the creation, and, thus, the recovery of the tempdb.
Those flags will put you in a position to detach and move or restore the
various system databases depending on which one you choose. However, there
are other caveats. What if someone else connects to these databases while
you are trying to do your work? For recovery, no one can have access to the
database you want to restore, becuase it has to be taken offline. This is
why Single User Mode is NECESSARY, but not SUFFICIENT.
There is one other trace flag you should be aware of, 4022, this bypasses
the launch of startup procedures. These too can cause havoc if you're
attempting to restore or manipulate system database. In addition, you will
want to "allow updates" through the use of sp_configure.
I know; that's a lot of work. Hence, there is another command line
parameter to make this all easier: sqlservr -f. This puts the instance
(default in this case) in MINIMAL CONFIGURATION and SINGLE USER mode. You
can only use a tool like Query Analyzer and only one connection to the
SYSTEM will be allowed as well as no recovery, no tempdb, no startup procs,
allow system updates, etc., etc., etc.
SQLSERVR -f
Try it. Also, have a look at the following:
Moving SQL Server databases to a new location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
Starting SQL Server with Minimal Configuration
http://msdn.microsoft.com/library/en-us/adminsql/ad_1_start_1cxa.asp?frame=true
Finally, the backup you are using must be of a master database AT THE SAME
BUILD LEVEL as the system you are trying to restore it to. If not, then you
must get it there, either by patching or by reinstalling and then ONLY
patching to the level of the backup you are attempting to use.
Hope this helps.
Sincerely,
Anthony Thomas
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:%23Oz%23yXzTFHA.3952@.TK2MSFTNGP15.phx.gbl...
Hello,
I am trying to restore the master DB and I am not getting anywhere.
I stor the server, then restart it in single user mode (sqlservr.exe -c -m
from command prompt)
When I open Enterprise Manager I find that my user DB is in single user
mode, master is not, so when I try to restore I get a message that master
must be in single user mode.
I must be missing something.
Anu help would be appreciated
Ragnar