Keep Your Data Safe

A Microsoft SQL database might be one of the most important pieces of data on your campus. A database can contain financial information, student grades, employees’ and parents’ personal information, and other information that is critical to your school’s operation. The following are a few quick steps that will help you make sure your Microsoft SQL databases are being backed up correctly.

For this example, I worked with Microsoft SQL 2005 Standard Edition, but the same concept can be used for most versions of SQL server. The first step is to start SQL Server Management Studio. Connect or log in to whichever SQL server you want to back up the databases on. This does not have to be the same SQL server that you are physically on. I actually install the SQL Server Management Studio on my workstation and connect to all of my SQL servers remotely.

Once logged in, expand the Management folder and right click on Maintenance Plans. Choose a Maintenance Plan Wizard and select the name of the job you want it to do. Click Change at the bottom to set a schedule for this backup job to run. I set mine to run every night, but it depends on how much storage you have. I also have a script that I run every night after I run my backup job that compresses the database files to help save server space.

After you have selected your schedule, move to the next window and pick what the job will do. I like to separate my jobs so that each job only does one thing, but some prefer an all-in-one job. Choose Back Up Database (Full) and move on to the next window. This will allow you to set the order of your jobs in case you had several jobs inside a single maintenance plan.

Next, select the drop down at the top of the screen under databases and place check marks into the databases that you want to back up. And yes, you can choose more than one. Now choose whether you want the backup to expire after a certain amount of days or on a specific date. I recommend setting this to around 60 to 90 days. This will delete any backups that are older than the number of days you select on the server.

You should back up your entire database backups to a central backup server that has some form of offsite storage. Also choose where you want the backup file to be written and the extension, which should be .BAK. The next window offers some reporting services. I always write the log to file but choose not to have it send me e-mails. After that, you are done. I would suggest going back through the Maintenance Wizard and creating a weekly plan for doing a database integrity check and a weekly plan for rebuilding the index. Just make sure you have them set to occur at different times so they don’t interfere with each other.

Finally, run a .BAT script that compresses your .BAK file into a .RAR file and deletes the old .BAK file. Do this after you run the nightly backup but just before you copy the files over to your main backup server. I use the built-in Windows Scheduler to set this job to run each night. The one thing you need is a copy of Winrar installed on the server. Winrar is an archive manager and costs less than $30 per copy. Below is an example of a .BAT file that you can copy and paste into Notepad.

My install of Winrar is in e:\Program Files\Winrar but the default location for this will be c:\Program Files\Winrar. The “a” after the location of the file just tells rar.exe to add the following files to an archive. The “-ag yyyymmddBCK” will add the current date to the end of the compressed file, which is a great way of keeping track of all of your database files. The “-inul” will disable any error messages that could stop the compression from completing. “-y” will basically answer yes to any queries. The “SQLBUP.rar” is the name of the archive file to create and then our –ag command will add the date to the back of that file. The “*bak” tells it to archive any and all files that end in .BAK. And finally, the last command “del *.bak” deletes any .BAK files left in that directory. This script takes a 1GB to 1.5GB database backup and turns it into a 100MB to 200MB RAR file, which saves a lot of time when copying it to your main backup server.