Cant take Backups

Hi
I have Database in SQL-Server2000 size is 1.37 GB i am tryn to take backup followin are the problems:-
1. Maintainence Plan for Backup Fails for DB
2. Jobs fails for Backup

--> I goto SQL Server Logs Error message is Operating system error 23(Data error (cyclic redundancy check).)

--> Now i am tryin Backup using command :-
Backup Database 'DB1' To Disk ='Path' followin error occurs:-
"Nonrecoverable I/O error occurred on file"

--> Now i am doing back-up directly from Enterprise Manager following error message occurs same error comes

Whats surprising Jobs, maintainence plan, Backup for other database is working perfectly, i checked HDD doesnt have any bad blocks etc. i have even changed path for Backups but still only for these database it gives problem

With Regards

Have you issued a DBCC CHECKDB against the database?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Can't Shrink log File

Hi, All,
I tried to shrink a log file which was restored from production backup, but
got error. The log file is about only 10% usage according to the result of
DBCC SQLPERF ( LOGSPACE ).
When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
message:
Cannot shrink log file 2 (productionlog) because all logical log files are
in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
-- -- -- -- -- --
14 2 92888 76753 92888 76752
We do have Replication set up in Production, but I drop all replications
after restored it to Test environment. We are using SQL Server 2000.
Any help will be appreciated.
PerayuDid you backup the log first? Refer to:
http://support.microsoft.com/?id=272318
-Sue
On Thu, 8 Dec 2005 08:37:43 -0600, "Perayu"
<yu.he@.state.mn.us.Remove4Replay> wrote:

>Hi, All,
>I tried to shrink a log file which was restored from production backup, but
>got error. The log file is about only 10% usage according to the result of
>DBCC SQLPERF ( LOGSPACE ).
>When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
>message:
>Cannot shrink log file 2 (productionlog) because all logical log files are
>in use.
>DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
>-- -- -- -- -- --
>14 2 92888 76753 92888 76752
>We do have Replication set up in Production, but I drop all replications
>after restored it to Test environment. We are using SQL Server 2000.
>Any help will be appreciated.
>Perayu
>|||I did not run Backup before run ShrintFile. But we backup log file everyday.
I just want to truncate the unused portion because the SQLPERF shows there
are only 10% space are used.
Thanks for your help.
Perayu
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0dthp1pqj5hnpejtc0gao5hr1a5rcaju27@.
4ax.com...
> Did you backup the log first? Refer to:
> http://support.microsoft.com/?id=272318
> -Sue
> On Thu, 8 Dec 2005 08:37:43 -0600, "Perayu"
> <yu.he@.state.mn.us.Remove4Replay> wrote:
>
>|||Here's some comments on the topic: http://www.karaszi.com/SQLServer/in...i
nk.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:%23XBv5XM$FHA.3444@.TK2MSFTNGP10.phx.gbl...
>I did not run Backup before run ShrintFile. But we backup log file everyday
. I just want to
>truncate the unused portion because the SQLPERF shows there are only 10% sp
ace are used.
> Thanks for your help.
> Perayu
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:0dthp1pqj5hnpejtc0gao5hr1a5rcaju27@.
4ax.com...
>

Can't Shrink log File

Hi, All,
I tried to shrink a log file which was restored from production backup, but
got error. The log file is about only 10% usage according to the result of
DBCC SQLPERF ( LOGSPACE ).
When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
message:
Cannot shrink log file 2 (productionlog) because all logical log files are
in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
-- -- -- -- -- --
14 2 92888 76753 92888 76752
We do have Replication set up in Production, but I drop all replications
after restored it to Test environment. We are using SQL Server 2000.
Any help will be appreciated.
Perayu
Did you backup the log first? Refer to:
http://support.microsoft.com/?id=272318
-Sue
On Thu, 8 Dec 2005 08:37:43 -0600, "Perayu"
<yu.he@.state.mn.us.Remove4Replay> wrote:

