backup error - single user mode needed?

Hi Everyone,
One of my clients is running SQL 2000 SP4 and the Data Maintenance Plan
logs shows a log with...
Database master: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
SQL Server Driver][SQL Server]Repair statement not processed. Database
needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
...
[5] Database master: Database Backup...
The backup was not performed since data verification errors were found.

There are similar messages for databases model and msdb.
Questions:
- should master, model and msdb be included in the daily backup?
- how does one get into single user mode and repair this?
Thanks
Richard
Hi,
In your maintenance plan, uncheck the Attempt to repair minor problems
checkbox in Integrity tab.
With that checkbox unchecked, integrity checks will be still done on the
databases and as a good practice verify your maintenance logs
for any integroty errors. If there is any error you could manually correct
it.
Thanks
Hari
SQL Server MVP
"Richard Fagen" <no_spam@.aol.com> wrote in message
news:O3JolEukFHA.2792@.TK2MSFTNGP10.phx.gbl...
> Hi Everyone,
> One of my clients is running SQL 2000 SP4 and the Data Maintenance Plan
> logs shows a log with...
> --
> Database master: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> ...
> [5] Database master: Database Backup...
> The backup was not performed since data verification errors were
> found.
>
> --
> There are similar messages for databases model and msdb.
> Questions:
> - should master, model and msdb be included in the daily backup?
> - how does one get into single user mode and repair this?
> Thanks
> Richard
|||hi Richard,
Richard Fagen wrote:
is the disk damaged?

> Questions:
> - should master, model and msdb be included in the daily backup?
usually not.. only when server modifications occur, like implementing new
jobs, alerts, logins and so on...

> - how does one get into single user mode and repair this?
http://msdn.microsoft.com/library/de...start_4nhh.asp
http://msdn.microsoft.com/library/de...kprst_4g4w.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea Montanari wrote:
> hi Richard,
> Richard Fagen wrote:
> is the disk damaged?
always forget the Maintenance Plan is that intrusive (and bugged ) ..
always prefer self written jobs :D
yep.. Hari is right
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Hari,
I'll uncheck the box and try it again.
I use SQL as part of SBS 2000/2003 and that seems to go against what
I've read in books. All the books say be sure to check that box
However, since your both SQL MVPs and Andrea has helped me out before,
I'll go with your recommendations.
Richard
p.s. the one who says to check it, Harry Brelsford, is an MVP too, but
of SBS not SQL
Hari Prasad wrote:
> Hi,
> In your maintenance plan, uncheck the Attempt to repair minor problems
> checkbox in Integrity tab.
> With that checkbox unchecked, integrity checks will be still done on the
> databases and as a good practice verify your maintenance logs
> for any integroty errors. If there is any error you could manually correct
> it.
> Thanks
> Hari
> SQL Server MVP
>
> "Richard Fagen" <no_spam@.aol.com> wrote in message
> news:O3JolEukFHA.2792@.TK2MSFTNGP10.phx.gbl...
>
>
|||Hi Andrea,

> is the disk damaged?
Not that I know of. Besides, I remotely logged into a few SBS clients
to test the data maintenance plan with backing up master, model and msdb
and they all had the same error messages.
I guess it wouldn't hurt to have everyone run a scandisk (I try to get
them to do it on their own, but they never remember) and doublecheck.

> usually not.. only when server modifications occur, like implementing new
> jobs, alerts, logins and so on...
I can manually backup master, model and msdb via the Enterprise Manager,
just not via the maintenance plan.
I hope they fix this in SQL 2005

> http://msdn.microsoft.com/library/de...start_4nhh.asp
> http://msdn.microsoft.com/library/de...kprst_4g4w.asp
Thanks for the links
Richard
|||hi Richard,
Richard Fagen wrote:
> Hi Andrea,
> Not that I know of. Besides, I remotely logged into a few SBS clients
> to test the data maintenance plan with backing up master, model and
> msdb and they all had the same error messages.
see Hari post... Maintenance Plan is "intrusive" by default and tryes to
perform activities requiring single user access...

> I guess it wouldn't hurt to have everyone run a scandisk (I try to get
> them to do it on their own, but they never remember) and doublecheck.
but ok. checking disk integrity is always neat :D

> I can manually backup master, model and msdb via the Enterprise
> Manager, just not via the maintenance plan.
again, see Hari answer...

