restore master database: mode

Showing posts with label mode. Show all posts

Friday, March 30, 2012

Restored database in loading mode.

I tried to restore a dump database to a newly created
database.After it prompts the msg that the database has
beem successfully restored, in the Enterprise Manager, the
database (the restored database) is on the loading mode.
Is there any solution for this.
The data is running on SQL 2000(service pack 3A) in Win
Server 2003.
Pls advise.
Thanks.Seems like you forgot to do the Recovery. Check the "Recovering a Database
Without Restoring" topic in Books OnLine
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\ad
minsql.chm::/ad_bkprst_4zc7.htm).
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Ash'" <sukli@.hotmail.com> wrote in message
news:0c1101c38afb$e92d4b70$a401280a@.phx.gbl...
> I tried to restore a dump database to a newly created
> database.After it prompts the msg that the database has
> beem successfully restored, in the Enterprise Manager, the
> database (the restored database) is on the loading mode.
> Is there any solution for this.
> The data is running on SQL 2000(service pack 3A) in Win
> Server 2003.
> Pls advise.
> Thanks.|||In addition to Dejans message when you restore a database it is unavailable
(loading) while you restore the differential and all of the transaction
logs. When you have completed the restore, you run recovery will rolls back
incomplete transactions and makes the database available.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Ash'" <sukli@.hotmail.com> wrote in message
news:0c1101c38afb$e92d4b70$a401280a@.phx.gbl...
> I tried to restore a dump database to a newly created
> database.After it prompts the msg that the database has
> beem successfully restored, in the Enterprise Manager, the
> database (the restored database) is on the loading mode.
> Is there any solution for this.
> The data is running on SQL 2000(service pack 3A) in Win
> Server 2003.
> Pls advise.
> Thanks.

Monday, March 26, 2012

Restore to read-only mode

Hi all,

EM has the following restore option:

"Leave database read-only and able to restore additional transaction logs."

Using this, I can restore to a named transaction, then run a SELECT statement that outputs the current state of my data to a file while the db is still in load mode.

What is the T-SQL version of this EM property, if it exists. I'm using:

restore log MyDatabase from MyDiskDevice with file=1, norecovery,
stopatmark = 'mymark'

I can't find a read-only attribute in BOL though to use with the RESTORE statement. Can I do this in script?

Thanks,

BObALTER DATABASE <dbname> SET READ_ONLY WITH ROLLBACK IMMEDIATE|||Thanks Brett. I'll give it a try.

Bob

Wednesday, March 21, 2012

Restore single user mode

