Backup fails with ConnectionRead (WrapperRead()

For the past week my maintenance plan that backs up the
master and my main database has been failing, but only on
my database. The master backup works fine. The plan fails
when trying to execute:
BACKUP DATABASE [WebTools] TO DISK = N'G:\SQLBackup\WebTools\WebTools_db_200310290834.BAK'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
When I try running this command via Enterprise Manager, it
reports a syntax error "WITHINIT" (no space). I removed
all the WITH settings and re-ran it, but then it failed
with a ConnectionRead error.
Using Query Analyzer, the command runs for a few seconds
and then reports the error:
[Microsoft][ODBC SQL Server Driver][Shared Memory]
ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
THe backup, however, continues and completes successfully
according to Event Viewer.
Can anyone shed some light on this? TIA.
I'm running W2K SP4, SQL2K SP3 on a Dell 2600 with a Xeon
processor (which looks like 2 CPUs).Hello David,
I would appreciate your patience while I am looking into this issue. I'm now performing some
troubleshoots on your issue and will post my response at soon as I have update for you.
Thanks for posting to MSDN Managed Newsgroup.
Regares,
Billy Yao
Microsoft Online Partner Support|||Hi David,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with your issue.
From your description, I understand that your maintenance plan failed for database backup
errors. Therefore, you performed the backup manually but another network error occurred.
However, the backup did succeed according to your event log.
Have I fully understood you David? If there is anything I misunderstood, please feel free to let
me know.
Considering the backup did succeed and the network error is so general, I suspect that the
issue is located in the network library. It is recommended that you apply the latest MDAC 2.8 to
suppress this symptom. You can download this MDAC via:
http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-
185d0506396c&DisplayLang=en
If you use Named Pipes Net-Library to connect to the SQL Server database, I strongly
recommend you use the TCP/IP Net-Library instead.
For the exact steps to set the TCP/IP network library on the client where you are performing the
backup or the restore operation, see the "How to configure a client to use TCP/IP (Client
Network Utility)" chapter in SQL Server 2000 Books Online.
When you connect to an instance of SQL Server by using SQL Query Analyzer, you can force
the connection to use the TCP/IP Net-Library. To do this, type the name of the instance of SQL
Server with the tcp prefix in the SQL Server text box in the Connect to SQL Server dialog box.
This appears as follows:
tcp:SQL Server Name
Another possible cause of backup failure is that there was no space in the disk drive due to
the fact that the transaction logs were not got truncated. In this case, the logs may fill up disk
space, and you need to shrink the transaction log files first.
To resolve this problem, please follow the steps in the following articles to truncate the
transaction log
272318 INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318
David, please apply the suggestions above and let me know if it helps you resolve your
problem. If there is anything more I can assist you with, please feel free to post it in the group.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks Billy.
First... I had already installed MDAC 2.8 as part of an
attempt to resolve a different problem, and disk space
wasn't an issue.
I then tried to use QA to connect via TCP to SQL Server,
but couldn't. I checked the network settings in SQL Server
and it indicated both Named Pipes and TCP/IP were
available. When I checked the log, however, it said it was
only listening on the named pipes.
I did some more digging, but nothing fixed that problem. I
wound up removing SQL Server from the system and re-
installing it from scratch. The log showed it was now
listening on TCP as well. When I tried the backups via QA
and the maintenance plan, both worked.|||Dear David,
Thank you for your good news!
I'm glad that the problem was solved by re-installing the SQL Server. I
think the cause of SQL Server not listening on TCP may be related to some
register key was revised with some unexpect reasons (such as by some
applications).
Anyway, I appreciate your logical troubleshooting and congratulations on
your finding the cause and solve the problem.
Thank you for participating our newsgroup!
- Billy

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