> I hope they fix this in SQL 2005
SQLExpress will not include the Agent, so no Maintenance Plan at all... but
worse, no jobs at all, and that includes both admin/house keeping jobs as
other dataload,dml jobs
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||From Microsoft Article 290622:
BUG: Databse Manintenance Plan on System Databases faile on Intergrity
Check if "Attempt to repair minor problems" is Selected
... The database maintenance plan attempts to place the system databases
in the single user mode in order to execute CkDBRepair. However the
Master and MSDB databases cannot be placed in single user mode because of
constantly running system processes like LAZYWRITER, LOG WRITER, SQL
Agent... The repair step fails to run and the job reports a failure...
There is no fix for tis bug as yet. The workaround is to disable the
"Attempt to repair any minor problems" option in your maintenance plan.
Lyle
In article <O3JolEukFHA.2792@.TK2MSFTNGP10.phx.gbl>, no_spam@.aol.com
says...
> Hi Everyone,
> One of my clients is running SQL 2000 SP4 and the Data Maintenance Plan
> logs shows a log with...
> --
> Database master: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> ...
> [5] Database master: Database Backup...
> The backup was not performed since data verification errors were found.
>
> --
> There are similar messages for databases model and msdb.
> Questions:
> - should master, model and msdb be included in the daily backup?
> - how does one get into single user mode and repair this?
> Thanks
> Richard
>
|||Hi Lyle,
Thanks for the article. Finally, a great explanation
Richard
Lyle wrote:

> From Microsoft Article 290622:
> BUG: Databse Manintenance Plan on System Databases faile on Intergrity
> Check if "Attempt to repair minor problems" is Selected
> ... The database maintenance plan attempts to place the system databases
> in the single user mode in order to execute CkDBRepair. However the
> Master and MSDB databases cannot be placed in single user mode because of
> constantly running system processes like LAZYWRITER, LOG WRITER, SQL
> Agent... The repair step fails to run and the job reports a failure...
> There is no fix for tis bug as yet. The workaround is to disable the
> "Attempt to repair any minor problems" option in your maintenance plan.
> Lyle
sql

Backup DTS packages

We have a development environment where there are 4 development servers and a single production server.

some of our processes rely of some complex DTS packages.

what is the perferred method to create copies of these DTS packages so that they can be put onto all the development systems ?Check out this article:

http://www.codeproject.com/useritems/DTS_Export.asp|||thanks - thats what i was looking for.

it even has the added bonus of some code to reset all the connections to a different server.sql

Backup a single table?

In SQL 6.5 you could backup a single table in a db to a
backupdevice or a file. I can not find this in the newer
versions SQL 7.0 and SQL 2000. Is this feature removed?
I know that the command has changed from DUMP to BACKUP
and that you nowadays often backup db-files or filegroups
instead.
This feature has been removed with SQL2000
"Stefan" <stefan@.discussions.microsoft.com> wrote in message
news:033c01c4b815$4a8a0600$a601280a@.phx.gbl...
> In SQL 6.5 you could backup a single table in a db to a
> backupdevice or a file. I can not find this in the newer
> versions SQL 7.0 and SQL 2000. Is this feature removed?
> I know that the command has changed from DUMP to BACKUP
> and that you nowadays often backup db-files or filegroups
> instead.
>
|||> I know that the command has changed from DUMP to BACKUP
> and that you nowadays often backup db-files or filegroups
> instead.
You will have to either (a) put it onto its own filegroup and backup the
filegroup, or (b) DTS a copy into an empty database and backup that
database.
http://www.aspfaq.com/
(Reverse address to reply.)

Backup a single table?

In SQL 6.5 you could backup a single table in a db to a
backupdevice or a file. I can not find this in the newer
versions SQL 7.0 and SQL 2000. Is this feature removed?
I know that the command has changed from DUMP to BACKUP
and that you nowadays often backup db-files or filegroups
instead.This feature has been removed with SQL2000
"Stefan" <stefan@.discussions.microsoft.com> wrote in message
news:033c01c4b815$4a8a0600$a601280a@.phx.gbl...
> In SQL 6.5 you could backup a single table in a db to a
> backupdevice or a file. I can not find this in the newer
> versions SQL 7.0 and SQL 2000. Is this feature removed?
> I know that the command has changed from DUMP to BACKUP
> and that you nowadays often backup db-files or filegroups
> instead.
>|||> I know that the command has changed from DUMP to BACKUP
> and that you nowadays often backup db-files or filegroups
> instead.
You will have to either (a) put it onto its own filegroup and backup the
filegroup, or (b) DTS a copy into an empty database and backup that
database.
--
http://www.aspfaq.com/
(Reverse address to reply.)

Backup a single table?

In SQL 6.5 you could backup a single table in a db to a
backupdevice or a file. I can not find this in the newer
versions SQL 7.0 and SQL 2000. Is this feature removed?
I know that the command has changed from DUMP to BACKUP
and that you nowadays often backup db-files or filegroups
instead.This feature has been removed with SQL2000
"Stefan" <stefan@.discussions.microsoft.com> wrote in message
news:033c01c4b815$4a8a0600$a601280a@.phx.gbl...
> In SQL 6.5 you could backup a single table in a db to a
> backupdevice or a file. I can not find this in the newer
> versions SQL 7.0 and SQL 2000. Is this feature removed?
> I know that the command has changed from DUMP to BACKUP
> and that you nowadays often backup db-files or filegroups
> instead.
>|||> I know that the command has changed from DUMP to BACKUP
> and that you nowadays often backup db-files or filegroups
> instead.
You will have to either (a) put it onto its own filegroup and backup the
filegroup, or (b) DTS a copy into an empty database and backup that
database.
http://www.aspfaq.com/
(Reverse address to reply.)

