Database Backup Solution

As I said in one of my articles, we usually work from remote and don't have a common database server for our development purposes. So one day what happened was one of my developer was doing some db object changes, like almost 20-25 changes. However after he did his changes he didn't backup it up (not a DBA). Next day he was supposed to send the script to me so that I make the same changes on my copy of database and distribute it to others so that every developer and myself are on the same version of database. But unfortunately one of his computer's disk crashed and he lost almost all development work. And database work was totally lost, nor we had any database backup to recover the work from. And most of the developers use SQL Express edition so they might not be well equiped with proper tool

Usually I have seen developers when working in organised manner they usually don't worry about the database server anyway its a DBA job to maintain it. But in the scenerio that I explained, developer is expected to do it himself and protect his work. However SQL Server does not allow physical file backup, so the next best option and the best practise is to backup the database using backup commands.

Here is a simple win batch file for the same,

/********** backup.bat **************************/

date/T >> BackupLog.txttime /T>> BackupLog.txt
osql -E -SSLYSLE-PC\GP_SQL2K5 -Q"BACKUP DATABASE TestDB TO DISK = 'C:\Girish_Patil\DB_Backup\DB_Backup_%random%.BAK'" -w4000 >> BackupLog.txt
time /T>> BackupLog.txtdate/T >> BackupLog.txt
echo "X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~" >> BackupLog.txt

/**************************************************/

The parameters are hardcoded, you can modify them as per your requirements. This script logs all the message to BackupLog.txt file if you want to analyze anytime if something goes wrong. I use %random% variable to generate random number and concatenate with backup file name so that there is no overwriting of backup files or any file name conflicts. Now, to automate it further just using Windows Scheduler to run this backup.bat file as scheduled job. Thats it your done with simple database backup solution that meets your purpose.