Dear all,
I backup the master, msdb, and my database from sql server 2000 by the add a backup devices and backup all of these db into the backup devices..
Then it have one .bak file including the db's. Is it can restore by the sql server 2000 when i reinstall the windows server and sql 2000 server?
or can i restore it into a new hardware system with windows server and sql 2000server?
Hi;
This BAK file can be restored in the below scenarios:
1. Restore in the same SQL server with a different name.
2. Restore it in the SQL Server after installation of Windows and SQL Server
3. Restore it in a SQL server with the same database name in a new hardware
For all the above you can use "RESTORE database " command.
Note:
This BAK files can be used when there is a crash of database. Better copy
this BAK file to
a safe place preferably to a TAPE device.
Thanks
Hari
MCDBA
"ken" <anonymous@.discussions.microsoft.com> wrote in message
news:672AFD82-EF81-4080-96D2-6344C048DCA2@.microsoft.com...
> Dear all,
> I backup the master, msdb, and my database from sql server 2000 by the add
a backup devices and backup all of these db into the backup devices..
> Then it have one .bak file including the db's. Is it can restore by the
sql server 2000 when i reinstall the windows server and sql 2000 server?
> or can i restore it into a new hardware system with windows server and sql
2000server?
|||The SQL Server that you are restoring to has to have the same build number
as the one the backup came from.
This applies to the system databases, not the user databases.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||What is the build number?
If i reinstall the windows server as well as the sql server, will the build number become a new number?

backup database

Dear all,
I backup the master, msdb, and my database from sql server 2000 by the add a
backup devices and backup all of these db into the backup devices..
Then it have one .bak file including the db's. Is it can restore by the sql
server 2000 when i reinstall the windows server and sql 2000 server?
or can i restore it into a new hardware system with windows server and sql 2
000server?Hi;
This BAK file can be restored in the below scenarios:
1. Restore in the same SQL server with a different name.
2. Restore it in the SQL Server after installation of Windows and SQL Server
3. Restore it in a SQL server with the same database name in a new hardware
For all the above you can use "RESTORE database " command.
Note:
This BAK files can be used when there is a crash of database. Better copy
this BAK file to
a safe place preferably to a TAPE device.
Thanks
Hari
MCDBA
"ken" <anonymous@.discussions.microsoft.com> wrote in message
news:672AFD82-EF81-4080-96D2-6344C048DCA2@.microsoft.com...
> Dear all,
> I backup the master, msdb, and my database from sql server 2000 by the add
a backup devices and backup all of these db into the backup devices..
> Then it have one .bak file including the db's. Is it can restore by the
sql server 2000 when i reinstall the windows server and sql 2000 server?
> or can i restore it into a new hardware system with windows server and sql
2000server?|||The SQL Server that you are restoring to has to have the same build number
as the one the backup came from.
This applies to the system databases, not the user databases.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||What is the build number?
If i reinstall the windows server as well as the sql server, will the build
number become a new number?

Backup and restore question plz help!

If i have old full database back up of master and msdn and every other database that i had on a old sql server 2000 enterprise. can i use the back up file to recreate the same eviroment on a new installed fresh sql server 2000 enterprise boxYes you can restore to the original state.|||The instance must have the same Service-packs already applied however. For example, If the backups are from SQL with service pack 3. Master won't restore on an SQL without this service pack.

Backup and restore databases and access rights

Hi,

I am creating a way of working in order to "copy" databases from a master SQL Server 2000 database to the developers local machines.

I want to create a master SQL Server at our office location. Whenever our developers works on site the master is used. To be able to work locally, a backup has been created of the master and is distributed to the developers. On their local machines, they restore the database to be able to develop locally.

I have created a backup on my laptop using on my domain account (not the account that created the database, but I can use it when developing). I moved the backup file to another computer and restored it using my domain account on that computer. I could not see all of the tables, users or stored procedures, only those which type are System, not User. When i log in to the local administrators account I can see them all. When I log back into my domain account I can see all of the tables.

Anyone knows why?

/M

SQL Enterprise Manager doesn't refresh objects very well. You actually have to force it to refresh sometimes by hitting F5 and even that doesn't always work. Something to try in this case is a quick select statement in query analyzer of a table that you know should be there. Typically it will either show the results or give you the error associated with permissions.

It also sounds like it could be db permissions problems. Typically, SEM connects to the local server via the sa login. So when you login as yourself, you are probably connecting to the server as yourself in SEM. Also keep in mind that objects are owned by their creators and unless permissions are specifically granted for other users/roles or the current user is an admin, you won't be able to work with these objects. So if you created objects under one login (such as sa) you may not be able to see them under another login (such as your domain account). sp_changeobjectowner is a way to modify the owner for each object. sp_MScheck_uid_owns_anything will help determine if the user owns anything; just use the id of the user as the only parameter.

Another option would be to grant all permissions to the public role for all objects as they are created. This is not advised because you are not using SQL Security like it should. But roles are a good thing in environments like this, but these take some setup and administration time.

