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 database design

How do I export/create a script into a file with create table...index...
views etc for an existing database ?
Hi,
script the database using the tools from either Enterprise Manager or
Managment Studio (you did not specify the Sl Server version). They have
a task to script out the data, in multiple steps can be defined which
objects should be select for the script creation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de

Backup database design

How do I export/create a script into a file with create table...index...
views etc for an existing database ?Hi,
script the database using the tools from either Enterprise Manager or
Managment Studio (you did not specify the Sl Server version). They have
a task to script out the data, in multiple steps can be defined which
objects should be select for the script creation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--

Backup database and index

Just wonder is what my understanding is correct:
1) If I put database and index onto same filegroup, when I schedule a
database backup, is the index will also be backup together with the database
?
2) If I put database and index onto separate filegroups, I need to schedule
2 backup jobs: one backup for database, another job for index at the same
schedule datetime ?
Alan,
Fear not! No matter how many data files or filegroups that you have, a full
backup will preserve all the data in the database including indexes, as long
as you don't specify the filegroup or filename in the backup statement.
Indeed the same is true for transaction log and differential backups too.
e.g.
BACKUP DATABASE Northwind NwindGroup1 TO <dumpdevice> will only backup the
NWindGroup1 filegroup.
however:
BACKUP DATABASE Northwind TO <dumpdevice> backs up all files across all
filegroups.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%233Nt6i3qEHA.3244@.tk2msftngp13.phx.gbl...
> Just wonder is what my understanding is correct:
> 1) If I put database and index onto same filegroup, when I schedule a
> database backup, is the index will also be backup together with the
database
> ?
> 2) If I put database and index onto separate filegroups, I need to
schedule
> 2 backup jobs: one backup for database, another job for index at the same
> schedule datetime ?
>
|||Additionally , SQL Server checks for and requires that a table and index be
backed up together... Even if you put them on separate filegroups and try to
backup only one of the FGs and not both, SQL will stop you with an error
that indicates you must backup and table and all of it's indexes together.
As Mark says,, Fear not!
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
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%233Nt6i3qEHA.3244@.tk2msftngp13.phx.gbl...
> Just wonder is what my understanding is correct:
> 1) If I put database and index onto same filegroup, when I schedule a
> database backup, is the index will also be backup together with the
database
> ?
> 2) If I put database and index onto separate filegroups, I need to
schedule
> 2 backup jobs: one backup for database, another job for index at the same
> schedule datetime ?
>

Backup database and index

Just wonder is what my understanding is correct:
1) If I put database and index onto same filegroup, when I schedule a
database backup, is the index will also be backup together with the database
?
2) If I put database and index onto separate filegroups, I need to schedule
2 backup jobs: one backup for database, another job for index at the same
schedule datetime ?Alan,
Fear not! No matter how many data files or filegroups that you have, a full
backup will preserve all the data in the database including indexes, as long
as you don't specify the filegroup or filename in the backup statement.
Indeed the same is true for transaction log and differential backups too.
e.g.
BACKUP DATABASE Northwind NwindGroup1 TO <dumpdevice> will only backup the
NWindGroup1 filegroup.
however:
BACKUP DATABASE Northwind TO <dumpdevice> backs up all files across all
filegroups.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%233Nt6i3qEHA.3244@.tk2msftngp13.phx.gbl...
> Just wonder is what my understanding is correct:
> 1) If I put database and index onto same filegroup, when I schedule a
> database backup, is the index will also be backup together with the
database
> ?
> 2) If I put database and index onto separate filegroups, I need to
schedule
> 2 backup jobs: one backup for database, another job for index at the same
> schedule datetime ?
>|||Additionally , SQL Server checks for and requires that a table and index be
backed up together... Even if you put them on separate filegroups and try to
backup only one of the FGs and not both, SQL will stop you with an error
that indicates you must backup and table and all of it's indexes together.
As Mark says,, Fear not!
--
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
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%233Nt6i3qEHA.3244@.tk2msftngp13.phx.gbl...
> Just wonder is what my understanding is correct:
> 1) If I put database and index onto same filegroup, when I schedule a
> database backup, is the index will also be backup together with the
database
> ?
> 2) If I put database and index onto separate filegroups, I need to
schedule
> 2 backup jobs: one backup for database, another job for index at the same
> schedule datetime ?
>