backup

Hi
how to backup a single db on multiple files to split into smaller files .
Regards
AjayBACKUP DATABASE northwind TO
DISK = 'c:\bkp\backup_1.BAK' ,
DISK = 'c:\bkp\backup_2.BAK' ,
DISK = 'c:\bkp\backup_3.BAK'
This will stripe the backup across all these files.
However, all devices must be of the same type ex : disk,
tape etc.
- Vishal

Backing up transaction logs

Hi,
I have a single Windows Server 2003 and SQL Server 2000.
I have created a maintanance plan to back up my SQL data
base as well as the transaction log. I am getting this
error in my event viewer and haven't been able to figure
out why. I have recreated the job, making sure I was
signed in as administrator and it didn't change
anything. I have also gone into Logins under enterprise
manager and the user xxxdomainxxx\administrator does not
exist and I an not able to add it (it says it can't find
it). Any help is appreciated. Thanks
SQL Server Scheduled Job 'Transaction Log Backup Job for
DB Maintenance Plan 'DB Maintenance Plan2''
(0xB829073EE903F94A8783BC84D54506C6) - Status: Failed -
Invoked on: 2004-03-30 13:00:00 - Message: The job
failed. Unable to determine if the owner
(xxxdomainxxx\Administrator) of job Transaction Log
Backup Job for DB Maintenance Plan 'DB Maintenance Plan2'
has server access (reason: Could not obtain information
about Windows NT group/user 'xxxdomainxxx\Administrator'.
[SQLSTATE 42000] (Error 8198)).Have a look at these. I tend to set job owner to sa to avoid this
http://support.microsoft.com/defaul...3&Product=sql2k
http://support.microsoft.com/defaul...4&Product=sql2k
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Travis" <anonymous@.discussions.microsoft.com> wrote in message
news:15b4301c41684$27b8e7a0$a301280a@.phx
.gbl...
> Hi,
> I have a single Windows Server 2003 and SQL Server 2000.
> I have created a maintanance plan to back up my SQL data
> base as well as the transaction log. I am getting this
> error in my event viewer and haven't been able to figure
> out why. I have recreated the job, making sure I was
> signed in as administrator and it didn't change
> anything. I have also gone into Logins under enterprise
> manager and the user xxxdomainxxx\administrator does not
> exist and I an not able to add it (it says it can't find
> it). Any help is appreciated. Thanks
>
> SQL Server Scheduled Job 'Transaction Log Backup Job for
> DB Maintenance Plan 'DB Maintenance Plan2''
> (0xB829073EE903F94A8783BC84D54506C6) - Status: Failed -
> Invoked on: 2004-03-30 13:00:00 - Message: The job
> failed. Unable to determine if the owner
> (xxxdomainxxx\Administrator) of job Transaction Log
> Backup Job for DB Maintenance Plan 'DB Maintenance Plan2'
> has server access (reason: Could not obtain information
> about Windows NT group/user 'xxxdomainxxx\Administrator'.
> [SQLSTATE 42000] (Error 8198)).|||Thanks for your reply. I set the job owner to sa like
you suggested. However, now I am getting this error in
my event viewer. I think it says it was invoked by
administrator because I went to jobs and right clicked
and selected run? Any other ideas? Thanks
SQL Server Scheduled Job 'Transaction Log Backup Job for
DB Maintenance Plan 'DB Maintenance Plan1''
(0xD35D479ECC6E8840A93C091C8876BF07) - Status: Failed -
Invoked on: 2004-03-30 15:29:31 - Message: The job
failed. The Job was invoked by User
xxxdomainxxx\Administrator. The last step to run was
step 1 (Step 1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.

>--Original Message--
>Have a look at these. I tend to set job owner to sa to
avoid this
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;241643&Product=sql2k
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;834124&Product=sql2k
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Travis" <anonymous@.discussions.microsoft.com> wrote in
message
> news:15b4301c41684$27b8e7a0$a301280a@.phx
.gbl...
2000.
data
figure
enterprise
not
find
for
Plan2'
group/user 'xxxdomainxxx\Administrator'.
>
>.
>|||You'll need to get the details of the failure to resolve
the issue.
o find the details of the failures with maintenance plans,
you need to go to the maintenance plans, right click on the
plan that is failing and select Database Maintenance Plan
History.
-Sue
On Tue, 30 Mar 2004 12:41:21 -0800, "Travis"
<anonymous@.discussions.microsoft.com> wrote:
>Thanks for your reply. I set the job owner to sa like
>you suggested. However, now I am getting this error in
>my event viewer. I think it says it was invoked by
>administrator because I went to jobs and right clicked
>and selected run? Any other ideas? Thanks
>SQL Server Scheduled Job 'Transaction Log Backup Job for
>DB Maintenance Plan 'DB Maintenance Plan1''
>(0xD35D479ECC6E8840A93C091C8876BF07) - Status: Failed -
>Invoked on: 2004-03-30 15:29:31 - Message: The job
>failed. The Job was invoked by User
>xxxdomainxxx\Administrator. The last step to run was
>step 1 (Step 1).
>For more information, see Help and Support Center at
>http://go.microsoft.com/fwlink/events.asp.
>
>avoid this
>us;241643&Product=sql2k
>us;834124&Product=sql2k
>message
>2000.
>data
>figure
>enterprise
>not
>find
>for
>Plan2'
>group/user 'xxxdomainxxx\Administrator'.|||Thanks for your reply!
I did what you said and it looks like it is backing up
the "master" and "msdb" databases and it shouldn't be.
So should I recreate the mantenance plan and not include
these in the backup, or will I need them?
Thanks!

>--Original Message--
> You'll need to get the details of the failure to resolve
>the issue.
>o find the details of the failures with maintenance
plans,
>you need to go to the maintenance plans, right click on
the
>plan that is failing and select Database Maintenance Plan
>History.
>-Sue
>On Tue, 30 Mar 2004 12:41:21 -0800, "Travis"
><anonymous@.discussions.microsoft.com> wrote:
>
for
in
this
Failed -
information
>.
>|||If you specified a report you should see more error details in there. From
the sound of it you may have specified a single plan for all databases and
included log backups. You can't do log backups of master and SQLAgent will
set msdb to simple when it starts if it's not already so you can't really do
if for msdb either. I'd usggest 1 plan for system databases (full backups
only) , 1 for user databases in simple mode and one for databases in full
recovery mode with log backups.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Travis" <anonymous@.discussions.microsoft.com> wrote in message
news:131a101c4169e$5a5e9b90$a601280a@.phx
.gbl...
> Thanks for your reply!
> I did what you said and it looks like it is backing up
> the "master" and "msdb" databases and it shouldn't be.
> So should I recreate the mantenance plan and not include
> these in the backup, or will I need them?
> Thanks!
>
> plans,
> the
> for
> in
> this
> Failed -
> information|||I got it working with no errors! I did like you said and
created 3 seperate plans. Thanks so much for your help.

>--Original Message--
>If you specified a report you should see more error
details in there. From
>the sound of it you may have specified a single plan for
all databases and
>included log backups. You can't do log backups of master
and SQLAgent will
>set msdb to simple when it starts if it's not already so
you can't really do
>if for msdb either. I'd usggest 1 plan for system
databases (full backups
>only) , 1 for user databases in simple mode and one for
databases in full
>recovery mode with log backups.
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Travis" <anonymous@.discussions.microsoft.com> wrote in
message
> news:131a101c4169e$5a5e9b90$a601280a@.phx
.gbl...
include
resolve
on
Plan
like
in
clicked
Failed -
to
scid=kb;en-
scid=kb;en-
SQL
was
does
can't
Job
Log
>
>.
>|||The error that I am having(which is along the same vane) is occurring
when trying to delete old Tlog backup files.
Any ideas how to fix this one?
Travis wrote:[vbcol=seagreen]
> I got it working with no errors! I did like you said and
> created 3 seperate plans. Thanks so much for your help.
>
> details in there. From
> all databases and
> and SQLAgent will
> you can't really do
> databases (full backups
> databases in full
> message
> include
> resolve
> on
> Plan
> like
> in
> clicked
> Failed -
> to
> scid=kb;en-
> scid=kb;en-
> SQL
> was
> does
> can't
> Job
> Log|||There are a couple of completely different error messages
posted in this thread. Could you please post exactly what
problems you are having and the exact error message?
For example, is this an error you get with a maintenance
plan, please post that and some details about the task in
the maintenance plan. If you are receiving errors when a
maintenance plan runs, get the details of the error from the
maintenance plan history and post those.
It's hard to tell from piggy backing off this thread what
your specific issue is. If you could post more details,
someone should be able to help you out.
Thanks -
-Sue
On 20 Jan 2005 07:10:19 -0800, "Tim"
<Timothy.Juarez@.Ipaper.com> wrote:
[vbcol=seagreen]
>The error that I am having(which is along the same vane) is occurring
>when trying to delete old Tlog backup files.
>
>Any ideas how to fix this one?
>
>Travis wrote: