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
Showing posts with label single. Show all posts
Showing posts with label single. Show all posts
Wednesday, March 28, 2012
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
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
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
Monday, March 26, 2012
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?
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?
Friday, March 23, 2012
Restoring SQL 6.5 DBs
Hi
I am trying to restore several databases to a single server due to restricti
ons on hardware. Each server had previously had a database that was named ex
actly the same on each server. Therefore, the new instances will be appended
with an appropriate suffix
to distinguish it from the already existing database on the server.
The problem is actually trying to restore the first database. I have created
a new database and named it accordingly. I have a backup of the database I
want to restore to this server. When I try and restore it by entering the pa
th of the .dat file, the 'r
estore now' option is not highlighted.
Can anyone help point me in the right direction.
Many thanks
KalpeshHi Kalpesh,
Not sure what happend with Enterprise manager. The "Restore Now" button will
be enabled only after you
select the destination database , "Add file" button , and select the
correct backup file to restore and click "close".
(Now the Restore Now button will be enabled)...
ISQL_W
--
Why dont you try executing the below command to restore the database from
ISQL_W .
LOAD Database <dbname> from disk='C:\backup\dbname.DMP' with stats=10
(Do the same step for all the databases)
Note:
1. Change the file name and directory based on your requirement.
2. Ensure that SQL 6.5 is patched with SP5a + SP5a Post update
Thanks
Hari
MCDBA
"Kalpesh" <kalpeshvaghela@.eu.spherion.com> wrote in message
news:F1798555-BB00-435F-A9E2-AE546950EAFF@.microsoft.com...
> Hi
> I am trying to restore several databases to a single server due to
restrictions on hardware. Each server had previously had a database that was
named exactly the same on each server. Therefore, the new instances will be
appended with an appropriate suffix to distinguish it from the already
existing database on the server.
> The problem is actually trying to restore the first database. I have
created a new database and named it accordingly. I have a backup of the
database I want to restore to this server. When I try and restore it by
entering the path of the .dat file, the 'restore now' option is not
highlighted.
> Can anyone help point me in the right direction.
> Many thanks
> Kalpesh
I am trying to restore several databases to a single server due to restricti
ons on hardware. Each server had previously had a database that was named ex
actly the same on each server. Therefore, the new instances will be appended
with an appropriate suffix
to distinguish it from the already existing database on the server.
The problem is actually trying to restore the first database. I have created
a new database and named it accordingly. I have a backup of the database I
want to restore to this server. When I try and restore it by entering the pa
th of the .dat file, the 'r
estore now' option is not highlighted.
Can anyone help point me in the right direction.
Many thanks
KalpeshHi Kalpesh,
Not sure what happend with Enterprise manager. The "Restore Now" button will
be enabled only after you
select the destination database , "Add file" button , and select the
correct backup file to restore and click "close".
(Now the Restore Now button will be enabled)...
ISQL_W
--
Why dont you try executing the below command to restore the database from
ISQL_W .
LOAD Database <dbname> from disk='C:\backup\dbname.DMP' with stats=10
(Do the same step for all the databases)
Note:
1. Change the file name and directory based on your requirement.
2. Ensure that SQL 6.5 is patched with SP5a + SP5a Post update
Thanks
Hari
MCDBA
"Kalpesh" <kalpeshvaghela@.eu.spherion.com> wrote in message
news:F1798555-BB00-435F-A9E2-AE546950EAFF@.microsoft.com...
> Hi
> I am trying to restore several databases to a single server due to
restrictions on hardware. Each server had previously had a database that was
named exactly the same on each server. Therefore, the new instances will be
appended with an appropriate suffix to distinguish it from the already
existing database on the server.
> The problem is actually trying to restore the first database. I have
created a new database and named it accordingly. I have a backup of the
database I want to restore to this server. When I try and restore it by
entering the path of the .dat file, the 'restore now' option is not
highlighted.
> Can anyone help point me in the right direction.
> Many thanks
> Kalpesh
Wednesday, March 21, 2012
Restoring SQL 6.5 DBs
H
I am trying to restore several databases to a single server due to restrictions on hardware. Each server had previously had a database that was named exactly the same on each server. Therefore, the new instances will be appended with an appropriate suffix to distinguish it from the already existing database on the server
The problem is actually trying to restore the first database. I have created a new database and named it accordingly. I have a backup of the database I want to restore to this server. When I try and restore it by entering the path of the .dat file, the 'restore now' option is not highlighted
Can anyone help point me in the right direction
Many thank
KalpeshHi Kalpesh,
Not sure what happend with Enterprise manager. The "Restore Now" button will
be enabled only after you
select the destination database , "Add file" button , and select the
correct backup file to restore and click "close".
(Now the Restore Now button will be enabled)...
ISQL_W
--
Why dont you try executing the below command to restore the database from
ISQL_W .
LOAD Database <dbname> from disk='C:\backup\dbname.DMP' with stats=10
(Do the same step for all the databases)
Note:
1. Change the file name and directory based on your requirement.
2. Ensure that SQL 6.5 is patched with SP5a + SP5a Post update
Thanks
Hari
MCDBA
"Kalpesh" <kalpeshvaghela@.eu.spherion.com> wrote in message
news:F1798555-BB00-435F-A9E2-AE546950EAFF@.microsoft.com...
> Hi
> I am trying to restore several databases to a single server due to
restrictions on hardware. Each server had previously had a database that was
named exactly the same on each server. Therefore, the new instances will be
appended with an appropriate suffix to distinguish it from the already
existing database on the server.
> The problem is actually trying to restore the first database. I have
created a new database and named it accordingly. I have a backup of the
database I want to restore to this server. When I try and restore it by
entering the path of the .dat file, the 'restore now' option is not
highlighted.
> Can anyone help point me in the right direction.
> Many thanks
> Kalpesh
I am trying to restore several databases to a single server due to restrictions on hardware. Each server had previously had a database that was named exactly the same on each server. Therefore, the new instances will be appended with an appropriate suffix to distinguish it from the already existing database on the server
The problem is actually trying to restore the first database. I have created a new database and named it accordingly. I have a backup of the database I want to restore to this server. When I try and restore it by entering the path of the .dat file, the 'restore now' option is not highlighted
Can anyone help point me in the right direction
Many thank
KalpeshHi Kalpesh,
Not sure what happend with Enterprise manager. The "Restore Now" button will
be enabled only after you
select the destination database , "Add file" button , and select the
correct backup file to restore and click "close".
(Now the Restore Now button will be enabled)...
ISQL_W
--
Why dont you try executing the below command to restore the database from
ISQL_W .
LOAD Database <dbname> from disk='C:\backup\dbname.DMP' with stats=10
(Do the same step for all the databases)
Note:
1. Change the file name and directory based on your requirement.
2. Ensure that SQL 6.5 is patched with SP5a + SP5a Post update
Thanks
Hari
MCDBA
"Kalpesh" <kalpeshvaghela@.eu.spherion.com> wrote in message
news:F1798555-BB00-435F-A9E2-AE546950EAFF@.microsoft.com...
> Hi
> I am trying to restore several databases to a single server due to
restrictions on hardware. Each server had previously had a database that was
named exactly the same on each server. Therefore, the new instances will be
appended with an appropriate suffix to distinguish it from the already
existing database on the server.
> The problem is actually trying to restore the first database. I have
created a new database and named it accordingly. I have a backup of the
database I want to restore to this server. When I try and restore it by
entering the path of the .dat file, the 'restore now' option is not
highlighted.
> Can anyone help point me in the right direction.
> Many thanks
> Kalpesh
Restoring SQL 6.5 DBs
Hi
I am trying to restore several databases to a single server due to restrictions on hardware. Each server had previously had a database that was named exactly the same on each server. Therefore, the new instances will be appended with an appropriate suffix
to distinguish it from the already existing database on the server.
The problem is actually trying to restore the first database. I have created a new database and named it accordingly. I have a backup of the database I want to restore to this server. When I try and restore it by entering the path of the .dat file, the 'r
estore now' option is not highlighted.
Can anyone help point me in the right direction.
Many thanks
Kalpesh
Hi Kalpesh,
Not sure what happend with Enterprise manager. The "Restore Now" button will
be enabled only after you
select the destination database , "Add file" button , and select the
correct backup file to restore and click "close".
(Now the Restore Now button will be enabled)...
ISQL_W
Why dont you try executing the below command to restore the database from
ISQL_W .
LOAD Database <dbname> from disk='C:\backup\dbname.DMP' with stats=10
(Do the same step for all the databases)
Note:
1. Change the file name and directory based on your requirement.
2. Ensure that SQL 6.5 is patched with SP5a + SP5a Post update
Thanks
Hari
MCDBA
"Kalpesh" <kalpeshvaghela@.eu.spherion.com> wrote in message
news:F1798555-BB00-435F-A9E2-AE546950EAFF@.microsoft.com...
> Hi
> I am trying to restore several databases to a single server due to
restrictions on hardware. Each server had previously had a database that was
named exactly the same on each server. Therefore, the new instances will be
appended with an appropriate suffix to distinguish it from the already
existing database on the server.
> The problem is actually trying to restore the first database. I have
created a new database and named it accordingly. I have a backup of the
database I want to restore to this server. When I try and restore it by
entering the path of the .dat file, the 'restore now' option is not
highlighted.
> Can anyone help point me in the right direction.
> Many thanks
> Kalpesh
I am trying to restore several databases to a single server due to restrictions on hardware. Each server had previously had a database that was named exactly the same on each server. Therefore, the new instances will be appended with an appropriate suffix
to distinguish it from the already existing database on the server.
The problem is actually trying to restore the first database. I have created a new database and named it accordingly. I have a backup of the database I want to restore to this server. When I try and restore it by entering the path of the .dat file, the 'r
estore now' option is not highlighted.
Can anyone help point me in the right direction.
Many thanks
Kalpesh
Hi Kalpesh,
Not sure what happend with Enterprise manager. The "Restore Now" button will
be enabled only after you
select the destination database , "Add file" button , and select the
correct backup file to restore and click "close".
(Now the Restore Now button will be enabled)...
ISQL_W
Why dont you try executing the below command to restore the database from
ISQL_W .
LOAD Database <dbname> from disk='C:\backup\dbname.DMP' with stats=10
(Do the same step for all the databases)
Note:
1. Change the file name and directory based on your requirement.
2. Ensure that SQL 6.5 is patched with SP5a + SP5a Post update
Thanks
Hari
MCDBA
"Kalpesh" <kalpeshvaghela@.eu.spherion.com> wrote in message
news:F1798555-BB00-435F-A9E2-AE546950EAFF@.microsoft.com...
> Hi
> I am trying to restore several databases to a single server due to
restrictions on hardware. Each server had previously had a database that was
named exactly the same on each server. Therefore, the new instances will be
appended with an appropriate suffix to distinguish it from the already
existing database on the server.
> The problem is actually trying to restore the first database. I have
created a new database and named it accordingly. I have a backup of the
database I want to restore to this server. When I try and restore it by
entering the path of the .dat file, the 'restore now' option is not
highlighted.
> Can anyone help point me in the right direction.
> Many thanks
> Kalpesh
restoring single table
Hi All,
I am taking transaction log backup every six hours. Now i want to restore just one particular table before 2 hrs of now.
How can i go about pls help me as its very urgent.
TIA
RegardsRESTORE
bcp out|||hi thanks for the reply
Can u be more specific as i am very new to this database
TIA
Regards|||You can restore the database to a second instance of the database and then DTS or use BCP to send the table over to your live database. If you are new I would reccomend using the DTS import/export wizard (remember to delete destination rows).
The other way is you could buy a third party tool called lumigent log explorer that would allow you to do this without restoring.
HTH
I am taking transaction log backup every six hours. Now i want to restore just one particular table before 2 hrs of now.
How can i go about pls help me as its very urgent.
TIA
RegardsRESTORE
bcp out|||hi thanks for the reply
Can u be more specific as i am very new to this database
TIA
Regards|||You can restore the database to a second instance of the database and then DTS or use BCP to send the table over to your live database. If you are new I would reccomend using the DTS import/export wizard (remember to delete destination rows).
The other way is you could buy a third party tool called lumigent log explorer that would allow you to do this without restoring.
HTH
Tuesday, March 20, 2012
Restoring multiple transaction logs from a single file
Hi all,
I have a home-grown log shipping setup at work which I'm trying to
modify. At present if the standby server cannot apply the logs before
the live server writes a new set (usually because the db cannot be
locked), it fails and we have to do a full resync between the servers.
I've put in a mechanism whereby the status of the last attempt to apply
the logs to the standby database is recorded. If the primary sees that
the last attempt was successful it does a BACKUP LOG ... WITH INIT.
If it sees the last attempt was not successful it does the same but
WITH NOINIT, which I gather appends the new logs onto the end of the
current lot. This way the transaction logs will queue up every hour
until I can solve whatever the issue between the servers is so they can
be applied, thus saving a full 4 hour resync on a 220GB database.
Problem is this. For example, the secondary can't apply logs because
it can't get exclusive lock on the db. It marks that database as out
of sync in a table. Next time the primary backs up logs it sees the
secondary is not up to date so it appends the logs to the last lot
instead of overwriting them (WITH NOINIT). Next time the secondary
does manage to apply the logs. Now I thought that would mean it gets
ALL the logs and applies them, except I get this error message:
Executed as user: STEWARTMILNE\sqlosp1. The log in this backup set
begins at LSN 1247944000000500300001, which is too late to apply to the
database. An earlier log backup that includes LSN
1247592000000017600001 can be restored. [SQLSTATE 42000] (Error 4305)
RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).
The step failed.
Now I know that means that basically there is a gap between the last
log to be applied and the one we're attempting to apply now. What I
don't understand is how that can be since the last lot of logs to be
successfully applied included more than one BACKUP LOG's worth. How
can I tell RESTORE LOG to apply ALL the log backups appended together
instead of just the first ones in the set?
TIA
Niall
> How
> can I tell RESTORE LOG to apply ALL the log backups appended together
> instead of just the first ones in the set?
You can't. So you have to write some code that uses RESTORE HEADERONLY, and based the result does
several RESTORE LOG commands.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<niallporter@.yahoo.co.uk> wrote in message
news:1158846017.059168.76240@.m73g2000cwd.googlegro ups.com...
> Hi all,
> I have a home-grown log shipping setup at work which I'm trying to
> modify. At present if the standby server cannot apply the logs before
> the live server writes a new set (usually because the db cannot be
> locked), it fails and we have to do a full resync between the servers.
> I've put in a mechanism whereby the status of the last attempt to apply
> the logs to the standby database is recorded. If the primary sees that
> the last attempt was successful it does a BACKUP LOG ... WITH INIT.
> If it sees the last attempt was not successful it does the same but
> WITH NOINIT, which I gather appends the new logs onto the end of the
> current lot. This way the transaction logs will queue up every hour
> until I can solve whatever the issue between the servers is so they can
> be applied, thus saving a full 4 hour resync on a 220GB database.
> Problem is this. For example, the secondary can't apply logs because
> it can't get exclusive lock on the db. It marks that database as out
> of sync in a table. Next time the primary backs up logs it sees the
> secondary is not up to date so it appends the logs to the last lot
> instead of overwriting them (WITH NOINIT). Next time the secondary
> does manage to apply the logs. Now I thought that would mean it gets
> ALL the logs and applies them, except I get this error message:
> Executed as user: STEWARTMILNE\sqlosp1. The log in this backup set
> begins at LSN 1247944000000500300001, which is too late to apply to the
> database. An earlier log backup that includes LSN
> 1247592000000017600001 can be restored. [SQLSTATE 42000] (Error 4305)
> RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).
> The step failed.
> Now I know that means that basically there is a gap between the last
> log to be applied and the one we're attempting to apply now. What I
> don't understand is how that can be since the last lot of logs to be
> successfully applied included more than one BACKUP LOG's worth. How
> can I tell RESTORE LOG to apply ALL the log backups appended together
> instead of just the first ones in the set?
> TIA
> Niall
>
I have a home-grown log shipping setup at work which I'm trying to
modify. At present if the standby server cannot apply the logs before
the live server writes a new set (usually because the db cannot be
locked), it fails and we have to do a full resync between the servers.
I've put in a mechanism whereby the status of the last attempt to apply
the logs to the standby database is recorded. If the primary sees that
the last attempt was successful it does a BACKUP LOG ... WITH INIT.
If it sees the last attempt was not successful it does the same but
WITH NOINIT, which I gather appends the new logs onto the end of the
current lot. This way the transaction logs will queue up every hour
until I can solve whatever the issue between the servers is so they can
be applied, thus saving a full 4 hour resync on a 220GB database.
Problem is this. For example, the secondary can't apply logs because
it can't get exclusive lock on the db. It marks that database as out
of sync in a table. Next time the primary backs up logs it sees the
secondary is not up to date so it appends the logs to the last lot
instead of overwriting them (WITH NOINIT). Next time the secondary
does manage to apply the logs. Now I thought that would mean it gets
ALL the logs and applies them, except I get this error message:
Executed as user: STEWARTMILNE\sqlosp1. The log in this backup set
begins at LSN 1247944000000500300001, which is too late to apply to the
database. An earlier log backup that includes LSN
1247592000000017600001 can be restored. [SQLSTATE 42000] (Error 4305)
RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).
The step failed.
Now I know that means that basically there is a gap between the last
log to be applied and the one we're attempting to apply now. What I
don't understand is how that can be since the last lot of logs to be
successfully applied included more than one BACKUP LOG's worth. How
can I tell RESTORE LOG to apply ALL the log backups appended together
instead of just the first ones in the set?
TIA
Niall
> How
> can I tell RESTORE LOG to apply ALL the log backups appended together
> instead of just the first ones in the set?
You can't. So you have to write some code that uses RESTORE HEADERONLY, and based the result does
several RESTORE LOG commands.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<niallporter@.yahoo.co.uk> wrote in message
news:1158846017.059168.76240@.m73g2000cwd.googlegro ups.com...
> Hi all,
> I have a home-grown log shipping setup at work which I'm trying to
> modify. At present if the standby server cannot apply the logs before
> the live server writes a new set (usually because the db cannot be
> locked), it fails and we have to do a full resync between the servers.
> I've put in a mechanism whereby the status of the last attempt to apply
> the logs to the standby database is recorded. If the primary sees that
> the last attempt was successful it does a BACKUP LOG ... WITH INIT.
> If it sees the last attempt was not successful it does the same but
> WITH NOINIT, which I gather appends the new logs onto the end of the
> current lot. This way the transaction logs will queue up every hour
> until I can solve whatever the issue between the servers is so they can
> be applied, thus saving a full 4 hour resync on a 220GB database.
> Problem is this. For example, the secondary can't apply logs because
> it can't get exclusive lock on the db. It marks that database as out
> of sync in a table. Next time the primary backs up logs it sees the
> secondary is not up to date so it appends the logs to the last lot
> instead of overwriting them (WITH NOINIT). Next time the secondary
> does manage to apply the logs. Now I thought that would mean it gets
> ALL the logs and applies them, except I get this error message:
> Executed as user: STEWARTMILNE\sqlosp1. The log in this backup set
> begins at LSN 1247944000000500300001, which is too late to apply to the
> database. An earlier log backup that includes LSN
> 1247592000000017600001 can be restored. [SQLSTATE 42000] (Error 4305)
> RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).
> The step failed.
> Now I know that means that basically there is a gap between the last
> log to be applied and the one we're attempting to apply now. What I
> don't understand is how that can be since the last lot of logs to be
> successfully applied included more than one BACKUP LOG's worth. How
> can I tell RESTORE LOG to apply ALL the log backups appended together
> instead of just the first ones in the set?
> TIA
> Niall
>
Restoring multiple transaction logs from a single file
Hi all,
I have a home-grown log shipping setup at work which I'm trying to
modify. At present if the standby server cannot apply the logs before
the live server writes a new set (usually because the db cannot be
locked), it fails and we have to do a full resync between the servers.
I've put in a mechanism whereby the status of the last attempt to apply
the logs to the standby database is recorded. If the primary sees that
the last attempt was successful it does a BACKUP LOG ... WITH INIT.
If it sees the last attempt was not successful it does the same but
WITH NOINIT, which I gather appends the new logs onto the end of the
current lot. This way the transaction logs will queue up every hour
until I can solve whatever the issue between the servers is so they can
be applied, thus saving a full 4 hour resync on a 220GB database.
Problem is this. For example, the secondary can't apply logs because
it can't get exclusive lock on the db. It marks that database as out
of sync in a table. Next time the primary backs up logs it sees the
secondary is not up to date so it appends the logs to the last lot
instead of overwriting them (WITH NOINIT). Next time the secondary
does manage to apply the logs. Now I thought that would mean it gets
ALL the logs and applies them, except I get this error message:
Executed as user: STEWARTMILNE\sqlosp1. The log in this backup set
begins at LSN 1247944000000500300001, which is too late to apply to the
database. An earlier log backup that includes LSN
1247592000000017600001 can be restored. [SQLSTATE 42000] (Error 4305)
RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).
The step failed.
Now I know that means that basically there is a gap between the last
log to be applied and the one we're attempting to apply now. What I
don't understand is how that can be since the last lot of logs to be
successfully applied included more than one BACKUP LOG's worth. How
can I tell RESTORE LOG to apply ALL the log backups appended together
instead of just the first ones in the set?
TIA
Niall> How
> can I tell RESTORE LOG to apply ALL the log backups appended together
> instead of just the first ones in the set?
You can't. So you have to write some code that uses RESTORE HEADERONLY, and
based the result does
several RESTORE LOG commands.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<niallporter@.yahoo.co.uk> wrote in message
news:1158846017.059168.76240@.m73g2000cwd.googlegroups.com...
> Hi all,
> I have a home-grown log shipping setup at work which I'm trying to
> modify. At present if the standby server cannot apply the logs before
> the live server writes a new set (usually because the db cannot be
> locked), it fails and we have to do a full resync between the servers.
> I've put in a mechanism whereby the status of the last attempt to apply
> the logs to the standby database is recorded. If the primary sees that
> the last attempt was successful it does a BACKUP LOG ... WITH INIT.
> If it sees the last attempt was not successful it does the same but
> WITH NOINIT, which I gather appends the new logs onto the end of the
> current lot. This way the transaction logs will queue up every hour
> until I can solve whatever the issue between the servers is so they can
> be applied, thus saving a full 4 hour resync on a 220GB database.
> Problem is this. For example, the secondary can't apply logs because
> it can't get exclusive lock on the db. It marks that database as out
> of sync in a table. Next time the primary backs up logs it sees the
> secondary is not up to date so it appends the logs to the last lot
> instead of overwriting them (WITH NOINIT). Next time the secondary
> does manage to apply the logs. Now I thought that would mean it gets
> ALL the logs and applies them, except I get this error message:
> Executed as user: STEWARTMILNE\sqlosp1. The log in this backup set
> begins at LSN 1247944000000500300001, which is too late to apply to the
> database. An earlier log backup that includes LSN
> 1247592000000017600001 can be restored. [SQLSTATE 42000] (Error 4305)
> RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).
> The step failed.
> Now I know that means that basically there is a gap between the last
> log to be applied and the one we're attempting to apply now. What I
> don't understand is how that can be since the last lot of logs to be
> successfully applied included more than one BACKUP LOG's worth. How
> can I tell RESTORE LOG to apply ALL the log backups appended together
> instead of just the first ones in the set?
> TIA
> Niall
>
I have a home-grown log shipping setup at work which I'm trying to
modify. At present if the standby server cannot apply the logs before
the live server writes a new set (usually because the db cannot be
locked), it fails and we have to do a full resync between the servers.
I've put in a mechanism whereby the status of the last attempt to apply
the logs to the standby database is recorded. If the primary sees that
the last attempt was successful it does a BACKUP LOG ... WITH INIT.
If it sees the last attempt was not successful it does the same but
WITH NOINIT, which I gather appends the new logs onto the end of the
current lot. This way the transaction logs will queue up every hour
until I can solve whatever the issue between the servers is so they can
be applied, thus saving a full 4 hour resync on a 220GB database.
Problem is this. For example, the secondary can't apply logs because
it can't get exclusive lock on the db. It marks that database as out
of sync in a table. Next time the primary backs up logs it sees the
secondary is not up to date so it appends the logs to the last lot
instead of overwriting them (WITH NOINIT). Next time the secondary
does manage to apply the logs. Now I thought that would mean it gets
ALL the logs and applies them, except I get this error message:
Executed as user: STEWARTMILNE\sqlosp1. The log in this backup set
begins at LSN 1247944000000500300001, which is too late to apply to the
database. An earlier log backup that includes LSN
1247592000000017600001 can be restored. [SQLSTATE 42000] (Error 4305)
RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).
The step failed.
Now I know that means that basically there is a gap between the last
log to be applied and the one we're attempting to apply now. What I
don't understand is how that can be since the last lot of logs to be
successfully applied included more than one BACKUP LOG's worth. How
can I tell RESTORE LOG to apply ALL the log backups appended together
instead of just the first ones in the set?
TIA
Niall> How
> can I tell RESTORE LOG to apply ALL the log backups appended together
> instead of just the first ones in the set?
You can't. So you have to write some code that uses RESTORE HEADERONLY, and
based the result does
several RESTORE LOG commands.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<niallporter@.yahoo.co.uk> wrote in message
news:1158846017.059168.76240@.m73g2000cwd.googlegroups.com...
> Hi all,
> I have a home-grown log shipping setup at work which I'm trying to
> modify. At present if the standby server cannot apply the logs before
> the live server writes a new set (usually because the db cannot be
> locked), it fails and we have to do a full resync between the servers.
> I've put in a mechanism whereby the status of the last attempt to apply
> the logs to the standby database is recorded. If the primary sees that
> the last attempt was successful it does a BACKUP LOG ... WITH INIT.
> If it sees the last attempt was not successful it does the same but
> WITH NOINIT, which I gather appends the new logs onto the end of the
> current lot. This way the transaction logs will queue up every hour
> until I can solve whatever the issue between the servers is so they can
> be applied, thus saving a full 4 hour resync on a 220GB database.
> Problem is this. For example, the secondary can't apply logs because
> it can't get exclusive lock on the db. It marks that database as out
> of sync in a table. Next time the primary backs up logs it sees the
> secondary is not up to date so it appends the logs to the last lot
> instead of overwriting them (WITH NOINIT). Next time the secondary
> does manage to apply the logs. Now I thought that would mean it gets
> ALL the logs and applies them, except I get this error message:
> Executed as user: STEWARTMILNE\sqlosp1. The log in this backup set
> begins at LSN 1247944000000500300001, which is too late to apply to the
> database. An earlier log backup that includes LSN
> 1247592000000017600001 can be restored. [SQLSTATE 42000] (Error 4305)
> RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).
> The step failed.
> Now I know that means that basically there is a gap between the last
> log to be applied and the one we're attempting to apply now. What I
> don't understand is how that can be since the last lot of logs to be
> successfully applied included more than one BACKUP LOG's worth. How
> can I tell RESTORE LOG to apply ALL the log backups appended together
> instead of just the first ones in the set?
> TIA
> Niall
>
Restoring multiple transaction logs from a single file
Hi all,
I have a home-grown log shipping setup at work which I'm trying to
modify. At present if the standby server cannot apply the logs before
the live server writes a new set (usually because the db cannot be
locked), it fails and we have to do a full resync between the servers.
I've put in a mechanism whereby the status of the last attempt to apply
the logs to the standby database is recorded. If the primary sees that
the last attempt was successful it does a BACKUP LOG ... WITH INIT.
If it sees the last attempt was not successful it does the same but
WITH NOINIT, which I gather appends the new logs onto the end of the
current lot. This way the transaction logs will queue up every hour
until I can solve whatever the issue between the servers is so they can
be applied, thus saving a full 4 hour resync on a 220GB database.
Problem is this. For example, the secondary can't apply logs because
it can't get exclusive lock on the db. It marks that database as out
of sync in a table. Next time the primary backs up logs it sees the
secondary is not up to date so it appends the logs to the last lot
instead of overwriting them (WITH NOINIT). Next time the secondary
does manage to apply the logs. Now I thought that would mean it gets
ALL the logs and applies them, except I get this error message:
Executed as user: STEWARTMILNE\sqlosp1. The log in this backup set
begins at LSN 1247944000000500300001, which is too late to apply to the
database. An earlier log backup that includes LSN
1247592000000017600001 can be restored. [SQLSTATE 42000] (Error 4305)
RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).
The step failed.
Now I know that means that basically there is a gap between the last
log to be applied and the one we're attempting to apply now. What I
don't understand is how that can be since the last lot of logs to be
successfully applied included more than one BACKUP LOG's worth. How
can I tell RESTORE LOG to apply ALL the log backups appended together
instead of just the first ones in the set?
TIA
Niall> How
> can I tell RESTORE LOG to apply ALL the log backups appended together
> instead of just the first ones in the set?
You can't. So you have to write some code that uses RESTORE HEADERONLY, and based the result does
several RESTORE LOG commands.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<niallporter@.yahoo.co.uk> wrote in message
news:1158846017.059168.76240@.m73g2000cwd.googlegroups.com...
> Hi all,
> I have a home-grown log shipping setup at work which I'm trying to
> modify. At present if the standby server cannot apply the logs before
> the live server writes a new set (usually because the db cannot be
> locked), it fails and we have to do a full resync between the servers.
> I've put in a mechanism whereby the status of the last attempt to apply
> the logs to the standby database is recorded. If the primary sees that
> the last attempt was successful it does a BACKUP LOG ... WITH INIT.
> If it sees the last attempt was not successful it does the same but
> WITH NOINIT, which I gather appends the new logs onto the end of the
> current lot. This way the transaction logs will queue up every hour
> until I can solve whatever the issue between the servers is so they can
> be applied, thus saving a full 4 hour resync on a 220GB database.
> Problem is this. For example, the secondary can't apply logs because
> it can't get exclusive lock on the db. It marks that database as out
> of sync in a table. Next time the primary backs up logs it sees the
> secondary is not up to date so it appends the logs to the last lot
> instead of overwriting them (WITH NOINIT). Next time the secondary
> does manage to apply the logs. Now I thought that would mean it gets
> ALL the logs and applies them, except I get this error message:
> Executed as user: STEWARTMILNE\sqlosp1. The log in this backup set
> begins at LSN 1247944000000500300001, which is too late to apply to the
> database. An earlier log backup that includes LSN
> 1247592000000017600001 can be restored. [SQLSTATE 42000] (Error 4305)
> RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).
> The step failed.
> Now I know that means that basically there is a gap between the last
> log to be applied and the one we're attempting to apply now. What I
> don't understand is how that can be since the last lot of logs to be
> successfully applied included more than one BACKUP LOG's worth. How
> can I tell RESTORE LOG to apply ALL the log backups appended together
> instead of just the first ones in the set?
> TIA
> Niall
>
I have a home-grown log shipping setup at work which I'm trying to
modify. At present if the standby server cannot apply the logs before
the live server writes a new set (usually because the db cannot be
locked), it fails and we have to do a full resync between the servers.
I've put in a mechanism whereby the status of the last attempt to apply
the logs to the standby database is recorded. If the primary sees that
the last attempt was successful it does a BACKUP LOG ... WITH INIT.
If it sees the last attempt was not successful it does the same but
WITH NOINIT, which I gather appends the new logs onto the end of the
current lot. This way the transaction logs will queue up every hour
until I can solve whatever the issue between the servers is so they can
be applied, thus saving a full 4 hour resync on a 220GB database.
Problem is this. For example, the secondary can't apply logs because
it can't get exclusive lock on the db. It marks that database as out
of sync in a table. Next time the primary backs up logs it sees the
secondary is not up to date so it appends the logs to the last lot
instead of overwriting them (WITH NOINIT). Next time the secondary
does manage to apply the logs. Now I thought that would mean it gets
ALL the logs and applies them, except I get this error message:
Executed as user: STEWARTMILNE\sqlosp1. The log in this backup set
begins at LSN 1247944000000500300001, which is too late to apply to the
database. An earlier log backup that includes LSN
1247592000000017600001 can be restored. [SQLSTATE 42000] (Error 4305)
RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).
The step failed.
Now I know that means that basically there is a gap between the last
log to be applied and the one we're attempting to apply now. What I
don't understand is how that can be since the last lot of logs to be
successfully applied included more than one BACKUP LOG's worth. How
can I tell RESTORE LOG to apply ALL the log backups appended together
instead of just the first ones in the set?
TIA
Niall> How
> can I tell RESTORE LOG to apply ALL the log backups appended together
> instead of just the first ones in the set?
You can't. So you have to write some code that uses RESTORE HEADERONLY, and based the result does
several RESTORE LOG commands.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<niallporter@.yahoo.co.uk> wrote in message
news:1158846017.059168.76240@.m73g2000cwd.googlegroups.com...
> Hi all,
> I have a home-grown log shipping setup at work which I'm trying to
> modify. At present if the standby server cannot apply the logs before
> the live server writes a new set (usually because the db cannot be
> locked), it fails and we have to do a full resync between the servers.
> I've put in a mechanism whereby the status of the last attempt to apply
> the logs to the standby database is recorded. If the primary sees that
> the last attempt was successful it does a BACKUP LOG ... WITH INIT.
> If it sees the last attempt was not successful it does the same but
> WITH NOINIT, which I gather appends the new logs onto the end of the
> current lot. This way the transaction logs will queue up every hour
> until I can solve whatever the issue between the servers is so they can
> be applied, thus saving a full 4 hour resync on a 220GB database.
> Problem is this. For example, the secondary can't apply logs because
> it can't get exclusive lock on the db. It marks that database as out
> of sync in a table. Next time the primary backs up logs it sees the
> secondary is not up to date so it appends the logs to the last lot
> instead of overwriting them (WITH NOINIT). Next time the secondary
> does manage to apply the logs. Now I thought that would mean it gets
> ALL the logs and applies them, except I get this error message:
> Executed as user: STEWARTMILNE\sqlosp1. The log in this backup set
> begins at LSN 1247944000000500300001, which is too late to apply to the
> database. An earlier log backup that includes LSN
> 1247592000000017600001 can be restored. [SQLSTATE 42000] (Error 4305)
> RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).
> The step failed.
> Now I know that means that basically there is a gap between the last
> log to be applied and the one we're attempting to apply now. What I
> don't understand is how that can be since the last lot of logs to be
> successfully applied included more than one BACKUP LOG's worth. How
> can I tell RESTORE LOG to apply ALL the log backups appended together
> instead of just the first ones in the set?
> TIA
> Niall
>
Monday, March 12, 2012
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.. .
>
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...
>
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
>>
>>
>
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
>>
>>
>
restoring master database - single user mode
I am trying to restore a backup of a master database from one server to a
new server.
I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a command
prompt to start it in single user mode. It finished with "SQL Global
counter collection task is created." It appears to stop at this point -
there is no prompt.
While in single-user mode, I open Query Analyzer and attempt to log in both
with Windows Authentication and with SQL Server authentication, using the sa
login.
In both cases, I get an error "Login failed for user '(login)'. Reason:
Server is in single user mode. Only one administrator can connect at this
time."
Thanks for any suggestions.
-BillThere might be an EM connection that is already open.
"bill" <belgie@.datamti.com> wrote in message
news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
> I am trying to restore a backup of a master database from one server to a
> new server.
> I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a
command
> prompt to start it in single user mode. It finished with "SQL Global
> counter collection task is created." It appears to stop at this point -
> there is no prompt.
> While in single-user mode, I open Query Analyzer and attempt to log in
both
> with Windows Authentication and with SQL Server authentication, using the
sa
> login.
> In both cases, I get an error "Login failed for user '(login)'. Reason:
> Server is in single user mode. Only one administrator can connect at this
> time."
> Thanks for any suggestions.
> -Bill
>|||Good thought, but that's not it.
Thanks!
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...
> There might be an EM connection that is already open.
> "bill" <belgie@.datamti.com> wrote in message
> news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
a[vbcol=seagreen]
> command
> both
the[vbcol=seagreen]
> sa
this[vbcol=seagreen]
>|||Agent? Other services or users? You need to find who is connected...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bill" <belgie@.datamti.com> wrote in message news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...
> Good thought, but that's not it.
> Thanks!
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...
> a
> the
> this
>|||How can I do this? I can't connect to SQL Server to see the connections.
Thanks
Bill
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oj%23tuBCuEHA.2956@.TK2MSFTNGP12.phx.gbl...
> Agent? Other services or users? You need to find who is connected...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bill" <belgie@.datamti.com> wrote in message
news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...
to[vbcol=seagreen]
Global[vbcol=seagreen]
point -[vbcol=seagreen]
in[vbcol=seagreen]
using[vbcol=seagreen]
Reason:[vbcol=seagreen]
>|||Start stopping SQL server Agent, and other possible services that might use
SQL server. You might
need to get physical access to the machine, or terminal services, so you can
work locally on the
machine stopping services and perhaps even disabling network access (which i
sn't doable over
terminal services, of course).
Another thing you can try is to login using OSQL.EXE instead of Query Analyz
er. QA *might* try to
open a connection for Object Browser and that *might* get connected before y
our query window.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bill" <belgie@.datamti.com> wrote in message news:emBg4NEuEHA.348@.tk2msftngp13.phx.gbl...[vb
col=seagreen]
> How can I do this? I can't connect to SQL Server to see the connections.
> Thanks
> Bill
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:Oj%23tuBCuEHA.2956@.TK2MSFTNGP12.phx.gbl...
> news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...
> to
> Global
> point -
> in
> using
> Reason:
>[/vbcol]
new server.
I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a command
prompt to start it in single user mode. It finished with "SQL Global
counter collection task is created." It appears to stop at this point -
there is no prompt.
While in single-user mode, I open Query Analyzer and attempt to log in both
with Windows Authentication and with SQL Server authentication, using the sa
login.
In both cases, I get an error "Login failed for user '(login)'. Reason:
Server is in single user mode. Only one administrator can connect at this
time."
Thanks for any suggestions.
-BillThere might be an EM connection that is already open.
"bill" <belgie@.datamti.com> wrote in message
news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
> I am trying to restore a backup of a master database from one server to a
> new server.
> I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a
command
> prompt to start it in single user mode. It finished with "SQL Global
> counter collection task is created." It appears to stop at this point -
> there is no prompt.
> While in single-user mode, I open Query Analyzer and attempt to log in
both
> with Windows Authentication and with SQL Server authentication, using the
sa
> login.
> In both cases, I get an error "Login failed for user '(login)'. Reason:
> Server is in single user mode. Only one administrator can connect at this
> time."
> Thanks for any suggestions.
> -Bill
>|||Good thought, but that's not it.
Thanks!
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...
> There might be an EM connection that is already open.
> "bill" <belgie@.datamti.com> wrote in message
> news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
a[vbcol=seagreen]
> command
> both
the[vbcol=seagreen]
> sa
this[vbcol=seagreen]
>|||Agent? Other services or users? You need to find who is connected...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bill" <belgie@.datamti.com> wrote in message news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...
> Good thought, but that's not it.
> Thanks!
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...
> a
> the
> this
>|||How can I do this? I can't connect to SQL Server to see the connections.
Thanks
Bill
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oj%23tuBCuEHA.2956@.TK2MSFTNGP12.phx.gbl...
> Agent? Other services or users? You need to find who is connected...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bill" <belgie@.datamti.com> wrote in message
news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...
to[vbcol=seagreen]
Global[vbcol=seagreen]
point -[vbcol=seagreen]
in[vbcol=seagreen]
using[vbcol=seagreen]
Reason:[vbcol=seagreen]
>|||Start stopping SQL server Agent, and other possible services that might use
SQL server. You might
need to get physical access to the machine, or terminal services, so you can
work locally on the
machine stopping services and perhaps even disabling network access (which i
sn't doable over
terminal services, of course).
Another thing you can try is to login using OSQL.EXE instead of Query Analyz
er. QA *might* try to
open a connection for Object Browser and that *might* get connected before y
our query window.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bill" <belgie@.datamti.com> wrote in message news:emBg4NEuEHA.348@.tk2msftngp13.phx.gbl...[vb
col=seagreen]
> How can I do this? I can't connect to SQL Server to see the connections.
> Thanks
> Bill
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:Oj%23tuBCuEHA.2956@.TK2MSFTNGP12.phx.gbl...
> news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...
> to
> Global
> point -
> in
> using
> Reason:
>[/vbcol]
restoring master database - single user mode
I am trying to restore a backup of a master database from one server to a
new server.
I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a command
prompt to start it in single user mode. It finished with "SQL Global
counter collection task is created." It appears to stop at this point -
there is no prompt.
While in single-user mode, I open Query Analyzer and attempt to log in both
with Windows Authentication and with SQL Server authentication, using the sa
login.
In both cases, I get an error "Login failed for user '(login)'. Reason:
Server is in single user mode. Only one administrator can connect at this
time."
Thanks for any suggestions.
-Bill
There might be an EM connection that is already open.
"bill" <belgie@.datamti.com> wrote in message
news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
> I am trying to restore a backup of a master database from one server to a
> new server.
> I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a
command
> prompt to start it in single user mode. It finished with "SQL Global
> counter collection task is created." It appears to stop at this point -
> there is no prompt.
> While in single-user mode, I open Query Analyzer and attempt to log in
both
> with Windows Authentication and with SQL Server authentication, using the
sa
> login.
> In both cases, I get an error "Login failed for user '(login)'. Reason:
> Server is in single user mode. Only one administrator can connect at this
> time."
> Thanks for any suggestions.
> -Bill
>
|||Good thought, but that's not it.
Thanks!
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> There might be an EM connection that is already open.
> "bill" <belgie@.datamti.com> wrote in message
> news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
a[vbcol=seagreen]
> command
> both
the[vbcol=seagreen]
> sa
this
>
|||Agent? Other services or users? You need to find who is connected...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bill" <belgie@.datamti.com> wrote in message news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...
> Good thought, but that's not it.
> Thanks!
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...
> a
> the
> this
>
|||How can I do this? I can't connect to SQL Server to see the connections.
Thanks
Bill
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oj%23tuBCuEHA.2956@.TK2MSFTNGP12.phx.gbl...
> Agent? Other services or users? You need to find who is connected...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bill" <belgie@.datamti.com> wrote in message
news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
to[vbcol=seagreen]
Global[vbcol=seagreen]
point -[vbcol=seagreen]
in[vbcol=seagreen]
using[vbcol=seagreen]
Reason:
>
|||Start stopping SQL server Agent, and other possible services that might use SQL server. You might
need to get physical access to the machine, or terminal services, so you can work locally on the
machine stopping services and perhaps even disabling network access (which isn't doable over
terminal services, of course).
Another thing you can try is to login using OSQL.EXE instead of Query Analyzer. QA *might* try to
open a connection for Object Browser and that *might* get connected before your query window.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bill" <belgie@.datamti.com> wrote in message news:emBg4NEuEHA.348@.tk2msftngp13.phx.gbl...
> How can I do this? I can't connect to SQL Server to see the connections.
> Thanks
> Bill
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Oj%23tuBCuEHA.2956@.TK2MSFTNGP12.phx.gbl...
> news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...
> to
> Global
> point -
> in
> using
> Reason:
>
new server.
I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a command
prompt to start it in single user mode. It finished with "SQL Global
counter collection task is created." It appears to stop at this point -
there is no prompt.
While in single-user mode, I open Query Analyzer and attempt to log in both
with Windows Authentication and with SQL Server authentication, using the sa
login.
In both cases, I get an error "Login failed for user '(login)'. Reason:
Server is in single user mode. Only one administrator can connect at this
time."
Thanks for any suggestions.
-Bill
There might be an EM connection that is already open.
"bill" <belgie@.datamti.com> wrote in message
news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
> I am trying to restore a backup of a master database from one server to a
> new server.
> I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a
command
> prompt to start it in single user mode. It finished with "SQL Global
> counter collection task is created." It appears to stop at this point -
> there is no prompt.
> While in single-user mode, I open Query Analyzer and attempt to log in
both
> with Windows Authentication and with SQL Server authentication, using the
sa
> login.
> In both cases, I get an error "Login failed for user '(login)'. Reason:
> Server is in single user mode. Only one administrator can connect at this
> time."
> Thanks for any suggestions.
> -Bill
>
|||Good thought, but that's not it.
Thanks!
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> There might be an EM connection that is already open.
> "bill" <belgie@.datamti.com> wrote in message
> news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
a[vbcol=seagreen]
> command
> both
the[vbcol=seagreen]
> sa
this
>
|||Agent? Other services or users? You need to find who is connected...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bill" <belgie@.datamti.com> wrote in message news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...
> Good thought, but that's not it.
> Thanks!
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...
> a
> the
> this
>
|||How can I do this? I can't connect to SQL Server to see the connections.
Thanks
Bill
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oj%23tuBCuEHA.2956@.TK2MSFTNGP12.phx.gbl...
> Agent? Other services or users? You need to find who is connected...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bill" <belgie@.datamti.com> wrote in message
news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
to[vbcol=seagreen]
Global[vbcol=seagreen]
point -[vbcol=seagreen]
in[vbcol=seagreen]
using[vbcol=seagreen]
Reason:
>
|||Start stopping SQL server Agent, and other possible services that might use SQL server. You might
need to get physical access to the machine, or terminal services, so you can work locally on the
machine stopping services and perhaps even disabling network access (which isn't doable over
terminal services, of course).
Another thing you can try is to login using OSQL.EXE instead of Query Analyzer. QA *might* try to
open a connection for Object Browser and that *might* get connected before your query window.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bill" <belgie@.datamti.com> wrote in message news:emBg4NEuEHA.348@.tk2msftngp13.phx.gbl...
> How can I do this? I can't connect to SQL Server to see the connections.
> Thanks
> Bill
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Oj%23tuBCuEHA.2956@.TK2MSFTNGP12.phx.gbl...
> news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...
> to
> Global
> point -
> in
> using
> Reason:
>
restoring master database - single user mode
I am trying to restore a backup of a master database from one server to a
new server.
I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a command
prompt to start it in single user mode. It finished with "SQL Global
counter collection task is created." It appears to stop at this point -
there is no prompt.
While in single-user mode, I open Query Analyzer and attempt to log in both
with Windows Authentication and with SQL Server authentication, using the sa
login.
In both cases, I get an error "Login failed for user '(login)'. Reason:
Server is in single user mode. Only one administrator can connect at this
time."
Thanks for any suggestions.
-BillThere might be an EM connection that is already open.
"bill" <belgie@.datamti.com> wrote in message
news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
> I am trying to restore a backup of a master database from one server to a
> new server.
> I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a
command
> prompt to start it in single user mode. It finished with "SQL Global
> counter collection task is created." It appears to stop at this point -
> there is no prompt.
> While in single-user mode, I open Query Analyzer and attempt to log in
both
> with Windows Authentication and with SQL Server authentication, using the
sa
> login.
> In both cases, I get an error "Login failed for user '(login)'. Reason:
> Server is in single user mode. Only one administrator can connect at this
> time."
> Thanks for any suggestions.
> -Bill
>|||Good thought, but that's not it.
Thanks!
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...
> There might be an EM connection that is already open.
> "bill" <belgie@.datamti.com> wrote in message
> news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
> > I am trying to restore a backup of a master database from one server to
a
> > new server.
> >
> > I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a
> command
> > prompt to start it in single user mode. It finished with "SQL Global
> > counter collection task is created." It appears to stop at this point -
> > there is no prompt.
> >
> > While in single-user mode, I open Query Analyzer and attempt to log in
> both
> > with Windows Authentication and with SQL Server authentication, using
the
> sa
> > login.
> >
> > In both cases, I get an error "Login failed for user '(login)'. Reason:
> > Server is in single user mode. Only one administrator can connect at
this
> > time."
> >
> > Thanks for any suggestions.
> > -Bill
> >
> >
>|||Agent? Other services or users? You need to find who is connected...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bill" <belgie@.datamti.com> wrote in message news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...
> Good thought, but that's not it.
> Thanks!
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...
> > There might be an EM connection that is already open.
> >
> > "bill" <belgie@.datamti.com> wrote in message
> > news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
> > > I am trying to restore a backup of a master database from one server to
> a
> > > new server.
> > >
> > > I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a
> > command
> > > prompt to start it in single user mode. It finished with "SQL Global
> > > counter collection task is created." It appears to stop at this point -
> > > there is no prompt.
> > >
> > > While in single-user mode, I open Query Analyzer and attempt to log in
> > both
> > > with Windows Authentication and with SQL Server authentication, using
> the
> > sa
> > > login.
> > >
> > > In both cases, I get an error "Login failed for user '(login)'. Reason:
> > > Server is in single user mode. Only one administrator can connect at
> this
> > > time."
> > >
> > > Thanks for any suggestions.
> > > -Bill
> > >
> > >
> >
> >
>|||How can I do this? I can't connect to SQL Server to see the connections.
Thanks
Bill
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oj%23tuBCuEHA.2956@.TK2MSFTNGP12.phx.gbl...
> Agent? Other services or users? You need to find who is connected...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bill" <belgie@.datamti.com> wrote in message
news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...
> > Good thought, but that's not it.
> >
> > Thanks!
> >
> > "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> > news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...
> > > There might be an EM connection that is already open.
> > >
> > > "bill" <belgie@.datamti.com> wrote in message
> > > news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
> > > > I am trying to restore a backup of a master database from one server
to
> > a
> > > > new server.
> > > >
> > > > I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a
> > > command
> > > > prompt to start it in single user mode. It finished with "SQL
Global
> > > > counter collection task is created." It appears to stop at this
point -
> > > > there is no prompt.
> > > >
> > > > While in single-user mode, I open Query Analyzer and attempt to log
in
> > > both
> > > > with Windows Authentication and with SQL Server authentication,
using
> > the
> > > sa
> > > > login.
> > > >
> > > > In both cases, I get an error "Login failed for user '(login)'.
Reason:
> > > > Server is in single user mode. Only one administrator can connect at
> > this
> > > > time."
> > > >
> > > > Thanks for any suggestions.
> > > > -Bill
> > > >
> > > >
> > >
> > >
> >
> >
>|||Start stopping SQL server Agent, and other possible services that might use SQL server. You might
need to get physical access to the machine, or terminal services, so you can work locally on the
machine stopping services and perhaps even disabling network access (which isn't doable over
terminal services, of course).
Another thing you can try is to login using OSQL.EXE instead of Query Analyzer. QA *might* try to
open a connection for Object Browser and that *might* get connected before your query window.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bill" <belgie@.datamti.com> wrote in message news:emBg4NEuEHA.348@.tk2msftngp13.phx.gbl...
> How can I do this? I can't connect to SQL Server to see the connections.
> Thanks
> Bill
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Oj%23tuBCuEHA.2956@.TK2MSFTNGP12.phx.gbl...
>> Agent? Other services or users? You need to find who is connected...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "bill" <belgie@.datamti.com> wrote in message
> news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...
>> > Good thought, but that's not it.
>> >
>> > Thanks!
>> >
>> > "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
>> > news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...
>> > > There might be an EM connection that is already open.
>> > >
>> > > "bill" <belgie@.datamti.com> wrote in message
>> > > news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
>> > > > I am trying to restore a backup of a master database from one server
> to
>> > a
>> > > > new server.
>> > > >
>> > > > I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a
>> > > command
>> > > > prompt to start it in single user mode. It finished with "SQL
> Global
>> > > > counter collection task is created." It appears to stop at this
> point -
>> > > > there is no prompt.
>> > > >
>> > > > While in single-user mode, I open Query Analyzer and attempt to log
> in
>> > > both
>> > > > with Windows Authentication and with SQL Server authentication,
> using
>> > the
>> > > sa
>> > > > login.
>> > > >
>> > > > In both cases, I get an error "Login failed for user '(login)'.
> Reason:
>> > > > Server is in single user mode. Only one administrator can connect at
>> > this
>> > > > time."
>> > > >
>> > > > Thanks for any suggestions.
>> > > > -Bill
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
>
new server.
I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a command
prompt to start it in single user mode. It finished with "SQL Global
counter collection task is created." It appears to stop at this point -
there is no prompt.
While in single-user mode, I open Query Analyzer and attempt to log in both
with Windows Authentication and with SQL Server authentication, using the sa
login.
In both cases, I get an error "Login failed for user '(login)'. Reason:
Server is in single user mode. Only one administrator can connect at this
time."
Thanks for any suggestions.
-BillThere might be an EM connection that is already open.
"bill" <belgie@.datamti.com> wrote in message
news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
> I am trying to restore a backup of a master database from one server to a
> new server.
> I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a
command
> prompt to start it in single user mode. It finished with "SQL Global
> counter collection task is created." It appears to stop at this point -
> there is no prompt.
> While in single-user mode, I open Query Analyzer and attempt to log in
both
> with Windows Authentication and with SQL Server authentication, using the
sa
> login.
> In both cases, I get an error "Login failed for user '(login)'. Reason:
> Server is in single user mode. Only one administrator can connect at this
> time."
> Thanks for any suggestions.
> -Bill
>|||Good thought, but that's not it.
Thanks!
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...
> There might be an EM connection that is already open.
> "bill" <belgie@.datamti.com> wrote in message
> news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
> > I am trying to restore a backup of a master database from one server to
a
> > new server.
> >
> > I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a
> command
> > prompt to start it in single user mode. It finished with "SQL Global
> > counter collection task is created." It appears to stop at this point -
> > there is no prompt.
> >
> > While in single-user mode, I open Query Analyzer and attempt to log in
> both
> > with Windows Authentication and with SQL Server authentication, using
the
> sa
> > login.
> >
> > In both cases, I get an error "Login failed for user '(login)'. Reason:
> > Server is in single user mode. Only one administrator can connect at
this
> > time."
> >
> > Thanks for any suggestions.
> > -Bill
> >
> >
>|||Agent? Other services or users? You need to find who is connected...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bill" <belgie@.datamti.com> wrote in message news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...
> Good thought, but that's not it.
> Thanks!
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...
> > There might be an EM connection that is already open.
> >
> > "bill" <belgie@.datamti.com> wrote in message
> > news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
> > > I am trying to restore a backup of a master database from one server to
> a
> > > new server.
> > >
> > > I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a
> > command
> > > prompt to start it in single user mode. It finished with "SQL Global
> > > counter collection task is created." It appears to stop at this point -
> > > there is no prompt.
> > >
> > > While in single-user mode, I open Query Analyzer and attempt to log in
> > both
> > > with Windows Authentication and with SQL Server authentication, using
> the
> > sa
> > > login.
> > >
> > > In both cases, I get an error "Login failed for user '(login)'. Reason:
> > > Server is in single user mode. Only one administrator can connect at
> this
> > > time."
> > >
> > > Thanks for any suggestions.
> > > -Bill
> > >
> > >
> >
> >
>|||How can I do this? I can't connect to SQL Server to see the connections.
Thanks
Bill
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oj%23tuBCuEHA.2956@.TK2MSFTNGP12.phx.gbl...
> Agent? Other services or users? You need to find who is connected...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bill" <belgie@.datamti.com> wrote in message
news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...
> > Good thought, but that's not it.
> >
> > Thanks!
> >
> > "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> > news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...
> > > There might be an EM connection that is already open.
> > >
> > > "bill" <belgie@.datamti.com> wrote in message
> > > news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
> > > > I am trying to restore a backup of a master database from one server
to
> > a
> > > > new server.
> > > >
> > > > I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a
> > > command
> > > > prompt to start it in single user mode. It finished with "SQL
Global
> > > > counter collection task is created." It appears to stop at this
point -
> > > > there is no prompt.
> > > >
> > > > While in single-user mode, I open Query Analyzer and attempt to log
in
> > > both
> > > > with Windows Authentication and with SQL Server authentication,
using
> > the
> > > sa
> > > > login.
> > > >
> > > > In both cases, I get an error "Login failed for user '(login)'.
Reason:
> > > > Server is in single user mode. Only one administrator can connect at
> > this
> > > > time."
> > > >
> > > > Thanks for any suggestions.
> > > > -Bill
> > > >
> > > >
> > >
> > >
> >
> >
>|||Start stopping SQL server Agent, and other possible services that might use SQL server. You might
need to get physical access to the machine, or terminal services, so you can work locally on the
machine stopping services and perhaps even disabling network access (which isn't doable over
terminal services, of course).
Another thing you can try is to login using OSQL.EXE instead of Query Analyzer. QA *might* try to
open a connection for Object Browser and that *might* get connected before your query window.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bill" <belgie@.datamti.com> wrote in message news:emBg4NEuEHA.348@.tk2msftngp13.phx.gbl...
> How can I do this? I can't connect to SQL Server to see the connections.
> Thanks
> Bill
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Oj%23tuBCuEHA.2956@.TK2MSFTNGP12.phx.gbl...
>> Agent? Other services or users? You need to find who is connected...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "bill" <belgie@.datamti.com> wrote in message
> news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...
>> > Good thought, but that's not it.
>> >
>> > Thanks!
>> >
>> > "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
>> > news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...
>> > > There might be an EM connection that is already open.
>> > >
>> > > "bill" <belgie@.datamti.com> wrote in message
>> > > news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
>> > > > I am trying to restore a backup of a master database from one server
> to
>> > a
>> > > > new server.
>> > > >
>> > > > I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a
>> > > command
>> > > > prompt to start it in single user mode. It finished with "SQL
> Global
>> > > > counter collection task is created." It appears to stop at this
> point -
>> > > > there is no prompt.
>> > > >
>> > > > While in single-user mode, I open Query Analyzer and attempt to log
> in
>> > > both
>> > > > with Windows Authentication and with SQL Server authentication,
> using
>> > the
>> > > sa
>> > > > login.
>> > > >
>> > > > In both cases, I get an error "Login failed for user '(login)'.
> Reason:
>> > > > Server is in single user mode. Only one administrator can connect at
>> > this
>> > > > time."
>> > > >
>> > > > Thanks for any suggestions.
>> > > > -Bill
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
>
Subscribe to:
Posts (Atom)