backup

Hi friends,
I don't have master db has backup itself. Now how to rebuild the db?
Thanks in advance
vanithaRead about REBUILDM.EXE in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:C6BC86AF-7E94-456D-80F3-37B2D4FEBC41@.microsoft.com...
> Hi friends,
> I don't have master db has backup itself. Now how to rebuild the db?
> Thanks in advance
> vanitha

Backup

We are running SQL Server 2000, SP3. Do we need to stop SQL service before
backing up all the database (master, msdb, etc.)?
Thanks.No, SQL Server has always allowed you to back up a database while the server
is running.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jill Johnson" <jjohnson362000@.yahoo.com> wrote in message
news:#TVYkJfZDHA.4020@.tk2msftngp13.phx.gbl...
> We are running SQL Server 2000, SP3. Do we need to stop SQL service
before
> backing up all the database (master, msdb, etc.)?
> Thanks.
>|||Thank you, Kalen.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uaBGjNfZDHA.1280@.tk2msftngp13.phx.gbl...
> No, SQL Server has always allowed you to back up a database while the
server
> is running.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Jill Johnson" <jjohnson362000@.yahoo.com> wrote in message
> news:#TVYkJfZDHA.4020@.tk2msftngp13.phx.gbl...
> > We are running SQL Server 2000, SP3. Do we need to stop SQL service
> before
> > backing up all the database (master, msdb, etc.)?
> >
> > Thanks.
> >
> >
>

Backup

How do you back up the Agent Jobs and the Local Packages under Data
Transformation services? Does this get backed up when you back up the Master
database?
Thanks
RickThey get backed up when you backup the msdb database.
-Sue
On Wed, 13 Aug 2003 15:14:27 -0700, "Rick V"
<rick@.di-wave.com> wrote:
>How do you back up the Agent Jobs and the Local Packages under Data
>Transformation services? Does this get backed up when you back up the Master
>database?
> Thanks
> Rick
>|||Thanks very much!!!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:9ngljvsocne65i7n5lsftphqpddsqs579u@.4ax.com...
> They get backed up when you backup the msdb database.
> -Sue
> On Wed, 13 Aug 2003 15:14:27 -0700, "Rick V"
> <rick@.di-wave.com> wrote:
> >How do you back up the Agent Jobs and the Local Packages under Data
> >Transformation services? Does this get backed up when you back up the
Master
> >database?
> >
> > Thanks
> > Rick
> >
>

Backing up the Services Master Key

What happens if you don't backup the Service Master Key, or don't know where
the backup is? Will this keep you from restoring the Master Database? How
does this backup play in the disaster recovery?Hi
"DBA GUY" wrote:
> What happens if you don't backup the Service Master Key, or don't know where
> the backup is? Will this keep you from restoring the Master Database? How
> does this backup play in the disaster recovery?
Check out
http://blogs.msdn.com/lcris/archive/2005/07/08/sql-server-2005-a-look-at-the-master-keys.aspx
and other posts on the blog. The absolute worse case will be that you won't
be able to access the encrypted data in any of your databases, but if you
know the passwords to the DMKs you can drop the SMK encryption and re-apply
it.
John

Backing up the Services Master Key

What happens if you don't backup the Service Master Key, or don't know where
the backup is? Will this keep you from restoring the Master Database? How
does this backup play in the disaster recovery?
Hi
"DBA GUY" wrote:

> What happens if you don't backup the Service Master Key, or don't know where
> the backup is? Will this keep you from restoring the Master Database? How
> does this backup play in the disaster recovery?
Check out
http://blogs.msdn.com/lcris/archive/2005/07/08/sql-server-2005-a-look-at-the-master-keys.aspx
and other posts on the blog. The absolute worse case will be that you won't
be able to access the encrypted data in any of your databases, but if you
know the passwords to the DMKs you can drop the SMK encryption and re-apply
it.
John

Backing up the Services Master Key

What happens if you don't backup the Service Master Key, or don't know where
the backup is? Will this keep you from restoring the Master Database? How
does this backup play in the disaster recovery?Hi
"DBA GUY" wrote:

> What happens if you don't backup the Service Master Key, or don't know whe
re
> the backup is? Will this keep you from restoring the Master Database? Ho
w
> does this backup play in the disaster recovery?
Check out
http://blogs.msdn.com/lcris/archive.../>
r-keys.aspx
and other posts on the blog. The absolute worse case will be that you won't
be able to access the encrypted data in any of your databases, but if you
know the passwords to the DMKs you can drop the SMK encryption and re-apply
it.
John

Backing up the Master, Model and MSDB databases

Hi,
I am new to SQL 2000 Server and as far as I have read, it seems that I
should backup the Master, Model and MSDB databases.
However, Should I...
1.) Backup the transaction log as part of the maintenance plan?
2.) Reorganize data and index pages?
3.) Remove unused space from database files?
4.) Check database integrity and should I include or exclude indexes? If
indexes are included/excluded, should I check to Attempt to repair any minor
problems and should I perform these checks before doing backups?
5.) Create a folder for each database?
6.) Remove files older than the default of 4 weeks or should I extend the
period?
Lastly,
7.) Write history to the table msdb.dbo.sysdbmaintplan_history and should I
limit rows to 1k, 5k, 10k, 100k rows for this plan?
Thank You in advance,
Dave
Try to give an answer to all of your numbered qustions, but remember that:
a) Is a good practice make a backup of your system database whenever you've
done some action that updates them; so, regarding the master, for example,
any CREATE, ALTER or DROP statement will surely update it, and make a backup
is a good idea; regarding the MSDB, each create, alter or drop of some Job,
Alert, Operator or any SSIS implementation, etc. will update it; regarding
the model, if you modify it could be a good idea make a backup.
About your qustions:
1) No for master database (you CANNOT backup transaction log of the master
DB!), optional for MSDB and model (but this means you apply a recovery
strategy, and you should carefully consider the interval between each backup)
2) No for master, optional for msdb and model; Oops: you can reorganise just
indexes, not data, considering clustered indexes as indexes and not data...
3)NEVER !!!
4)No for master, optional for msdb and model
5) and 6) As you like.
Generally, the recovery strategy for the system databases differs from the
one adopted for User databases, and a strategy too "regular" could be not so
efficient (suppose you plan a weekly - ora dayly - strategy, but on Tuesday
(for example) some big migration or some big deployment produces a lot of
updates on your system databases: I suggest a backup as soon as possible,
because a crash before the nightly batches could mean an important loss.
Gilberto Zampatti
"Dave" wrote:

> Hi,
> I am new to SQL 2000 Server and as far as I have read, it seems that I
> should backup the Master, Model and MSDB databases.
> However, Should I...
> 1.) Backup the transaction log as part of the maintenance plan?
> 2.) Reorganize data and index pages?
> 3.) Remove unused space from database files?
> 4.) Check database integrity and should I include or exclude indexes? If
> indexes are included/excluded, should I check to Attempt to repair any minor
> problems and should I perform these checks before doing backups?
> 5.) Create a folder for each database?
> 6.) Remove files older than the default of 4 weeks or should I extend the
> period?
> Lastly,
> 7.) Write history to the table msdb.dbo.sysdbmaintplan_history and should I
> limit rows to 1k, 5k, 10k, 100k rows for this plan?
> Thank You in advance,
> Dave

Backing up the Master, Model and MSDB databases