>Hi, All,
>I tried to shrink a log file which was restored from production backup, but
>got error. The log file is about only 10% usage according to the result of
>DBCC SQLPERF ( LOGSPACE ).
>When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
>message:
>Cannot shrink log file 2 (productionlog) because all logical log files are
>in use.
>DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
>-- -- -- -- -- --
>14 2 92888 76753 92888 76752
>We do have Replication set up in Production, but I drop all replications
>after restored it to Test environment. We are using SQL Server 2000.
>Any help will be appreciated.
>Perayu
>
|||I did not run Backup before run ShrintFile. But we backup log file everyday.
I just want to truncate the unused portion because the SQLPERF shows there
are only 10% space are used.
Thanks for your help.
Perayu
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0dthp1pqj5hnpejtc0gao5hr1a5rcaju27@.4ax.com...
> Did you backup the log first? Refer to:
> http://support.microsoft.com/?id=272318
> -Sue
> On Thu, 8 Dec 2005 08:37:43 -0600, "Perayu"
> <yu.he@.state.mn.us.Remove4Replay> wrote:
>
|||Here's some comments on the topic: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:%23XBv5XM$FHA.3444@.TK2MSFTNGP10.phx.gbl...
>I did not run Backup before run ShrintFile. But we backup log file everyday. I just want to
>truncate the unused portion because the SQLPERF shows there are only 10% space are used.
> Thanks for your help.
> Perayu
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:0dthp1pqj5hnpejtc0gao5hr1a5rcaju27@.4ax.com...
>
sql

Can't Shrink log File

Hi, All,
I tried to shrink a log file which was restored from production backup, but
got error. The log file is about only 10% usage according to the result of
DBCC SQLPERF ( LOGSPACE ).
When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
message:
Cannot shrink log file 2 (productionlog) because all logical log files are
in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
-- -- -- -- -- --
14 2 92888 76753 92888 76752
We do have Replication set up in Production, but I drop all replications
after restored it to Test environment. We are using SQL Server 2000.
Any help will be appreciated.
PerayuDid you backup the log first? Refer to:
http://support.microsoft.com/?id=272318
-Sue
On Thu, 8 Dec 2005 08:37:43 -0600, "Perayu"
<yu.he@.state.mn.us.Remove4Replay> wrote:
>Hi, All,
>I tried to shrink a log file which was restored from production backup, but
>got error. The log file is about only 10% usage according to the result of
>DBCC SQLPERF ( LOGSPACE ).
>When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
>message:
>Cannot shrink log file 2 (productionlog) because all logical log files are
>in use.
>DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
>-- -- -- -- -- --
>14 2 92888 76753 92888 76752
>We do have Replication set up in Production, but I drop all replications
>after restored it to Test environment. We are using SQL Server 2000.
>Any help will be appreciated.
>Perayu
>|||I did not run Backup before run ShrintFile. But we backup log file everyday.
I just want to truncate the unused portion because the SQLPERF shows there
are only 10% space are used.
Thanks for your help.
Perayu
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0dthp1pqj5hnpejtc0gao5hr1a5rcaju27@.4ax.com...
> Did you backup the log first? Refer to:
> http://support.microsoft.com/?id=272318
> -Sue
> On Thu, 8 Dec 2005 08:37:43 -0600, "Perayu"
> <yu.he@.state.mn.us.Remove4Replay> wrote:
>>Hi, All,
>>I tried to shrink a log file which was restored from production backup,
>>but
>>got error. The log file is about only 10% usage according to the result of
>>DBCC SQLPERF ( LOGSPACE ).
>>When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
>>message:
>>Cannot shrink log file 2 (productionlog) because all logical log files are
>>in use.
>>DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
>>-- -- -- -- -- --
>>14 2 92888 76753 92888 76752
>>We do have Replication set up in Production, but I drop all replications
>>after restored it to Test environment. We are using SQL Server 2000.
>>Any help will be appreciated.
>>Perayu
>|||Here's some comments on the topic: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:%23XBv5XM$FHA.3444@.TK2MSFTNGP10.phx.gbl...
>I did not run Backup before run ShrintFile. But we backup log file everyday. I just want to
>truncate the unused portion because the SQLPERF shows there are only 10% space are used.
> Thanks for your help.
> Perayu
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:0dthp1pqj5hnpejtc0gao5hr1a5rcaju27@.4ax.com...
>> Did you backup the log first? Refer to:
>> http://support.microsoft.com/?id=272318
>> -Sue
>> On Thu, 8 Dec 2005 08:37:43 -0600, "Perayu"
>> <yu.he@.state.mn.us.Remove4Replay> wrote:
>>Hi, All,
>>I tried to shrink a log file which was restored from production backup, but
>>got error. The log file is about only 10% usage according to the result of
>>DBCC SQLPERF ( LOGSPACE ).
>>When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
>>message:
>>Cannot shrink log file 2 (productionlog) because all logical log files are
>>in use.
>>DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
>>-- -- -- -- -- --
>>14 2 92888 76753 92888 76752
>>We do have Replication set up in Production, but I drop all replications
>>after restored it to Test environment. We are using SQL Server 2000.
>>Any help will be appreciated.
>>Perayu
>>
>

can't shrink a log file

actually i have a db file size of 400MB and it's log file about 4.5GB, i rea
d
all about backup log and shrink the file, and the problems is every virtual
log file has an status of active, some are really old ( 2002 ), how can i
turn off the virtual files and left only one active (the last)Do you have any uncommitted transactions? What does DBCC OPENTRAN output?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fidel Ramrez" <Fidel Ramrez@.discussions.microsoft.com> wrote in message
news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> actually i have a db file size of 400MB and it's log file about 4.5GB, i
read
> all about backup log and shrink the file, and the problems is every
virtual
> log file has an status of active, some are really old ( 2002 ), how can i
> turn off the virtual files and left only one active (the last)|||this is the message result
Información de transacción para la base de datos 'SII'.
Información de transacción duplicada:
LSN distribuido más antiguo : (4796:432:1)
LSN no distribuido más antiguo : (4796:457:1)
Ejecución de DBCC completada. Si hay mensajes de error, consulte al
administrador del sistema.
transaction information for db SII (my db)
transaction information duplicated
LSN older distributed : (4796:432:1)
LSN older not distributed : (4796:457:1)
DBCC execution completed. If there are error messages, contact system
administrator.
?What's next?
"Paul S Randal [MS]" wrote:

> Do you have any uncommitted transactions? What does DBCC OPENTRAN output?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Fidel Ram_rez" <Fidel Ramrez@.discussions.microsoft.com> wrote in message
> news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> read
> virtual
>
>|||Looks like you're running SQL Server 7.0 - is that correct? If so, DBCC
OPENTRAN doesn't tell you the start time of the oldest transaction but I
suspect you've got an uncommitted transaction that's preventing the log from
being truncated. Your best bet is to contact PSS
(http://support.microsoft.com) for help and guidance.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fidel Ramirez" <Fidel Ramirez@.discussions.microsoft.com> wrote in message
news:54DEF4D8-CA4B-497C-A998-FE7E0FB59346@.microsoft.com...[vbcol=seagreen]
> this is the message result
> Informacin de transaccin para la base de datos 'SII'.
> Informacin de transaccin duplicada:
> LSN distribuido ms antiguo : (4796:432:1)
> LSN no distribuido ms antiguo : (4796:457:1)
> Ejecucin de DBCC completada. Si hay mensajes de error, consulte al
> administrador del sistema.
>
> transaction information for db SII (my db)
> transaction information duplicated
> LSN older distributed : (4796:432:1)
> LSN older not distributed : (4796:457:1)
> DBCC execution completed. If there are error messages, contact system
> administrator.
> What's next?
>
> "Paul S Randal [MS]" wrote:
>
output?[vbcol=seagreen]
rights.[vbcol=seagreen]
message[vbcol=seagreen]
i[vbcol=seagreen]
can i[vbcol=seagreen]|||thnks a lot i'll do it
"Paul S Randal [MS]" wrote:

> Looks like you're running SQL Server 7.0 - is that correct? If so, DBCC
> OPENTRAN doesn't tell you the start time of the oldest transaction but I
> suspect you've got an uncommitted transaction that's preventing the log fr
om
> being truncated. Your best bet is to contact PSS
> (http://support.microsoft.com) for help and guidance.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Fidel Ramirez" <Fidel Ramirez@.discussions.microsoft.com> wrote in message
> news:54DEF4D8-CA4B-497C-A998-FE7E0FB59346@.microsoft.com...
> output?
> rights.
> message
> i
> can i
>
>

can't shrink a log file

actually i have a db file size of 400MB and it's log file about 4.5GB, i read
all about backup log and shrink the file, and the problems is every virtual
log file has an status of active, some are really old ( 2002 ), how can i
turn off the virtual files and left only one active (the last)
Do you have any uncommitted transactions? What does DBCC OPENTRAN output?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fidel Ramrez" <Fidel Ramrez@.discussions.microsoft.com> wrote in message
news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> actually i have a db file size of 400MB and it's log file about 4.5GB, i
read
> all about backup log and shrink the file, and the problems is every
virtual
> log file has an status of active, some are really old ( 2002 ), how can i
> turn off the virtual files and left only one active (the last)
|||this is the message result
Información de transacción para la base de datos 'SII'.
Información de transacción duplicada:
LSN distribuido más antiguo : (4796:432:1)
LSN no distribuido más antiguo : (4796:457:1)
Ejecución de DBCC completada. Si hay mensajes de error, consulte al
administrador del sistema.
transaction information for db SII (my db)
transaction information duplicated
LSN older distributed : (4796:432:1)
LSN older not distributed : (4796:457:1)
DBCC execution completed. If there are error messages, contact system
administrator.
?What's next?
"Paul S Randal [MS]" wrote:

> Do you have any uncommitted transactions? What does DBCC OPENTRAN output?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Fidel RamXrez" <Fidel Ramrez@.discussions.microsoft.com> wrote in message
> news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> read
> virtual
>
>
|||Looks like you're running SQL Server 7.0 - is that correct? If so, DBCC
OPENTRAN doesn't tell you the start time of the oldest transaction but I
suspect you've got an uncommitted transaction that's preventing the log from
being truncated. Your best bet is to contact PSS
(http://support.microsoft.com) for help and guidance.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fidel Ramirez" <Fidel Ramirez@.discussions.microsoft.com> wrote in message
news:54DEF4D8-CA4B-497C-A998-FE7E0FB59346@.microsoft.com...[vbcol=seagreen]
> this is the message result
> Informacin de transaccin para la base de datos 'SII'.
> Informacin de transaccin duplicada:
> LSN distribuido ms antiguo : (4796:432:1)
> LSN no distribuido ms antiguo : (4796:457:1)
> Ejecucin de DBCC completada. Si hay mensajes de error, consulte al
> administrador del sistema.
>
> transaction information for db SII (my db)
> transaction information duplicated
> LSN older distributed : (4796:432:1)
> LSN older not distributed : (4796:457:1)
> DBCC execution completed. If there are error messages, contact system
> administrator.
> What's next?
>
> "Paul S Randal [MS]" wrote:
output?[vbcol=seagreen]
rights.[vbcol=seagreen]
message[vbcol=seagreen]
i[vbcol=seagreen]
can i[vbcol=seagreen]
|||thnks a lot i'll do it
"Paul S Randal [MS]" wrote:

> Looks like you're running SQL Server 7.0 - is that correct? If so, DBCC
> OPENTRAN doesn't tell you the start time of the oldest transaction but I
> suspect you've got an uncommitted transaction that's preventing the log from
> being truncated. Your best bet is to contact PSS
> (http://support.microsoft.com) for help and guidance.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Fidel Ramirez" <Fidel Ramirez@.discussions.microsoft.com> wrote in message
> news:54DEF4D8-CA4B-497C-A998-FE7E0FB59346@.microsoft.com...
> output?
> rights.
> message
> i
> can i
>
>

can't shrink a log file

actually i have a db file size of 400MB and it's log file about 4.5GB, i read
all about backup log and shrink the file, and the problems is every virtual
log file has an status of active, some are really old ( 2002 ), how can i
turn off the virtual files and left only one active (the last)Do you have any uncommitted transactions? What does DBCC OPENTRAN output?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fidel Ramírez" <Fidel Ramrez@.discussions.microsoft.com> wrote in message
news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> actually i have a db file size of 400MB and it's log file about 4.5GB, i
read
> all about backup log and shrink the file, and the problems is every
virtual
> log file has an status of active, some are really old ( 2002 ), how can i
> turn off the virtual files and left only one active (the last)|||this is the message result
Información de transacción para la base de datos 'SII'.
Información de transacción duplicada:
LSN distribuido más antiguo : (4796:432:1)
LSN no distribuido más antiguo : (4796:457:1)
Ejecución de DBCC completada. Si hay mensajes de error, consulte al
administrador del sistema.
transaction information for db SII (my db)
transaction information duplicated
LSN older distributed : (4796:432:1)
LSN older not distributed : (4796:457:1)
DBCC execution completed. If there are error messages, contact system
administrator.
¿What's next?
"Paul S Randal [MS]" wrote:
> Do you have any uncommitted transactions? What does DBCC OPENTRAN output?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Fidel Ramírez" <Fidel Ramrez@.discussions.microsoft.com> wrote in message
> news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> > actually i have a db file size of 400MB and it's log file about 4.5GB, i
> read
> > all about backup log and shrink the file, and the problems is every
> virtual
> > log file has an status of active, some are really old ( 2002 ), how can i
> > turn off the virtual files and left only one active (the last)
>
>|||Looks like you're running SQL Server 7.0 - is that correct? If so, DBCC
OPENTRAN doesn't tell you the start time of the oldest transaction but I
suspect you've got an uncommitted transaction that's preventing the log from
being truncated. Your best bet is to contact PSS
(http://support.microsoft.com) for help and guidance.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fidel Ramirez" <Fidel Ramirez@.discussions.microsoft.com> wrote in message
news:54DEF4D8-CA4B-497C-A998-FE7E0FB59346@.microsoft.com...
> this is the message result
> Información de transacción para la base de datos 'SII'.
> Información de transacción duplicada:
> LSN distribuido más antiguo : (4796:432:1)
> LSN no distribuido más antiguo : (4796:457:1)
> Ejecución de DBCC completada. Si hay mensajes de error, consulte al
> administrador del sistema.
>
> transaction information for db SII (my db)
> transaction information duplicated
> LSN older distributed : (4796:432:1)
> LSN older not distributed : (4796:457:1)
> DBCC execution completed. If there are error messages, contact system
> administrator.
> ¿What's next?
>
> "Paul S Randal [MS]" wrote:
> > Do you have any uncommitted transactions? What does DBCC OPENTRAN
output?
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "Fidel Ramírez" <Fidel Ramrez@.discussions.microsoft.com> wrote in
message
> > news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> > > actually i have a db file size of 400MB and it's log file about 4.5GB,
i
> > read
> > > all about backup log and shrink the file, and the problems is every
> > virtual
> > > log file has an status of active, some are really old ( 2002 ), how
can i
> > > turn off the virtual files and left only one active (the last)
> >
> >
> >|||thnks a lot i'll do it
"Paul S Randal [MS]" wrote:
> Looks like you're running SQL Server 7.0 - is that correct? If so, DBCC
> OPENTRAN doesn't tell you the start time of the oldest transaction but I
> suspect you've got an uncommitted transaction that's preventing the log from
> being truncated. Your best bet is to contact PSS
> (http://support.microsoft.com) for help and guidance.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Fidel Ramirez" <Fidel Ramirez@.discussions.microsoft.com> wrote in message
> news:54DEF4D8-CA4B-497C-A998-FE7E0FB59346@.microsoft.com...
> > this is the message result
> >
> > Información de transacción para la base de datos 'SII'.
> >
> > Información de transacción duplicada:
> > LSN distribuido más antiguo : (4796:432:1)
> > LSN no distribuido más antiguo : (4796:457:1)
> > Ejecución de DBCC completada. Si hay mensajes de error, consulte al
> > administrador del sistema.
> >
> >
> > transaction information for db SII (my db)
> > transaction information duplicated
> > LSN older distributed : (4796:432:1)
> > LSN older not distributed : (4796:457:1)
> > DBCC execution completed. If there are error messages, contact system
> > administrator.
> >
> > ¿What's next?
> >
> >
> > "Paul S Randal [MS]" wrote:
> >
> > > Do you have any uncommitted transactions? What does DBCC OPENTRAN
> output?
> > >
> > > --
> > > Paul Randal
> > > Dev Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "Fidel Ramírez" <Fidel Ramrez@.discussions.microsoft.com> wrote in
> message
> > > news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> > > > actually i have a db file size of 400MB and it's log file about 4.5GB,
> i
> > > read
> > > > all about backup log and shrink the file, and the problems is every
> > > virtual
> > > > log file has an status of active, some are really old ( 2002 ), how
> can i
> > > > turn off the virtual files and left only one active (the last)
> > >
> > >
> > >
>
>

can't set backup retention

We have a SQL 2000 MSDE when I use maintenance plan to set up
backups, Remove files older than window, when I click on the screw down lit
for the options (minutes, hours, days, weeks, months), there is nothing to
choose.
I remember there is an msde table or something needs to add. I forgot about
it...
Any advice from Microsoft...
I have searched on google, nothing showing up except talking about SQL
2005...This is only for SQL 2000 MSDE.Hi everybody,
Can I get answer for this today'
"renhai" wrote:

> We have a SQL 2000 MSDE when I use maintenance plan to set up
> backups, Remove files older than window, when I click on the screw down li
t
> for the options (minutes, hours, days, weeks, months), there is nothing t
o
> choose.
> I remember there is an msde table or something needs to add. I forgot abou
t
> it...
> Any advice from Microsoft...
> I have searched on google, nothing showing up except talking about SQL
> 2005...This is only for SQL 2000 MSDE.
>|||I think maybe your are thinking about the missing registry
key. That process executes:
xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLSe
rver',
N'BackupDirectory'
If there is no BackupDirectory string value in the key
HKLM\Software\Microsoft\MSSQLServer\MSSQ
LServer, you get an
error and nothing is displayed for the time increments.
-Sue
On Fri, 19 May 2006 14:58:01 -0700, renhai
<renhai@.discussions.microsoft.com> wrote:

>We have a SQL 2000 MSDE when I use maintenance plan to set up
>backups, Remove files older than window, when I click on the screw down lit
>for the options (minutes, hours, days, weeks, months), there is nothing to
>choose.
>I remember there is an msde table or something needs to add. I forgot about
>it...
>Any advice from Microsoft...
>I have searched on google, nothing showing up except talking about SQL
>2005...This is only for SQL 2000 MSDE.sql

can't set backup retention

We have a SQL 2000 MSDE when I use maintenance plan to set up
backups, Remove files older than window, when I click on the screw down lit
for the options (minutes, hours, days, weeks, months), there is nothing to
choose.
I remember there is an msde table or something needs to add. I forgot about
it...
Any advice from Microsoft...
I have searched on google, nothing showing up except talking about SQL
2005...This is only for SQL 2000 MSDE.Hi everybody,
Can I get answer for this today'
"renhai" wrote:
> We have a SQL 2000 MSDE when I use maintenance plan to set up
> backups, Remove files older than window, when I click on the screw down lit
> for the options (minutes, hours, days, weeks, months), there is nothing to
> choose.
> I remember there is an msde table or something needs to add. I forgot about
> it...
> Any advice from Microsoft...
> I have searched on google, nothing showing up except talking about SQL
> 2005...This is only for SQL 2000 MSDE.
>|||I think maybe your are thinking about the missing registry
key. That process executes:
xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory'
If there is no BackupDirectory string value in the key
HKLM\Software\Microsoft\MSSQLServer\MSSQLServer, you get an
error and nothing is displayed for the time increments.
-Sue
On Fri, 19 May 2006 14:58:01 -0700, renhai
<renhai@.discussions.microsoft.com> wrote:
>We have a SQL 2000 MSDE when I use maintenance plan to set up
>backups, Remove files older than window, when I click on the screw down lit
>for the options (minutes, hours, days, weeks, months), there is nothing to
>choose.
>I remember there is an msde table or something needs to add. I forgot about
>it...
>Any advice from Microsoft...
>I have searched on google, nothing showing up except talking about SQL
>2005...This is only for SQL 2000 MSDE.

Can't see Mapped Drives in Database Maintenance tool!

I have SQL2000 & SQL2005 running on Windows 2003 servers.
I am using the Database Maintenance tool to backup selected databases. I
realized the only drives I can save these backups to are the local drives. We
don't have enough space on these local drives, so is there anyway to see the
network mapped drives for this procedure?
Thanks,
Ted
Don't use a mapped drive. Use the UNC pathing instead. You need to ensure
the account SQL Server is running under has permissions to read and write to
this share first.
http://support.microsoft.com/kb/555128
Andrew J. Kelly SQL MVP
"Infomax" <Infomax@.discussions.microsoft.com> wrote in message
news:EBB765BE-0A41-456C-9615-C6AB069AAADB@.microsoft.com...
>I have SQL2000 & SQL2005 running on Windows 2003 servers.
> I am using the Database Maintenance tool to backup selected databases. I
> realized the only drives I can save these backups to are the local drives.
> We
> don't have enough space on these local drives, so is there anyway to see
> the
> network mapped drives for this procedure?
> Thanks,
> Ted

Can't see D partition

I have SQL 2000 and SQL 2005 installed on a XP machine. The machine has one
physical drive divided into 5 partitions. When I choose to backup or restor
e
I can only see C and E partitions but not the D partition. All three are
NTFS partitions.
Thanks in advance.
JohnI found the problem, but don't understand why. The D partition did not have
the Everyone group define to read the drive, however other groups as well as
myself specifically have rights to it. Why does the group Everyone need to
have read in order to see it?
"John" wrote:

> I have SQL 2000 and SQL 2005 installed on a XP machine. The machine has o
ne
> physical drive divided into 5 partitions. When I choose to backup or rest
ore
> I can only see C and E partitions but not the D partition. All three are
> NTFS partitions.
> Thanks in advance.
> John|||John wrote:
> I have SQL 2000 and SQL 2005 installed on a XP machine. The machine has o
ne
> physical drive divided into 5 partitions. When I choose to backup or rest
ore
> I can only see C and E partitions but not the D partition. All three are
> NTFS partitions.
> Thanks in advance.
> John
Probably a permissions issue. What happens if you use the BACKUP
DATABASE command to send a backup to that volume?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||John wrote:
> I found the problem, but don't understand why. The D partition did not ha
ve
> the Everyone group define to read the drive, however other groups as well
as
> myself specifically have rights to it. Why does the group Everyone need t
o
> have read in order to see it?
>
When you run a backup, it's going to be run within the context of the
user that SQL Server is running under, NOT your personal login. Check
the service account for the SQL Server service, if that user doesn't
have rights to see the D: partition, then it's not going to show up in
your GUI.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Playing some more and comparing to a collegues setup, it appers to be the
local group system was missing.
Thanks so much for the quick responses.
"Tracy McKibben" wrote:

> John wrote:
> Probably a permissions issue. What happens if you use the BACKUP
> DATABASE command to send a backup to that volume?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Can't see D partition

I have SQL 2000 and SQL 2005 installed on a XP machine. The machine has one
physical drive divided into 5 partitions. When I choose to backup or restore
I can only see C and E partitions but not the D partition. All three are
NTFS partitions.
Thanks in advance.
JohnI found the problem, but don't understand why. The D partition did not have
the Everyone group define to read the drive, however other groups as well as
myself specifically have rights to it. Why does the group Everyone need to
have read in order to see it?
"John" wrote:
> I have SQL 2000 and SQL 2005 installed on a XP machine. The machine has one
> physical drive divided into 5 partitions. When I choose to backup or restore
> I can only see C and E partitions but not the D partition. All three are
> NTFS partitions.
> Thanks in advance.
> John|||John wrote:
> I have SQL 2000 and SQL 2005 installed on a XP machine. The machine has one
> physical drive divided into 5 partitions. When I choose to backup or restore
> I can only see C and E partitions but not the D partition. All three are
> NTFS partitions.
> Thanks in advance.
> John
Probably a permissions issue. What happens if you use the BACKUP
DATABASE command to send a backup to that volume?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||John wrote:
> I found the problem, but don't understand why. The D partition did not have
> the Everyone group define to read the drive, however other groups as well as
> myself specifically have rights to it. Why does the group Everyone need to
> have read in order to see it?
>
When you run a backup, it's going to be run within the context of the
user that SQL Server is running under, NOT your personal login. Check
the service account for the SQL Server service, if that user doesn't
have rights to see the D: partition, then it's not going to show up in
your GUI.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Playing some more and comparing to a collegues setup, it appers to be the
local group system was missing.
Thanks so much for the quick responses.
"Tracy McKibben" wrote:
> John wrote:
> > I have SQL 2000 and SQL 2005 installed on a XP machine. The machine has one
> > physical drive divided into 5 partitions. When I choose to backup or restore
> > I can only see C and E partitions but not the D partition. All three are
> > NTFS partitions.
> >
> > Thanks in advance.
> > John
> Probably a permissions issue. What happens if you use the BACKUP
> DATABASE command to send a backup to that volume?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Cant Schedule Backup

Hello All,

I using sqlserver 6.5 and I am trying to schedule a backup and I get this error message, how can I resolve this? This is the error message I am getting:

"The SQLExecutive service is not currently running on this server. This prevents task backup-master from being run."

Thanks in Advance.How about starting SQLExecutive service? I'm sure it'll help a lot!|||how is that done. There doesn't seem to be a start up for that service or choice to start it up. The only check box is a startup autumatically when server startup.

Can't run rskeymgmt after SP2

After installing SP2 for SQL 2005 I can no longer run rskeymgmt. I was
trying to backup the encryption key
(rskeymgmt -e -fc:\rsdbkey.txt -ppassword) but I get the following error:
The Report Server Windows service in not responding to RPC requests. The
service may not be running.
I have checked and the service is running. Are there any issues with SP2?On Apr 5, 1:21 pm, "Tim Kelley" <tkel...@.company.com> wrote:
> After installing SP2 for SQL 2005 I can no longer run rskeymgmt. I was
> trying to backup the encryption key
> (rskeymgmt -e -fc:\rsdbkey.txt -ppassword) but I get the following error:
> The Report Server Windows service in not responding to RPC requests. The
> service may not be running.
> I have checked and the service is running. Are there any issues with SP2?
This article might be helpful:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1323751&SiteID=1
Regards,
Enrique Martinez
Sr. Software Consultant

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.

Hi Oscar,

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]