Script to automate the backup operation.

Question

Script to automate the backup operation.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
July 8, 2010 at 9:20 am

#224682

Dear Friends,
Need your assistance..
Can anyone please provide me the scripts to perform the below functionality:
"Want to write SQL Script to check the 'Backup drive' disk space before nightly full database job runs. If the drive has enough space then only the backup job will be initiated else it will throw an error message that drive has insufficient free space to our inbox." Want to write the script in such a way that it can be invoked from the central server where all the production and non prod servers are connected." This should be applicable to SQL Server 2000, 2005 and 2008.
Please furnish your guide lines.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

that requirement reads like the person who wrote it already knows the answer/how to do it...this isn't a homework question or anything, is it?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

Yes, you're correct. This is a hometask as well as an improvement plan to the existing system. While writing the script, I am unable to come up with a good script to calculate the total amount of size of the databases becoz it should comprise FULL, T-log, for that I was looking for an expert suggestion...becoz I am not a hard core programmer.

By replying to such comments, people get score one 'Credit'. I appreciate your skills....Good!

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Have you checked the script library here? Lots and lots of backup-related scripts.

It's trivial to get the size of previous backups from the tables in MSDB (I leave as an exercise to the reader to identify which table), or you can identify the size of the database contents (again left as an exercise to the reader) to estimate full backup size.

I believe Paul Randal has, somewhere on his blog, a script to calculate the size of a differential backup.

Log backups are harder, maybe start with the size of the used portion of the log (which you can get with one of the DBCC SQLPERF options)

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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

Thnaks Gail...I will certainly look for the articles\scripts.

I requested for some better approach. Writing the code would not be very difficult, since regarding the script I can open BOL\google and can do the same.

FYI, MSDB..backupset table is not good in my scenario...since my project we are using LiteSpeed backup. backup_size column doesn't actually say the correct size of the DBs.

I'm using xp_fixeddrives to get the available space and from xp_cmdshell retrieving the total bkp size of all .BKP files from the default bkp folder. I want to compare here...and I am stalled here to look for a better approach.

Thanks.