Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

What went wrong in this backup? Expand / Collapse
Author
Message
Posted Friday, February 19, 2010 7:59 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:08 PM
Points: 721, Visits: 1,264
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
Post #869103
Posted Friday, February 19, 2010 8:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:27 AM
Points: 1,735, Visits: 1,947
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
Post #869117
Posted Friday, February 19, 2010 8:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 41,517, Visits: 34,432
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 2008, MVP
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

Post #869118
Posted Friday, February 19, 2010 8:26 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:10 PM
Points: 955, Visits: 3,254
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
Post #869125
Posted Friday, February 19, 2010 8:48 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 4:08 PM
Points: 4,379, Visits: 9,469
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #869149
Posted Friday, February 19, 2010 8:51 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 4:08 PM
Points: 4,379, Visits: 9,469
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #869152
Posted Friday, February 19, 2010 8:54 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:10 PM
Points: 955, Visits: 3,254
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
Post #869157
Posted Friday, February 19, 2010 8:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:49 PM
Points: 32,768, Visits: 14,929
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
Post #869159
Posted Friday, February 19, 2010 9:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 41,517, Visits: 34,432
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 2008, MVP
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

Post #869179
Posted Friday, February 19, 2010 9:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 41,517, Visits: 34,432
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 2008, MVP
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

Post #869180
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse