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

Where is Maint. Plan/SSIS task history stored Expand / Collapse
Author
Message
Posted Friday, July 25, 2014 8:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:23 PM
Points: 13, Visits: 158
I'm using maintenance plans to create backup jobs. I need to know what table the step output messages are stored in.

Our IT guys brought up an -> old <- clone of our production SQL Server (virtual machines) under a different name. All the scheduled jobs started running. My backup folder is on a different storage device. The correct production backup jobs and the backup jobs from this old clone were all writing to the same backup folder. Now I need a backup file. When I look in the folder I have two .bak files with the same time, and slightly different file names. If I can find the full file name in a step history table, I can tell which server wrote which backup file.

Using SSMS and looking at the job/step history, the backup file names are all truncated. ex:
" Source: Back Up Database Task Executing query "BACKUP DATABASE [FDTS] TO DISK = N'\\OurServer\S...".: 50% complete End Progress ... 100% complete End Progress "

The text log files under \mssql\Log don't contain the backup filename.

Any other suggestions are welcome.
Tom

Post #1596234
Posted Friday, July 25, 2014 8:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:49 AM
Points: 5,216, Visits: 5,110
msdb.dbo.backupmediafamily contains the list of .bak / .trn files that have been created.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1596239
Posted Friday, July 25, 2014 10:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:23 PM
Points: 13, Visits: 158
You are correct. msdb..backupmediafamily is exactly what I was looking for.

Now I'm a little confused, though.
In the backupmediafamily table on my production server, I see a row for each of the backup files. SSMS, job History shows one execution of the job.
On the old server in, SSMS when I vew job history, I can see that the backup job ran at the same time as on the production server, but there are no rows in backupmediafamily for that database at that time.

Did the scheduled job on the old server kick off the SSIS job on the production server? (This 'old' server was the production server and had the same name when the snapshot of the virtual server was made.)
If both jobs ran on my production server, can I be sure that the two .bak files are of the same (production) database?

Thanks in advance,
Tom
Post #1596321
Posted Friday, July 25, 2014 1:14 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:31 AM
Points: 4,358, Visits: 9,537
Maintenance plans store the database connection information inside the package. So yes, when you clone a server all maintenance plans on the new system will have a connection to the old server - and will run against that old system.

You cannot change the connection information - so you need to delete the maintenance plans on the new system and recreate them.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1596378
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse