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 and Restore using SQL Server with Merge Replication

We have a project where we are replicating filtered data sets to SQL Express
clients. We are thinking of a back and restore approach that uses SQL
Express. The a SQL Express Instance would receive all data thru replication
then that data set would be backed up. We are pursuing the approach because
of distance and location of the central replication server and logistics of
backing it up. Or managers have requested Microsoftâ's documented or
undocumented view on this approach. If there is any online documentation
that outlines a process like this or a document that illustrates that this
can be done and is a feasible back and restore approach, please post back.
Thanks in advance.
--
Alexander L. Wykel
MIT ProfessionalsHi Alexander,
I am not sure if I totally understand your meaning. Based on your
description, I understand that you have a project which can replicate
filtered data sets to your SQL Express clients and that you would like to
know of a backup and restore approach for your SQL Express clients so that
they can backup the data set after they receive all data through
replication function in your project. So your question here is that you
would like to know a method for backing up your data set in your SQL
Express client, right?
If I have misunderstood, please let me know.
Unfortunately there is no backup method at partial data set level in SQL
Server 2005, but only at file level or database level. Regarding this, you
can refer to BACKUP DATABASE statement in SQL Server 2005 Books Online.
At SQL Server side, I think that you may consider the following two ways:
1. Use incremental database backup for your SQL Express client.
2. Create those tables on a dedicated file and then only backup your
database on that file.
Of course if your client database is not large, you may directly perform a
full database backup. This depends on your database size and performance
requirement.
You may refer to the following articles:
Copying Databases with Backup and Restore
http://msdn2.microsoft.com/en-us/library/ms190436.aspx
Backing Up and Restoring Databases in SQL Server
http://technet.microsoft.com/en-us/library/ms187048.aspx
Strategies for Backing Up and Restoring Merge Replication
http://technet.microsoft.com/en-us/library/ms152497.aspx
Backing Up and Restoring Replicated Databases
http://technet.microsoft.com/en-us/library/ms151152.aspx
If you have any other questions or concerns, please feel free to let me
know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
=========================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================|||Sorry Charles,
My outline of the project details left al lot of unresolved issues for you
to have to piece together. The process is simple, for now forget about the
individual clients whom are receiving filtered data sets. What our primary
aim is to replicate the entire data set from the publisher to a local
Subscriber SQL Express instance. Our Corporate network is distributed and
our disaster recovery center is located off site (which we intend on
implementing via hot spare), so we would like to have local copy of the
entire data set (the data that is really important to our organization, to
use to rebuild the server while itâ's down and the hot spare is in use) not
the replication and publisher subscriber information, that we would have to
rebuild. If worst comes to worst we can rebuild that from the main
replicated data set which will be managed locally.
I guess the critical path question is can SQL Express server in this
capacity for our needs? Can you rebuild a SQL Publisher Server Instance from
an SQL Express Subscriber Instance? I have heard of Replication Database
Mirroring techniques:
â'The Publisher and Distributor must be Microsoft SQL Server 2005.
Subscribers can be SQL Server 2005 or a previous versionâ'
â'Replication supports mirroring the publication database for merge
replication and for transactional replication with read-only Subscribers or
queued updating Subscribers. Immediate updating Subscribers, Oracle
Publishers, Publishers in a peer-to-peer topology, and republishing are not
supported.â'
SQL Server 2005 Books On-line (September 2007)
Replication and Database Mirroring
- http://msdn2.microsoft.com/en-us/library/ms151799.aspx
Not sure if this means we can use this technology. From what Iâ'm reading it
looks like it does, could you clarify it for me please?
--
Alexander L. Wykel
MIT Professionals
"Charles Wang[MSFT]" wrote:
> Hi Alexander,
> I am not sure if I totally understand your meaning. Based on your
> description, I understand that you have a project which can replicate
> filtered data sets to your SQL Express clients and that you would like to
> know of a backup and restore approach for your SQL Express clients so that
> they can backup the data set after they receive all data through
> replication function in your project. So your question here is that you
> would like to know a method for backing up your data set in your SQL
> Express client, right?
> If I have misunderstood, please let me know.
> Unfortunately there is no backup method at partial data set level in SQL
> Server 2005, but only at file level or database level. Regarding this, you
> can refer to BACKUP DATABASE statement in SQL Server 2005 Books Online.
> At SQL Server side, I think that you may consider the following two ways:
> 1. Use incremental database backup for your SQL Express client.
> 2. Create those tables on a dedicated file and then only backup your
> database on that file.
> Of course if your client database is not large, you may directly perform a
> full database backup. This depends on your database size and performance
> requirement.
> You may refer to the following articles:
> Copying Databases with Backup and Restore
> http://msdn2.microsoft.com/en-us/library/ms190436.aspx
> Backing Up and Restoring Databases in SQL Server
> http://technet.microsoft.com/en-us/library/ms187048.aspx
> Strategies for Backing Up and Restoring Merge Replication
> http://technet.microsoft.com/en-us/library/ms152497.aspx
> Backing Up and Restoring Replicated Databases
> http://technet.microsoft.com/en-us/library/ms151152.aspx
> If you have any other questions or concerns, please feel free to let me
> know. Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =========================================================> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg@.microsoft.com.
> =========================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================>
>
>
>
>
>
>|||I have one minor correction, the site we intend to replicate the full data
set is off site (SQL Express Subscriber). This allows us to incrementally
send data as it arrives as apposed to sending large backup distributions.
Then the main back up would then proceed from the off site location. We
intend to back up the MDF file as apposed to creating a backup, then if the
main location goes down we send the attach the MDF and rebuild the Publisher
Data Set from the full copy of the Subscriber MDF data set.
--
Alexander L. Wykel
MIT Professionals
"Alexander Wykel" wrote:
> Sorry Charles,
> My outline of the project details left al lot of unresolved issues for you
> to have to piece together. The process is simple, for now forget about the
> individual clients whom are receiving filtered data sets. What our primary
> aim is to replicate the entire data set from the publisher to a local
> Subscriber SQL Express instance. Our Corporate network is distributed and
> our disaster recovery center is located off site (which we intend on
> implementing via hot spare), so we would like to have local copy of the
> entire data set (the data that is really important to our organization, to
> use to rebuild the server while itâ's down and the hot spare is in use) not
> the replication and publisher subscriber information, that we would have to
> rebuild. If worst comes to worst we can rebuild that from the main
> replicated data set which will be managed locally.
> I guess the critical path question is can SQL Express server in this
> capacity for our needs? Can you rebuild a SQL Publisher Server Instance from
> an SQL Express Subscriber Instance? I have heard of Replication Database
> Mirroring techniques:
> â'The Publisher and Distributor must be Microsoft SQL Server 2005.
> Subscribers can be SQL Server 2005 or a previous versionâ'
> â'Replication supports mirroring the publication database for merge
> replication and for transactional replication with read-only Subscribers or
> queued updating Subscribers. Immediate updating Subscribers, Oracle
> Publishers, Publishers in a peer-to-peer topology, and republishing are not
> supported.â'
> SQL Server 2005 Books On-line (September 2007)
> Replication and Database Mirroring
> - http://msdn2.microsoft.com/en-us/library/ms151799.aspx
> Not sure if this means we can use this technology. From what Iâ'm reading it
> looks like it does, could you clarify it for me please?
> --
> Alexander L. Wykel
> MIT Professionals
>
> "Charles Wang[MSFT]" wrote:
> > Hi Alexander,
> > I am not sure if I totally understand your meaning. Based on your
> > description, I understand that you have a project which can replicate
> > filtered data sets to your SQL Express clients and that you would like to
> > know of a backup and restore approach for your SQL Express clients so that
> > they can backup the data set after they receive all data through
> > replication function in your project. So your question here is that you
> > would like to know a method for backing up your data set in your SQL
> > Express client, right?
> > If I have misunderstood, please let me know.
> >
> > Unfortunately there is no backup method at partial data set level in SQL
> > Server 2005, but only at file level or database level. Regarding this, you
> > can refer to BACKUP DATABASE statement in SQL Server 2005 Books Online.
> >
> > At SQL Server side, I think that you may consider the following two ways:
> > 1. Use incremental database backup for your SQL Express client.
> > 2. Create those tables on a dedicated file and then only backup your
> > database on that file.
> >
> > Of course if your client database is not large, you may directly perform a
> > full database backup. This depends on your database size and performance
> > requirement.
> >
> > You may refer to the following articles:
> > Copying Databases with Backup and Restore
> > http://msdn2.microsoft.com/en-us/library/ms190436.aspx
> >
> > Backing Up and Restoring Databases in SQL Server
> > http://technet.microsoft.com/en-us/library/ms187048.aspx
> >
> > Strategies for Backing Up and Restoring Merge Replication
> > http://technet.microsoft.com/en-us/library/ms152497.aspx
> >
> > Backing Up and Restoring Replicated Databases
> > http://technet.microsoft.com/en-us/library/ms151152.aspx
> >
> > If you have any other questions or concerns, please feel free to let me
> > know. Have a nice day!
> >
> > Best regards,
> > Charles Wang
> > Microsoft Online Community Support
> > =========================================================> > Delighting our customers is our #1 priority. We welcome your
> > comments and suggestions about how we can improve the
> > support we provide to you. Please feel free to let my manager
> > know what you think of the level of service provided. You can
> > send feedback directly to my manager at: msdnmg@.microsoft.com.
> > =========================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > =========================================================> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >|||Hi Alexander,
Thanks for your response.
Indeed Database Mirroring can provide high availability (automatic
failover) at database level, however it does not support SQL Server Express
Edition. Only SQL Server 2005 Standard/Enterprise Edition can support this
feature.
For SQL Server Express Edition, you can use SQL Server replication to
replicate data from your publisher to your subscriber. However do you
really need merge replication? If the data change on your subscriber need
not be merged to your publisher, I recommend that you use Transactional
replication so that you can win the best performance among the existing
three types of SQL Server replication.
Though transactional replication has very high performance for replicating
data, actually it is also not a real time replication as well as other type
of replication, so if you want to use detach/attach methods to recover your
database on your publisher, there might be still at a risk of a little part
data loss. Since the data is very critical to your organization, I
recommend that you may consider the following solutions for your publisher
server: SQL Server failover cluster, or Database Mirroring, or Replication
together with Database Mirroring, or Replication together with failover
cluster.
With SQL Server failover cluster or Database Mirroring for your publisher
servers (two or more nodes), when the primary publisher is down, the
secondary publisher will automatically failover to be the primary
publisher. By using replication together with the failover function, it
brings you an additional level of security safeguard. You can distribute
your data to a different location. If some emergent situations happen, you
can recover your publisher by using this data, though there might be a
little data loss.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
=========================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================