I have the following code to restore a database (it was not written by
me)
Dim ServerName As String
Dim DBName As String
Dim BackupToRestore As String
ServerName = "PENTIUM4\VIPER"
DBName = "master"
BackupToRestore = "ViperData\Bmaster"
Dim oServer As SQLDMO.SQLServer
Dim oRestore As SQLDMO.Restore
On Error GoTo Handler
'simple err checking
If ServerName = "" Or DBName = "" Or BackupToRestore = "" Then
MsgBox "You MUST provide server name, database name, and the name of
the bak file you want to restore", vbInformation + vbOKOnly, "Error"
Exit Sub
End If
'open connection to server
Set oServer = New SQLDMO.SQLServer
With oServer
.LoginSecure = True
.Connect ServerName
End With
'also need a restore object
Set oRestore = New SQLDMO.Restore
'use the 'with' construct to minimize property lookups
With oRestore
'this is where your backup will be restored to
.Database = DBName
'same as EM or TSQL, you can restore database, file, or log, here
we're going to
'use database
.Action = SQLDMORestore_Database
'this is the "force restore over existing database" option
.ReplaceDatabase = True
'this does a restore from a file instead of a device - note that we're
still
'restoring a database, NOT a file group
.Files = BackupToRestore
'do it
.SQLRestore oServer
End With
'standard clean up
Set oRestore = Nothing
oServer.DisConnect
Set oServer = Nothing
Exit Sub
Handler:
If MsgBox(Err.Description & ". Would you like to continue?",
vbInformation + vbYesNo) = vbYes Then
Resume Next
End If
I keep getting the error message that the sql server must be in single
use mode
The serve comes with a program I purchased and is controlled by
Microsoft service manager 8.00.760
How do i run it in single user mode?It sounds like you are trying to restore the database "Master" To =restore master the entire SQl server must be in single user mode. This =means starting SQl server from a command prompt or adjusting the startup =parameters for running it a a service. From a command line is simplest - =sqlservr -m.
Full detail;s of starting in single user mode are in BOL.
Mike John
<NoNothing@.aol.com> wrote in message =news:3kt3101tic672lvlp4fj78tepkqg4pviuf@.4ax.com...
> I have the following code to restore a database (it was not written by
> me)
> > Dim ServerName As String
> Dim DBName As String
> Dim BackupToRestore As String
> ServerName =3D "PENTIUM4\VIPER"
> DBName =3D "master"
> BackupToRestore =3D "ViperData\Bmaster"
> > Dim oServer As SQLDMO.SQLServer
> Dim oRestore As SQLDMO.Restore
> On Error GoTo Handler
> > 'simple err checking
> If ServerName =3D "" Or DBName =3D "" Or BackupToRestore =3D "" Then
> MsgBox "You MUST provide server name, database name, and the name of
> the bak file you want to restore", vbInformation + vbOKOnly, "Error"
> Exit Sub
> End If
> > 'open connection to server
> Set oServer =3D New SQLDMO.SQLServer
> With oServer
> .LoginSecure =3D True
> .Connect ServerName
> End With
> > 'also need a restore object
> Set oRestore =3D New SQLDMO.Restore
> > 'use the 'with' construct to minimize property lookups
> With oRestore
> 'this is where your backup will be restored to
> .Database =3D DBName
> 'same as EM or TSQL, you can restore database, file, or log, here
> we're going to
> 'use database
> .Action =3D SQLDMORestore_Database
> 'this is the "force restore over existing database" option
> .ReplaceDatabase =3D True
> 'this does a restore from a file instead of a device - note that we're
> still
> 'restoring a database, NOT a file group
> .Files =3D BackupToRestore
> 'do it
> .SQLRestore oServer
> End With
> > 'standard clean up
> Set oRestore =3D Nothing
> oServer.DisConnect
> Set oServer =3D Nothing
> > Exit Sub
> > Handler:
> If MsgBox(Err.Description & ". Would you like to continue?",
> vbInformation + vbYesNo) =3D vbYes Then
> Resume Next
> End If
> > > > I keep getting the error message that the sql server must be in single
> use mode
> > The serve comes with a program I purchased and is controlled by
> Microsoft service manager 8.00.760
> > How do i run it in single user mode?
>|||Yes I had read apbout that however two things. First to start my
server I need to type sqlservr.exe NOT sqlmangr.exe and when I type
sqlservr.exe -m I still get the error message
Am I using a different version of MS sqlmanager?
On Sat, 24 Jan 2004 08:23:04 -0000, "Mike John"
<Mike.John@.knowledgepool.spamtrap.com> wrote:
>It sounds like you are trying to restore the database "Master" To restore master the entire SQl server must be in single user mode. This means starting SQl server from a command prompt or adjusting the startup parameters for running it a a service. From a command line is simplest - sqlservr -m.
>Full detail;s of starting in single user mode are in BOL.
>Mike John
><NoNothing@.aol.com> wrote in message news:3kt3101tic672lvlp4fj78tepkqg4pviuf@.4ax.com...
>> I have the following code to restore a database (it was not written by
>> me)
>> Dim ServerName As String
>> Dim DBName As String
>> Dim BackupToRestore As String
>> ServerName = "PENTIUM4\VIPER"
>> DBName = "master"
>> BackupToRestore = "ViperData\Bmaster"
>> Dim oServer As SQLDMO.SQLServer
>> Dim oRestore As SQLDMO.Restore
>> On Error GoTo Handler
>> 'simple err checking
>> If ServerName = "" Or DBName = "" Or BackupToRestore = "" Then
>> MsgBox "You MUST provide server name, database name, and the name of
>> the bak file you want to restore", vbInformation + vbOKOnly, "Error"
>> Exit Sub
>> End If
>> 'open connection to server
>> Set oServer = New SQLDMO.SQLServer
>> With oServer
>> .LoginSecure = True
>> .Connect ServerName
>> End With
>> 'also need a restore object
>> Set oRestore = New SQLDMO.Restore
>> 'use the 'with' construct to minimize property lookups
>> With oRestore
>> 'this is where your backup will be restored to
>> .Database = DBName
>> 'same as EM or TSQL, you can restore database, file, or log, here
>> we're going to
>> 'use database
>> .Action = SQLDMORestore_Database
>> 'this is the "force restore over existing database" option
>> .ReplaceDatabase = True
>> 'this does a restore from a file instead of a device - note that we're
>> still
>> 'restoring a database, NOT a file group
>> .Files = BackupToRestore
>> 'do it
>> .SQLRestore oServer
>> End With
>> 'standard clean up
>> Set oRestore = Nothing
>> oServer.DisConnect
>> Set oServer = Nothing
>> Exit Sub
>> Handler:
>> If MsgBox(Err.Description & ". Would you like to continue?",
>> vbInformation + vbYesNo) = vbYes Then
>> Resume Next
>> End If
>>
>> I keep getting the error message that the sql server must be in single
>> use mode
>> The serve comes with a program I purchased and is controlled by
>> Microsoft service manager 8.00.760
>> How do i run it in single user mode?|||Soory for my ignorance what is BOL?
On Sat, 24 Jan 2004 08:23:04 -0000, "Mike John"
<Mike.John@.knowledgepool.spamtrap.com> wrote:
>It sounds like you are trying to restore the database "Master" To restore master the entire SQl server must be in single user mode. This means starting SQl server from a command prompt or adjusting the startup parameters for running it a a service. From a command line is simplest - sqlservr -m.
>Full detail;s of starting in single user mode are in BOL.
>Mike John
><NoNothing@.aol.com> wrote in message news:3kt3101tic672lvlp4fj78tepkqg4pviuf@.4ax.com...
>> I have the following code to restore a database (it was not written by
>> me)
>> Dim ServerName As String
>> Dim DBName As String
>> Dim BackupToRestore As String
>> ServerName = "PENTIUM4\VIPER"
>> DBName = "master"
>> BackupToRestore = "ViperData\Bmaster"
>> Dim oServer As SQLDMO.SQLServer
>> Dim oRestore As SQLDMO.Restore
>> On Error GoTo Handler
>> 'simple err checking
>> If ServerName = "" Or DBName = "" Or BackupToRestore = "" Then
>> MsgBox "You MUST provide server name, database name, and the name of
>> the bak file you want to restore", vbInformation + vbOKOnly, "Error"
>> Exit Sub
>> End If
>> 'open connection to server
>> Set oServer = New SQLDMO.SQLServer
>> With oServer
>> .LoginSecure = True
>> .Connect ServerName
>> End With
>> 'also need a restore object
>> Set oRestore = New SQLDMO.Restore
>> 'use the 'with' construct to minimize property lookups
>> With oRestore
>> 'this is where your backup will be restored to
>> .Database = DBName
>> 'same as EM or TSQL, you can restore database, file, or log, here
>> we're going to
>> 'use database
>> .Action = SQLDMORestore_Database
>> 'this is the "force restore over existing database" option
>> .ReplaceDatabase = True
>> 'this does a restore from a file instead of a device - note that we're
>> still
>> 'restoring a database, NOT a file group
>> .Files = BackupToRestore
>> 'do it
>> .SQLRestore oServer
>> End With
>> 'standard clean up
>> Set oRestore = Nothing
>> oServer.DisConnect
>> Set oServer = Nothing
>> Exit Sub
>> Handler:
>> If MsgBox(Err.Description & ". Would you like to continue?",
>> vbInformation + vbYesNo) = vbYes Then
>> Resume Next
>> End If
>>
>> I keep getting the error message that the sql server must be in single
>> use mode
>> The serve comes with a program I purchased and is controlled by
>> Microsoft service manager 8.00.760
>> How do i run it in single user mode?|||BOL is Books online. Comes with SQlmserver and is the full product =manual. Should read by everyone!
SQLSERVR.EXE is the command you want, sqlmangr is the little app that =sits in the system tray tellimng you its running.
Look up restoring the master database in Books Online and you will find =all the steps, but I would question WHY your VB code is trying to =restore master if you were not aware of it. Do you really want to =restore master?
Mike John
<NoNothing@.aol.com> wrote in message =news:0lg710lh55q4pc37jqotvp0khl0c8ob838@.4ax.com...
> Soory for my ignorance what is BOL?
> > On Sat, 24 Jan 2004 08:23:04 -0000, "Mike John"
> <Mike.John@.knowledgepool.spamtrap.com> wrote:
> > >It sounds like you are trying to restore the database "Master" To =restore master the entire SQl server must be in single user mode. This =means starting SQl server from a command prompt or adjusting the startup =parameters for running it a a service. From a command line is simplest - =sqlservr -m.
> >
> >Full detail;s of starting in single user mode are in BOL.
> >
> >Mike John
> >
> ><NoNothing@.aol.com> wrote in message =news:3kt3101tic672lvlp4fj78tepkqg4pviuf@.4ax.com...
> >> I have the following code to restore a database (it was not written =by
> >> me)
> >> > >> Dim ServerName As String
> >> Dim DBName As String
> >> Dim BackupToRestore As String
> >> ServerName =3D "PENTIUM4\VIPER"
> >> DBName =3D "master"
> >> BackupToRestore =3D "ViperData\Bmaster"
> >> > >> Dim oServer As SQLDMO.SQLServer
> >> Dim oRestore As SQLDMO.Restore
> >> On Error GoTo Handler
> >> > >> 'simple err checking
> >> If ServerName =3D "" Or DBName =3D "" Or BackupToRestore =3D "" =Then
> >> MsgBox "You MUST provide server name, database name, and the name =of
> >> the bak file you want to restore", vbInformation + vbOKOnly, ="Error"
> >> Exit Sub
> >> End If
> >> > >> 'open connection to server
> >> Set oServer =3D New SQLDMO.SQLServer
> >> With oServer
> >> .LoginSecure =3D True
> >> .Connect ServerName
> >> End With
> >> > >> 'also need a restore object
> >> Set oRestore =3D New SQLDMO.Restore
> >> > >> 'use the 'with' construct to minimize property lookups
> >> With oRestore
> >> 'this is where your backup will be restored to
> >> .Database =3D DBName
> >> 'same as EM or TSQL, you can restore database, file, or log, here
> >> we're going to
> >> 'use database
> >> .Action =3D SQLDMORestore_Database
> >> 'this is the "force restore over existing database" option
> >> .ReplaceDatabase =3D True
> >> 'this does a restore from a file instead of a device - note that =we're
> >> still
> >> 'restoring a database, NOT a file group
> >> .Files =3D BackupToRestore
> >> 'do it
> >> .SQLRestore oServer
> >> End With
> >> > >> 'standard clean up
> >> Set oRestore =3D Nothing
> >> oServer.DisConnect
> >> Set oServer =3D Nothing
> >> > >> Exit Sub
> >> > >> Handler:
> >> If MsgBox(Err.Description & ". Would you like to continue?",
> >> vbInformation + vbYesNo) =3D vbYes Then
> >> Resume Next
> >> End If
> >> > >> > >> > >> I keep getting the error message that the sql server must be in =single
> >> use mode
> >> > >> The serve comes with a program I purchased and is controlled by
> >> Microsoft service manager 8.00.760
> >> > >> How do i run it in single user mode?
> >> >

Restore single user mode

quote:

> I have the following code to restore a database (it was not written by
> me)
>=20
> Dim ServerName As String
> Dim DBName As String
> Dim BackupToRestore As String
> ServerName =3D "PENTIUM4\VIPER"
> DBName =3D "master"
> BackupToRestore =3D "ViperData\Bmaster"
>=20
> Dim oServer As SQLDMO.SQLServer
> Dim oRestore As SQLDMO.Restore
> On Error GoTo Handler
> =20
> 'simple err checking
> If ServerName =3D "" Or DBName =3D "" Or BackupToRestore =3D "" Then
> MsgBox "You MUST provide server name, database name, and the name of
> the bak file you want to restore", vbInformation + vbOKOnly, "Error"
> Exit Sub
> End If
> =20
> 'open connection to server
> Set oServer =3D New SQLDMO.SQLServer
> With oServer
> .LoginSecure =3D True
> .Connect ServerName
> End With
> =20
> 'also need a restore object
> Set oRestore =3D New SQLDMO.Restore
> =20
> 'use the 'with' construct to minimize property lookups
> With oRestore
> 'this is where your backup will be restored to
> .Database =3D DBName
> 'same as EM or TSQL, you can restore database, file, or log, here
> we're going to
> 'use database
> .Action =3D SQLDMORestore_Database
> 'this is the "force restore over existing database" option
> .ReplaceDatabase =3D True
> 'this does a restore from a file instead of a device - note that we're
> still
> 'restoring a database, NOT a file group
> .Files =3D BackupToRestore
> 'do it
> .SQLRestore oServer
> End With
> =20
> 'standard clean up
> Set oRestore =3D Nothing
> oServer.DisConnect
> Set oServer =3D Nothing
> =20
> Exit Sub
> =20
> Handler:
> If MsgBox(Err.Description & ". Would you like to continue?",
> vbInformation + vbYesNo) =3D vbYes Then
> Resume Next
> End If
>=20
>=20
>=20
> I keep getting the error message that the sql server must be in single
> use mode
>=20
> The serve comes with a program I purchased and is controlled by
> Microsoft service manager 8.00.760
>=20
> How do i run it in single user mode?
>

|||Yes I had read apbout that however two things. First to start my
server I need to type sqlservr.exe NOT sqlmangr.exe and when I type
sqlservr.exe -m I still get the error message
Am I using a different version of MS sqlmanager?
On Sat, 24 Jan 2004 08:23:04 -0000, "Mike John"
<Mike.John@.knowledgepool.spamtrap.com> wrote:

quote:

>It sounds like you are trying to restore the database "Master" To restore master the entire
SQl server must be in single user mode. This means starting SQl server from a command promp
t or adjusting the startup parameters for running it a a service. From

a command line is simplest - sqlservr -m.[QUOTE]
>Full detail;s of starting in single user mode are in BOL.
>Mike John
><NoNothing@.aol.com> wrote in message news:3kt3101tic672lvlp4fj78tepkqg4pviu
f@.4ax.com...|||Soory for my ignorance what is BOL?
On Sat, 24 Jan 2004 08:23:04 -0000, "Mike John"
<Mike.John@.knowledgepool.spamtrap.com> wrote:

quote:

>It sounds like you are trying to restore the database "Master" To restore master the entire
SQl server must be in single user mode. This means starting SQl server from a command promp
t or adjusting the startup parameters for running it a a service. From

a command line is simplest - sqlservr -m.[QUOTE]
>Full detail;s of starting in single user mode are in BOL.
>Mike John
><NoNothing@.aol.com> wrote in message news:3kt3101tic672lvlp4fj78tepkqg4pviu
f@.4ax.com...|||BOL is Books online. Comes with SQlmserver and is the full product =
manual. Should read by everyone!
SQLSERVR.EXE is the command you want, sqlmangr is the little app that =
sits in the system tray tellimng you its running.
Look up restoring the master database in Books Online and you will find =
all the steps, but I would question WHY your VB code is trying to =
restore master if you were not aware of it. Do you really want to =
restore master?
Mike John
<NoNothing@.aol.com> wrote in message =
news:0lg710lh55q4pc37jqotvp0khl0c8ob838@.
4ax.com...

quote:

> Soory for my ignorance what is BOL?
>=20
> On Sat, 24 Jan 2004 08:23:04 -0000, "Mike John"
> <Mike.John@.knowledgepool.spamtrap.com> wrote:
>=20
restore master the entire SQl server must be in single user mode. This =
means starting SQl server from a command prompt or adjusting the startup =
parameters for running it a a service. From a command line is simplest - =
sqlservr -m.[QUOTE]
news:3kt3101tic672lvlp4fj78tepkqg4pviuf@.
4ax.com...[QUOTE]
by[QUOTE]
Then[QUOTE]
of[QUOTE]
"Error"[QUOTE]
we're[QUOTE]
single[QUOTE]
>

Tuesday, March 20, 2012

Restore runs longer in batch mode

I have created a Job that uses "cmcexec" to execute a SQL script. This Job
brings SQL to single_user mode then restores from a directory that is local
to the server.
1) When I run this restore in the GUI it takes 15 minutes but is still
executing
(1 + hours).
2) How can I tell that it is actually running and not hung up?
Hi,
From Query Analyzer, Could you execute the syetm procedure sp_who2 and see
the status of the backup process.
Repeatedly execute sp2 and check if the CPU time and DISK IO are increasing
for backup process. If the value is increasing
then the process is active.
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:E13E54D2-C77F-4A44-BDAB-630166185032@.microsoft.com...
> I have created a Job that uses "cmcexec" to execute a SQL script. This
Job
> brings SQL to single_user mode then restores from a directory that is
local
> to the server.
> 1) When I run this restore in the GUI it takes 15 minutes but is still
> executing
> (1 + hours).
> 2) How can I tell that it is actually running and not hung up?

Monday, February 20, 2012

restore master database backup to a new server, single user mode

Hi,
I am using Win2000+SQL2000.
I have a backup copy of MASTER database, I need to retore it my newly setup
SQL server.
I use the command "sqlservr.exe -c -m" from DOS command prompt to startup
SQL server single user,
I can not connect to this sql servre instance by using SQL query analyzer,
kindly please tell me the steps to restore the master database to a new
server.
Thanks
Michael
Hi,
What is the error you are getting while connecting to query analyzer.
Have a look into steps (15 - 28) in the attached link.
http://www.dbarecovery.com/restoremasterdb.html
You have to restore the Master database and then the user databases.
Otherwise Master database restore will
overwrite the existing database entries.
Thanks
Hari
MCDBA
"Michael" <Ruihuiwen@.hotmail.com> wrote in message
news:Oo6cOqNgEHA.3132@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am using Win2000+SQL2000.
> I have a backup copy of MASTER database, I need to retore it my newly
setup
> SQL server.
> I use the command "sqlservr.exe -c -m" from DOS command prompt to startup
> SQL server single user,
> I can not connect to this sql servre instance by using SQL query analyzer,
> kindly please tell me the steps to restore the master database to a new
> server.
> Thanks
> Michael
>
>
>

restore master database backup to a new server, single user mode

Hi,
I am using Win2000+SQL2000.
I have a backup copy of MASTER database, I need to retore it my newly setup
SQL server.
I use the command "sqlservr.exe -c -m" from DOS command prompt to startup
SQL server single user,
I can not connect to this sql servre instance by using SQL query analyzer,
kindly please tell me the steps to restore the master database to a new
server.
Thanks
MichaelHi,
What is the error you are getting while connecting to query analyzer.
Have a look into steps (15 - 28) in the attached link.
http://www.dbarecovery.com/restoremasterdb.html
You have to restore the Master database and then the user databases.
Otherwise Master database restore will
overwrite the existing database entries.
Thanks
Hari
MCDBA
"Michael" <Ruihuiwen@.hotmail.com> wrote in message
news:Oo6cOqNgEHA.3132@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am using Win2000+SQL2000.
> I have a backup copy of MASTER database, I need to retore it my newly
setup
> SQL server.
> I use the command "sqlservr.exe -c -m" from DOS command prompt to startup
> SQL server single user,
> I can not connect to this sql servre instance by using SQL query analyzer,
> kindly please tell me the steps to restore the master database to a new
> server.
> Thanks
> Michael
>
>
>

restore master database backup to a new server, single user mode

Hi,
I am using Win2000+SQL2000.
I have a backup copy of MASTER database, I need to retore it my newly setup
SQL server.
I use the command "sqlservr.exe -c -m" from DOS command prompt to startup
SQL server single user,
I can not connect to this sql servre instance by using SQL query analyzer,
kindly please tell me the steps to restore the master database to a new
server.
Thanks
MichaelHi,
What is the error you are getting while connecting to query analyzer.
Have a look into steps (15 - 28) in the attached link.
http://www.dbarecovery.com/restoremasterdb.html
You have to restore the Master database and then the user databases.
Otherwise Master database restore will
overwrite the existing database entries.
Thanks
Hari
MCDBA
"Michael" <Ruihuiwen@.hotmail.com> wrote in message
news:Oo6cOqNgEHA.3132@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am using Win2000+SQL2000.
> I have a backup copy of MASTER database, I need to retore it my newly
setup
> SQL server.
> I use the command "sqlservr.exe -c -m" from DOS command prompt to startup
> SQL server single user,
> I can not connect to this sql servre instance by using SQL query analyzer,
> kindly please tell me the steps to restore the master database to a new
> server.
> Thanks
> Michael
>
>
>

restore master database

I have two instances on a sql2000 server. I need to change master database to
single user mode,
so I can restore the master database from a different server.
I tried the command line, and queries. none of them worked.
ThanksDid you try?
sqlservr -m
Why do you want to restore the master database from a different server?
Ben Nevarez, MCDBA, OCP
Database Administrator
"anoni" wrote:
> I have two instances on a sql2000 server. I need to change master database to
> single user mode,
> so I can restore the master database from a different server.
> I tried the command line, and queries. none of them worked.
> Thanks|||I did.
I need to have production master database to restored on testing sql server
master database.
"Ben Nevarez" wrote:
> Did you try?
> sqlservr -m
> Why do you want to restore the master database from a different server?
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "anoni" wrote:
> > I have two instances on a sql2000 server. I need to change master database to
> > single user mode,
> > so I can restore the master database from a different server.
> >
> > I tried the command line, and queries. none of them worked.
> >
> > Thanks|||It is not master the one that needs to be in single user mode but the entire
SQL Server instance. Stop SQL Server and run
sqlservr -m
If you have errors let us know.
Ben Nevarez, MCDBA, OCP
Database Administrator
"anoni" wrote:
> I did.
> I need to have production master database to restored on testing sql server
> master database.
> "Ben Nevarez" wrote:
> >
> > Did you try?
> >
> > sqlservr -m
> >
> > Why do you want to restore the master database from a different server?
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "anoni" wrote:
> >
> > > I have two instances on a sql2000 server. I need to change master database to
> > > single user mode,
> > > so I can restore the master database from a different server.
> > >
> > > I tried the command line, and queries. none of them worked.
> > >
> > > Thanks|||I did try the command.
it stop at Recover complete then showed prompt(at next line). sql service
never started.
"Ben Nevarez" wrote:
> It is not master the one that needs to be in single user mode but the entire
> SQL Server instance. Stop SQL Server and run
> sqlservr -m
> If you have errors let us know.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "anoni" wrote:
> > I did.
> > I need to have production master database to restored on testing sql server
> > master database.
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > Did you try?
> > >
> > > sqlservr -m
> > >
> > > Why do you want to restore the master database from a different server?
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "anoni" wrote:
> > >
> > > > I have two instances on a sql2000 server. I need to change master database to
> > > > single user mode,
> > > > so I can restore the master database from a different server.
> > > >
> > > > I tried the command line, and queries. none of them worked.
> > > >
> > > > Thanks|||how do you restore database while sql is not running?
"Ben Nevarez" wrote:
> It is not master the one that needs to be in single user mode but the entire
> SQL Server instance. Stop SQL Server and run
> sqlservr -m
> If you have errors let us know.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "anoni" wrote:
> > I did.
> > I need to have production master database to restored on testing sql server
> > master database.
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > Did you try?
> > >
> > > sqlservr -m
> > >
> > > Why do you want to restore the master database from a different server?
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "anoni" wrote:
> > >
> > > > I have two instances on a sql2000 server. I need to change master database to
> > > > single user mode,
> > > > so I can restore the master database from a different server.
> > > >
> > > > I tried the command line, and queries. none of them worked.
> > > >
> > > > Thanks|||When you use
sqlservr -m
do you see
SQL Server started in single user mode. Updates ...
on the screen or on the SQL Server log? (It is on line 10 on my test
instalation.)
Do you see any error?
Ben Nevarez, MCDBA, OCP
Database Administrator
"anoni" wrote:
> I did try the command.
> it stop at Recover complete then showed prompt(at next line). sql service
> never started.
> "Ben Nevarez" wrote:
> >
> > It is not master the one that needs to be in single user mode but the entire
> > SQL Server instance. Stop SQL Server and run
> >
> > sqlservr -m
> >
> > If you have errors let us know.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "anoni" wrote:
> >
> > > I did.
> > > I need to have production master database to restored on testing sql server
> > > master database.
> > >
> > > "Ben Nevarez" wrote:
> > >
> > > >
> > > > Did you try?
> > > >
> > > > sqlservr -m
> > > >
> > > > Why do you want to restore the master database from a different server?
> > > >
> > > > Ben Nevarez, MCDBA, OCP
> > > > Database Administrator
> > > >
> > > >
> > > > "anoni" wrote:
> > > >
> > > > > I have two instances on a sql2000 server. I need to change master database to
> > > > > single user mode,
> > > > > so I can restore the master database from a different server.
> > > > >
> > > > > I tried the command line, and queries. none of them worked.
> > > > >
> > > > > Thanks|||I do see the message. But how do i recover the database since the sql service
is not running from enterprise mgr
"Ben Nevarez" wrote:
> When you use
> sqlservr -m
> do you see
> SQL Server started in single user mode. Updates ...
> on the screen or on the SQL Server log? (It is on line 10 on my test
> instalation.)
> Do you see any error?
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "anoni" wrote:
> > I did try the command.
> > it stop at Recover complete then showed prompt(at next line). sql service
> > never started.
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > It is not master the one that needs to be in single user mode but the entire
> > > SQL Server instance. Stop SQL Server and run
> > >
> > > sqlservr -m
> > >
> > > If you have errors let us know.
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "anoni" wrote:
> > >
> > > > I did.
> > > > I need to have production master database to restored on testing sql server
> > > > master database.
> > > >
> > > > "Ben Nevarez" wrote:
> > > >
> > > > >
> > > > > Did you try?
> > > > >
> > > > > sqlservr -m
> > > > >
> > > > > Why do you want to restore the master database from a different server?
> > > > >
> > > > > Ben Nevarez, MCDBA, OCP
> > > > > Database Administrator
> > > > >
> > > > >
> > > > > "anoni" wrote:
> > > > >
> > > > > > I have two instances on a sql2000 server. I need to change master database to
> > > > > > single user mode,
> > > > > > so I can restore the master database from a different server.
> > > > > >
> > > > > > I tried the command line, and queries. none of them worked.
> > > > > >
> > > > > > Thanks|||You need SQL Server running in single user admin mode. Is that a default
instance?
Try sqlservr -m again and copy/paste the text output here.
Ben Nevarez, MCDBA, OCP
Database Administrator
"anoni" wrote:
> how do you restore database while sql is not running?
> "Ben Nevarez" wrote:
> >
> > It is not master the one that needs to be in single user mode but the entire
> > SQL Server instance. Stop SQL Server and run
> >
> > sqlservr -m
> >
> > If you have errors let us know.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "anoni" wrote:
> >
> > > I did.
> > > I need to have production master database to restored on testing sql server
> > > master database.
> > >
> > > "Ben Nevarez" wrote:
> > >
> > > >
> > > > Did you try?
> > > >
> > > > sqlservr -m
> > > >
> > > > Why do you want to restore the master database from a different server?
> > > >
> > > > Ben Nevarez, MCDBA, OCP
> > > > Database Administrator
> > > >
> > > >
> > > > "anoni" wrote:
> > > >
> > > > > I have two instances on a sql2000 server. I need to change master database to
> > > > > single user mode,
> > > > > so I can restore the master database from a different server.
> > > > >
> > > > > I tried the command line, and queries. none of them worked.
> > > > >
> > > > > Thanks|||after ran the sqlservr -m, sql is running in single user mode. But from the
Enterprise mgr. it shows sql is stopped.
I do I restore the master database after I sql running in single user mode
since I can not restore it from the Enterprise mgr?
"Ben Nevarez" wrote:
> You need SQL Server running in single user admin mode. Is that a default
> instance?
> Try sqlservr -m again and copy/paste the text output here.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "anoni" wrote:
> > how do you restore database while sql is not running?
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > It is not master the one that needs to be in single user mode but the entire
> > > SQL Server instance. Stop SQL Server and run
> > >
> > > sqlservr -m
> > >
> > > If you have errors let us know.
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "anoni" wrote:
> > >
> > > > I did.
> > > > I need to have production master database to restored on testing sql server
> > > > master database.
> > > >
> > > > "Ben Nevarez" wrote:
> > > >
> > > > >
> > > > > Did you try?
> > > > >
> > > > > sqlservr -m
> > > > >
> > > > > Why do you want to restore the master database from a different server?
> > > > >
> > > > > Ben Nevarez, MCDBA, OCP
> > > > > Database Administrator
> > > > >
> > > > >
> > > > > "anoni" wrote:
> > > > >
> > > > > > I have two instances on a sql2000 server. I need to change master database to
> > > > > > single user mode,
> > > > > > so I can restore the master database from a different server.
> > > > > >
> > > > > > I tried the command line, and queries. none of them worked.
> > > > > >
> > > > > > Thanks|||no. it is not the default instance. it is the second instance that i created.
The default is ok. I could not restore the second instance.
"Ben Nevarez" wrote:
> You need SQL Server running in single user admin mode. Is that a default
> instance?
> Try sqlservr -m again and copy/paste the text output here.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "anoni" wrote:
> > how do you restore database while sql is not running?
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > It is not master the one that needs to be in single user mode but the entire
> > > SQL Server instance. Stop SQL Server and run
> > >
> > > sqlservr -m
> > >
> > > If you have errors let us know.
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "anoni" wrote:
> > >
> > > > I did.
> > > > I need to have production master database to restored on testing sql server
> > > > master database.
> > > >
> > > > "Ben Nevarez" wrote:
> > > >
> > > > >
> > > > > Did you try?
> > > > >
> > > > > sqlservr -m
> > > > >
> > > > > Why do you want to restore the master database from a different server?
> > > > >
> > > > > Ben Nevarez, MCDBA, OCP
> > > > > Database Administrator
> > > > >
> > > > >
> > > > > "anoni" wrote:
> > > > >
> > > > > > I have two instances on a sql2000 server. I need to change master database to
> > > > > > single user mode,
> > > > > > so I can restore the master database from a different server.
> > > > > >
> > > > > > I tried the command line, and queries. none of them worked.
> > > > > >
> > > > > > Thanks|||When SQL Server is running in single user admin mode onle one admin session
can connect using Enterprise Manager or Query Analyzer. I have seen that EM
shows like the instance is down (red square) but I can still do right-click
and select Connect.
Ben Nevarez
"anoni" wrote:
> after ran the sqlservr -m, sql is running in single user mode. But from the
> Enterprise mgr. it shows sql is stopped.
> I do I restore the master database after I sql running in single user mode
> since I can not restore it from the Enterprise mgr?
> "Ben Nevarez" wrote:
> >
> > You need SQL Server running in single user admin mode. Is that a default
> > instance?
> >
> > Try sqlservr -m again and copy/paste the text output here.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "anoni" wrote:
> >
> > > how do you restore database while sql is not running?
> > >
> > > "Ben Nevarez" wrote:
> > >
> > > >
> > > > It is not master the one that needs to be in single user mode but the entire
> > > > SQL Server instance. Stop SQL Server and run
> > > >
> > > > sqlservr -m
> > > >
> > > > If you have errors let us know.
> > > >
> > > > Ben Nevarez, MCDBA, OCP
> > > > Database Administrator
> > > >
> > > >
> > > > "anoni" wrote:
> > > >
> > > > > I did.
> > > > > I need to have production master database to restored on testing sql server
> > > > > master database.
> > > > >
> > > > > "Ben Nevarez" wrote:
> > > > >
> > > > > >
> > > > > > Did you try?
> > > > > >
> > > > > > sqlservr -m
> > > > > >
> > > > > > Why do you want to restore the master database from a different server?
> > > > > >
> > > > > > Ben Nevarez, MCDBA, OCP
> > > > > > Database Administrator
> > > > > >
> > > > > >
> > > > > > "anoni" wrote:
> > > > > >
> > > > > > > I have two instances on a sql2000 server. I need to change master database to
> > > > > > > single user mode,
> > > > > > > so I can restore the master database from a different server.
> > > > > > >
> > > > > > > I tried the command line, and queries. none of them worked.
> > > > > > >
> > > > > > > Thanks|||Yes, EM doesn't understand the SQL Server is started as it is started in a special way...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:F5D9C15E-FE3C-4A5B-B001-988626F761DF@.microsoft.com...
> When SQL Server is running in single user admin mode onle one admin session
> can connect using Enterprise Manager or Query Analyzer. I have seen that EM
> shows like the instance is down (red square) but I can still do right-click
> and select Connect.
> Ben Nevarez
>
> "anoni" wrote:
>> after ran the sqlservr -m, sql is running in single user mode. But from the
>> Enterprise mgr. it shows sql is stopped.
>> I do I restore the master database after I sql running in single user mode
>> since I can not restore it from the Enterprise mgr?
>> "Ben Nevarez" wrote:
>> >
>> > You need SQL Server running in single user admin mode. Is that a default
>> > instance?
>> >
>> > Try sqlservr -m again and copy/paste the text output here.
>> >
>> > Ben Nevarez, MCDBA, OCP
>> > Database Administrator
>> >
>> >
>> > "anoni" wrote:
>> >
>> > > how do you restore database while sql is not running?
>> > >
>> > > "Ben Nevarez" wrote:
>> > >
>> > > >
>> > > > It is not master the one that needs to be in single user mode but the entire
>> > > > SQL Server instance. Stop SQL Server and run
>> > > >
>> > > > sqlservr -m
>> > > >
>> > > > If you have errors let us know.
>> > > >
>> > > > Ben Nevarez, MCDBA, OCP
>> > > > Database Administrator
>> > > >
>> > > >
>> > > > "anoni" wrote:
>> > > >
>> > > > > I did.
>> > > > > I need to have production master database to restored on testing sql server
>> > > > > master database.
>> > > > >
>> > > > > "Ben Nevarez" wrote:
>> > > > >
>> > > > > >
>> > > > > > Did you try?
>> > > > > >
>> > > > > > sqlservr -m
>> > > > > >
>> > > > > > Why do you want to restore the master database from a different server?
>> > > > > >
>> > > > > > Ben Nevarez, MCDBA, OCP
>> > > > > > Database Administrator
>> > > > > >
>> > > > > >
>> > > > > > "anoni" wrote:
>> > > > > >
>> > > > > > > I have two instances on a sql2000 server. I need to change master database to
>> > > > > > > single user mode,
>> > > > > > > so I can restore the master database from a different server.
>> > > > > > >
>> > > > > > > I tried the command line, and queries. none of them worked.
>> > > > > > >
>> > > > > > > Thanks

restore master database

restore master and not working the enterprise manager**

Hi
I started sqlserver 2000 as single user mode
and then tried to restore master db,and
it prompted "restoring master was completed successfully.and shut down the
sqlserver to complete."
but after this message the window of restoring remained
on main window and everything seemed to be halted
and I had to end task the enterprise manager and restarted
the computer but when I decided to run enterprise manager
again and to open the branches following error appearred:
"cannot open user default database login failed .pleas verify
sql server is running and check your sql server registration
properties by right clicking on it."
how can I repair this problem?
do I have to reinstall it again?
any help would be greatly thanked.Looks like you restored from an old master backup, and hence the default
database assigned to the login with which you registered the server in
Enterprise Manager is different from what's stored in the backup.
I suggest you go to command prompt, and login to SQL Server using the same
login with which you registered the server in Enterprise Manager (also
specify the default database as master). Here's how you would do it:
OSQL -S ServerNameHere -U LoginNameHere -P PasswordHere -d master
If you are using Windows authentication, then run the following command
instead:
OSQL -S ServerNameHere -E -d master
Once logged into SQL Server, use sp_defaultdb procedure to change the
default database of your login to an exisiting database. Once this is done
successfully, quit OSQL, go back to Enterprise Manager, and now you should
be able to connect fine. You can find more info and examples on sp_defaultdb
in SQL Server Books Online.
Note that if you create new databases before the backup (from which you
restored now) was taken, then you will have to restore or attach those
databases again.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsayj8ju4hqligo@.msnews.microsoft.com...
Hi
I started sqlserver 2000 as single user mode
and then tried to restore master db,and
it prompted "restoring master was completed successfully.and shut down the
sqlserver to complete."
but after this message the window of restoring remained
on main window and everything seemed to be halted
and I had to end task the enterprise manager and restarted
the computer but when I decided to run enterprise manager
again and to open the branches following error appearred:
"cannot open user default database login failed .pleas verify
sql server is running and check your sql server registration
properties by right clicking on it."
how can I repair this problem?
do I have to reinstall it again?
any help would be greatly thanked.

