another backup question?  
Author Message
alane





PostPosted: Sun Dec 03 08:31:26 CST 2006 Top

SQL Server Developer >> another backup question?

In maintenance plan, Do you run integrity check before or after the database
backup?

Thanks again,
Culam

SQL Server9  
 
 
Uri





PostPosted: Sun Dec 03 08:31:26 CST 2006 Top

SQL Server Developer >> another backup question? culam
It appears to be before backup database , however , i'd not rely on this.
I'd suggest you write two jobs which have one for FULL database backup and
the second one for BACKUP LOG file depends on your db settings






> In maintenance plan, Do you run integrity check before or after the
> database
> backup?
>
> Thanks again,
> Culam


 
 
Tom





PostPosted: Sun Dec 03 08:35:40 CST 2006 Top

SQL Server Developer >> another backup question? Be careful with integrity checks, since you can get blocking. Ideally, the
integrity check should be done on a copy of the DB.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.


In maintenance plan, Do you run integrity check before or after the database
backup?

Thanks again,
Culam

 
 
culam





PostPosted: Sun Dec 03 09:07:00 CST 2006 Top

SQL Server Developer >> another backup question? Thanks for the warning.
Is this why I get error when I tried to run it. Error message is too
general to pin-point the problem. What is the consequence of what I just
did: ran the integrity check on the server that has no user on it today.
But it cause error regarless.

Thanks again,
Culam



> Be careful with integrity checks, since you can get blocking. Ideally, the
> integrity check should be done on a copy of the DB.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..


> In maintenance plan, Do you run integrity check before or after the database
> backup?
>
> Thanks again,
> Culam
>
>
 
 
Tom





PostPosted: Sun Dec 03 09:12:03 CST 2006 Top

SQL Server Developer >> another backup question? We'd have to see the error number and message.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.


Thanks for the warning.
Is this why I get error when I tried to run it. Error message is too
general to pin-point the problem. What is the consequence of what I just
did: ran the integrity check on the server that has no user on it today.
But it cause error regarless.

Thanks again,
Culam



> Be careful with integrity checks, since you can get blocking. Ideally,
> the
> integrity check should be done on a copy of the DB.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..


> In maintenance plan, Do you run integrity check before or after the
> database
> backup?
>
> Thanks again,
> Culam
>
>

 
 
culam





PostPosted: Sun Dec 03 09:22:00 CST 2006 Top

SQL Server Developer >> another backup question? Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed.



> We'd have to see the error number and message.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..


> Thanks for the warning.
> Is this why I get error when I tried to run it. Error message is too
> general to pin-point the problem. What is the consequence of what I just
> did: ran the integrity check on the server that has no user on it today.
> But it cause error regarless.
>
> Thanks again,
> Culam
>

>
> > Be careful with integrity checks, since you can get blocking. Ideally,
> > the
> > integrity check should be done on a copy of the DB.
> >
> > --
> > Tom
> >
> > ----------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > ..


> > In maintenance plan, Do you run integrity check before or after the
> > database
> > backup?
> >
> > Thanks again,
> > Culam
> >
> >
>
>
 
 
Hari





PostPosted: Sun Dec 03 09:33:57 CST 2006 Top

SQL Server Developer >> another backup question? Hello,

Did you enabled the Transaction log backup as well in the maintenance plan.
If you enabled the transaction log backup ensure that your recovery model
should either FULL or BULK_LOGGED..

One more thing is... Please run the integrity checks which is seperate from
backup. Please choose a time where the database access is almost 0.

Thanks
Hari




> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
> 42000]
> (Error 22029). The step failed.
>

>
>> We'd have to see the error number and message.
>>
>> --
>> Tom
>>
>> ----------------------------------------------------
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> ..


>> Thanks for the warning.
>> Is this why I get error when I tried to run it. Error message is too
>> general to pin-point the problem. What is the consequence of what I just
>> did: ran the integrity check on the server that has no user on it today.
>> But it cause error regarless.
>>
>> Thanks again,
>> Culam
>>

>>
>> > Be careful with integrity checks, since you can get blocking. Ideally,
>> > the
>> > integrity check should be done on a copy of the DB.
>> >
>> > --
>> > Tom
>> >
>> > ----------------------------------------------------
>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> > SQL Server MVP
>> > Toronto, ON Canada
>> > ..


>> > In maintenance plan, Do you run integrity check before or after the
>> > database
>> > backup?
>> >
>> > Thanks again,
>> > Culam
>> >
>> >
>>
>>


 
 
Erland





PostPosted: Sun Dec 03 10:38:22 CST 2006 Top

SQL Server Developer >> another backup question?
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
> 42000]
> (Error 22029). The step failed.

I can't find any message 22029 in sysmessages. In
C:\Program Files\Microsoft SQL Server\MSSQL\LOG there is a log from the
maintenance plan itself. It should have more information.

--


Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 
 
Tracy





PostPosted: Mon Dec 04 08:24:56 CST 2006 Top

SQL Server Developer >> another backup question?
> In maintenance plan, Do you run integrity check before or after the database
> backup?
>
> Thanks again,
> Culam

It seems that you are having some difficulty with the maintenance plan
wizard. There are really three basic "maintenance" functions that you
need to worry about. You can schedule your own processes to do these
things, and can ignore that maintenance wizard. By scheduling your own
processes, you'll have a better understanding of, and better control
over, the housekeeping tasks that are running on your machines:

1. Full backups
- See "BACKUP DATABASE" in Books Online
- Depending on your recovery needs, you'll run this weekly or daily.
For Simple mode databases, daily is recommended. For Bulk-Logged or
Full mode, you can run weekly full backups with transaction log backups
in between. This script will help automate your backups:
http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html

2. Log backups
- See "BACKUP LOG" in Books Online
- Depending on your recovery needs, you run this between full backups.
You cannot run log backups for Simple mode databases. For Full or
Bulk-Logged modes, a common schedule is to run weekly Full backups, with
hourly log backups.

3. Integrity checks
- See DBCC CHECKDB in Books Online
- Run this at an interval you are comfortable with. In my environment,
we run this daily, against a restored copy of the production databases.
This process can cause blocking and slow performance, so avoid running
it against production data during live hours if possible. If this
process reports any errors, then you can repair those using the various
DBCC commands available to you.

4. Index defrag (optional)
- See DBCC DBREINDEX or DBCC INDEXDEFRAG in Books Online
- On tables with lots of INSERT or UPDATE activity, indexes become
fragmented over time. Fragmentation can become severe enough to impact
performance of certain queries. Use these two commands to remove that
fragmentation. Here is a script that will rebuild those indexes that
have become badly fragmented:
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall....html

5. Cleanup of old backup files (optional)
- See xp_cmdshell in Books Online
- Backup files, unless overwritten, will accumulate on disk. You'll
need some process, within SQL or otherwise, to delete these backups
files when they're no longer needed. Here's a script that I use for this:
http://realsqlguy.com/serendipity/archives/9-Out-With-The-Old.html


--
Tracy McKibben
MCDBA
http://www.realsqlguy.com