July 25, 2014 at 8:20 am
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
July 25, 2014 at 8:29 am
msdb.dbo.backupmediafamily contains the list of .bak / .trn files that have been created.
July 25, 2014 at 10:54 am
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
July 25, 2014 at 1:14 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply