July 15, 2013 at 12:57 pm
Hello All,
I need to monitor size of most recent SQL .BAK file in each folder (each database has it's folder) and insert the name and size to a SQL table. This can be a job which would run everyday.
Can someone please suggest me the bets way to achieve these results?
Thanks,
July 15, 2013 at 1:10 pm
Check out this table.
select * from msdb.dbo.backupset
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 15, 2013 at 1:36 pm
Thank you Sean.
This is the way I had earlier but now reqirements and scope have changed. I need to get the lastest backup file size from the backup server where backups from all the servers are located. Production, UAT, DEV, TRAINING, Sharepoint, Reporting etc.. all of these servers have their own daily backup jobs and all the files are on one server. I don't want to go by each server and run the backupset command to get the file size.
I am looking for more like SSIS package which can loop through the folders on a given server name and get the latest .BAK file and it's size in each folder.
July 15, 2013 at 1:47 pm
MyTSQLRepl (7/15/2013)
Thank you Sean.This is the way I had earlier but now reqirements and scope have changed. I need to get the lastest backup file size from the backup server where backups from all the servers are located. Production, UAT, DEV, TRAINING, Sharepoint, Reporting etc.. all of these servers have their own daily backup jobs and all the files are on one server. I don't want to go by each server and run the backupset command to get the file size.
I am looking for more like SSIS package which can loop through the folders on a given server name and get the latest .BAK file and it's size in each folder.
Take a look here. http://msdn.microsoft.com/en-us/library/ms141724.aspx
You can use the foreach file. You just specify the folder and if you want to traverse subdirectories.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 15, 2013 at 1:58 pm
Thank you Sean
Are you saying to use the ackup set table for all the servers in foreach loop? I though there could be a way either by using SQL or VB code in side Foreach loop to get the latest file in each folder and it's size
July 15, 2013 at 2:10 pm
MyTSQLRepl (7/15/2013)
Thank you SeanAre you saying to use the ackup set table for all the servers in foreach loop? I though there could be a way either by using SQL or VB code in side Foreach loop to get the latest file in each folder and it's size
The foreach file would let you look at the files on disc. I thought that was what you were looking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply