Recently, we had a brand new Production server hosted on a Windows 2003 Cluster along with SQL Server 2005 with Service Pack 3. This was an OLTP system hosted in a DataCentre in New Jersey,United States of America. The server had 16 CPU’s along with 32 GB of Memory. The server initially had just one database named ABC whose size was around 93 GB.As per the agreed backup policy with the Customer, we always transfer the Production Database Backup onto the Staging Server over the N/W through Robocopy. This staging server was located at the NewYork Data Centre.Transferring a file of 93 GB over the N/W was really a tedious task as sometimes even after 24 hours passed by but the file never got transferred successfully, also on couple of occassions, I have seen that even though the file got transferred properly but it was a corrupted one. In this article, Satnam Singh an SQL Server DBA from Larsen&Toubro Infotech,Mumbai,India discusses one such approach which he used on his Production Server to streamline the backup process.

Just FYI, we didn’t had any third party utility like Idera,Red-Gate Software,LiteSpeed to compress the DB Backup Files.

The first step was to develop a stored procedure which will take the Full backup of the database named ABC and compress it on the server itself. Here is one of the great T-SQL script of all times which i used for this purpose.

In order to execute the above Stored Procedure, I use the below T-SQL,

Where ABC is the name of the Database whose backup needs to be performed,B:\DB-BACKUPS\ABC\Full\ is the path where the Full Backup of the database resides,B:\LogShippingLogs\ is the path where the compressed copy of the DB Backups will reside, F means that the backup performed will be a Full Backup.

Once the Full Backup of the database gets archived successfully, the next step would be transfer it onto the staging server. I accomplish this task by executing the below stored procedure against the master database on the server.

RAR files. One important point tomention here isthat this isa Cut->Copy->Paste operation.

Once the archived copy ofthe databasegets transferred onto the staging server, the nextstep istosend a notification email tothe team

confirming the same. Inordertoachieve this, I firstcreatea DatabaseMail Profile named DBMail onthe server andthenuse the below T-SQL script against the msdb databasetosend the email alert.

Once the email alert is sent, I then delete the original Full Backup from the Production Server itself. This is accomplished by executing the
below T-SQL against the master database on the server.

This is the approach which we use on our Production Database Server to manage the Full Backups happening on a daily basis. The backup size being 93 GB, it takes around 13.5 hours for the entire operation to get completed. Also with 16 CPU’s and 32 GB of Memory, till date we haven’t faced any performance issue on the Production Server. One important advice, if you have a poor hardware on the server then I would normally advice you not to implement the above procedure because it will badly hurt the Performance of the Application.

After the Full Backup, the next step is to have the Proper differential Backup happening on the server. We normally have the policy of maintaining copy of differential backup on the server. Differential Backup’s are important because in case of  emergencies, it will help us for faster Recovery of data.

Also one cannot completely rely on the Transactional Log Shipping procedure. Inordertotake the differential backup, I have a job scheduled every 3 hours,which internally has the below T-SQL executing within it.

differential backup happens, we deletethe Old copy using the below T-SQL against the master database,

The above approach is the one which I use to manage the differential backups on my servers.

After the differential backups comes the Transactional Log Backups. For the Transactional Log Backups,we have the Transactional Log Shipping procedure in place on the Production Server which keeps on taking the Transactional Log Backups every 15 minutes. The retention period of the Transactional Log Backups has been been specified as 16 hours.

This was an approach which we used on our Production server to effectively manage the Database Backups. Please do provide us your suggestions which would have helped us to make the backup strategy much more effective and better.



Satnam Singh

Like us on FaceBook Follow us on Twitter

Follow me on FaceBook| Join the fastest growing SQL Server group on FaceBook