Hi,
I am new to SQL 2000 Server and as far as I have read, it seems that I
should backup the Master, Model and MSDB databases.
However, Should I...
1.) Backup the transaction log as part of the maintenance plan?
2.) Reorganize data and index pages?
3.) Remove unused space from database files?
4.) Check database integrity and should I include or exclude indexes? If
indexes are included/excluded, should I check to Attempt to repair any minor
problems and should I perform these checks before doing backups?
5.) Create a folder for each database?
6.) Remove files older than the default of 4 weeks or should I extend the
period?
Lastly,
7.) Write history to the table msdb.dbo.sysdbmaintplan_history and should I
limit rows to 1k, 5k, 10k, 100k rows for this plan?
Thank You in advance,
DaveTry to give an answer to all of your numbered qustions, but remember that:
a) Is a good practice make a backup of your system database whenever you've
done some action that updates them; so, regarding the master, for example,
any CREATE, ALTER or DROP statement will surely update it, and make a backup
is a good idea; regarding the MSDB, each create, alter or drop of some Job,
Alert, Operator or any SSIS implementation, etc. will update it; regarding
the model, if you modify it could be a good idea make a backup.
About your qustions:
1) No for master database (you CANNOT backup transaction log of the master
DB!), optional for MSDB and model (but this means you apply a recovery
strategy, and you should carefully consider the interval between each backup
)
2) No for master, optional for msdb and model; Oops: you can reorganise just
indexes, not data, considering clustered indexes as indexes and not data...
3)NEVER !!!
4)No for master, optional for msdb and model
5) and 6) As you like.
Generally, the recovery strategy for the system databases differs from the
one adopted for User databases, and a strategy too "regular" could be not so
efficient (suppose you plan a weekly - ora dayly - strategy, but on Tuesday
(for example) some big migration or some big deployment produces a lot of
updates on your system databases: I suggest a backup as soon as possible,
because a crash before the nightly batches could mean an important loss.
Gilberto Zampatti
"Dave" wrote:

> Hi,
> I am new to SQL 2000 Server and as far as I have read, it seems that I
> should backup the Master, Model and MSDB databases.
> However, Should I...
> 1.) Backup the transaction log as part of the maintenance plan?
> 2.) Reorganize data and index pages?
> 3.) Remove unused space from database files?
> 4.) Check database integrity and should I include or exclude indexes? If
> indexes are included/excluded, should I check to Attempt to repair any min
or
> problems and should I perform these checks before doing backups?
> 5.) Create a folder for each database?
> 6.) Remove files older than the default of 4 weeks or should I extend the
> period?
> Lastly,
> 7.) Write history to the table msdb.dbo.sysdbmaintplan_history and should
I
> limit rows to 1k, 5k, 10k, 100k rows for this plan?
> Thank You in advance,
> Dave

Backing up the Master, Model and MSDB databases

Hi,
I am new to SQL 2000 Server and as far as I have read, it seems that I
should backup the Master, Model and MSDB databases.
However, Should I...
1.) Backup the transaction log as part of the maintenance plan?
2.) Reorganize data and index pages?
3.) Remove unused space from database files?
4.) Check database integrity and should I include or exclude indexes? If
indexes are included/excluded, should I check to Attempt to repair any minor
problems and should I perform these checks before doing backups?
5.) Create a folder for each database?
6.) Remove files older than the default of 4 weeks or should I extend the
period?
Lastly,
7.) Write history to the table msdb.dbo.sysdbmaintplan_history and should I
limit rows to 1k, 5k, 10k, 100k rows for this plan?
Thank You in advance,
DaveTry to give an answer to all of your numbered qustions, but remember that:
a) Is a good practice make a backup of your system database whenever you've
done some action that updates them; so, regarding the master, for example,
any CREATE, ALTER or DROP statement will surely update it, and make a backup
is a good idea; regarding the MSDB, each create, alter or drop of some Job,
Alert, Operator or any SSIS implementation, etc. will update it; regarding
the model, if you modify it could be a good idea make a backup.
About your qustions:
1) No for master database (you CANNOT backup transaction log of the master
DB!), optional for MSDB and model (but this means you apply a recovery
strategy, and you should carefully consider the interval between each backup)
2) No for master, optional for msdb and model; Oops: you can reorganise just
indexes, not data, considering clustered indexes as indexes and not data...
3)NEVER !!!
4)No for master, optional for msdb and model
5) and 6) As you like.
Generally, the recovery strategy for the system databases differs from the
one adopted for User databases, and a strategy too "regular" could be not so
efficient (suppose you plan a weekly - ora dayly - strategy, but on Tuesday
(for example) some big migration or some big deployment produces a lot of
updates on your system databases: I suggest a backup as soon as possible,
because a crash before the nightly batches could mean an important loss.
Gilberto Zampatti
"Dave" wrote:
> Hi,
> I am new to SQL 2000 Server and as far as I have read, it seems that I
> should backup the Master, Model and MSDB databases.
> However, Should I...
> 1.) Backup the transaction log as part of the maintenance plan?
> 2.) Reorganize data and index pages?
> 3.) Remove unused space from database files?
> 4.) Check database integrity and should I include or exclude indexes? If
> indexes are included/excluded, should I check to Attempt to repair any minor
> problems and should I perform these checks before doing backups?
> 5.) Create a folder for each database?
> 6.) Remove files older than the default of 4 weeks or should I extend the
> period?
> Lastly,
> 7.) Write history to the table msdb.dbo.sysdbmaintplan_history and should I
> limit rows to 1k, 5k, 10k, 100k rows for this plan?
> Thank You in advance,
> Dave