I have an ERP system, and I am try to take the db offline to restore, but it
will not. Any suggestions. I even rebooted
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:B16E4BFD-5BC0-4FEE-B53E-53F22D10CFA9@.microsoft.com...
>I have an ERP system, and I am try to take the db offline to restore, but
>it
> will not. Any suggestions. I even rebooted
Run sp_who to see who is currently connected to the database.
You can then use the KILL command to kill off the spids who are connected.
Once that is done, you should be able to put the database in db use only.
Rick Sawtell
MCT, MCSD, MCDBA
|||easier than killing them, (especially if you have many users connected),
you can:
ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE SET ONLINE
that should disconnect the buggers :-)
Rick Sawtell wrote:
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:B16E4BFD-5BC0-4FEE-B53E-53F22D10CFA9@.microsoft.com...
>
> Run sp_who to see who is currently connected to the database.
> You can then use the KILL command to kill off the spids who are connected.
> Once that is done, you should be able to put the database in db use only.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
|||Good one Uri...
That goes into the toolchest!
Rick
"Uri Dor" <tablul@.newsgroups.nospam> wrote in message
news:eBm4jxg0EHA.1860@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> easier than killing them, (especially if you have many users connected),
> you can:
> ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE
> ALTER DATABASE SET ONLINE
> that should disconnect the buggers :-)
> Rick Sawtell wrote:
Showing posts with label restore. Show all posts
Showing posts with label restore. Show all posts
Thursday, March 29, 2012
Can't take db offline
I have an ERP system, and I am try to take the db offline to restore, but it
will not. Any suggestions. I even rebooted"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:B16E4BFD-5BC0-4FEE-B53E-53F22D10CFA9@.microsoft.com...
>I have an ERP system, and I am try to take the db offline to restore, but
>it
> will not. Any suggestions. I even rebooted
Run sp_who to see who is currently connected to the database.
You can then use the KILL command to kill off the spids who are connected.
Once that is done, you should be able to put the database in db use only.
Rick Sawtell
MCT, MCSD, MCDBA|||easier than killing them, (especially if you have many users connected),
you can:
ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE SET ONLINE
that should disconnect the buggers :-)
Rick Sawtell wrote:
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:B16E4BFD-5BC0-4FEE-B53E-53F22D10CFA9@.microsoft.com...
>>I have an ERP system, and I am try to take the db offline to restore, but
>>it
>>will not. Any suggestions. I even rebooted
>
> Run sp_who to see who is currently connected to the database.
> You can then use the KILL command to kill off the spids who are connected.
> Once that is done, you should be able to put the database in db use only.
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Good one Uri...
That goes into the toolchest!
Rick
"Uri Dor" <tablul@.newsgroups.nospam> wrote in message
news:eBm4jxg0EHA.1860@.TK2MSFTNGP15.phx.gbl...
> easier than killing them, (especially if you have many users connected),
> you can:
> ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE
> ALTER DATABASE SET ONLINE
> that should disconnect the buggers :-)
> Rick Sawtell wrote:
>> "DBA" <DBA@.discussions.microsoft.com> wrote in message
>> news:B16E4BFD-5BC0-4FEE-B53E-53F22D10CFA9@.microsoft.com...
>>I have an ERP system, and I am try to take the db offline to restore, but
>>it
>>will not. Any suggestions. I even rebooted
>>
>> Run sp_who to see who is currently connected to the database.
>> You can then use the KILL command to kill off the spids who are
>> connected. Once that is done, you should be able to put the database in
>> db use only.
>> Rick Sawtell
>> MCT, MCSD, MCDBA
will not. Any suggestions. I even rebooted"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:B16E4BFD-5BC0-4FEE-B53E-53F22D10CFA9@.microsoft.com...
>I have an ERP system, and I am try to take the db offline to restore, but
>it
> will not. Any suggestions. I even rebooted
Run sp_who to see who is currently connected to the database.
You can then use the KILL command to kill off the spids who are connected.
Once that is done, you should be able to put the database in db use only.
Rick Sawtell
MCT, MCSD, MCDBA|||easier than killing them, (especially if you have many users connected),
you can:
ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE SET ONLINE
that should disconnect the buggers :-)
Rick Sawtell wrote:
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:B16E4BFD-5BC0-4FEE-B53E-53F22D10CFA9@.microsoft.com...
>>I have an ERP system, and I am try to take the db offline to restore, but
>>it
>>will not. Any suggestions. I even rebooted
>
> Run sp_who to see who is currently connected to the database.
> You can then use the KILL command to kill off the spids who are connected.
> Once that is done, you should be able to put the database in db use only.
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Good one Uri...
That goes into the toolchest!
Rick
"Uri Dor" <tablul@.newsgroups.nospam> wrote in message
news:eBm4jxg0EHA.1860@.TK2MSFTNGP15.phx.gbl...
> easier than killing them, (especially if you have many users connected),
> you can:
> ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE
> ALTER DATABASE SET ONLINE
> that should disconnect the buggers :-)
> Rick Sawtell wrote:
>> "DBA" <DBA@.discussions.microsoft.com> wrote in message
>> news:B16E4BFD-5BC0-4FEE-B53E-53F22D10CFA9@.microsoft.com...
>>I have an ERP system, and I am try to take the db offline to restore, but
>>it
>>will not. Any suggestions. I even rebooted
>>
>> Run sp_who to see who is currently connected to the database.
>> You can then use the KILL command to kill off the spids who are
>> connected. Once that is done, you should be able to put the database in
>> db use only.
>> Rick Sawtell
>> MCT, MCSD, MCDBA
Can't take db offline
I have an ERP system, and I am try to take the db offline to restore, but it
will not. Any suggestions. I even rebooted"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:B16E4BFD-5BC0-4FEE-B53E-53F22D10CFA9@.microsoft.com...
>I have an ERP system, and I am try to take the db offline to restore, but
>it
> will not. Any suggestions. I even rebooted
Run sp_who to see who is currently connected to the database.
You can then use the KILL command to kill off the spids who are connected.
Once that is done, you should be able to put the database in db use only.
Rick Sawtell
MCT, MCSD, MCDBA|||easier than killing them, (especially if you have many users connected),
you can:
ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE SET ONLINE
that should disconnect the buggers :-)
Rick Sawtell wrote:
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:B16E4BFD-5BC0-4FEE-B53E-53F22D10CFA9@.microsoft.com...
>
>
> Run sp_who to see who is currently connected to the database.
> You can then use the KILL command to kill off the spids who are connected.
> Once that is done, you should be able to put the database in db use only.
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Good one Uri...
That goes into the toolchest!
Rick
"Uri Dor" <tablul@.newsgroups.nospam> wrote in message
news:eBm4jxg0EHA.1860@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> easier than killing them, (especially if you have many users connected),
> you can:
> ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE
> ALTER DATABASE SET ONLINE
> that should disconnect the buggers :-)
> Rick Sawtell wrote:
>sql
will not. Any suggestions. I even rebooted"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:B16E4BFD-5BC0-4FEE-B53E-53F22D10CFA9@.microsoft.com...
>I have an ERP system, and I am try to take the db offline to restore, but
>it
> will not. Any suggestions. I even rebooted
Run sp_who to see who is currently connected to the database.
You can then use the KILL command to kill off the spids who are connected.
Once that is done, you should be able to put the database in db use only.
Rick Sawtell
MCT, MCSD, MCDBA|||easier than killing them, (especially if you have many users connected),
you can:
ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE SET ONLINE
that should disconnect the buggers :-)
Rick Sawtell wrote:
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:B16E4BFD-5BC0-4FEE-B53E-53F22D10CFA9@.microsoft.com...
>
>
> Run sp_who to see who is currently connected to the database.
> You can then use the KILL command to kill off the spids who are connected.
> Once that is done, you should be able to put the database in db use only.
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Good one Uri...
That goes into the toolchest!
Rick
"Uri Dor" <tablul@.newsgroups.nospam> wrote in message
news:eBm4jxg0EHA.1860@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> easier than killing them, (especially if you have many users connected),
> you can:
> ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE
> ALTER DATABASE SET ONLINE
> that should disconnect the buggers :-)
> Rick Sawtell wrote:
>sql
Tuesday, March 20, 2012
Can't see mapped drives
Hi,
Am unable to see mapped drive when going to restore db in
sql 2000 enterprise manager. Service is set to start
with a lan acct. acct is in the local machine admin grp
and remote machine admin grp.
Any suggestions:-)
Thanks in advanceDo not use mapped drives for backup\restore with SQL server. Use UNC names
instead. SQL may or may not see a mapped drive correctly but will always
find a UNC share that it has permission and connection to.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:8f9b01c40541$3ade3230$a301280a@.phx.gbl...
> Hi,
> Am unable to see mapped drive when going to restore db in
> sql 2000 enterprise manager. Service is set to start
> with a lan acct. acct is in the local machine admin grp
> and remote machine admin grp.
> Any suggestions:-)
> Thanks in advance|||Hi,
You cant see the mapped drives in Enterprise manager. Rather you can
directly mention the UNC path directly in Enterprise manager or use
Restore database command from Query Analyzer.
Restore database <dbname> from disk='\\machinename\share\backupfile.bak'
with stats=10 With
Move 'logicaldatafile' to physcaldatafile',
Move 'logiallogfile' to physcallogfile'
stats will show the restore status
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:8f9b01c40541$3ade3230$a301280a@.phx.gbl...
> Hi,
> Am unable to see mapped drive when going to restore db in
> sql 2000 enterprise manager. Service is set to start
> with a lan acct. acct is in the local machine admin grp
> and remote machine admin grp.
> Any suggestions:-)
> Thanks in advance|||It is possible to get SQL Server to use mapped network drives, through the u
se of trace flag 1807.
Check out knowledge base article #304261 for full details
http://support.microsoft.com/defaul...1&Product=sql2k
Am unable to see mapped drive when going to restore db in
sql 2000 enterprise manager. Service is set to start
with a lan acct. acct is in the local machine admin grp
and remote machine admin grp.
Any suggestions:-)
Thanks in advanceDo not use mapped drives for backup\restore with SQL server. Use UNC names
instead. SQL may or may not see a mapped drive correctly but will always
find a UNC share that it has permission and connection to.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:8f9b01c40541$3ade3230$a301280a@.phx.gbl...
> Hi,
> Am unable to see mapped drive when going to restore db in
> sql 2000 enterprise manager. Service is set to start
> with a lan acct. acct is in the local machine admin grp
> and remote machine admin grp.
> Any suggestions:-)
> Thanks in advance|||Hi,
You cant see the mapped drives in Enterprise manager. Rather you can
directly mention the UNC path directly in Enterprise manager or use
Restore database command from Query Analyzer.
Restore database <dbname> from disk='\\machinename\share\backupfile.bak'
with stats=10 With
Move 'logicaldatafile' to physcaldatafile',
Move 'logiallogfile' to physcallogfile'
stats will show the restore status
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:8f9b01c40541$3ade3230$a301280a@.phx.gbl...
> Hi,
> Am unable to see mapped drive when going to restore db in
> sql 2000 enterprise manager. Service is set to start
> with a lan acct. acct is in the local machine admin grp
> and remote machine admin grp.
> Any suggestions:-)
> Thanks in advance|||It is possible to get SQL Server to use mapped network drives, through the u
se of trace flag 1807.
Check out knowledge base article #304261 for full details
http://support.microsoft.com/defaul...1&Product=sql2k
Thursday, March 8, 2012
Cant restore the database backup. Exclusive access could not be obtained
Hi everyone,
Hope somebody can help me on this.
I did a full BACKUP for two SQL databases using SQL Server Managament Studio. When trying to RESTORE the DBs, I get the following error:
"System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)"
There are NO users using the application. These are DBs for the Portfolio Server 2007 application.
After exploring a little bit, I found that there are several "SLEEPING" processes with an "AWATING COMMAND" flag. The processes come from the Portfolio Server Application, maybe from previous sessions.
Can they be deleted? if so, would there be any serious consequences?
Thank you for your help on this.
Oscar E.
Take a look at this article, I think it will help.
Killing ProcessIDs using SMO
http://sqlblogcasts.com/blogs/seanprice/archive/2007/07/11/Killing-ProcessIDs-using-SMO.aspx
Mark.
|||Forcibly terminate all user processes as step 1 of your scheduled task - here's a stored proc that I got from somewhere that does this, for a named db:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--Type procedure
--author mak mak_999@.yahoo.com
--date written 4/19/2000
--project maintenance
--objective procedure to kill process for a given database
ALTER procedure usp_killprocess @.dbname varchar(128) as
set nocount on
set quoted_identifier off
declare @.kill_id int
declare @.query varchar(320)
declare killprocess_cursor cursor for
select a.spid from sysprocesses a join
sysdatabases b on a.dbid=b.dbid where b.name=@.dbname
open killprocess_cursor
fetch next from killprocess_cursor into @.kill_id
while(@.@.fetch_status =0)
begin
set @.query = 'kill '+ convert(varchar,@.kill_id)
exec (@.query)
fetch next from killprocess_cursor into @.kill_id
end
close killprocess_cursor
deallocate killprocess_cursor
--usage
--exec usp_killprocess "mydatabasename"
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I use this as part of a scheduled backup live/restore to development (kill users before performing the restore) task and it works fine.
Cant restore the database backup in SQL server 2005?
I wrote a sql like this to restore my database backup
use test
RESTORE DATABASE [Lorenzo]
FROM DISK = N'D:\Lorenzo'
WITH FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10
GO
But this is the error message that i am getting, but still i have 58.5GB free space in my hard drive ?
what is the wrongwith this SQL server 2005 ? i am using sql server 2005 standard edition !
===== Error=====
Msg 3257, Level 16, State 1, Line 2
There is insufficient free space on disk volume 'C:\' to create the database. The database requires 63689129984 additional free bytes, while only 54682599424 bytes are available.
Msg 3119, Level 16, State 4, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
any idea ! i am so stuck with this now !
thanks
regards
sujithf
Sujithf:
To me it looks like you don't have enough disk to cover the "nominal" size of the dtabase. You might have 58.5 GB freespace on the harddrive, but it looks like the database wants somewhere around 63GB. Do you have multiple drives on your server? If so, try allocating one of your files on an alternate drive. If not, see if you can purge some data off the drive.
Also, try to keep your database files off the OS drive whenever possible (and it looks like that is not possible in this case). In many cases in which I have needed space I have been able to migrate backups off to another disk or even to an external device. See if there are any old "large" backups that are clearly not needed.
|||
Dave
But my database backup size is just 5.11 GB ?
why is it need too many free space on c : drive to restore just 5.11 Gb size database ?
regards
sujithf
|||
Sujithf:
Again, this has to do with the "nominal" database size. If you have a pre-copy database with 70 GB and 69GB free space you are still going to need 70GB to restore the database. This is what I am calling the "nominal" size. Try shrinking your database. Often times you will have a large log file that can be shrunk and you also might be able to shrink your data file.
|||
Dave
thank you very much for u r profesional ideas Dave,
now i am going to shrink my database
my current allocated space is :60743.63 MB
avialable free space 1585.26 MP 2%
so what should i type for Maximum free space in files sfter shrinking ? ...........%
sujithf
|||Well, this means that you require about 60GB of space. I don't think you are going to be do much of a shrink. If this is the case, I think you are going to have to scrounge to free up some space. Yikes!
Dave
Yes dave i will allocate some free space in to my c drive by tomorrow
and again many thanks 4 u r information,cos i was so stuck diring this week to do this task,
now i know at least a solution for this matter
thanks to MSDN forum and Mr.dave
regards
sujith
|||You're welcome. :-)
Cant Restore SQL Server databases: Exclusive access could not be obtained
Hi everyone,
Hope somebody can help me on this.
I did a full BACKUP for two SQL databases using SQL Server Managament Studio. When trying to RESTORE the DBs, I get the following error:
"System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)"
There are NO users using the application. These are DBs for the Portfolio Server 2007 application.
After exploring a little bit, I found that there are several "SLEEPING" processes with an "AWATING COMMAND" flag. The processes come from the Portfolio Server Application, maybe from previous sessions.
Can they be deleted? if so, would there be any serious consequences?
Thank you for your help on this.
Oscar E.
Hi Oscar,
you can go ahead and kill those process as they are no longer active.......those connections are inactive and shud not cause any harm to db ....
cool
|||Thank you very much Deepak.
It worked.
Regards,
Oscar E.
Cant Restore SQL Server databases: Exclusive access could not be obtained
Hi everyone,
Hope somebody can help me on this.
I did a full BACKUP for two SQL databases using SQL Server Managament Studio. When trying to RESTORE the DBs, I get the following error:
"System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)"
There are NO users using the application. These are DBs for the Portfolio Server 2007 application.
After exploring a little bit, I found that there are several "SLEEPING" processes with an "AWATING COMMAND" flag. The processes come from the Portfolio Server Application, maybe from previous sessions.
Can they be deleted? if so, would there be any serious consequences?
Thank you for your help on this.
Oscar E.
Hi Oscar,
you can go ahead and kill those process as they are no longer active.......those connections are inactive and shud not cause any harm to db ....
cool
|||Thank you very much Deepak.
It worked.
Regards,
Oscar E.
Cant Restore SQL Server databases: Exclusive access could not be obtained
Hi everyone,
Hope somebody can help me on this.
I did a full BACKUP for two SQL databases using SQL Server Managament Studio. When trying to RESTORE the DBs, I get the following error:
"System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)"
There are NO users using the application. These are DBs for the Portfolio Server 2007 application.
After exploring a little bit, I found that there are several "SLEEPING" processes with an "AWATING COMMAND" flag. The processes come from the Portfolio Server Application, maybe from previous sessions.
Can they be deleted? if so, would there be any serious consequences?
Thank you for your help on this.
Oscar E.
Hi Oscar,
you can go ahead and kill those process as they are no longer active.......those connections are inactive and shud not cause any harm to db ....
cool
|||Thank you very much Deepak.
It worked.
Regards,
Oscar E.
Can't restore master db!
Hi:
I've got a problem that's causing me headaches! I'm using Veritas Backup Exec to perform my db backups. I've just
recently taken over the DBA responsibilities and one of the first things I've wanted to do is test those backups since
we've never had occassion to use the backups. I have a test system that I'm using and have it configured exactly as the
production system.
After installing MS SQL Server 7 putting on SP4 and all the security patches I try to restore all the databases. I can
get all my databases restored except the master db that contains all my security info. I keep getting the message
"must be in single user mode". I've tried to set the master db into single user mode and there's just no way to do it!!
I've tried using the EM and the sp_dboption. Without that master db restored I'm in deep doo-doo!
Any ideas how I can handle this? Any help/suggestions is greatly appreciated.
TIA
Cathy
SQL Server 7( SP4), NT4.0
Hi,
To restore the master database you have start the SQL server itself in
Single user mode.
1. Stop the MSSQL server and sql agent service
2. Go to command prompt and execute below command,.
sqlservr.exe -c -m
3. Login to sql server using query analyzer and retore the master database.
4. After the restore .. go to old comamnd prompt and press CONTRL and C to
close the old session
5. Go to control panel and start the MSSQL server and Sql agent service.
Note:
I recommend you to restore the master database first followed with other
databases.
Thanks
Hari
MCDBA
"Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in message
news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.4ax.com...
> Hi:
> I've got a problem that's causing me headaches! I'm using Veritas Backup
Exec to perform my db backups. I've just
> recently taken over the DBA responsibilities and one of the first things
I've wanted to do is test those backups since
> we've never had occassion to use the backups. I have a test system that
I'm using and have it configured exactly as the
> production system.
> After installing MS SQL Server 7 putting on SP4 and all the security
patches I try to restore all the databases. I can
> get all my databases restored except the master db that contains all my
security info. I keep getting the message
> "must be in single user mode". I've tried to set the master db into
single user mode and there's just no way to do it!!
> I've tried using the EM and the sp_dboption. Without that master db
restored I'm in deep doo-doo!
> Any ideas how I can handle this? Any help/suggestions is greatly
appreciated.
> TIA
> Cathy
> SQL Server 7( SP4), NT4.0
|||This method will work. I think Hari copied and pasted the answer directly
from Books Online! I would have provided exactly the same answer. I
probably would have also pointed you at Books Online (within your SQL Server
program group). BACKUP and RESTORE information and examples can be found
there. Knowing how to restore the data that you are backing up is
important. Cathy, you are smart to be doing your research now (instead of
when the entire company is waiting for you to restore the data).
Keith
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi,
> To restore the master database you have start the SQL server itself in
> Single user mode.
> 1. Stop the MSSQL server and sql agent service
> 2. Go to command prompt and execute below command,.
> sqlservr.exe -c -m
> 3. Login to sql server using query analyzer and retore the master
database.
> 4. After the restore .. go to old comamnd prompt and press CONTRL and C to
> close the old session
> 5. Go to control panel and start the MSSQL server and Sql agent service.
> Note:
> I recommend you to restore the master database first followed with other
> databases.
> Thanks
> Hari
> MCDBA
>
> "Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in
message
> news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.4ax.com...
> Exec to perform my db backups. I've just
> I've wanted to do is test those backups since
> I'm using and have it configured exactly as the
> patches I try to restore all the databases. I can
> security info. I keep getting the message
> single user mode and there's just no way to do it!!
> restored I'm in deep doo-doo!
> appreciated.
>
|||Thanks guys for your prompt reply.
I thought I was starting the SQL Server in single user mode: I'm using the command "net start mssqlserver -m"... Is this not correct?
"Keith Kratochvil" wrote:
> This method will work. I think Hari copied and pasted the answer directly
> from Books Online! I would have provided exactly the same answer. I
> probably would have also pointed you at Books Online (within your SQL Server
> program group). BACKUP and RESTORE information and examples can be found
> there. Knowing how to restore the data that you are backing up is
> important. Cathy, you are smart to be doing your research now (instead of
> when the entire company is waiting for you to restore the data).
> --
> Keith
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> database.
> message
>
|||No, NET START starts the MSSQLSERVER service, and the -m is a parameter to
NET START, which doesn't take any parameters so it wouldn't know what to do
with it.
You need to run the sqlservr executable, and the pass the -m flag to it as
Hari suggested.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"caeriel" <caeriel@.discussions.microsoft.com> wrote in message
news:9542FB0A-84B5-4DDD-8498-87C5F4A91D4A@.microsoft.com...
> Thanks guys for your prompt reply.
> I thought I was starting the SQL Server in single user mode: I'm using
the command "net start mssqlserver -m"... Is this not correct?[vbcol=seagreen]
> "Keith Kratochvil" wrote:
directly[vbcol=seagreen]
Server[vbcol=seagreen]
found[vbcol=seagreen]
of[vbcol=seagreen]
C to[vbcol=seagreen]
service.[vbcol=seagreen]
other[vbcol=seagreen]
Backup[vbcol=seagreen]
things[vbcol=seagreen]
that[vbcol=seagreen]
my[vbcol=seagreen]
|||Keith: I'm still unable to restore the master database...
I tried starting the server as you & Hari instructed; the server started in single user mode. However, the restore has to be issued from the Veritas software interface. Here's the SQL Server log.
*************************************
================================================== ====================
Job server: Test
Job name: MasterDB NoRecov 8-03
Job started: Tuesday, August 03, 2004 at 10:08:07 AM
Job type: Restore
Job Log: BEX41.txt
================================================== ====================
Drive and media information from media mount:
Robotic Library Name:
Drive Name: Quantum 1
Slot: 0
Media GUID: {2E9659D2-C5B5-43BF-BD2A-502B2E1054E1}
Media Label: Test Wk1 Monday
Overwrite Protection Time Left: None
Append Time Left: Infinite
================================================== ====================
Job Operation - Restore
================================================== ====================
Restoring data to Test .
Storage Media #1: " Media created 8/2/04 08:24:56 PM "
Backup performed on 8/3/04 at 8:12 AM
Backup set #6: " Full Backup "
Restore started on 8/3/04 at 10:10:35 AM.
An error occurred on a query to database master.
^ ^ ^ ^ ^
SQL Server returned the following error message:
RESTORE DATABASE must be used in single user mode when trying to restore the master database.
^ ^ ^ ^ ^
Restore completed on 8/3/04 at 10:13:42 AM.
1 item was skipped.
Processed 0 bytes in 3 minutes and 7 seconds.
Throughput rate: 0.0 MB/min
================================================== ====================
Job ended: Tuesday, August 03, 2004 at 10:14:31 AM
Job completion status: Failed
================================================== ====================
************************************************** **
2004-08-03 09:55:16.51 kernel Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
Nov 27 1998 22:20:07
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
2004-08-03 09:55:16.51 kernel Copyright (C) 1988-1997 Microsoft Corporation.
2004-08-03 09:55:16.52 kernel All rights reserved.
2004-08-03 09:55:16.52 kernel Logging SQL Server messages in file 'D:\MSSQL7\log\ERRORLOG'.
2004-08-03 09:55:16.58 kernel initconfig: Number of user connections limited to 32767.
2004-08-03 09:55:16.58 kernel SQL Server is starting at priority class 'normal'(1 CPU detected).
2004-08-03 09:55:16.70 kernel User Mode Scheduler configured for thread processing
2004-08-03 09:55:17.96 server Directory Size: 10801
2004-08-03 09:55:18.05 spid1 Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks
2004-08-03 09:55:18.05 kernel Attempting to initialize Distributed Transaction Coordinator.
2004-08-03 09:55:18.82 spid1 Failed to obtain TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE
2004-08-03 09:55:18.83 spid1 Warning ******************
2004-08-03 09:55:18.83 spid1 SQL Server started in single user mode. Updates allowed to system catalogs.
2004-08-03 09:55:18.84 spid1 Warning ******************
2004-08-03 09:55:18.85 spid1 Starting up database 'master'.
2004-08-03 09:55:18.85 spid1 Opening file D:\MSSQL7\data\master.mdf.
2004-08-03 09:55:18.89 spid1 Opening file D:\MSSQL7\data\mastlog.ldf.
2004-08-03 09:55:18.99 spid1 Loading SQL Server's Unicode collation.
2004-08-03 09:55:19.01 spid1 Loading SQL Server's non-Unicode sort order and character set.
2004-08-03 09:55:19.24 spid1 4 transactions rolled forward in database 'master' (1).
2004-08-03 09:55:19.25 spid1 0 transactions rolled back in database 'master' (1).
2004-08-03 09:55:19.35 spid1 Starting up database 'model'.
2004-08-03 09:55:19.36 spid1 Opening file D:\MSSQL7\DATA\model.mdf.
2004-08-03 09:55:19.43 spid1 Opening file D:\MSSQL7\DATA\modellog.ldf.
2004-08-03 09:55:19.63 spid1 Clearing tempdb database.
2004-08-03 09:55:19.69 spid1 Creating file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:19.87 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:19.97 spid1 Creating file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:19.99 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:20.12 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:20.14 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:21.15 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:21.25 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:21.34 spid1 Starting up database 'tempdb'.
2004-08-03 09:55:21.38 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:21.41 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:21.69 spid1 Server name is 'AOATESTBOX'.
2004-08-03 09:55:21.70 kernel Using 'SQLEVN70.DLL' version '7.00.623'.
2004-08-03 09:55:21.71 kernel Using 'OPENDS60.DLL' version '7.00.00.0623'.
2004-08-03 09:55:21.71 ods Using 'SSNMPN70.DLL' version '7.0.623' to listen on '\\.\pipe\sql\query'.
2004-08-03 09:55:21.72 ods Using 'SSMSSO70.DLL' version '7.0.623' to listen on '3180'.
2004-08-03 09:55:21.82 ods Using 'SSMSRP70.DLL' version '7.0.623' to listen on 'AOATESTBOX'.
2004-08-03 09:55:22.68 spid5 Starting up database 'msdb'.
2004-08-03 09:55:22.68 spid5 Opening file D:\MSSQL7\DATA\msdbdata.mdf.
2004-08-03 09:55:22.70 spid6 Starting up database 'pubs'.
2004-08-03 09:55:22.70 spid6 Opening file D:\MSSQL7\DATA\pubs.mdf.
2004-08-03 09:55:22.72 spid7 Starting up database 'Northwind'.
2004-08-03 09:55:22.72 spid7 Opening file D:\MSSQL7\DATA\northwnd.mdf.
2004-08-03 09:55:22.82 spid5 Opening file D:\MSSQL7\DATA\msdblog.ldf.
2004-08-03 09:55:22.85 spid7 Opening file D:\MSSQL7\DATA\northwnd.ldf.
2004-08-03 09:55:22.87 spid6 Opening file D:\MSSQL7\DATA\pubs_log.ldf.
2004-08-03 09:55:23.55 spid1 Recovery complete.
2004-08-03 09:55:23.56 spid1 SQL Server's Unicode collation is:
2004-08-03 09:55:23.57 spid1 'English' (ID = 1033).
2004-08-03 09:55:23.57 spid1 comparison style = 196609.
2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode sort order is:
2004-08-03 09:55:23.58 spid1 'nocase_850' (ID = 42).
2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode character set is:
2004-08-03 09:55:23.58 spid1 'cp850' (ID = 2).
2004-08-03 09:57:00.02 spid6 Using 'xpstar.dll' version '1998.11.13' to execute extended stored procedure 'sp_MSgetversion'.
2004-08-03 10:16:44.05 kernel SQL Server shutdown due to Ctrl-C or Ctrl-Break signal.
************************************************** *****
Here's the Veritas log:
************************************************
************************************************
"Keith Kratochvil" wrote:
> This method will work. I think Hari copied and pasted the answer directly
> from Books Online! I would have provided exactly the same answer. I
> probably would have also pointed you at Books Online (within your SQL Server
> program group). BACKUP and RESTORE information and examples can be found
> there. Knowing how to restore the data that you are backing up is
> important. Cathy, you are smart to be doing your research now (instead of
> when the entire company is waiting for you to restore the data).
> --
> Keith
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> database.
> message
>
|||This may be a problem with Veritas software. Can you get the backup files
from Veritas, and do the restore manually through Query Analyzer?
Restore database master from disk = "C:\whatever"
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in message
news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.4ax.com...
> Hi:
> I've got a problem that's causing me headaches! I'm using Veritas Backup
Exec to perform my db backups. I've just
> recently taken over the DBA responsibilities and one of the first things
I've wanted to do is test those backups since
> we've never had occassion to use the backups. I have a test system that
I'm using and have it configured exactly as the
> production system.
> After installing MS SQL Server 7 putting on SP4 and all the security
patches I try to restore all the databases. I can
> get all my databases restored except the master db that contains all my
security info. I keep getting the message
> "must be in single user mode". I've tried to set the master db into
single user mode and there's just no way to do it!!
> I've tried using the EM and the sp_dboption. Without that master db
restored I'm in deep doo-doo!
> Any ideas how I can handle this? Any help/suggestions is greatly
appreciated.
> TIA
> Cathy
> SQL Server 7( SP4), NT4.0
|||Try restoring the backup files to disk using Veritas and then restore the
database using T-SQL executed via Query Analyzer.
RESTORE DATABASE foo FROM DISK = 'x:\foo.bak'
(syntax and examples within Books Online)
Keith
"caeriel" <caeriel@.discussions.microsoft.com> wrote in message
news:B6837C91-9566-4CB1-B3D0-73DB610E6232@.microsoft.com...
> Keith: I'm still unable to restore the master database...
> I tried starting the server as you & Hari instructed; the server started
in single user mode. However, the restore has to be issued from the Veritas
software interface. Here's the SQL Server log.
> *************************************
> ================================================== ====================
> Job server: Test
> Job name: MasterDB NoRecov 8-03
> Job started: Tuesday, August 03, 2004 at 10:08:07 AM
> Job type: Restore
> Job Log: BEX41.txt
> ================================================== ====================
> Drive and media information from media mount:
> Robotic Library Name:
> Drive Name: Quantum 1
> Slot: 0
> Media GUID: {2E9659D2-C5B5-43BF-BD2A-502B2E1054E1}
> Media Label: Test Wk1 Monday
> Overwrite Protection Time Left: None
> Append Time Left: Infinite
> ================================================== ====================
> Job Operation - Restore
> ================================================== ====================
> Restoring data to Test .
> Storage Media #1: " Media created 8/2/04 08:24:56 PM "
> Backup performed on 8/3/04 at 8:12 AM
> Backup set #6: " Full Backup "
> Restore started on 8/3/04 at 10:10:35 AM.
> An error occurred on a query to database master.
> ^ ^ ^ ^ ^
> SQL Server returned the following error message:
> RESTORE DATABASE must be used in single user mode when trying to restore
the master database.
> ^ ^ ^ ^ ^
> Restore completed on 8/3/04 at 10:13:42 AM.
> 1 item was skipped.
>
> Processed 0 bytes in 3 minutes and 7 seconds.
> Throughput rate: 0.0 MB/min
> ================================================== ====================
> Job ended: Tuesday, August 03, 2004 at 10:14:31 AM
> Job completion status: Failed
> ================================================== ====================
> ************************************************** **
> 2004-08-03 09:55:16.51 kernel Microsoft SQL Server 7.00 - 7.00.623
(Intel X86)
> Nov 27 1998 22:20:07
> Copyright (c) 1988-1998 Microsoft Corporation
> Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
> 2004-08-03 09:55:16.51 kernel Copyright (C) 1988-1997 Microsoft
Corporation.
> 2004-08-03 09:55:16.52 kernel All rights reserved.
> 2004-08-03 09:55:16.52 kernel Logging SQL Server messages in file
'D:\MSSQL7\log\ERRORLOG'.
> 2004-08-03 09:55:16.58 kernel initconfig: Number of user connections
limited to 32767.
> 2004-08-03 09:55:16.58 kernel SQL Server is starting at priority class
'normal'(1 CPU detected).
> 2004-08-03 09:55:16.70 kernel User Mode Scheduler configured for thread
processing
> 2004-08-03 09:55:17.96 server Directory Size: 10801
> 2004-08-03 09:55:18.05 spid1 Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks
> 2004-08-03 09:55:18.05 kernel Attempting to initialize Distributed
Transaction Coordinator.
> 2004-08-03 09:55:18.82 spid1 Failed to obtain
TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE
> 2004-08-03 09:55:18.83 spid1 Warning ******************
> 2004-08-03 09:55:18.83 spid1 SQL Server started in single user mode.
Updates allowed to system catalogs.
> 2004-08-03 09:55:18.84 spid1 Warning ******************
> 2004-08-03 09:55:18.85 spid1 Starting up database 'master'.
> 2004-08-03 09:55:18.85 spid1 Opening file D:\MSSQL7\data\master.mdf.
> 2004-08-03 09:55:18.89 spid1 Opening file D:\MSSQL7\data\mastlog.ldf.
> 2004-08-03 09:55:18.99 spid1 Loading SQL Server's Unicode collation.
> 2004-08-03 09:55:19.01 spid1 Loading SQL Server's non-Unicode sort
order and character set.
> 2004-08-03 09:55:19.24 spid1 4 transactions rolled forward in database
'master' (1).
> 2004-08-03 09:55:19.25 spid1 0 transactions rolled back in database
'master' (1).
> 2004-08-03 09:55:19.35 spid1 Starting up database 'model'.
> 2004-08-03 09:55:19.36 spid1 Opening file D:\MSSQL7\DATA\model.mdf.
> 2004-08-03 09:55:19.43 spid1 Opening file D:\MSSQL7\DATA\modellog.ldf.
> 2004-08-03 09:55:19.63 spid1 Clearing tempdb database.
> 2004-08-03 09:55:19.69 spid1 Creating file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:19.87 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:19.97 spid1 Creating file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:19.99 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:20.12 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:20.14 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.15 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:21.25 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.34 spid1 Starting up database 'tempdb'.
> 2004-08-03 09:55:21.38 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:21.41 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.69 spid1 Server name is 'AOATESTBOX'.
> 2004-08-03 09:55:21.70 kernel Using 'SQLEVN70.DLL' version '7.00.623'.
> 2004-08-03 09:55:21.71 kernel Using 'OPENDS60.DLL' version
'7.00.00.0623'.
> 2004-08-03 09:55:21.71 ods Using 'SSNMPN70.DLL' version '7.0.623' to
listen on '\\.\pipe\sql\query'.
> 2004-08-03 09:55:21.72 ods Using 'SSMSSO70.DLL' version '7.0.623' to
listen on '3180'.
> 2004-08-03 09:55:21.82 ods Using 'SSMSRP70.DLL' version '7.0.623' to
listen on 'AOATESTBOX'.
> 2004-08-03 09:55:22.68 spid5 Starting up database 'msdb'.
> 2004-08-03 09:55:22.68 spid5 Opening file D:\MSSQL7\DATA\msdbdata.mdf.
> 2004-08-03 09:55:22.70 spid6 Starting up database 'pubs'.
> 2004-08-03 09:55:22.70 spid6 Opening file D:\MSSQL7\DATA\pubs.mdf.
> 2004-08-03 09:55:22.72 spid7 Starting up database 'Northwind'.
> 2004-08-03 09:55:22.72 spid7 Opening file D:\MSSQL7\DATA\northwnd.mdf.
> 2004-08-03 09:55:22.82 spid5 Opening file D:\MSSQL7\DATA\msdblog.ldf.
> 2004-08-03 09:55:22.85 spid7 Opening file D:\MSSQL7\DATA\northwnd.ldf.
> 2004-08-03 09:55:22.87 spid6 Opening file D:\MSSQL7\DATA\pubs_log.ldf.
> 2004-08-03 09:55:23.55 spid1 Recovery complete.
> 2004-08-03 09:55:23.56 spid1 SQL Server's Unicode collation is:
> 2004-08-03 09:55:23.57 spid1 'English' (ID = 1033).
> 2004-08-03 09:55:23.57 spid1 comparison style = 196609.
> 2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode sort order is:
> 2004-08-03 09:55:23.58 spid1 'nocase_850' (ID = 42).
> 2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode character set is:
> 2004-08-03 09:55:23.58 spid1 'cp850' (ID = 2).
> 2004-08-03 09:57:00.02 spid6 Using 'xpstar.dll' version '1998.11.13' to
execute extended stored procedure 'sp_MSgetversion'.
> 2004-08-03 10:16:44.05 kernel SQL Server shutdown due to Ctrl-C or
Ctrl-Break signal.[vbcol=seagreen]
> ************************************************** *****
> Here's the Veritas log:
> ************************************************
> ************************************************
> "Keith Kratochvil" wrote:
directly[vbcol=seagreen]
Server[vbcol=seagreen]
found[vbcol=seagreen]
of[vbcol=seagreen]
C to[vbcol=seagreen]
service.[vbcol=seagreen]
other[vbcol=seagreen]
Backup[vbcol=seagreen]
things[vbcol=seagreen]
that[vbcol=seagreen]
my[vbcol=seagreen]
I've got a problem that's causing me headaches! I'm using Veritas Backup Exec to perform my db backups. I've just
recently taken over the DBA responsibilities and one of the first things I've wanted to do is test those backups since
we've never had occassion to use the backups. I have a test system that I'm using and have it configured exactly as the
production system.
After installing MS SQL Server 7 putting on SP4 and all the security patches I try to restore all the databases. I can
get all my databases restored except the master db that contains all my security info. I keep getting the message
"must be in single user mode". I've tried to set the master db into single user mode and there's just no way to do it!!
I've tried using the EM and the sp_dboption. Without that master db restored I'm in deep doo-doo!
Any ideas how I can handle this? Any help/suggestions is greatly appreciated.
TIA
Cathy
SQL Server 7( SP4), NT4.0
Hi,
To restore the master database you have start the SQL server itself in
Single user mode.
1. Stop the MSSQL server and sql agent service
2. Go to command prompt and execute below command,.
sqlservr.exe -c -m
3. Login to sql server using query analyzer and retore the master database.
4. After the restore .. go to old comamnd prompt and press CONTRL and C to
close the old session
5. Go to control panel and start the MSSQL server and Sql agent service.
Note:
I recommend you to restore the master database first followed with other
databases.
Thanks
Hari
MCDBA
"Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in message
news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.4ax.com...
> Hi:
> I've got a problem that's causing me headaches! I'm using Veritas Backup
Exec to perform my db backups. I've just
> recently taken over the DBA responsibilities and one of the first things
I've wanted to do is test those backups since
> we've never had occassion to use the backups. I have a test system that
I'm using and have it configured exactly as the
> production system.
> After installing MS SQL Server 7 putting on SP4 and all the security
patches I try to restore all the databases. I can
> get all my databases restored except the master db that contains all my
security info. I keep getting the message
> "must be in single user mode". I've tried to set the master db into
single user mode and there's just no way to do it!!
> I've tried using the EM and the sp_dboption. Without that master db
restored I'm in deep doo-doo!
> Any ideas how I can handle this? Any help/suggestions is greatly
appreciated.
> TIA
> Cathy
> SQL Server 7( SP4), NT4.0
|||This method will work. I think Hari copied and pasted the answer directly
from Books Online! I would have provided exactly the same answer. I
probably would have also pointed you at Books Online (within your SQL Server
program group). BACKUP and RESTORE information and examples can be found
there. Knowing how to restore the data that you are backing up is
important. Cathy, you are smart to be doing your research now (instead of
when the entire company is waiting for you to restore the data).
Keith
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi,
> To restore the master database you have start the SQL server itself in
> Single user mode.
> 1. Stop the MSSQL server and sql agent service
> 2. Go to command prompt and execute below command,.
> sqlservr.exe -c -m
> 3. Login to sql server using query analyzer and retore the master
database.
> 4. After the restore .. go to old comamnd prompt and press CONTRL and C to
> close the old session
> 5. Go to control panel and start the MSSQL server and Sql agent service.
> Note:
> I recommend you to restore the master database first followed with other
> databases.
> Thanks
> Hari
> MCDBA
>
> "Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in
message
> news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.4ax.com...
> Exec to perform my db backups. I've just
> I've wanted to do is test those backups since
> I'm using and have it configured exactly as the
> patches I try to restore all the databases. I can
> security info. I keep getting the message
> single user mode and there's just no way to do it!!
> restored I'm in deep doo-doo!
> appreciated.
>
|||Thanks guys for your prompt reply.
I thought I was starting the SQL Server in single user mode: I'm using the command "net start mssqlserver -m"... Is this not correct?
"Keith Kratochvil" wrote:
> This method will work. I think Hari copied and pasted the answer directly
> from Books Online! I would have provided exactly the same answer. I
> probably would have also pointed you at Books Online (within your SQL Server
> program group). BACKUP and RESTORE information and examples can be found
> there. Knowing how to restore the data that you are backing up is
> important. Cathy, you are smart to be doing your research now (instead of
> when the entire company is waiting for you to restore the data).
> --
> Keith
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> database.
> message
>
|||No, NET START starts the MSSQLSERVER service, and the -m is a parameter to
NET START, which doesn't take any parameters so it wouldn't know what to do
with it.
You need to run the sqlservr executable, and the pass the -m flag to it as
Hari suggested.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"caeriel" <caeriel@.discussions.microsoft.com> wrote in message
news:9542FB0A-84B5-4DDD-8498-87C5F4A91D4A@.microsoft.com...
> Thanks guys for your prompt reply.
> I thought I was starting the SQL Server in single user mode: I'm using
the command "net start mssqlserver -m"... Is this not correct?[vbcol=seagreen]
> "Keith Kratochvil" wrote:
directly[vbcol=seagreen]
Server[vbcol=seagreen]
found[vbcol=seagreen]
of[vbcol=seagreen]
C to[vbcol=seagreen]
service.[vbcol=seagreen]
other[vbcol=seagreen]
Backup[vbcol=seagreen]
things[vbcol=seagreen]
that[vbcol=seagreen]
my[vbcol=seagreen]
|||Keith: I'm still unable to restore the master database...
I tried starting the server as you & Hari instructed; the server started in single user mode. However, the restore has to be issued from the Veritas software interface. Here's the SQL Server log.
*************************************
================================================== ====================
Job server: Test
Job name: MasterDB NoRecov 8-03
Job started: Tuesday, August 03, 2004 at 10:08:07 AM
Job type: Restore
Job Log: BEX41.txt
================================================== ====================
Drive and media information from media mount:
Robotic Library Name:
Drive Name: Quantum 1
Slot: 0
Media GUID: {2E9659D2-C5B5-43BF-BD2A-502B2E1054E1}
Media Label: Test Wk1 Monday
Overwrite Protection Time Left: None
Append Time Left: Infinite
================================================== ====================
Job Operation - Restore
================================================== ====================
Restoring data to Test .
Storage Media #1: " Media created 8/2/04 08:24:56 PM "
Backup performed on 8/3/04 at 8:12 AM
Backup set #6: " Full Backup "
Restore started on 8/3/04 at 10:10:35 AM.
An error occurred on a query to database master.
^ ^ ^ ^ ^
SQL Server returned the following error message:
RESTORE DATABASE must be used in single user mode when trying to restore the master database.
^ ^ ^ ^ ^
Restore completed on 8/3/04 at 10:13:42 AM.
1 item was skipped.
Processed 0 bytes in 3 minutes and 7 seconds.
Throughput rate: 0.0 MB/min
================================================== ====================
Job ended: Tuesday, August 03, 2004 at 10:14:31 AM
Job completion status: Failed
================================================== ====================
************************************************** **
2004-08-03 09:55:16.51 kernel Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
Nov 27 1998 22:20:07
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
2004-08-03 09:55:16.51 kernel Copyright (C) 1988-1997 Microsoft Corporation.
2004-08-03 09:55:16.52 kernel All rights reserved.
2004-08-03 09:55:16.52 kernel Logging SQL Server messages in file 'D:\MSSQL7\log\ERRORLOG'.
2004-08-03 09:55:16.58 kernel initconfig: Number of user connections limited to 32767.
2004-08-03 09:55:16.58 kernel SQL Server is starting at priority class 'normal'(1 CPU detected).
2004-08-03 09:55:16.70 kernel User Mode Scheduler configured for thread processing
2004-08-03 09:55:17.96 server Directory Size: 10801
2004-08-03 09:55:18.05 spid1 Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks
2004-08-03 09:55:18.05 kernel Attempting to initialize Distributed Transaction Coordinator.
2004-08-03 09:55:18.82 spid1 Failed to obtain TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE
2004-08-03 09:55:18.83 spid1 Warning ******************
2004-08-03 09:55:18.83 spid1 SQL Server started in single user mode. Updates allowed to system catalogs.
2004-08-03 09:55:18.84 spid1 Warning ******************
2004-08-03 09:55:18.85 spid1 Starting up database 'master'.
2004-08-03 09:55:18.85 spid1 Opening file D:\MSSQL7\data\master.mdf.
2004-08-03 09:55:18.89 spid1 Opening file D:\MSSQL7\data\mastlog.ldf.
2004-08-03 09:55:18.99 spid1 Loading SQL Server's Unicode collation.
2004-08-03 09:55:19.01 spid1 Loading SQL Server's non-Unicode sort order and character set.
2004-08-03 09:55:19.24 spid1 4 transactions rolled forward in database 'master' (1).
2004-08-03 09:55:19.25 spid1 0 transactions rolled back in database 'master' (1).
2004-08-03 09:55:19.35 spid1 Starting up database 'model'.
2004-08-03 09:55:19.36 spid1 Opening file D:\MSSQL7\DATA\model.mdf.
2004-08-03 09:55:19.43 spid1 Opening file D:\MSSQL7\DATA\modellog.ldf.
2004-08-03 09:55:19.63 spid1 Clearing tempdb database.
2004-08-03 09:55:19.69 spid1 Creating file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:19.87 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:19.97 spid1 Creating file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:19.99 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:20.12 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:20.14 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:21.15 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:21.25 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:21.34 spid1 Starting up database 'tempdb'.
2004-08-03 09:55:21.38 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:21.41 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:21.69 spid1 Server name is 'AOATESTBOX'.
2004-08-03 09:55:21.70 kernel Using 'SQLEVN70.DLL' version '7.00.623'.
2004-08-03 09:55:21.71 kernel Using 'OPENDS60.DLL' version '7.00.00.0623'.
2004-08-03 09:55:21.71 ods Using 'SSNMPN70.DLL' version '7.0.623' to listen on '\\.\pipe\sql\query'.
2004-08-03 09:55:21.72 ods Using 'SSMSSO70.DLL' version '7.0.623' to listen on '3180'.
2004-08-03 09:55:21.82 ods Using 'SSMSRP70.DLL' version '7.0.623' to listen on 'AOATESTBOX'.
2004-08-03 09:55:22.68 spid5 Starting up database 'msdb'.
2004-08-03 09:55:22.68 spid5 Opening file D:\MSSQL7\DATA\msdbdata.mdf.
2004-08-03 09:55:22.70 spid6 Starting up database 'pubs'.
2004-08-03 09:55:22.70 spid6 Opening file D:\MSSQL7\DATA\pubs.mdf.
2004-08-03 09:55:22.72 spid7 Starting up database 'Northwind'.
2004-08-03 09:55:22.72 spid7 Opening file D:\MSSQL7\DATA\northwnd.mdf.
2004-08-03 09:55:22.82 spid5 Opening file D:\MSSQL7\DATA\msdblog.ldf.
2004-08-03 09:55:22.85 spid7 Opening file D:\MSSQL7\DATA\northwnd.ldf.
2004-08-03 09:55:22.87 spid6 Opening file D:\MSSQL7\DATA\pubs_log.ldf.
2004-08-03 09:55:23.55 spid1 Recovery complete.
2004-08-03 09:55:23.56 spid1 SQL Server's Unicode collation is:
2004-08-03 09:55:23.57 spid1 'English' (ID = 1033).
2004-08-03 09:55:23.57 spid1 comparison style = 196609.
2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode sort order is:
2004-08-03 09:55:23.58 spid1 'nocase_850' (ID = 42).
2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode character set is:
2004-08-03 09:55:23.58 spid1 'cp850' (ID = 2).
2004-08-03 09:57:00.02 spid6 Using 'xpstar.dll' version '1998.11.13' to execute extended stored procedure 'sp_MSgetversion'.
2004-08-03 10:16:44.05 kernel SQL Server shutdown due to Ctrl-C or Ctrl-Break signal.
************************************************** *****
Here's the Veritas log:
************************************************
************************************************
"Keith Kratochvil" wrote:
> This method will work. I think Hari copied and pasted the answer directly
> from Books Online! I would have provided exactly the same answer. I
> probably would have also pointed you at Books Online (within your SQL Server
> program group). BACKUP and RESTORE information and examples can be found
> there. Knowing how to restore the data that you are backing up is
> important. Cathy, you are smart to be doing your research now (instead of
> when the entire company is waiting for you to restore the data).
> --
> Keith
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> database.
> message
>
|||This may be a problem with Veritas software. Can you get the backup files
from Veritas, and do the restore manually through Query Analyzer?
Restore database master from disk = "C:\whatever"
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in message
news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.4ax.com...
> Hi:
> I've got a problem that's causing me headaches! I'm using Veritas Backup
Exec to perform my db backups. I've just
> recently taken over the DBA responsibilities and one of the first things
I've wanted to do is test those backups since
> we've never had occassion to use the backups. I have a test system that
I'm using and have it configured exactly as the
> production system.
> After installing MS SQL Server 7 putting on SP4 and all the security
patches I try to restore all the databases. I can
> get all my databases restored except the master db that contains all my
security info. I keep getting the message
> "must be in single user mode". I've tried to set the master db into
single user mode and there's just no way to do it!!
> I've tried using the EM and the sp_dboption. Without that master db
restored I'm in deep doo-doo!
> Any ideas how I can handle this? Any help/suggestions is greatly
appreciated.
> TIA
> Cathy
> SQL Server 7( SP4), NT4.0
|||Try restoring the backup files to disk using Veritas and then restore the
database using T-SQL executed via Query Analyzer.
RESTORE DATABASE foo FROM DISK = 'x:\foo.bak'
(syntax and examples within Books Online)
Keith
"caeriel" <caeriel@.discussions.microsoft.com> wrote in message
news:B6837C91-9566-4CB1-B3D0-73DB610E6232@.microsoft.com...
> Keith: I'm still unable to restore the master database...
> I tried starting the server as you & Hari instructed; the server started
in single user mode. However, the restore has to be issued from the Veritas
software interface. Here's the SQL Server log.
> *************************************
> ================================================== ====================
> Job server: Test
> Job name: MasterDB NoRecov 8-03
> Job started: Tuesday, August 03, 2004 at 10:08:07 AM
> Job type: Restore
> Job Log: BEX41.txt
> ================================================== ====================
> Drive and media information from media mount:
> Robotic Library Name:
> Drive Name: Quantum 1
> Slot: 0
> Media GUID: {2E9659D2-C5B5-43BF-BD2A-502B2E1054E1}
> Media Label: Test Wk1 Monday
> Overwrite Protection Time Left: None
> Append Time Left: Infinite
> ================================================== ====================
> Job Operation - Restore
> ================================================== ====================
> Restoring data to Test .
> Storage Media #1: " Media created 8/2/04 08:24:56 PM "
> Backup performed on 8/3/04 at 8:12 AM
> Backup set #6: " Full Backup "
> Restore started on 8/3/04 at 10:10:35 AM.
> An error occurred on a query to database master.
> ^ ^ ^ ^ ^
> SQL Server returned the following error message:
> RESTORE DATABASE must be used in single user mode when trying to restore
the master database.
> ^ ^ ^ ^ ^
> Restore completed on 8/3/04 at 10:13:42 AM.
> 1 item was skipped.
>
> Processed 0 bytes in 3 minutes and 7 seconds.
> Throughput rate: 0.0 MB/min
> ================================================== ====================
> Job ended: Tuesday, August 03, 2004 at 10:14:31 AM
> Job completion status: Failed
> ================================================== ====================
> ************************************************** **
> 2004-08-03 09:55:16.51 kernel Microsoft SQL Server 7.00 - 7.00.623
(Intel X86)
> Nov 27 1998 22:20:07
> Copyright (c) 1988-1998 Microsoft Corporation
> Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
> 2004-08-03 09:55:16.51 kernel Copyright (C) 1988-1997 Microsoft
Corporation.
> 2004-08-03 09:55:16.52 kernel All rights reserved.
> 2004-08-03 09:55:16.52 kernel Logging SQL Server messages in file
'D:\MSSQL7\log\ERRORLOG'.
> 2004-08-03 09:55:16.58 kernel initconfig: Number of user connections
limited to 32767.
> 2004-08-03 09:55:16.58 kernel SQL Server is starting at priority class
'normal'(1 CPU detected).
> 2004-08-03 09:55:16.70 kernel User Mode Scheduler configured for thread
processing
> 2004-08-03 09:55:17.96 server Directory Size: 10801
> 2004-08-03 09:55:18.05 spid1 Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks
> 2004-08-03 09:55:18.05 kernel Attempting to initialize Distributed
Transaction Coordinator.
> 2004-08-03 09:55:18.82 spid1 Failed to obtain
TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE
> 2004-08-03 09:55:18.83 spid1 Warning ******************
> 2004-08-03 09:55:18.83 spid1 SQL Server started in single user mode.
Updates allowed to system catalogs.
> 2004-08-03 09:55:18.84 spid1 Warning ******************
> 2004-08-03 09:55:18.85 spid1 Starting up database 'master'.
> 2004-08-03 09:55:18.85 spid1 Opening file D:\MSSQL7\data\master.mdf.
> 2004-08-03 09:55:18.89 spid1 Opening file D:\MSSQL7\data\mastlog.ldf.
> 2004-08-03 09:55:18.99 spid1 Loading SQL Server's Unicode collation.
> 2004-08-03 09:55:19.01 spid1 Loading SQL Server's non-Unicode sort
order and character set.
> 2004-08-03 09:55:19.24 spid1 4 transactions rolled forward in database
'master' (1).
> 2004-08-03 09:55:19.25 spid1 0 transactions rolled back in database
'master' (1).
> 2004-08-03 09:55:19.35 spid1 Starting up database 'model'.
> 2004-08-03 09:55:19.36 spid1 Opening file D:\MSSQL7\DATA\model.mdf.
> 2004-08-03 09:55:19.43 spid1 Opening file D:\MSSQL7\DATA\modellog.ldf.
> 2004-08-03 09:55:19.63 spid1 Clearing tempdb database.
> 2004-08-03 09:55:19.69 spid1 Creating file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:19.87 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:19.97 spid1 Creating file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:19.99 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:20.12 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:20.14 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.15 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:21.25 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.34 spid1 Starting up database 'tempdb'.
> 2004-08-03 09:55:21.38 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:21.41 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.69 spid1 Server name is 'AOATESTBOX'.
> 2004-08-03 09:55:21.70 kernel Using 'SQLEVN70.DLL' version '7.00.623'.
> 2004-08-03 09:55:21.71 kernel Using 'OPENDS60.DLL' version
'7.00.00.0623'.
> 2004-08-03 09:55:21.71 ods Using 'SSNMPN70.DLL' version '7.0.623' to
listen on '\\.\pipe\sql\query'.
> 2004-08-03 09:55:21.72 ods Using 'SSMSSO70.DLL' version '7.0.623' to
listen on '3180'.
> 2004-08-03 09:55:21.82 ods Using 'SSMSRP70.DLL' version '7.0.623' to
listen on 'AOATESTBOX'.
> 2004-08-03 09:55:22.68 spid5 Starting up database 'msdb'.
> 2004-08-03 09:55:22.68 spid5 Opening file D:\MSSQL7\DATA\msdbdata.mdf.
> 2004-08-03 09:55:22.70 spid6 Starting up database 'pubs'.
> 2004-08-03 09:55:22.70 spid6 Opening file D:\MSSQL7\DATA\pubs.mdf.
> 2004-08-03 09:55:22.72 spid7 Starting up database 'Northwind'.
> 2004-08-03 09:55:22.72 spid7 Opening file D:\MSSQL7\DATA\northwnd.mdf.
> 2004-08-03 09:55:22.82 spid5 Opening file D:\MSSQL7\DATA\msdblog.ldf.
> 2004-08-03 09:55:22.85 spid7 Opening file D:\MSSQL7\DATA\northwnd.ldf.
> 2004-08-03 09:55:22.87 spid6 Opening file D:\MSSQL7\DATA\pubs_log.ldf.
> 2004-08-03 09:55:23.55 spid1 Recovery complete.
> 2004-08-03 09:55:23.56 spid1 SQL Server's Unicode collation is:
> 2004-08-03 09:55:23.57 spid1 'English' (ID = 1033).
> 2004-08-03 09:55:23.57 spid1 comparison style = 196609.
> 2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode sort order is:
> 2004-08-03 09:55:23.58 spid1 'nocase_850' (ID = 42).
> 2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode character set is:
> 2004-08-03 09:55:23.58 spid1 'cp850' (ID = 2).
> 2004-08-03 09:57:00.02 spid6 Using 'xpstar.dll' version '1998.11.13' to
execute extended stored procedure 'sp_MSgetversion'.
> 2004-08-03 10:16:44.05 kernel SQL Server shutdown due to Ctrl-C or
Ctrl-Break signal.[vbcol=seagreen]
> ************************************************** *****
> Here's the Veritas log:
> ************************************************
> ************************************************
> "Keith Kratochvil" wrote:
directly[vbcol=seagreen]
Server[vbcol=seagreen]
found[vbcol=seagreen]
of[vbcol=seagreen]
C to[vbcol=seagreen]
service.[vbcol=seagreen]
other[vbcol=seagreen]
Backup[vbcol=seagreen]
things[vbcol=seagreen]
that[vbcol=seagreen]
my[vbcol=seagreen]
Can't restore master db!
Hi:
I've got a problem that's causing me headaches! I'm using Veritas Backup Exe
c to perform my db backups. I've just
recently taken over the DBA responsibilities and one of the first things I'v
e wanted to do is test those backups since
we've never had occassion to use the backups. I have a test system that I'm
using and have it configured exactly as the
production system.
After installing MS SQL Server 7 putting on SP4 and all the security patches
I try to restore all the databases. I can
get all my databases restored except the master db that contains all my secu
rity info. I keep getting the message
"must be in single user mode". I've tried to set the master db into single
user mode and there's just no way to do it!!
I've tried using the EM and the sp_dboption. Without that master db restore
d I'm in deep doo-doo!
Any ideas how I can handle this' Any help/suggestions is greatly appreciat
ed.
TIA
Cathy
SQL Server 7( SP4), NT4.0Hi,
To restore the master database you have start the SQL server itself in
Single user mode.
1. Stop the MSSQL server and sql agent service
2. Go to command prompt and execute below command,.
sqlservr.exe -c -m
3. Login to sql server using query analyzer and retore the master database.
4. After the restore .. go to old comamnd prompt and press CONTRL and C to
close the old session
5. Go to control panel and start the MSSQL server and Sql agent service.
Note:
I recommend you to restore the master database first followed with other
databases.
Thanks
Hari
MCDBA
"Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in message
news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.
4ax.com...
> Hi:
> I've got a problem that's causing me headaches! I'm using Veritas Backup
Exec to perform my db backups. I've just
> recently taken over the DBA responsibilities and one of the first things
I've wanted to do is test those backups since
> we've never had occassion to use the backups. I have a test system that
I'm using and have it configured exactly as the
> production system.
> After installing MS SQL Server 7 putting on SP4 and all the security
patches I try to restore all the databases. I can
> get all my databases restored except the master db that contains all my
security info. I keep getting the message
> "must be in single user mode". I've tried to set the master db into
single user mode and there's just no way to do it!!
> I've tried using the EM and the sp_dboption. Without that master db
restored I'm in deep doo-doo!
> Any ideas how I can handle this' Any help/suggestions is greatly
appreciated.
> TIA
> Cathy
> SQL Server 7( SP4), NT4.0|||This method will work. I think Hari copied and pasted the answer directly
from Books Online! I would have provided exactly the same answer. I
probably would have also pointed you at Books Online (within your SQL Server
program group). BACKUP and RESTORE information and examples can be found
there. Knowing how to restore the data that you are backing up is
important. Cathy, you are smart to be doing your research now (instead of
when the entire company is waiting for you to restore the data).
Keith
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi,
> To restore the master database you have start the SQL server itself in
> Single user mode.
> 1. Stop the MSSQL server and sql agent service
> 2. Go to command prompt and execute below command,.
> sqlservr.exe -c -m
> 3. Login to sql server using query analyzer and retore the master
database.
> 4. After the restore .. go to old comamnd prompt and press CONTRL and C to
> close the old session
> 5. Go to control panel and start the MSSQL server and Sql agent service.
> Note:
> I recommend you to restore the master database first followed with other
> databases.
> Thanks
> Hari
> MCDBA
>
> "Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in
message
> news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.
4ax.com...
> Exec to perform my db backups. I've just
> I've wanted to do is test those backups since
> I'm using and have it configured exactly as the
> patches I try to restore all the databases. I can
> security info. I keep getting the message
> single user mode and there's just no way to do it!!
> restored I'm in deep doo-doo!
> appreciated.
>|||Thanks guys for your prompt reply.
I thought I was starting the SQL Server in single user mode: I'm using the
command "net start mssqlserver -m"... Is this not correct'
"Keith Kratochvil" wrote:
> This method will work. I think Hari copied and pasted the answer directly
> from Books Online! I would have provided exactly the same answer. I
> probably would have also pointed you at Books Online (within your SQL Serv
er
> program group). BACKUP and RESTORE information and examples can be found
> there. Knowing how to restore the data that you are backing up is
> important. Cathy, you are smart to be doing your research now (instead of
> when the entire company is waiting for you to restore the data).
> --
> Keith
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> database.
> message
>|||No, NET START starts the MSSQLSERVER service, and the -m is a parameter to
NET START, which doesn't take any parameters so it wouldn't know what to do
with it.
You need to run the sqlservr executable, and the pass the -m flag to it as
Hari suggested.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"caeriel" <caeriel@.discussions.microsoft.com> wrote in message
news:9542FB0A-84B5-4DDD-8498-87C5F4A91D4A@.microsoft.com...
> Thanks guys for your prompt reply.
> I thought I was starting the SQL Server in single user mode: I'm using
the command "net start mssqlserver -m"... Is this not correct'[vbcol=seagreen]
> "Keith Kratochvil" wrote:
>
directly[vbcol=seagreen]
Server[vbcol=seagreen]
found[vbcol=seagreen]
of[vbcol=seagreen]
C to[vbcol=seagreen]
service.[vbcol=seagreen]
other[vbcol=seagreen]
Backup[vbcol=seagreen]
things[vbcol=seagreen]
that[vbcol=seagreen]
my[vbcol=seagreen]|||Keith: I'm still unable to restore the master database...
I tried starting the server as you & Hari instructed; the server started in
single user mode. However, the restore has to be issued from the Veritas so
ftware interface. Here's the SQL Server log.
*************************************
========================================
==============================
Job server: Test
Job name: MasterDB NoRecov 8-03
Job started: Tuesday, August 03, 2004 at 10:08:07 AM
Job type: Restore
Job Log: BEX41.txt
========================================
==============================
Drive and media information from media mount:
Robotic Library Name:
Drive Name: Quantum 1
Slot: 0
Media GUID: {2E9659D2-C5B5-43BF-BD2A-502B2E1054E1}
Media Label: Test Wk1 Monday
Overwrite Protection Time Left: None
Append Time Left: Infinite
========================================
==============================
Job Operation - Restore
========================================
==============================
Restoring data to Test .
Storage Media #1: " Media created 8/2/04 08:24:56 PM "
Backup performed on 8/3/04 at 8:12 AM
Backup set #6: " Full Backup "
Restore started on 8/3/04 at 10:10:35 AM.
An error occurred on a query to database master.
^ ^ ^ ^ ^
SQL Server returned the following error message:
RESTORE DATABASE must be used in single user mode when trying to restore the
master database.
^ ^ ^ ^ ^
Restore completed on 8/3/04 at 10:13:42 AM.
1 item was skipped.
Processed 0 bytes in 3 minutes and 7 seconds.
Throughput rate: 0.0 MB/min
---
========================================
==============================
Job ended: Tuesday, August 03, 2004 at 10:14:31 AM
Job completion status: Failed
========================================
==============================
****************************************
************
2004-08-03 09:55:16.51 kernel Microsoft SQL Server 7.00 - 7.00.623 (Intel
X86)
Nov 27 1998 22:20:07
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
2004-08-03 09:55:16.51 kernel Copyright (C) 1988-1997 Microsoft Corporatio
n.
2004-08-03 09:55:16.52 kernel All rights reserved.
2004-08-03 09:55:16.52 kernel Logging SQL Server messages in file 'D:\MSSQ
L7\log\ERRORLOG'.
2004-08-03 09:55:16.58 kernel initconfig: Number of user connections limit
ed to 32767.
2004-08-03 09:55:16.58 kernel SQL Server is starting at priority class 'no
rmal'(1 CPU detected).
2004-08-03 09:55:16.70 kernel User Mode Scheduler configured for thread pr
ocessing
2004-08-03 09:55:17.96 server Directory Size: 10801
2004-08-03 09:55:18.05 spid1 Using dynamic lock allocation. [2500] Lo
ck Blocks, [5000] Lock Owner Blocks
2004-08-03 09:55:18.05 kernel Attempting to initialize Distributed Transac
tion Coordinator.
2004-08-03 09:55:18.82 spid1 Failed to obtain TransactionDispenserInterfa
ce: XACT_E_TMNOTAVAILABLE
2004-08-03 09:55:18.83 spid1 Warning ******************
2004-08-03 09:55:18.83 spid1 SQL Server started in single user mode. Upda
tes allowed to system catalogs.
2004-08-03 09:55:18.84 spid1 Warning ******************
2004-08-03 09:55:18.85 spid1 Starting up database 'master'.
2004-08-03 09:55:18.85 spid1 Opening file D:\MSSQL7\data\master.mdf.
2004-08-03 09:55:18.89 spid1 Opening file D:\MSSQL7\data\mastlog.ldf.
2004-08-03 09:55:18.99 spid1 Loading SQL Server's Unicode collation.
2004-08-03 09:55:19.01 spid1 Loading SQL Server's non-Unicode sort order
and character set.
2004-08-03 09:55:19.24 spid1 4 transactions rolled forward in database 'm
aster' (1).
2004-08-03 09:55:19.25 spid1 0 transactions rolled back in database 'mast
er' (1).
2004-08-03 09:55:19.35 spid1 Starting up database 'model'.
2004-08-03 09:55:19.36 spid1 Opening file D:\MSSQL7\DATA\model.mdf.
2004-08-03 09:55:19.43 spid1 Opening file D:\MSSQL7\DATA\modellog.ldf.
2004-08-03 09:55:19.63 spid1 Clearing tempdb database.
2004-08-03 09:55:19.69 spid1 Creating file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:19.87 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:19.97 spid1 Creating file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:19.99 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:20.12 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:20.14 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:21.15 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:21.25 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:21.34 spid1 Starting up database 'tempdb'.
2004-08-03 09:55:21.38 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:21.41 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:21.69 spid1 Server name is 'AOATESTBOX'.
2004-08-03 09:55:21.70 kernel Using 'SQLEVN70.DLL' version '7.00.623'.
2004-08-03 09:55:21.71 kernel Using 'OPENDS60.DLL' version '7.00.00.0623'.
2004-08-03 09:55:21.71 ods Using 'SSNMPN70.DLL' version '7.0.623' to li
sten on '\\.\pipe\sql\query'.
2004-08-03 09:55:21.72 ods Using 'SSMSSO70.DLL' version '7.0.623' to li
sten on '3180'.
2004-08-03 09:55:21.82 ods Using 'SSMSRP70.DLL' version '7.0.623' to li
sten on 'AOATESTBOX'.
2004-08-03 09:55:22.68 spid5 Starting up database 'msdb'.
2004-08-03 09:55:22.68 spid5 Opening file D:\MSSQL7\DATA\msdbdata.mdf.
2004-08-03 09:55:22.70 spid6 Starting up database 'pubs'.
2004-08-03 09:55:22.70 spid6 Opening file D:\MSSQL7\DATA\pubs.mdf.
2004-08-03 09:55:22.72 spid7 Starting up database 'Northwind'.
2004-08-03 09:55:22.72 spid7 Opening file D:\MSSQL7\DATA\northwnd.mdf.
2004-08-03 09:55:22.82 spid5 Opening file D:\MSSQL7\DATA\msdblog.ldf.
2004-08-03 09:55:22.85 spid7 Opening file D:\MSSQL7\DATA\northwnd.ldf.
2004-08-03 09:55:22.87 spid6 Opening file D:\MSSQL7\DATA\pubs_log.ldf.
2004-08-03 09:55:23.55 spid1 Recovery complete.
2004-08-03 09:55:23.56 spid1 SQL Server's Unicode collation is:
2004-08-03 09:55:23.57 spid1 'English' (ID = 1033).
2004-08-03 09:55:23.57 spid1 comparison style = 196609.
2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode sort order is:
2004-08-03 09:55:23.58 spid1 'nocase_850' (ID = 42).
2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode character set is:
2004-08-03 09:55:23.58 spid1 'cp850' (ID = 2).
2004-08-03 09:57:00.02 spid6 Using 'xpstar.dll' version '1998.11.13' to e
xecute extended stored procedure 'sp_MSgetversion'.
2004-08-03 10:16:44.05 kernel SQL Server shutdown due to Ctrl-C or Ctrl-Br
eak signal.
****************************************
***************
Here's the Veritas log:
****************************************
********
****************************************
********
"Keith Kratochvil" wrote:
> This method will work. I think Hari copied and pasted the answer directly
> from Books Online! I would have provided exactly the same answer. I
> probably would have also pointed you at Books Online (within your SQL Serv
er
> program group). BACKUP and RESTORE information and examples can be found
> there. Knowing how to restore the data that you are backing up is
> important. Cathy, you are smart to be doing your research now (instead of
> when the entire company is waiting for you to restore the data).
> --
> Keith
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> database.
> message
>|||This may be a problem with Veritas software. Can you get the backup files
from Veritas, and do the restore manually through Query Analyzer?
Restore database master from disk = "C:\whatever"
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in message
news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.
4ax.com...
> Hi:
> I've got a problem that's causing me headaches! I'm using Veritas Backup
Exec to perform my db backups. I've just
> recently taken over the DBA responsibilities and one of the first things
I've wanted to do is test those backups since
> we've never had occassion to use the backups. I have a test system that
I'm using and have it configured exactly as the
> production system.
> After installing MS SQL Server 7 putting on SP4 and all the security
patches I try to restore all the databases. I can
> get all my databases restored except the master db that contains all my
security info. I keep getting the message
> "must be in single user mode". I've tried to set the master db into
single user mode and there's just no way to do it!!
> I've tried using the EM and the sp_dboption. Without that master db
restored I'm in deep doo-doo!
> Any ideas how I can handle this' Any help/suggestions is greatly
appreciated.
> TIA
> Cathy
> SQL Server 7( SP4), NT4.0|||Try restoring the backup files to disk using Veritas and then restore the
database using T-SQL executed via Query Analyzer.
RESTORE DATABASE foo FROM DISK = 'x:\foo.bak'
(syntax and examples within Books Online)
Keith
"caeriel" <caeriel@.discussions.microsoft.com> wrote in message
news:B6837C91-9566-4CB1-B3D0-73DB610E6232@.microsoft.com...
> Keith: I'm still unable to restore the master database...
> I tried starting the server as you & Hari instructed; the server started
in single user mode. However, the restore has to be issued from the Veritas
software interface. Here's the SQL Server log.
> *************************************
> ========================================
==============================
> Job server: Test
> Job name: MasterDB NoRecov 8-03
> Job started: Tuesday, August 03, 2004 at 10:08:07 AM
> Job type: Restore
> Job Log: BEX41.txt
> ========================================
==============================
> Drive and media information from media mount:
> Robotic Library Name:
> Drive Name: Quantum 1
> Slot: 0
> Media GUID: {2E9659D2-C5B5-43BF-BD2A-502B2E1054E1}
> Media Label: Test Wk1 Monday
> Overwrite Protection Time Left: None
> Append Time Left: Infinite
> ========================================
==============================
> Job Operation - Restore
> ========================================
==============================
> Restoring data to Test .
> Storage Media #1: " Media created 8/2/04 08:24:56 PM "
> Backup performed on 8/3/04 at 8:12 AM
> Backup set #6: " Full Backup "
> Restore started on 8/3/04 at 10:10:35 AM.
> An error occurred on a query to database master.
> ^ ^ ^ ^ ^
> SQL Server returned the following error message:
> RESTORE DATABASE must be used in single user mode when trying to restore
the master database.
> ^ ^ ^ ^ ^
> Restore completed on 8/3/04 at 10:13:42 AM.
> 1 item was skipped.
>
> Processed 0 bytes in 3 minutes and 7 seconds.
> Throughput rate: 0.0 MB/min
> ---
> ========================================
==============================
> Job ended: Tuesday, August 03, 2004 at 10:14:31 AM
> Job completion status: Failed
> ========================================
==============================
> ****************************************
************
> 2004-08-03 09:55:16.51 kernel Microsoft SQL Server 7.00 - 7.00.623
(Intel X86)
> Nov 27 1998 22:20:07
> Copyright (c) 1988-1998 Microsoft Corporation
> Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
> 2004-08-03 09:55:16.51 kernel Copyright (C) 1988-1997 Microsoft
Corporation.
> 2004-08-03 09:55:16.52 kernel All rights reserved.
> 2004-08-03 09:55:16.52 kernel Logging SQL Server messages in file
'D:\MSSQL7\log\ERRORLOG'.
> 2004-08-03 09:55:16.58 kernel initconfig: Number of user connections
limited to 32767.
> 2004-08-03 09:55:16.58 kernel SQL Server is starting at priority class
'normal'(1 CPU detected).
> 2004-08-03 09:55:16.70 kernel User Mode Scheduler configured for thread
processing
> 2004-08-03 09:55:17.96 server Directory Size: 10801
> 2004-08-03 09:55:18.05 spid1 Using dynamic lock allocation. [2500] Lock[/vb
col]
Blocks, [5000] Lock Owner Blocks[vbcol=seagreen]
> 2004-08-03 09:55:18.05 kernel Attempting to initialize Distributed
Transaction Coordinator.
> 2004-08-03 09:55:18.82 spid1 Failed to obtain
TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE
> 2004-08-03 09:55:18.83 spid1 Warning ******************
> 2004-08-03 09:55:18.83 spid1 SQL Server started in single user mode.
Updates allowed to system catalogs.
> 2004-08-03 09:55:18.84 spid1 Warning ******************
> 2004-08-03 09:55:18.85 spid1 Starting up database 'master'.
> 2004-08-03 09:55:18.85 spid1 Opening file D:\MSSQL7\data\master.mdf.
> 2004-08-03 09:55:18.89 spid1 Opening file D:\MSSQL7\data\mastlog.ldf.
> 2004-08-03 09:55:18.99 spid1 Loading SQL Server's Unicode collation.
> 2004-08-03 09:55:19.01 spid1 Loading SQL Server's non-Unicode sort
order and character set.
> 2004-08-03 09:55:19.24 spid1 4 transactions rolled forward in database
'master' (1).
> 2004-08-03 09:55:19.25 spid1 0 transactions rolled back in database
'master' (1).
> 2004-08-03 09:55:19.35 spid1 Starting up database 'model'.
> 2004-08-03 09:55:19.36 spid1 Opening file D:\MSSQL7\DATA\model.mdf.
> 2004-08-03 09:55:19.43 spid1 Opening file D:\MSSQL7\DATA\modellog.ldf.
> 2004-08-03 09:55:19.63 spid1 Clearing tempdb database.
> 2004-08-03 09:55:19.69 spid1 Creating file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:19.87 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:19.97 spid1 Creating file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:19.99 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:20.12 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:20.14 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.15 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:21.25 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.34 spid1 Starting up database 'tempdb'.
> 2004-08-03 09:55:21.38 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:21.41 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.69 spid1 Server name is 'AOATESTBOX'.
> 2004-08-03 09:55:21.70 kernel Using 'SQLEVN70.DLL' version '7.00.623'.
> 2004-08-03 09:55:21.71 kernel Using 'OPENDS60.DLL' version
'7.00.00.0623'.
> 2004-08-03 09:55:21.71 ods Using 'SSNMPN70.DLL' version '7.0.623' to
listen on '\\.\pipe\sql\query'.
> 2004-08-03 09:55:21.72 ods Using 'SSMSSO70.DLL' version '7.0.623' to
listen on '3180'.
> 2004-08-03 09:55:21.82 ods Using 'SSMSRP70.DLL' version '7.0.623' to
listen on 'AOATESTBOX'.
> 2004-08-03 09:55:22.68 spid5 Starting up database 'msdb'.
> 2004-08-03 09:55:22.68 spid5 Opening file D:\MSSQL7\DATA\msdbdata.mdf.
> 2004-08-03 09:55:22.70 spid6 Starting up database 'pubs'.
> 2004-08-03 09:55:22.70 spid6 Opening file D:\MSSQL7\DATA\pubs.mdf.
> 2004-08-03 09:55:22.72 spid7 Starting up database 'Northwind'.
> 2004-08-03 09:55:22.72 spid7 Opening file D:\MSSQL7\DATA\northwnd.mdf.
> 2004-08-03 09:55:22.82 spid5 Opening file D:\MSSQL7\DATA\msdblog.ldf.
> 2004-08-03 09:55:22.85 spid7 Opening file D:\MSSQL7\DATA\northwnd.ldf.
> 2004-08-03 09:55:22.87 spid6 Opening file D:\MSSQL7\DATA\pubs_log.ldf.
> 2004-08-03 09:55:23.55 spid1 Recovery complete.
> 2004-08-03 09:55:23.56 spid1 SQL Server's Unicode collation is:
> 2004-08-03 09:55:23.57 spid1 'English' (ID = 1033).
> 2004-08-03 09:55:23.57 spid1 comparison style = 196609.
> 2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode sort order is:
> 2004-08-03 09:55:23.58 spid1 'nocase_850' (ID = 42).
> 2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode character set is:
> 2004-08-03 09:55:23.58 spid1 'cp850' (ID = 2).
> 2004-08-03 09:57:00.02 spid6 Using 'xpstar.dll' version '1998.11.13' to
execute extended stored procedure 'sp_MSgetversion'.
> 2004-08-03 10:16:44.05 kernel SQL Server shutdown due to Ctrl-C or
Ctrl-Break signal.[vbcol=seagreen]
> ****************************************
***************
> Here's the Veritas log:
> ****************************************
********
> ****************************************
********
> "Keith Kratochvil" wrote:
>
directly[vbcol=seagreen]
Server[vbcol=seagreen]
found[vbcol=seagreen]
of[vbcol=seagreen]
C to[vbcol=seagreen]
service.[vbcol=seagreen]
other[vbcol=seagreen]
Backup[vbcol=seagreen]
things[vbcol=seagreen]
that[vbcol=seagreen]
my[vbcol=seagreen]
I've got a problem that's causing me headaches! I'm using Veritas Backup Exe
c to perform my db backups. I've just
recently taken over the DBA responsibilities and one of the first things I'v
e wanted to do is test those backups since
we've never had occassion to use the backups. I have a test system that I'm
using and have it configured exactly as the
production system.
After installing MS SQL Server 7 putting on SP4 and all the security patches
I try to restore all the databases. I can
get all my databases restored except the master db that contains all my secu
rity info. I keep getting the message
"must be in single user mode". I've tried to set the master db into single
user mode and there's just no way to do it!!
I've tried using the EM and the sp_dboption. Without that master db restore
d I'm in deep doo-doo!
Any ideas how I can handle this' Any help/suggestions is greatly appreciat
ed.
TIA
Cathy
SQL Server 7( SP4), NT4.0Hi,
To restore the master database you have start the SQL server itself in
Single user mode.
1. Stop the MSSQL server and sql agent service
2. Go to command prompt and execute below command,.
sqlservr.exe -c -m
3. Login to sql server using query analyzer and retore the master database.
4. After the restore .. go to old comamnd prompt and press CONTRL and C to
close the old session
5. Go to control panel and start the MSSQL server and Sql agent service.
Note:
I recommend you to restore the master database first followed with other
databases.
Thanks
Hari
MCDBA
"Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in message
news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.
4ax.com...
> Hi:
> I've got a problem that's causing me headaches! I'm using Veritas Backup
Exec to perform my db backups. I've just
> recently taken over the DBA responsibilities and one of the first things
I've wanted to do is test those backups since
> we've never had occassion to use the backups. I have a test system that
I'm using and have it configured exactly as the
> production system.
> After installing MS SQL Server 7 putting on SP4 and all the security
patches I try to restore all the databases. I can
> get all my databases restored except the master db that contains all my
security info. I keep getting the message
> "must be in single user mode". I've tried to set the master db into
single user mode and there's just no way to do it!!
> I've tried using the EM and the sp_dboption. Without that master db
restored I'm in deep doo-doo!
> Any ideas how I can handle this' Any help/suggestions is greatly
appreciated.
> TIA
> Cathy
> SQL Server 7( SP4), NT4.0|||This method will work. I think Hari copied and pasted the answer directly
from Books Online! I would have provided exactly the same answer. I
probably would have also pointed you at Books Online (within your SQL Server
program group). BACKUP and RESTORE information and examples can be found
there. Knowing how to restore the data that you are backing up is
important. Cathy, you are smart to be doing your research now (instead of
when the entire company is waiting for you to restore the data).
Keith
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi,
> To restore the master database you have start the SQL server itself in
> Single user mode.
> 1. Stop the MSSQL server and sql agent service
> 2. Go to command prompt and execute below command,.
> sqlservr.exe -c -m
> 3. Login to sql server using query analyzer and retore the master
database.
> 4. After the restore .. go to old comamnd prompt and press CONTRL and C to
> close the old session
> 5. Go to control panel and start the MSSQL server and Sql agent service.
> Note:
> I recommend you to restore the master database first followed with other
> databases.
> Thanks
> Hari
> MCDBA
>
> "Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in
message
> news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.
4ax.com...
> Exec to perform my db backups. I've just
> I've wanted to do is test those backups since
> I'm using and have it configured exactly as the
> patches I try to restore all the databases. I can
> security info. I keep getting the message
> single user mode and there's just no way to do it!!
> restored I'm in deep doo-doo!
> appreciated.
>|||Thanks guys for your prompt reply.
I thought I was starting the SQL Server in single user mode: I'm using the
command "net start mssqlserver -m"... Is this not correct'
"Keith Kratochvil" wrote:
> This method will work. I think Hari copied and pasted the answer directly
> from Books Online! I would have provided exactly the same answer. I
> probably would have also pointed you at Books Online (within your SQL Serv
er
> program group). BACKUP and RESTORE information and examples can be found
> there. Knowing how to restore the data that you are backing up is
> important. Cathy, you are smart to be doing your research now (instead of
> when the entire company is waiting for you to restore the data).
> --
> Keith
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> database.
> message
>|||No, NET START starts the MSSQLSERVER service, and the -m is a parameter to
NET START, which doesn't take any parameters so it wouldn't know what to do
with it.
You need to run the sqlservr executable, and the pass the -m flag to it as
Hari suggested.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"caeriel" <caeriel@.discussions.microsoft.com> wrote in message
news:9542FB0A-84B5-4DDD-8498-87C5F4A91D4A@.microsoft.com...
> Thanks guys for your prompt reply.
> I thought I was starting the SQL Server in single user mode: I'm using
the command "net start mssqlserver -m"... Is this not correct'[vbcol=seagreen]
> "Keith Kratochvil" wrote:
>
directly[vbcol=seagreen]
Server[vbcol=seagreen]
found[vbcol=seagreen]
of[vbcol=seagreen]
C to[vbcol=seagreen]
service.[vbcol=seagreen]
other[vbcol=seagreen]
Backup[vbcol=seagreen]
things[vbcol=seagreen]
that[vbcol=seagreen]
my[vbcol=seagreen]|||Keith: I'm still unable to restore the master database...
I tried starting the server as you & Hari instructed; the server started in
single user mode. However, the restore has to be issued from the Veritas so
ftware interface. Here's the SQL Server log.
*************************************
========================================
==============================
Job server: Test
Job name: MasterDB NoRecov 8-03
Job started: Tuesday, August 03, 2004 at 10:08:07 AM
Job type: Restore
Job Log: BEX41.txt
========================================
==============================
Drive and media information from media mount:
Robotic Library Name:
Drive Name: Quantum 1
Slot: 0
Media GUID: {2E9659D2-C5B5-43BF-BD2A-502B2E1054E1}
Media Label: Test Wk1 Monday
Overwrite Protection Time Left: None
Append Time Left: Infinite
========================================
==============================
Job Operation - Restore
========================================
==============================
Restoring data to Test .
Storage Media #1: " Media created 8/2/04 08:24:56 PM "
Backup performed on 8/3/04 at 8:12 AM
Backup set #6: " Full Backup "
Restore started on 8/3/04 at 10:10:35 AM.
An error occurred on a query to database master.
^ ^ ^ ^ ^
SQL Server returned the following error message:
RESTORE DATABASE must be used in single user mode when trying to restore the
master database.
^ ^ ^ ^ ^
Restore completed on 8/3/04 at 10:13:42 AM.
1 item was skipped.
Processed 0 bytes in 3 minutes and 7 seconds.
Throughput rate: 0.0 MB/min
---
========================================
==============================
Job ended: Tuesday, August 03, 2004 at 10:14:31 AM
Job completion status: Failed
========================================
==============================
****************************************
************
2004-08-03 09:55:16.51 kernel Microsoft SQL Server 7.00 - 7.00.623 (Intel
X86)
Nov 27 1998 22:20:07
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
2004-08-03 09:55:16.51 kernel Copyright (C) 1988-1997 Microsoft Corporatio
n.
2004-08-03 09:55:16.52 kernel All rights reserved.
2004-08-03 09:55:16.52 kernel Logging SQL Server messages in file 'D:\MSSQ
L7\log\ERRORLOG'.
2004-08-03 09:55:16.58 kernel initconfig: Number of user connections limit
ed to 32767.
2004-08-03 09:55:16.58 kernel SQL Server is starting at priority class 'no
rmal'(1 CPU detected).
2004-08-03 09:55:16.70 kernel User Mode Scheduler configured for thread pr
ocessing
2004-08-03 09:55:17.96 server Directory Size: 10801
2004-08-03 09:55:18.05 spid1 Using dynamic lock allocation. [2500] Lo
ck Blocks, [5000] Lock Owner Blocks
2004-08-03 09:55:18.05 kernel Attempting to initialize Distributed Transac
tion Coordinator.
2004-08-03 09:55:18.82 spid1 Failed to obtain TransactionDispenserInterfa
ce: XACT_E_TMNOTAVAILABLE
2004-08-03 09:55:18.83 spid1 Warning ******************
2004-08-03 09:55:18.83 spid1 SQL Server started in single user mode. Upda
tes allowed to system catalogs.
2004-08-03 09:55:18.84 spid1 Warning ******************
2004-08-03 09:55:18.85 spid1 Starting up database 'master'.
2004-08-03 09:55:18.85 spid1 Opening file D:\MSSQL7\data\master.mdf.
2004-08-03 09:55:18.89 spid1 Opening file D:\MSSQL7\data\mastlog.ldf.
2004-08-03 09:55:18.99 spid1 Loading SQL Server's Unicode collation.
2004-08-03 09:55:19.01 spid1 Loading SQL Server's non-Unicode sort order
and character set.
2004-08-03 09:55:19.24 spid1 4 transactions rolled forward in database 'm
aster' (1).
2004-08-03 09:55:19.25 spid1 0 transactions rolled back in database 'mast
er' (1).
2004-08-03 09:55:19.35 spid1 Starting up database 'model'.
2004-08-03 09:55:19.36 spid1 Opening file D:\MSSQL7\DATA\model.mdf.
2004-08-03 09:55:19.43 spid1 Opening file D:\MSSQL7\DATA\modellog.ldf.
2004-08-03 09:55:19.63 spid1 Clearing tempdb database.
2004-08-03 09:55:19.69 spid1 Creating file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:19.87 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:19.97 spid1 Creating file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:19.99 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:20.12 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:20.14 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:21.15 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:21.25 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:21.34 spid1 Starting up database 'tempdb'.
2004-08-03 09:55:21.38 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:21.41 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:21.69 spid1 Server name is 'AOATESTBOX'.
2004-08-03 09:55:21.70 kernel Using 'SQLEVN70.DLL' version '7.00.623'.
2004-08-03 09:55:21.71 kernel Using 'OPENDS60.DLL' version '7.00.00.0623'.
2004-08-03 09:55:21.71 ods Using 'SSNMPN70.DLL' version '7.0.623' to li
sten on '\\.\pipe\sql\query'.
2004-08-03 09:55:21.72 ods Using 'SSMSSO70.DLL' version '7.0.623' to li
sten on '3180'.
2004-08-03 09:55:21.82 ods Using 'SSMSRP70.DLL' version '7.0.623' to li
sten on 'AOATESTBOX'.
2004-08-03 09:55:22.68 spid5 Starting up database 'msdb'.
2004-08-03 09:55:22.68 spid5 Opening file D:\MSSQL7\DATA\msdbdata.mdf.
2004-08-03 09:55:22.70 spid6 Starting up database 'pubs'.
2004-08-03 09:55:22.70 spid6 Opening file D:\MSSQL7\DATA\pubs.mdf.
2004-08-03 09:55:22.72 spid7 Starting up database 'Northwind'.
2004-08-03 09:55:22.72 spid7 Opening file D:\MSSQL7\DATA\northwnd.mdf.
2004-08-03 09:55:22.82 spid5 Opening file D:\MSSQL7\DATA\msdblog.ldf.
2004-08-03 09:55:22.85 spid7 Opening file D:\MSSQL7\DATA\northwnd.ldf.
2004-08-03 09:55:22.87 spid6 Opening file D:\MSSQL7\DATA\pubs_log.ldf.
2004-08-03 09:55:23.55 spid1 Recovery complete.
2004-08-03 09:55:23.56 spid1 SQL Server's Unicode collation is:
2004-08-03 09:55:23.57 spid1 'English' (ID = 1033).
2004-08-03 09:55:23.57 spid1 comparison style = 196609.
2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode sort order is:
2004-08-03 09:55:23.58 spid1 'nocase_850' (ID = 42).
2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode character set is:
2004-08-03 09:55:23.58 spid1 'cp850' (ID = 2).
2004-08-03 09:57:00.02 spid6 Using 'xpstar.dll' version '1998.11.13' to e
xecute extended stored procedure 'sp_MSgetversion'.
2004-08-03 10:16:44.05 kernel SQL Server shutdown due to Ctrl-C or Ctrl-Br
eak signal.
****************************************
***************
Here's the Veritas log:
****************************************
********
****************************************
********
"Keith Kratochvil" wrote:
> This method will work. I think Hari copied and pasted the answer directly
> from Books Online! I would have provided exactly the same answer. I
> probably would have also pointed you at Books Online (within your SQL Serv
er
> program group). BACKUP and RESTORE information and examples can be found
> there. Knowing how to restore the data that you are backing up is
> important. Cathy, you are smart to be doing your research now (instead of
> when the entire company is waiting for you to restore the data).
> --
> Keith
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> database.
> message
>|||This may be a problem with Veritas software. Can you get the backup files
from Veritas, and do the restore manually through Query Analyzer?
Restore database master from disk = "C:\whatever"
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in message
news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.
4ax.com...
> Hi:
> I've got a problem that's causing me headaches! I'm using Veritas Backup
Exec to perform my db backups. I've just
> recently taken over the DBA responsibilities and one of the first things
I've wanted to do is test those backups since
> we've never had occassion to use the backups. I have a test system that
I'm using and have it configured exactly as the
> production system.
> After installing MS SQL Server 7 putting on SP4 and all the security
patches I try to restore all the databases. I can
> get all my databases restored except the master db that contains all my
security info. I keep getting the message
> "must be in single user mode". I've tried to set the master db into
single user mode and there's just no way to do it!!
> I've tried using the EM and the sp_dboption. Without that master db
restored I'm in deep doo-doo!
> Any ideas how I can handle this' Any help/suggestions is greatly
appreciated.
> TIA
> Cathy
> SQL Server 7( SP4), NT4.0|||Try restoring the backup files to disk using Veritas and then restore the
database using T-SQL executed via Query Analyzer.
RESTORE DATABASE foo FROM DISK = 'x:\foo.bak'
(syntax and examples within Books Online)
Keith
"caeriel" <caeriel@.discussions.microsoft.com> wrote in message
news:B6837C91-9566-4CB1-B3D0-73DB610E6232@.microsoft.com...
> Keith: I'm still unable to restore the master database...
> I tried starting the server as you & Hari instructed; the server started
in single user mode. However, the restore has to be issued from the Veritas
software interface. Here's the SQL Server log.
> *************************************
> ========================================
==============================
> Job server: Test
> Job name: MasterDB NoRecov 8-03
> Job started: Tuesday, August 03, 2004 at 10:08:07 AM
> Job type: Restore
> Job Log: BEX41.txt
> ========================================
==============================
> Drive and media information from media mount:
> Robotic Library Name:
> Drive Name: Quantum 1
> Slot: 0
> Media GUID: {2E9659D2-C5B5-43BF-BD2A-502B2E1054E1}
> Media Label: Test Wk1 Monday
> Overwrite Protection Time Left: None
> Append Time Left: Infinite
> ========================================
==============================
> Job Operation - Restore
> ========================================
==============================
> Restoring data to Test .
> Storage Media #1: " Media created 8/2/04 08:24:56 PM "
> Backup performed on 8/3/04 at 8:12 AM
> Backup set #6: " Full Backup "
> Restore started on 8/3/04 at 10:10:35 AM.
> An error occurred on a query to database master.
> ^ ^ ^ ^ ^
> SQL Server returned the following error message:
> RESTORE DATABASE must be used in single user mode when trying to restore
the master database.
> ^ ^ ^ ^ ^
> Restore completed on 8/3/04 at 10:13:42 AM.
> 1 item was skipped.
>
> Processed 0 bytes in 3 minutes and 7 seconds.
> Throughput rate: 0.0 MB/min
> ---
> ========================================
==============================
> Job ended: Tuesday, August 03, 2004 at 10:14:31 AM
> Job completion status: Failed
> ========================================
==============================
> ****************************************
************
> 2004-08-03 09:55:16.51 kernel Microsoft SQL Server 7.00 - 7.00.623
(Intel X86)
> Nov 27 1998 22:20:07
> Copyright (c) 1988-1998 Microsoft Corporation
> Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
> 2004-08-03 09:55:16.51 kernel Copyright (C) 1988-1997 Microsoft
Corporation.
> 2004-08-03 09:55:16.52 kernel All rights reserved.
> 2004-08-03 09:55:16.52 kernel Logging SQL Server messages in file
'D:\MSSQL7\log\ERRORLOG'.
> 2004-08-03 09:55:16.58 kernel initconfig: Number of user connections
limited to 32767.
> 2004-08-03 09:55:16.58 kernel SQL Server is starting at priority class
'normal'(1 CPU detected).
> 2004-08-03 09:55:16.70 kernel User Mode Scheduler configured for thread
processing
> 2004-08-03 09:55:17.96 server Directory Size: 10801
> 2004-08-03 09:55:18.05 spid1 Using dynamic lock allocation. [2500] Lock[/vb
col]
Blocks, [5000] Lock Owner Blocks[vbcol=seagreen]
> 2004-08-03 09:55:18.05 kernel Attempting to initialize Distributed
Transaction Coordinator.
> 2004-08-03 09:55:18.82 spid1 Failed to obtain
TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE
> 2004-08-03 09:55:18.83 spid1 Warning ******************
> 2004-08-03 09:55:18.83 spid1 SQL Server started in single user mode.
Updates allowed to system catalogs.
> 2004-08-03 09:55:18.84 spid1 Warning ******************
> 2004-08-03 09:55:18.85 spid1 Starting up database 'master'.
> 2004-08-03 09:55:18.85 spid1 Opening file D:\MSSQL7\data\master.mdf.
> 2004-08-03 09:55:18.89 spid1 Opening file D:\MSSQL7\data\mastlog.ldf.
> 2004-08-03 09:55:18.99 spid1 Loading SQL Server's Unicode collation.
> 2004-08-03 09:55:19.01 spid1 Loading SQL Server's non-Unicode sort
order and character set.
> 2004-08-03 09:55:19.24 spid1 4 transactions rolled forward in database
'master' (1).
> 2004-08-03 09:55:19.25 spid1 0 transactions rolled back in database
'master' (1).
> 2004-08-03 09:55:19.35 spid1 Starting up database 'model'.
> 2004-08-03 09:55:19.36 spid1 Opening file D:\MSSQL7\DATA\model.mdf.
> 2004-08-03 09:55:19.43 spid1 Opening file D:\MSSQL7\DATA\modellog.ldf.
> 2004-08-03 09:55:19.63 spid1 Clearing tempdb database.
> 2004-08-03 09:55:19.69 spid1 Creating file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:19.87 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:19.97 spid1 Creating file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:19.99 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:20.12 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:20.14 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.15 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:21.25 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.34 spid1 Starting up database 'tempdb'.
> 2004-08-03 09:55:21.38 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:21.41 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.69 spid1 Server name is 'AOATESTBOX'.
> 2004-08-03 09:55:21.70 kernel Using 'SQLEVN70.DLL' version '7.00.623'.
> 2004-08-03 09:55:21.71 kernel Using 'OPENDS60.DLL' version
'7.00.00.0623'.
> 2004-08-03 09:55:21.71 ods Using 'SSNMPN70.DLL' version '7.0.623' to
listen on '\\.\pipe\sql\query'.
> 2004-08-03 09:55:21.72 ods Using 'SSMSSO70.DLL' version '7.0.623' to
listen on '3180'.
> 2004-08-03 09:55:21.82 ods Using 'SSMSRP70.DLL' version '7.0.623' to
listen on 'AOATESTBOX'.
> 2004-08-03 09:55:22.68 spid5 Starting up database 'msdb'.
> 2004-08-03 09:55:22.68 spid5 Opening file D:\MSSQL7\DATA\msdbdata.mdf.
> 2004-08-03 09:55:22.70 spid6 Starting up database 'pubs'.
> 2004-08-03 09:55:22.70 spid6 Opening file D:\MSSQL7\DATA\pubs.mdf.
> 2004-08-03 09:55:22.72 spid7 Starting up database 'Northwind'.
> 2004-08-03 09:55:22.72 spid7 Opening file D:\MSSQL7\DATA\northwnd.mdf.
> 2004-08-03 09:55:22.82 spid5 Opening file D:\MSSQL7\DATA\msdblog.ldf.
> 2004-08-03 09:55:22.85 spid7 Opening file D:\MSSQL7\DATA\northwnd.ldf.
> 2004-08-03 09:55:22.87 spid6 Opening file D:\MSSQL7\DATA\pubs_log.ldf.
> 2004-08-03 09:55:23.55 spid1 Recovery complete.
> 2004-08-03 09:55:23.56 spid1 SQL Server's Unicode collation is:
> 2004-08-03 09:55:23.57 spid1 'English' (ID = 1033).
> 2004-08-03 09:55:23.57 spid1 comparison style = 196609.
> 2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode sort order is:
> 2004-08-03 09:55:23.58 spid1 'nocase_850' (ID = 42).
> 2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode character set is:
> 2004-08-03 09:55:23.58 spid1 'cp850' (ID = 2).
> 2004-08-03 09:57:00.02 spid6 Using 'xpstar.dll' version '1998.11.13' to
execute extended stored procedure 'sp_MSgetversion'.
> 2004-08-03 10:16:44.05 kernel SQL Server shutdown due to Ctrl-C or
Ctrl-Break signal.[vbcol=seagreen]
> ****************************************
***************
> Here's the Veritas log:
> ****************************************
********
> ****************************************
********
> "Keith Kratochvil" wrote:
>
directly[vbcol=seagreen]
Server[vbcol=seagreen]
found[vbcol=seagreen]
of[vbcol=seagreen]
C to[vbcol=seagreen]
service.[vbcol=seagreen]
other[vbcol=seagreen]
Backup[vbcol=seagreen]
things[vbcol=seagreen]
that[vbcol=seagreen]
my[vbcol=seagreen]
Can't restore master db!
Hi:
I've got a problem that's causing me headaches! I'm using Veritas Backup Exec to perform my db backups. I've just
recently taken over the DBA responsibilities and one of the first things I've wanted to do is test those backups since
we've never had occassion to use the backups. I have a test system that I'm using and have it configured exactly as the
production system.
After installing MS SQL Server 7 putting on SP4 and all the security patches I try to restore all the databases. I can
get all my databases restored except the master db that contains all my security info. I keep getting the message
"must be in single user mode". I've tried to set the master db into single user mode and there's just no way to do it!!
I've tried using the EM and the sp_dboption. Without that master db restored I'm in deep doo-doo!
Any ideas how I can handle this' Any help/suggestions is greatly appreciated.
TIA
Cathy
SQL Server 7( SP4), NT4.0Hi,
To restore the master database you have start the SQL server itself in
Single user mode.
1. Stop the MSSQL server and sql agent service
2. Go to command prompt and execute below command,.
sqlservr.exe -c -m
3. Login to sql server using query analyzer and retore the master database.
4. After the restore .. go to old comamnd prompt and press CONTRL and C to
close the old session
5. Go to control panel and start the MSSQL server and Sql agent service.
Note:
I recommend you to restore the master database first followed with other
databases.
Thanks
Hari
MCDBA
"Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in message
news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.4ax.com...
> Hi:
> I've got a problem that's causing me headaches! I'm using Veritas Backup
Exec to perform my db backups. I've just
> recently taken over the DBA responsibilities and one of the first things
I've wanted to do is test those backups since
> we've never had occassion to use the backups. I have a test system that
I'm using and have it configured exactly as the
> production system.
> After installing MS SQL Server 7 putting on SP4 and all the security
patches I try to restore all the databases. I can
> get all my databases restored except the master db that contains all my
security info. I keep getting the message
> "must be in single user mode". I've tried to set the master db into
single user mode and there's just no way to do it!!
> I've tried using the EM and the sp_dboption. Without that master db
restored I'm in deep doo-doo!
> Any ideas how I can handle this' Any help/suggestions is greatly
appreciated.
> TIA
> Cathy
> SQL Server 7( SP4), NT4.0|||This method will work. I think Hari copied and pasted the answer directly
from Books Online! I would have provided exactly the same answer. I
probably would have also pointed you at Books Online (within your SQL Server
program group). BACKUP and RESTORE information and examples can be found
there. Knowing how to restore the data that you are backing up is
important. Cathy, you are smart to be doing your research now (instead of
when the entire company is waiting for you to restore the data).
--
Keith
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi,
> To restore the master database you have start the SQL server itself in
> Single user mode.
> 1. Stop the MSSQL server and sql agent service
> 2. Go to command prompt and execute below command,.
> sqlservr.exe -c -m
> 3. Login to sql server using query analyzer and retore the master
database.
> 4. After the restore .. go to old comamnd prompt and press CONTRL and C to
> close the old session
> 5. Go to control panel and start the MSSQL server and Sql agent service.
> Note:
> I recommend you to restore the master database first followed with other
> databases.
> Thanks
> Hari
> MCDBA
>
> "Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in
message
> news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.4ax.com...
> > Hi:
> >
> > I've got a problem that's causing me headaches! I'm using Veritas Backup
> Exec to perform my db backups. I've just
> > recently taken over the DBA responsibilities and one of the first things
> I've wanted to do is test those backups since
> > we've never had occassion to use the backups. I have a test system that
> I'm using and have it configured exactly as the
> > production system.
> >
> > After installing MS SQL Server 7 putting on SP4 and all the security
> patches I try to restore all the databases. I can
> > get all my databases restored except the master db that contains all my
> security info. I keep getting the message
> > "must be in single user mode". I've tried to set the master db into
> single user mode and there's just no way to do it!!
> > I've tried using the EM and the sp_dboption. Without that master db
> restored I'm in deep doo-doo!
> >
> > Any ideas how I can handle this' Any help/suggestions is greatly
> appreciated.
> >
> > TIA
> >
> > Cathy
> >
> > SQL Server 7( SP4), NT4.0
>|||Thanks guys for your prompt reply.
I thought I was starting the SQL Server in single user mode: I'm using the command "net start mssqlserver -m"... Is this not correct'
"Keith Kratochvil" wrote:
> This method will work. I think Hari copied and pasted the answer directly
> from Books Online! I would have provided exactly the same answer. I
> probably would have also pointed you at Books Online (within your SQL Server
> program group). BACKUP and RESTORE information and examples can be found
> there. Knowing how to restore the data that you are backing up is
> important. Cathy, you are smart to be doing your research now (instead of
> when the entire company is waiting for you to restore the data).
> --
> Keith
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > To restore the master database you have start the SQL server itself in
> > Single user mode.
> >
> > 1. Stop the MSSQL server and sql agent service
> >
> > 2. Go to command prompt and execute below command,.
> >
> > sqlservr.exe -c -m
> >
> > 3. Login to sql server using query analyzer and retore the master
> database.
> >
> > 4. After the restore .. go to old comamnd prompt and press CONTRL and C to
> > close the old session
> >
> > 5. Go to control panel and start the MSSQL server and Sql agent service.
> >
> > Note:
> >
> > I recommend you to restore the master database first followed with other
> > databases.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in
> message
> > news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.4ax.com...
> > > Hi:
> > >
> > > I've got a problem that's causing me headaches! I'm using Veritas Backup
> > Exec to perform my db backups. I've just
> > > recently taken over the DBA responsibilities and one of the first things
> > I've wanted to do is test those backups since
> > > we've never had occassion to use the backups. I have a test system that
> > I'm using and have it configured exactly as the
> > > production system.
> > >
> > > After installing MS SQL Server 7 putting on SP4 and all the security
> > patches I try to restore all the databases. I can
> > > get all my databases restored except the master db that contains all my
> > security info. I keep getting the message
> > > "must be in single user mode". I've tried to set the master db into
> > single user mode and there's just no way to do it!!
> > > I've tried using the EM and the sp_dboption. Without that master db
> > restored I'm in deep doo-doo!
> > >
> > > Any ideas how I can handle this' Any help/suggestions is greatly
> > appreciated.
> > >
> > > TIA
> > >
> > > Cathy
> > >
> > > SQL Server 7( SP4), NT4.0
> >
> >
>|||No, NET START starts the MSSQLSERVER service, and the -m is a parameter to
NET START, which doesn't take any parameters so it wouldn't know what to do
with it.
You need to run the sqlservr executable, and the pass the -m flag to it as
Hari suggested.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"caeriel" <caeriel@.discussions.microsoft.com> wrote in message
news:9542FB0A-84B5-4DDD-8498-87C5F4A91D4A@.microsoft.com...
> Thanks guys for your prompt reply.
> I thought I was starting the SQL Server in single user mode: I'm using
the command "net start mssqlserver -m"... Is this not correct'
> "Keith Kratochvil" wrote:
> > This method will work. I think Hari copied and pasted the answer
directly
> > from Books Online! I would have provided exactly the same answer. I
> > probably would have also pointed you at Books Online (within your SQL
Server
> > program group). BACKUP and RESTORE information and examples can be
found
> > there. Knowing how to restore the data that you are backing up is
> > important. Cathy, you are smart to be doing your research now (instead
of
> > when the entire company is waiting for you to restore the data).
> >
> > --
> > Keith
> >
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > >
> > > To restore the master database you have start the SQL server itself in
> > > Single user mode.
> > >
> > > 1. Stop the MSSQL server and sql agent service
> > >
> > > 2. Go to command prompt and execute below command,.
> > >
> > > sqlservr.exe -c -m
> > >
> > > 3. Login to sql server using query analyzer and retore the master
> > database.
> > >
> > > 4. After the restore .. go to old comamnd prompt and press CONTRL and
C to
> > > close the old session
> > >
> > > 5. Go to control panel and start the MSSQL server and Sql agent
service.
> > >
> > > Note:
> > >
> > > I recommend you to restore the master database first followed with
other
> > > databases.
> > >
> > > Thanks
> > > Hari
> > > MCDBA
> > >
> > >
> > >
> > > "Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in
> > message
> > > news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.4ax.com...
> > > > Hi:
> > > >
> > > > I've got a problem that's causing me headaches! I'm using Veritas
Backup
> > > Exec to perform my db backups. I've just
> > > > recently taken over the DBA responsibilities and one of the first
things
> > > I've wanted to do is test those backups since
> > > > we've never had occassion to use the backups. I have a test system
that
> > > I'm using and have it configured exactly as the
> > > > production system.
> > > >
> > > > After installing MS SQL Server 7 putting on SP4 and all the security
> > > patches I try to restore all the databases. I can
> > > > get all my databases restored except the master db that contains all
my
> > > security info. I keep getting the message
> > > > "must be in single user mode". I've tried to set the master db into
> > > single user mode and there's just no way to do it!!
> > > > I've tried using the EM and the sp_dboption. Without that master db
> > > restored I'm in deep doo-doo!
> > > >
> > > > Any ideas how I can handle this' Any help/suggestions is greatly
> > > appreciated.
> > > >
> > > > TIA
> > > >
> > > > Cathy
> > > >
> > > > SQL Server 7( SP4), NT4.0
> > >
> > >
> >
> >|||This may be a problem with Veritas software. Can you get the backup files
from Veritas, and do the restore manually through Query Analyzer?
Restore database master from disk = "C:\whatever"
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in message
news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.4ax.com...
> Hi:
> I've got a problem that's causing me headaches! I'm using Veritas Backup
Exec to perform my db backups. I've just
> recently taken over the DBA responsibilities and one of the first things
I've wanted to do is test those backups since
> we've never had occassion to use the backups. I have a test system that
I'm using and have it configured exactly as the
> production system.
> After installing MS SQL Server 7 putting on SP4 and all the security
patches I try to restore all the databases. I can
> get all my databases restored except the master db that contains all my
security info. I keep getting the message
> "must be in single user mode". I've tried to set the master db into
single user mode and there's just no way to do it!!
> I've tried using the EM and the sp_dboption. Without that master db
restored I'm in deep doo-doo!
> Any ideas how I can handle this' Any help/suggestions is greatly
appreciated.
> TIA
> Cathy
> SQL Server 7( SP4), NT4.0|||Try restoring the backup files to disk using Veritas and then restore the
database using T-SQL executed via Query Analyzer.
RESTORE DATABASE foo FROM DISK = 'x:\foo.bak'
(syntax and examples within Books Online)
--
Keith
"caeriel" <caeriel@.discussions.microsoft.com> wrote in message
news:B6837C91-9566-4CB1-B3D0-73DB610E6232@.microsoft.com...
> Keith: I'm still unable to restore the master database...
> I tried starting the server as you & Hari instructed; the server started
in single user mode. However, the restore has to be issued from the Veritas
software interface. Here's the SQL Server log.
> *************************************
> ======================================================================> Job server: Test
> Job name: MasterDB NoRecov 8-03
> Job started: Tuesday, August 03, 2004 at 10:08:07 AM
> Job type: Restore
> Job Log: BEX41.txt
> ======================================================================> Drive and media information from media mount:
> Robotic Library Name:
> Drive Name: Quantum 1
> Slot: 0
> Media GUID: {2E9659D2-C5B5-43BF-BD2A-502B2E1054E1}
> Media Label: Test Wk1 Monday
> Overwrite Protection Time Left: None
> Append Time Left: Infinite
> ======================================================================> Job Operation - Restore
> ======================================================================> Restoring data to Test .
> Storage Media #1: " Media created 8/2/04 08:24:56 PM "
> Backup performed on 8/3/04 at 8:12 AM
> Backup set #6: " Full Backup "
> Restore started on 8/3/04 at 10:10:35 AM.
> An error occurred on a query to database master.
> ^ ^ ^ ^ ^
> SQL Server returned the following error message:
> RESTORE DATABASE must be used in single user mode when trying to restore
the master database.
> ^ ^ ^ ^ ^
> Restore completed on 8/3/04 at 10:13:42 AM.
> 1 item was skipped.
>
> Processed 0 bytes in 3 minutes and 7 seconds.
> Throughput rate: 0.0 MB/min
> ---
> ======================================================================> Job ended: Tuesday, August 03, 2004 at 10:14:31 AM
> Job completion status: Failed
> ======================================================================> ****************************************************
> 2004-08-03 09:55:16.51 kernel Microsoft SQL Server 7.00 - 7.00.623
(Intel X86)
> Nov 27 1998 22:20:07
> Copyright (c) 1988-1998 Microsoft Corporation
> Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
> 2004-08-03 09:55:16.51 kernel Copyright (C) 1988-1997 Microsoft
Corporation.
> 2004-08-03 09:55:16.52 kernel All rights reserved.
> 2004-08-03 09:55:16.52 kernel Logging SQL Server messages in file
'D:\MSSQL7\log\ERRORLOG'.
> 2004-08-03 09:55:16.58 kernel initconfig: Number of user connections
limited to 32767.
> 2004-08-03 09:55:16.58 kernel SQL Server is starting at priority class
'normal'(1 CPU detected).
> 2004-08-03 09:55:16.70 kernel User Mode Scheduler configured for thread
processing
> 2004-08-03 09:55:17.96 server Directory Size: 10801
> 2004-08-03 09:55:18.05 spid1 Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks
> 2004-08-03 09:55:18.05 kernel Attempting to initialize Distributed
Transaction Coordinator.
> 2004-08-03 09:55:18.82 spid1 Failed to obtain
TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE
> 2004-08-03 09:55:18.83 spid1 Warning ******************
> 2004-08-03 09:55:18.83 spid1 SQL Server started in single user mode.
Updates allowed to system catalogs.
> 2004-08-03 09:55:18.84 spid1 Warning ******************
> 2004-08-03 09:55:18.85 spid1 Starting up database 'master'.
> 2004-08-03 09:55:18.85 spid1 Opening file D:\MSSQL7\data\master.mdf.
> 2004-08-03 09:55:18.89 spid1 Opening file D:\MSSQL7\data\mastlog.ldf.
> 2004-08-03 09:55:18.99 spid1 Loading SQL Server's Unicode collation.
> 2004-08-03 09:55:19.01 spid1 Loading SQL Server's non-Unicode sort
order and character set.
> 2004-08-03 09:55:19.24 spid1 4 transactions rolled forward in database
'master' (1).
> 2004-08-03 09:55:19.25 spid1 0 transactions rolled back in database
'master' (1).
> 2004-08-03 09:55:19.35 spid1 Starting up database 'model'.
> 2004-08-03 09:55:19.36 spid1 Opening file D:\MSSQL7\DATA\model.mdf.
> 2004-08-03 09:55:19.43 spid1 Opening file D:\MSSQL7\DATA\modellog.ldf.
> 2004-08-03 09:55:19.63 spid1 Clearing tempdb database.
> 2004-08-03 09:55:19.69 spid1 Creating file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:19.87 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:19.97 spid1 Creating file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:19.99 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:20.12 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:20.14 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.15 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:21.25 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.34 spid1 Starting up database 'tempdb'.
> 2004-08-03 09:55:21.38 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:21.41 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.69 spid1 Server name is 'AOATESTBOX'.
> 2004-08-03 09:55:21.70 kernel Using 'SQLEVN70.DLL' version '7.00.623'.
> 2004-08-03 09:55:21.71 kernel Using 'OPENDS60.DLL' version
'7.00.00.0623'.
> 2004-08-03 09:55:21.71 ods Using 'SSNMPN70.DLL' version '7.0.623' to
listen on '\\.\pipe\sql\query'.
> 2004-08-03 09:55:21.72 ods Using 'SSMSSO70.DLL' version '7.0.623' to
listen on '3180'.
> 2004-08-03 09:55:21.82 ods Using 'SSMSRP70.DLL' version '7.0.623' to
listen on 'AOATESTBOX'.
> 2004-08-03 09:55:22.68 spid5 Starting up database 'msdb'.
> 2004-08-03 09:55:22.68 spid5 Opening file D:\MSSQL7\DATA\msdbdata.mdf.
> 2004-08-03 09:55:22.70 spid6 Starting up database 'pubs'.
> 2004-08-03 09:55:22.70 spid6 Opening file D:\MSSQL7\DATA\pubs.mdf.
> 2004-08-03 09:55:22.72 spid7 Starting up database 'Northwind'.
> 2004-08-03 09:55:22.72 spid7 Opening file D:\MSSQL7\DATA\northwnd.mdf.
> 2004-08-03 09:55:22.82 spid5 Opening file D:\MSSQL7\DATA\msdblog.ldf.
> 2004-08-03 09:55:22.85 spid7 Opening file D:\MSSQL7\DATA\northwnd.ldf.
> 2004-08-03 09:55:22.87 spid6 Opening file D:\MSSQL7\DATA\pubs_log.ldf.
> 2004-08-03 09:55:23.55 spid1 Recovery complete.
> 2004-08-03 09:55:23.56 spid1 SQL Server's Unicode collation is:
> 2004-08-03 09:55:23.57 spid1 'English' (ID = 1033).
> 2004-08-03 09:55:23.57 spid1 comparison style = 196609.
> 2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode sort order is:
> 2004-08-03 09:55:23.58 spid1 'nocase_850' (ID = 42).
> 2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode character set is:
> 2004-08-03 09:55:23.58 spid1 'cp850' (ID = 2).
> 2004-08-03 09:57:00.02 spid6 Using 'xpstar.dll' version '1998.11.13' to
execute extended stored procedure 'sp_MSgetversion'.
> 2004-08-03 10:16:44.05 kernel SQL Server shutdown due to Ctrl-C or
Ctrl-Break signal.
> *******************************************************
> Here's the Veritas log:
> ************************************************
> ************************************************
> "Keith Kratochvil" wrote:
> > This method will work. I think Hari copied and pasted the answer
directly
> > from Books Online! I would have provided exactly the same answer. I
> > probably would have also pointed you at Books Online (within your SQL
Server
> > program group). BACKUP and RESTORE information and examples can be
found
> > there. Knowing how to restore the data that you are backing up is
> > important. Cathy, you are smart to be doing your research now (instead
of
> > when the entire company is waiting for you to restore the data).
> >
> > --
> > Keith
> >
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > >
> > > To restore the master database you have start the SQL server itself in
> > > Single user mode.
> > >
> > > 1. Stop the MSSQL server and sql agent service
> > >
> > > 2. Go to command prompt and execute below command,.
> > >
> > > sqlservr.exe -c -m
> > >
> > > 3. Login to sql server using query analyzer and retore the master
> > database.
> > >
> > > 4. After the restore .. go to old comamnd prompt and press CONTRL and
C to
> > > close the old session
> > >
> > > 5. Go to control panel and start the MSSQL server and Sql agent
service.
> > >
> > > Note:
> > >
> > > I recommend you to restore the master database first followed with
other
> > > databases.
> > >
> > > Thanks
> > > Hari
> > > MCDBA
> > >
> > >
> > >
> > > "Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in
> > message
> > > news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.4ax.com...
> > > > Hi:
> > > >
> > > > I've got a problem that's causing me headaches! I'm using Veritas
Backup
> > > Exec to perform my db backups. I've just
> > > > recently taken over the DBA responsibilities and one of the first
things
> > > I've wanted to do is test those backups since
> > > > we've never had occassion to use the backups. I have a test system
that
> > > I'm using and have it configured exactly as the
> > > > production system.
> > > >
> > > > After installing MS SQL Server 7 putting on SP4 and all the security
> > > patches I try to restore all the databases. I can
> > > > get all my databases restored except the master db that contains all
my
> > > security info. I keep getting the message
> > > > "must be in single user mode". I've tried to set the master db into
> > > single user mode and there's just no way to do it!!
> > > > I've tried using the EM and the sp_dboption. Without that master db
> > > restored I'm in deep doo-doo!
> > > >
> > > > Any ideas how I can handle this' Any help/suggestions is greatly
> > > appreciated.
> > > >
> > > > TIA
> > > >
> > > > Cathy
> > > >
> > > > SQL Server 7( SP4), NT4.0
> > >
> > >
> >
> >
I've got a problem that's causing me headaches! I'm using Veritas Backup Exec to perform my db backups. I've just
recently taken over the DBA responsibilities and one of the first things I've wanted to do is test those backups since
we've never had occassion to use the backups. I have a test system that I'm using and have it configured exactly as the
production system.
After installing MS SQL Server 7 putting on SP4 and all the security patches I try to restore all the databases. I can
get all my databases restored except the master db that contains all my security info. I keep getting the message
"must be in single user mode". I've tried to set the master db into single user mode and there's just no way to do it!!
I've tried using the EM and the sp_dboption. Without that master db restored I'm in deep doo-doo!
Any ideas how I can handle this' Any help/suggestions is greatly appreciated.
TIA
Cathy
SQL Server 7( SP4), NT4.0Hi,
To restore the master database you have start the SQL server itself in
Single user mode.
1. Stop the MSSQL server and sql agent service
2. Go to command prompt and execute below command,.
sqlservr.exe -c -m
3. Login to sql server using query analyzer and retore the master database.
4. After the restore .. go to old comamnd prompt and press CONTRL and C to
close the old session
5. Go to control panel and start the MSSQL server and Sql agent service.
Note:
I recommend you to restore the master database first followed with other
databases.
Thanks
Hari
MCDBA
"Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in message
news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.4ax.com...
> Hi:
> I've got a problem that's causing me headaches! I'm using Veritas Backup
Exec to perform my db backups. I've just
> recently taken over the DBA responsibilities and one of the first things
I've wanted to do is test those backups since
> we've never had occassion to use the backups. I have a test system that
I'm using and have it configured exactly as the
> production system.
> After installing MS SQL Server 7 putting on SP4 and all the security
patches I try to restore all the databases. I can
> get all my databases restored except the master db that contains all my
security info. I keep getting the message
> "must be in single user mode". I've tried to set the master db into
single user mode and there's just no way to do it!!
> I've tried using the EM and the sp_dboption. Without that master db
restored I'm in deep doo-doo!
> Any ideas how I can handle this' Any help/suggestions is greatly
appreciated.
> TIA
> Cathy
> SQL Server 7( SP4), NT4.0|||This method will work. I think Hari copied and pasted the answer directly
from Books Online! I would have provided exactly the same answer. I
probably would have also pointed you at Books Online (within your SQL Server
program group). BACKUP and RESTORE information and examples can be found
there. Knowing how to restore the data that you are backing up is
important. Cathy, you are smart to be doing your research now (instead of
when the entire company is waiting for you to restore the data).
--
Keith
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi,
> To restore the master database you have start the SQL server itself in
> Single user mode.
> 1. Stop the MSSQL server and sql agent service
> 2. Go to command prompt and execute below command,.
> sqlservr.exe -c -m
> 3. Login to sql server using query analyzer and retore the master
database.
> 4. After the restore .. go to old comamnd prompt and press CONTRL and C to
> close the old session
> 5. Go to control panel and start the MSSQL server and Sql agent service.
> Note:
> I recommend you to restore the master database first followed with other
> databases.
> Thanks
> Hari
> MCDBA
>
> "Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in
message
> news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.4ax.com...
> > Hi:
> >
> > I've got a problem that's causing me headaches! I'm using Veritas Backup
> Exec to perform my db backups. I've just
> > recently taken over the DBA responsibilities and one of the first things
> I've wanted to do is test those backups since
> > we've never had occassion to use the backups. I have a test system that
> I'm using and have it configured exactly as the
> > production system.
> >
> > After installing MS SQL Server 7 putting on SP4 and all the security
> patches I try to restore all the databases. I can
> > get all my databases restored except the master db that contains all my
> security info. I keep getting the message
> > "must be in single user mode". I've tried to set the master db into
> single user mode and there's just no way to do it!!
> > I've tried using the EM and the sp_dboption. Without that master db
> restored I'm in deep doo-doo!
> >
> > Any ideas how I can handle this' Any help/suggestions is greatly
> appreciated.
> >
> > TIA
> >
> > Cathy
> >
> > SQL Server 7( SP4), NT4.0
>|||Thanks guys for your prompt reply.
I thought I was starting the SQL Server in single user mode: I'm using the command "net start mssqlserver -m"... Is this not correct'
"Keith Kratochvil" wrote:
> This method will work. I think Hari copied and pasted the answer directly
> from Books Online! I would have provided exactly the same answer. I
> probably would have also pointed you at Books Online (within your SQL Server
> program group). BACKUP and RESTORE information and examples can be found
> there. Knowing how to restore the data that you are backing up is
> important. Cathy, you are smart to be doing your research now (instead of
> when the entire company is waiting for you to restore the data).
> --
> Keith
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > To restore the master database you have start the SQL server itself in
> > Single user mode.
> >
> > 1. Stop the MSSQL server and sql agent service
> >
> > 2. Go to command prompt and execute below command,.
> >
> > sqlservr.exe -c -m
> >
> > 3. Login to sql server using query analyzer and retore the master
> database.
> >
> > 4. After the restore .. go to old comamnd prompt and press CONTRL and C to
> > close the old session
> >
> > 5. Go to control panel and start the MSSQL server and Sql agent service.
> >
> > Note:
> >
> > I recommend you to restore the master database first followed with other
> > databases.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in
> message
> > news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.4ax.com...
> > > Hi:
> > >
> > > I've got a problem that's causing me headaches! I'm using Veritas Backup
> > Exec to perform my db backups. I've just
> > > recently taken over the DBA responsibilities and one of the first things
> > I've wanted to do is test those backups since
> > > we've never had occassion to use the backups. I have a test system that
> > I'm using and have it configured exactly as the
> > > production system.
> > >
> > > After installing MS SQL Server 7 putting on SP4 and all the security
> > patches I try to restore all the databases. I can
> > > get all my databases restored except the master db that contains all my
> > security info. I keep getting the message
> > > "must be in single user mode". I've tried to set the master db into
> > single user mode and there's just no way to do it!!
> > > I've tried using the EM and the sp_dboption. Without that master db
> > restored I'm in deep doo-doo!
> > >
> > > Any ideas how I can handle this' Any help/suggestions is greatly
> > appreciated.
> > >
> > > TIA
> > >
> > > Cathy
> > >
> > > SQL Server 7( SP4), NT4.0
> >
> >
>|||No, NET START starts the MSSQLSERVER service, and the -m is a parameter to
NET START, which doesn't take any parameters so it wouldn't know what to do
with it.
You need to run the sqlservr executable, and the pass the -m flag to it as
Hari suggested.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"caeriel" <caeriel@.discussions.microsoft.com> wrote in message
news:9542FB0A-84B5-4DDD-8498-87C5F4A91D4A@.microsoft.com...
> Thanks guys for your prompt reply.
> I thought I was starting the SQL Server in single user mode: I'm using
the command "net start mssqlserver -m"... Is this not correct'
> "Keith Kratochvil" wrote:
> > This method will work. I think Hari copied and pasted the answer
directly
> > from Books Online! I would have provided exactly the same answer. I
> > probably would have also pointed you at Books Online (within your SQL
Server
> > program group). BACKUP and RESTORE information and examples can be
found
> > there. Knowing how to restore the data that you are backing up is
> > important. Cathy, you are smart to be doing your research now (instead
of
> > when the entire company is waiting for you to restore the data).
> >
> > --
> > Keith
> >
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > >
> > > To restore the master database you have start the SQL server itself in
> > > Single user mode.
> > >
> > > 1. Stop the MSSQL server and sql agent service
> > >
> > > 2. Go to command prompt and execute below command,.
> > >
> > > sqlservr.exe -c -m
> > >
> > > 3. Login to sql server using query analyzer and retore the master
> > database.
> > >
> > > 4. After the restore .. go to old comamnd prompt and press CONTRL and
C to
> > > close the old session
> > >
> > > 5. Go to control panel and start the MSSQL server and Sql agent
service.
> > >
> > > Note:
> > >
> > > I recommend you to restore the master database first followed with
other
> > > databases.
> > >
> > > Thanks
> > > Hari
> > > MCDBA
> > >
> > >
> > >
> > > "Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in
> > message
> > > news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.4ax.com...
> > > > Hi:
> > > >
> > > > I've got a problem that's causing me headaches! I'm using Veritas
Backup
> > > Exec to perform my db backups. I've just
> > > > recently taken over the DBA responsibilities and one of the first
things
> > > I've wanted to do is test those backups since
> > > > we've never had occassion to use the backups. I have a test system
that
> > > I'm using and have it configured exactly as the
> > > > production system.
> > > >
> > > > After installing MS SQL Server 7 putting on SP4 and all the security
> > > patches I try to restore all the databases. I can
> > > > get all my databases restored except the master db that contains all
my
> > > security info. I keep getting the message
> > > > "must be in single user mode". I've tried to set the master db into
> > > single user mode and there's just no way to do it!!
> > > > I've tried using the EM and the sp_dboption. Without that master db
> > > restored I'm in deep doo-doo!
> > > >
> > > > Any ideas how I can handle this' Any help/suggestions is greatly
> > > appreciated.
> > > >
> > > > TIA
> > > >
> > > > Cathy
> > > >
> > > > SQL Server 7( SP4), NT4.0
> > >
> > >
> >
> >|||This may be a problem with Veritas software. Can you get the backup files
from Veritas, and do the restore manually through Query Analyzer?
Restore database master from disk = "C:\whatever"
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in message
news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.4ax.com...
> Hi:
> I've got a problem that's causing me headaches! I'm using Veritas Backup
Exec to perform my db backups. I've just
> recently taken over the DBA responsibilities and one of the first things
I've wanted to do is test those backups since
> we've never had occassion to use the backups. I have a test system that
I'm using and have it configured exactly as the
> production system.
> After installing MS SQL Server 7 putting on SP4 and all the security
patches I try to restore all the databases. I can
> get all my databases restored except the master db that contains all my
security info. I keep getting the message
> "must be in single user mode". I've tried to set the master db into
single user mode and there's just no way to do it!!
> I've tried using the EM and the sp_dboption. Without that master db
restored I'm in deep doo-doo!
> Any ideas how I can handle this' Any help/suggestions is greatly
appreciated.
> TIA
> Cathy
> SQL Server 7( SP4), NT4.0|||Try restoring the backup files to disk using Veritas and then restore the
database using T-SQL executed via Query Analyzer.
RESTORE DATABASE foo FROM DISK = 'x:\foo.bak'
(syntax and examples within Books Online)
--
Keith
"caeriel" <caeriel@.discussions.microsoft.com> wrote in message
news:B6837C91-9566-4CB1-B3D0-73DB610E6232@.microsoft.com...
> Keith: I'm still unable to restore the master database...
> I tried starting the server as you & Hari instructed; the server started
in single user mode. However, the restore has to be issued from the Veritas
software interface. Here's the SQL Server log.
> *************************************
> ======================================================================> Job server: Test
> Job name: MasterDB NoRecov 8-03
> Job started: Tuesday, August 03, 2004 at 10:08:07 AM
> Job type: Restore
> Job Log: BEX41.txt
> ======================================================================> Drive and media information from media mount:
> Robotic Library Name:
> Drive Name: Quantum 1
> Slot: 0
> Media GUID: {2E9659D2-C5B5-43BF-BD2A-502B2E1054E1}
> Media Label: Test Wk1 Monday
> Overwrite Protection Time Left: None
> Append Time Left: Infinite
> ======================================================================> Job Operation - Restore
> ======================================================================> Restoring data to Test .
> Storage Media #1: " Media created 8/2/04 08:24:56 PM "
> Backup performed on 8/3/04 at 8:12 AM
> Backup set #6: " Full Backup "
> Restore started on 8/3/04 at 10:10:35 AM.
> An error occurred on a query to database master.
> ^ ^ ^ ^ ^
> SQL Server returned the following error message:
> RESTORE DATABASE must be used in single user mode when trying to restore
the master database.
> ^ ^ ^ ^ ^
> Restore completed on 8/3/04 at 10:13:42 AM.
> 1 item was skipped.
>
> Processed 0 bytes in 3 minutes and 7 seconds.
> Throughput rate: 0.0 MB/min
> ---
> ======================================================================> Job ended: Tuesday, August 03, 2004 at 10:14:31 AM
> Job completion status: Failed
> ======================================================================> ****************************************************
> 2004-08-03 09:55:16.51 kernel Microsoft SQL Server 7.00 - 7.00.623
(Intel X86)
> Nov 27 1998 22:20:07
> Copyright (c) 1988-1998 Microsoft Corporation
> Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
> 2004-08-03 09:55:16.51 kernel Copyright (C) 1988-1997 Microsoft
Corporation.
> 2004-08-03 09:55:16.52 kernel All rights reserved.
> 2004-08-03 09:55:16.52 kernel Logging SQL Server messages in file
'D:\MSSQL7\log\ERRORLOG'.
> 2004-08-03 09:55:16.58 kernel initconfig: Number of user connections
limited to 32767.
> 2004-08-03 09:55:16.58 kernel SQL Server is starting at priority class
'normal'(1 CPU detected).
> 2004-08-03 09:55:16.70 kernel User Mode Scheduler configured for thread
processing
> 2004-08-03 09:55:17.96 server Directory Size: 10801
> 2004-08-03 09:55:18.05 spid1 Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks
> 2004-08-03 09:55:18.05 kernel Attempting to initialize Distributed
Transaction Coordinator.
> 2004-08-03 09:55:18.82 spid1 Failed to obtain
TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE
> 2004-08-03 09:55:18.83 spid1 Warning ******************
> 2004-08-03 09:55:18.83 spid1 SQL Server started in single user mode.
Updates allowed to system catalogs.
> 2004-08-03 09:55:18.84 spid1 Warning ******************
> 2004-08-03 09:55:18.85 spid1 Starting up database 'master'.
> 2004-08-03 09:55:18.85 spid1 Opening file D:\MSSQL7\data\master.mdf.
> 2004-08-03 09:55:18.89 spid1 Opening file D:\MSSQL7\data\mastlog.ldf.
> 2004-08-03 09:55:18.99 spid1 Loading SQL Server's Unicode collation.
> 2004-08-03 09:55:19.01 spid1 Loading SQL Server's non-Unicode sort
order and character set.
> 2004-08-03 09:55:19.24 spid1 4 transactions rolled forward in database
'master' (1).
> 2004-08-03 09:55:19.25 spid1 0 transactions rolled back in database
'master' (1).
> 2004-08-03 09:55:19.35 spid1 Starting up database 'model'.
> 2004-08-03 09:55:19.36 spid1 Opening file D:\MSSQL7\DATA\model.mdf.
> 2004-08-03 09:55:19.43 spid1 Opening file D:\MSSQL7\DATA\modellog.ldf.
> 2004-08-03 09:55:19.63 spid1 Clearing tempdb database.
> 2004-08-03 09:55:19.69 spid1 Creating file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:19.87 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:19.97 spid1 Creating file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:19.99 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:20.12 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:20.14 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.15 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:21.25 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.34 spid1 Starting up database 'tempdb'.
> 2004-08-03 09:55:21.38 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:21.41 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.69 spid1 Server name is 'AOATESTBOX'.
> 2004-08-03 09:55:21.70 kernel Using 'SQLEVN70.DLL' version '7.00.623'.
> 2004-08-03 09:55:21.71 kernel Using 'OPENDS60.DLL' version
'7.00.00.0623'.
> 2004-08-03 09:55:21.71 ods Using 'SSNMPN70.DLL' version '7.0.623' to
listen on '\\.\pipe\sql\query'.
> 2004-08-03 09:55:21.72 ods Using 'SSMSSO70.DLL' version '7.0.623' to
listen on '3180'.
> 2004-08-03 09:55:21.82 ods Using 'SSMSRP70.DLL' version '7.0.623' to
listen on 'AOATESTBOX'.
> 2004-08-03 09:55:22.68 spid5 Starting up database 'msdb'.
> 2004-08-03 09:55:22.68 spid5 Opening file D:\MSSQL7\DATA\msdbdata.mdf.
> 2004-08-03 09:55:22.70 spid6 Starting up database 'pubs'.
> 2004-08-03 09:55:22.70 spid6 Opening file D:\MSSQL7\DATA\pubs.mdf.
> 2004-08-03 09:55:22.72 spid7 Starting up database 'Northwind'.
> 2004-08-03 09:55:22.72 spid7 Opening file D:\MSSQL7\DATA\northwnd.mdf.
> 2004-08-03 09:55:22.82 spid5 Opening file D:\MSSQL7\DATA\msdblog.ldf.
> 2004-08-03 09:55:22.85 spid7 Opening file D:\MSSQL7\DATA\northwnd.ldf.
> 2004-08-03 09:55:22.87 spid6 Opening file D:\MSSQL7\DATA\pubs_log.ldf.
> 2004-08-03 09:55:23.55 spid1 Recovery complete.
> 2004-08-03 09:55:23.56 spid1 SQL Server's Unicode collation is:
> 2004-08-03 09:55:23.57 spid1 'English' (ID = 1033).
> 2004-08-03 09:55:23.57 spid1 comparison style = 196609.
> 2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode sort order is:
> 2004-08-03 09:55:23.58 spid1 'nocase_850' (ID = 42).
> 2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode character set is:
> 2004-08-03 09:55:23.58 spid1 'cp850' (ID = 2).
> 2004-08-03 09:57:00.02 spid6 Using 'xpstar.dll' version '1998.11.13' to
execute extended stored procedure 'sp_MSgetversion'.
> 2004-08-03 10:16:44.05 kernel SQL Server shutdown due to Ctrl-C or
Ctrl-Break signal.
> *******************************************************
> Here's the Veritas log:
> ************************************************
> ************************************************
> "Keith Kratochvil" wrote:
> > This method will work. I think Hari copied and pasted the answer
directly
> > from Books Online! I would have provided exactly the same answer. I
> > probably would have also pointed you at Books Online (within your SQL
Server
> > program group). BACKUP and RESTORE information and examples can be
found
> > there. Knowing how to restore the data that you are backing up is
> > important. Cathy, you are smart to be doing your research now (instead
of
> > when the entire company is waiting for you to restore the data).
> >
> > --
> > Keith
> >
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > >
> > > To restore the master database you have start the SQL server itself in
> > > Single user mode.
> > >
> > > 1. Stop the MSSQL server and sql agent service
> > >
> > > 2. Go to command prompt and execute below command,.
> > >
> > > sqlservr.exe -c -m
> > >
> > > 3. Login to sql server using query analyzer and retore the master
> > database.
> > >
> > > 4. After the restore .. go to old comamnd prompt and press CONTRL and
C to
> > > close the old session
> > >
> > > 5. Go to control panel and start the MSSQL server and Sql agent
service.
> > >
> > > Note:
> > >
> > > I recommend you to restore the master database first followed with
other
> > > databases.
> > >
> > > Thanks
> > > Hari
> > > MCDBA
> > >
> > >
> > >
> > > "Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in
> > message
> > > news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.4ax.com...
> > > > Hi:
> > > >
> > > > I've got a problem that's causing me headaches! I'm using Veritas
Backup
> > > Exec to perform my db backups. I've just
> > > > recently taken over the DBA responsibilities and one of the first
things
> > > I've wanted to do is test those backups since
> > > > we've never had occassion to use the backups. I have a test system
that
> > > I'm using and have it configured exactly as the
> > > > production system.
> > > >
> > > > After installing MS SQL Server 7 putting on SP4 and all the security
> > > patches I try to restore all the databases. I can
> > > > get all my databases restored except the master db that contains all
my
> > > security info. I keep getting the message
> > > > "must be in single user mode". I've tried to set the master db into
> > > single user mode and there's just no way to do it!!
> > > > I've tried using the EM and the sp_dboption. Without that master db
> > > restored I'm in deep doo-doo!
> > > >
> > > > Any ideas how I can handle this' Any help/suggestions is greatly
> > > appreciated.
> > > >
> > > > TIA
> > > >
> > > > Cathy
> > > >
> > > > SQL Server 7( SP4), NT4.0
> > >
> > >
> >
> >
can't restore from backup
Hi,
I have two sql servers at two different facilities. I usually take the
backups from the live system and restore them to the backup system without
any problems until few days ago. The database we have is 21 GB. When I try
to restore it from backup, the restore tries to run for an hour and then an
error pops up:
Microsoft SQL - DMO (ODBC SQL State 42000)
An internal consistency error occurred. Contact technical support for
assistance. RESTORE DATABASE is terminating abnormally.
I tried it with two different backups and I am still getting the same error.
Any help is appreciated.
KatrinDoes the database come out clean from DBCC CHECKDB and DBCC CHECKCATALOG? If
it does (and you have searched KB and are up-to-date on service pack), I
suggest you open a case with MS Support.
--
Tibor Karaszi
"Katrin Koumpikova" <katrinkump@.hotmail.com> wrote in message
news:OLz5W%23zoDHA.2512@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two sql servers at two different facilities. I usually take the
> backups from the live system and restore them to the backup system without
> any problems until few days ago. The database we have is 21 GB. When I try
> to restore it from backup, the restore tries to run for an hour and then
an
> error pops up:
> Microsoft SQL - DMO (ODBC SQL State 42000)
> An internal consistency error occurred. Contact technical support for
> assistance. RESTORE DATABASE is terminating abnormally.
> I tried it with two different backups and I am still getting the same
error.
> Any help is appreciated.
>
> Katrin
>
I have two sql servers at two different facilities. I usually take the
backups from the live system and restore them to the backup system without
any problems until few days ago. The database we have is 21 GB. When I try
to restore it from backup, the restore tries to run for an hour and then an
error pops up:
Microsoft SQL - DMO (ODBC SQL State 42000)
An internal consistency error occurred. Contact technical support for
assistance. RESTORE DATABASE is terminating abnormally.
I tried it with two different backups and I am still getting the same error.
Any help is appreciated.
KatrinDoes the database come out clean from DBCC CHECKDB and DBCC CHECKCATALOG? If
it does (and you have searched KB and are up-to-date on service pack), I
suggest you open a case with MS Support.
--
Tibor Karaszi
"Katrin Koumpikova" <katrinkump@.hotmail.com> wrote in message
news:OLz5W%23zoDHA.2512@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two sql servers at two different facilities. I usually take the
> backups from the live system and restore them to the backup system without
> any problems until few days ago. The database we have is 21 GB. When I try
> to restore it from backup, the restore tries to run for an hour and then
an
> error pops up:
> Microsoft SQL - DMO (ODBC SQL State 42000)
> An internal consistency error occurred. Contact technical support for
> assistance. RESTORE DATABASE is terminating abnormally.
> I tried it with two different backups and I am still getting the same
error.
> Any help is appreciated.
>
> Katrin
>
Can't Restore Database to New Server
Hi
I am attempting to transfer a database from my server to my customers
server. I took the backup file on CD however when I try to do a restore with
it I get the following error
Microsoft SQL-DMO (ODBC SQLState:42000)
Device activation error:The physical file name
'd:\sqldata\mssql\data\sa.mdf' may be incorrect.
File 'SA-V1_dat' cannot be restored to 'd:\sqlData\MSSQL\data\sa.mdf'. Use
WITH MOVE to identify a valid location for the file.
Device activation error. The physical file name
'd:\sqlData\MSSQL\data\sa_log.ldf' may be incorrect.
File 'SA-V1_log' cannot be restored to 'd:\sqlData\MSSQL\data\sa_log.ldf'.
Use WITH MOVE to identify a valid location for the file.
RESTORE DATABASE is terminating abnormally.
Suggestions how to overcome this?
Regards
Meir
Do what the error message say. Read about the RESTORE command in Books Online and use the MOVE
option to specify desired physical files names for your database files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mrrcomp" <mrrcomp@.discussions.microsoft.com> wrote in message
news:41F751D3-42E2-4292-B0F5-77E593B6C75D@.microsoft.com...
> Hi
> I am attempting to transfer a database from my server to my customers
> server. I took the backup file on CD however when I try to do a restore with
> it I get the following error
> Microsoft SQL-DMO (ODBC SQLState:42000)
> Device activation error:The physical file name
> 'd:\sqldata\mssql\data\sa.mdf' may be incorrect.
> File 'SA-V1_dat' cannot be restored to 'd:\sqlData\MSSQL\data\sa.mdf'. Use
> WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
> 'd:\sqlData\MSSQL\data\sa_log.ldf' may be incorrect.
> File 'SA-V1_log' cannot be restored to 'd:\sqlData\MSSQL\data\sa_log.ldf'.
> Use WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
> Suggestions how to overcome this?
> Regards
> Meir
>
|||As error says to you, use MOVE TO option of the RESTORE command to move
physical files to a new location. If you use EM, check the Options tab when
restoring, there you can specify a new location.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"mrrcomp" <mrrcomp@.discussions.microsoft.com> wrote in message
news:41F751D3-42E2-4292-B0F5-77E593B6C75D@.microsoft.com...
> Hi
> I am attempting to transfer a database from my server to my customers
> server. I took the backup file on CD however when I try to do a restore
> with
> it I get the following error
> Microsoft SQL-DMO (ODBC SQLState:42000)
> Device activation error:The physical file name
> 'd:\sqldata\mssql\data\sa.mdf' may be incorrect.
> File 'SA-V1_dat' cannot be restored to 'd:\sqlData\MSSQL\data\sa.mdf'. Use
> WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
> 'd:\sqlData\MSSQL\data\sa_log.ldf' may be incorrect.
> File 'SA-V1_log' cannot be restored to 'd:\sqlData\MSSQL\data\sa_log.ldf'.
> Use WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
> Suggestions how to overcome this?
> Regards
> Meir
>
|||Hi,
To add on:
Try the below steps:-
From Query Analyzer:-
1. Using Restore filelistonly command identify the logical file names of the
database backup file
RESTORE FILELISTONLY from disk='c:\x.bak'
2. With the output of the above query use RESTORE database
RESTORE DATABASE <newdbname> from disk='c:\backup\x.bak'
WITH move 'logical_mdf_filename' to 'new physical name with path',
move 'logical_ldf_filename' to 'new physical log name with
Path'
After the restore refer sp_change_users_login to syncronize the Logins.
Thanks
Hari
SQL Server MVP
"mrrcomp" <mrrcomp@.discussions.microsoft.com> wrote in message
news:41F751D3-42E2-4292-B0F5-77E593B6C75D@.microsoft.com...
> Hi
> I am attempting to transfer a database from my server to my customers
> server. I took the backup file on CD however when I try to do a restore
> with
> it I get the following error
> Microsoft SQL-DMO (ODBC SQLState:42000)
> Device activation error:The physical file name
> 'd:\sqldata\mssql\data\sa.mdf' may be incorrect.
> File 'SA-V1_dat' cannot be restored to 'd:\sqlData\MSSQL\data\sa.mdf'. Use
> WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
> 'd:\sqlData\MSSQL\data\sa_log.ldf' may be incorrect.
> File 'SA-V1_log' cannot be restored to 'd:\sqlData\MSSQL\data\sa_log.ldf'.
> Use WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
> Suggestions how to overcome this?
> Regards
> Meir
>
I am attempting to transfer a database from my server to my customers
server. I took the backup file on CD however when I try to do a restore with
it I get the following error
Microsoft SQL-DMO (ODBC SQLState:42000)
Device activation error:The physical file name
'd:\sqldata\mssql\data\sa.mdf' may be incorrect.
File 'SA-V1_dat' cannot be restored to 'd:\sqlData\MSSQL\data\sa.mdf'. Use
WITH MOVE to identify a valid location for the file.
Device activation error. The physical file name
'd:\sqlData\MSSQL\data\sa_log.ldf' may be incorrect.
File 'SA-V1_log' cannot be restored to 'd:\sqlData\MSSQL\data\sa_log.ldf'.
Use WITH MOVE to identify a valid location for the file.
RESTORE DATABASE is terminating abnormally.
Suggestions how to overcome this?
Regards
Meir
Do what the error message say. Read about the RESTORE command in Books Online and use the MOVE
option to specify desired physical files names for your database files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mrrcomp" <mrrcomp@.discussions.microsoft.com> wrote in message
news:41F751D3-42E2-4292-B0F5-77E593B6C75D@.microsoft.com...
> Hi
> I am attempting to transfer a database from my server to my customers
> server. I took the backup file on CD however when I try to do a restore with
> it I get the following error
> Microsoft SQL-DMO (ODBC SQLState:42000)
> Device activation error:The physical file name
> 'd:\sqldata\mssql\data\sa.mdf' may be incorrect.
> File 'SA-V1_dat' cannot be restored to 'd:\sqlData\MSSQL\data\sa.mdf'. Use
> WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
> 'd:\sqlData\MSSQL\data\sa_log.ldf' may be incorrect.
> File 'SA-V1_log' cannot be restored to 'd:\sqlData\MSSQL\data\sa_log.ldf'.
> Use WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
> Suggestions how to overcome this?
> Regards
> Meir
>
|||As error says to you, use MOVE TO option of the RESTORE command to move
physical files to a new location. If you use EM, check the Options tab when
restoring, there you can specify a new location.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"mrrcomp" <mrrcomp@.discussions.microsoft.com> wrote in message
news:41F751D3-42E2-4292-B0F5-77E593B6C75D@.microsoft.com...
> Hi
> I am attempting to transfer a database from my server to my customers
> server. I took the backup file on CD however when I try to do a restore
> with
> it I get the following error
> Microsoft SQL-DMO (ODBC SQLState:42000)
> Device activation error:The physical file name
> 'd:\sqldata\mssql\data\sa.mdf' may be incorrect.
> File 'SA-V1_dat' cannot be restored to 'd:\sqlData\MSSQL\data\sa.mdf'. Use
> WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
> 'd:\sqlData\MSSQL\data\sa_log.ldf' may be incorrect.
> File 'SA-V1_log' cannot be restored to 'd:\sqlData\MSSQL\data\sa_log.ldf'.
> Use WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
> Suggestions how to overcome this?
> Regards
> Meir
>
|||Hi,
To add on:
Try the below steps:-
From Query Analyzer:-
1. Using Restore filelistonly command identify the logical file names of the
database backup file
RESTORE FILELISTONLY from disk='c:\x.bak'
2. With the output of the above query use RESTORE database
RESTORE DATABASE <newdbname> from disk='c:\backup\x.bak'
WITH move 'logical_mdf_filename' to 'new physical name with path',
move 'logical_ldf_filename' to 'new physical log name with
Path'
After the restore refer sp_change_users_login to syncronize the Logins.
Thanks
Hari
SQL Server MVP
"mrrcomp" <mrrcomp@.discussions.microsoft.com> wrote in message
news:41F751D3-42E2-4292-B0F5-77E593B6C75D@.microsoft.com...
> Hi
> I am attempting to transfer a database from my server to my customers
> server. I took the backup file on CD however when I try to do a restore
> with
> it I get the following error
> Microsoft SQL-DMO (ODBC SQLState:42000)
> Device activation error:The physical file name
> 'd:\sqldata\mssql\data\sa.mdf' may be incorrect.
> File 'SA-V1_dat' cannot be restored to 'd:\sqlData\MSSQL\data\sa.mdf'. Use
> WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
> 'd:\sqlData\MSSQL\data\sa_log.ldf' may be incorrect.
> File 'SA-V1_log' cannot be restored to 'd:\sqlData\MSSQL\data\sa_log.ldf'.
> Use WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
> Suggestions how to overcome this?
> Regards
> Meir
>
Subscribe to:
Posts (Atom)