Restore MASTER

Hi there,
My RAID data drive failed, and I need to restore the MASTER DB. When I
put the server into single user mode (sqlservr -m ) and launch
Enterprise Manager, or Query Analyzer, I can't log into to perform the
restore. It informs me that the server is running in single user mode
(which it is) and that only one admin can be connected.. I have even
attempt to connect using a named SQL user.
How can I overcome this problem?Make sure that the SQLServerAgent service is not running.
"Rowan Prior" wrote:
> Hi there,
> My RAID data drive failed, and I need to restore the MASTER DB. When I
> put the server into single user mode (sqlservr -m ) and launch
> Enterprise Manager, or Query Analyzer, I can't log into to perform the
> restore. It informs me that the server is running in single user mode
> (which it is) and that only one admin can be connected.. I have even
> attempt to connect using a named SQL user.
> How can I overcome this problem?
>

Friday, March 30, 2012

Restored database in loading mode.

Monday, March 26, 2012

Restore to read-only mode

Wednesday, March 21, 2012

Restore single user mode

Restore single user mode

Tuesday, March 20, 2012

Restore runs longer in batch mode

Monday, February 20, 2012

restore master database backup to a new server, single user mode

restore master database backup to a new server, single user mode

restore master database backup to a new server, single user mode

restore master database

restore master database

restore master database

restore master and not working the enterprise manager**

Restore MASTER

restore master database

Blog Archive

About Me