Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What went wrong in this backup?


What went wrong in this backup?

Author
Message
Rod
Rod
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1482 Visits: 1982
Yesterday I mentioned that I noticed the former dba hadn't setup a maintenance plan to backup the system databases. I added a subplan, to his maintenance plan, and had it run. I scheduled it to run on Friday mornings at 4 AM. This morning I checked it to see how it went.

First I looked at the history in under Maintenance Plans, and everything looked fine. But I thought I would check the job under SQL Server Agent | Jobs, and that told a different story. (Or perhaps, more complete.) But even so, I just don't see what's wrong. I'd appreciate it you'd look it over and could tell me what's wrong, and if there's anything I can/should do about it. Below is the entry from the Log File Viewer:

Date      2/19/2010 4:00:00 AM
Log Job History (Maintenance.weekly_full_system_backup)

Step ID 1
Server OURSERVER
Job Name Maintenance.weekly_full_system_backup
Step Name weekly_full_system_backup
Duration 00:00:04
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: OURSERVER\SYSTEM. ...sion 9.00.3042.00 for 32-bit Copyright (C)
Microsoft Corp 1984-2005. All rights reserved.
Started: 4:00:00 AM
Progress: 2010-02-19 04:00:01.36
Source: {39BBD14B-CFCA-4D5E-8C03-03E0DE0FFE42}
Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete
End Progress Progress: 2010-02-19 04:00:01.71 Source: Back Up Database Task
Executing query "BACKUP DATABASE [master] TO DISK = N'C:\Data\SQL ".: 50% complete
End Progress Progress: 2010-02-19 04:00:01.77 Source: Back Up Database Task
Executing query "declare @backupSetId as int select @backupSetId =".: 100% complete
End Progress Progress: 2010-02-19 04:00:01.85 Source: Back Up Database Task
Executing query "BACKUP DATABASE [model] TO DISK = N'C:\Data\SQL S".: 50% complete
End Progress Progress: 2010-02-19 04:00:01.88 Source: Back Up Database Task
Executing query "declare @backupSetId as in... The package execution fa... The step failed.



Kindest Regards,Rod
Connect with me on LinkedIn.
Bru Medishetty
Bru Medishetty
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1758 Visits: 1950
It doesn't have the info regarding the failure. Do you have any error logging for the SQL Job or Maintenance Plan ? That would give more info.


Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.com\LearnSQLWithBru

Twitter -- BruMedishetty
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54450 Visits: 44648
Not enough info. The messages are always truncated in the history log. Go into the job, set up a file to log output to and check what's in that file it it happens again.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


AndrewSQLDBA
AndrewSQLDBA
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1162 Visits: 3427
Instead of using a Maintenance Task, why not simply create an SSIS package that performs the backup. It is very simple to write a package. I have never relied on the "Maintenance Task"

Use a "Backup Database Task" from the Maintenance Plan Tasks" toolbox section. You can use a T-SQL Statement task to perform things like Transaction Log truncation. You can then shrink the log file. You can then use the Check Database Ingtegrity Task to check the database Integrity, and use the Maintenance Cleanup task to delete older backup files. Then use the Execute SQL Task to log the success or failure to a Database Maintenance database table. After that, you can use the send mail task to inform yourself that the backup was successful.

Much easier than these Maintenance Tasks I think. This will give you more control over each step and allow you to manually fire off any step if needed.

Please you can schedule this to fire off when needed.

Did you happen to verify the job performed without error before you began? When was the last time this fired off successfully? Instead of adding to one, just create a new one.

Are you placing the backup files on the same box? If so, why even bother, if that box dies, so do your backup files. Have SQL place the backups on another drive on another box.

You really only need to make a backup of the master when a new user is added, or a user is modified. You need to backup the MSDB only if a schedule is modified, or a SSIS package is added or modified.

Andrew SQLDBA
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5030 Visits: 9848
AndrewSQLDBA (2/19/2010)
Instead of using a Maintenance Task, why not simply create an SSIS package that performs the backup. It is very simple to write a package. I have never relied on the "Maintenance Task"

Use a "Backup Database Task" from the Maintenance Plan Tasks" toolbox section. You can use a T-SQL Statement task to perform things like Transaction Log truncation. You can then shrink the log file. You can then use the Check Database Ingtegrity Task to check the database Integrity, and use the Maintenance Cleanup task to delete older backup files. Then use the Execute SQL Task to log the success or failure to a Database Maintenance database table. After that, you can use the send mail task to inform yourself that the backup was successful.

Much easier than these Maintenance Tasks I think. This will give you more control over each step and allow you to manually fire off any step if needed.

Please you can schedule this to fire off when needed.

Did you happen to verify the job performed without error before you began? When was the last time this fired off successfully? Instead of adding to one, just create a new one.

Are you placing the backup files on the same box? If so, why even bother, if that box dies, so do your backup files. Have SQL place the backups on another drive on another box.

You really only need to make a backup of the master when a new user is added, or a user is modified. You need to backup the MSDB only if a schedule is modified, or a SSIS package is added or modified.

Andrew SQLDBA


Using SSIS is basically the same as using SSMS maintenance plans. SSMS maintenance plans are just a subset of SSIS that can be run in SSMS (which means you don't have to install integration services if you don't want to).

Granted, there are other things you can add in with SSIS that are not available to SSMS maintenance plans but I have not found that to be much more useful. In fact, I don't like hiding maintenance plans inside SSIS where it is harder to find them and manage them.

Either way - this statement is just wrong:

You can use a T-SQL Statement task to perform things like Transaction Log truncation. You can then shrink the log file.


You should not shrink the log file on a scheduled basis - unless you want to cause performance issues for your systems. You should not truncate the log file (ever) - unless you want to prevent the ability to restore your system to a point in time.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5030 Visits: 9848
If the history on the maintenance plan shows as successful, you have a backup file for all system databases and the job has failed, most likely the reason is because the client tools used to create the plan are older than the instance.

Validate that your client tools are upgraded to at least SP3 and the server is at least post SP2 (9.0.3054 is minimum).

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

AndrewSQLDBA
AndrewSQLDBA
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1162 Visits: 3427
I was at the understanding, and have tested this, that once the data is committed to the database, that is can be removed from the transaction log. I was able to keep the log file small, and restore up to the minute.

I read that in one of the SQL Books, I think that the book had a black cover, and with many pages. I have so many books, I cannot be certain.

Please let me know if I am incorrect about this info. But I was able to show it to a group of Oracle DBAs that is once worked with.

Andrew SQLDBA
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: Administrators
Points: 40542 Visits: 18846
I use separate packages for system backups. These maintenance plans have always worked well for me, and having them in separate packages allows me to run a separate set of backups if I'm making changes to systems (security, msdb stuff, etc)

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54450 Visits: 44648
AndrewSQLDBA (2/19/2010)
I was at the understanding, and have tested this, that once the data is committed to the database, that is can be removed from the transaction log.


Yes, and that's what happens in simple recovery (well, it's simplified description). In full recovery log records are retained until a log backup runs, the idea being back the log up to allow point in time recovery. Log truncation (backup log with truncate only) discards log records and prevents any more log backups (and hence any possibility of point-in-time recovery) until a full or diff backup is taken.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54450 Visits: 44648
AndrewSQLDBA (2/19/2010)
Are you placing the backup files on the same box? If so, why even bother, if that box dies, so do your backup files. Have SQL place the backups on another drive on another box.


Generally backing up across the network is a poor idea because of the speed of the network and the possibility of a single network glitch causing the entire backup to fail.
Backup local, copy remote.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search