Automated Backups of Your SQL Database

Ever find yourself taking precious time and backing up all your databases, only to forget and be behind? I am currently working on some Alpha-PreAlpha software whose database structure changes with time like any other application. To solve the issue with remembering to back up however, I decided to automate it, and not have to worry. There probably is a lot better ways to automate it, but this is the process that came to mind first since I have worked with batch files and automation before.

Step 1 : Building the Batch File

Before you start on the batch file, you will need the following information to plug in.

  • SQL Instance Name Will reference as SQLInstanceName in example
  • Username Will reference as Username in example
  • Password Will reference as Password in example
Start off by creating a new word document which will serve as your batch file. Either change the extension to .bat or select “Save As” and save as “AutoSQLBackup.bat”. (You can save the file as any name as you want, just make sure it has the bat extension) Basically, the power behind this method of automated SQL backups is the SQLCMD Utility. Let’s go ahead and start editing and open up the file you just created. Enter the following line
sqlcmd -S (local) -U Username -PPassword -i sp_BackupAllDatabases.sql
sqlcmd -S (local) -UUsername  -PPassword -Q “exec sp_BackupAllDatabases”

This will connect to your local instance and log in using the specified username and password, then look for the input file sp_BackupAllDatabases. Then the next line will actually run the stored procedure. The next step is insert a line to log when your script runs, that way you can monitor when the script was last ran, etc… very quickly.

echo %date% %TIME% >> Backup.log

This will write out to the file named ‘Backup.log’ and append to it the current date and time.

Step 2 : Building the Script

Now it’s time to build the stored procedure mentioned in the above step (sp_BackupAllDatabases). Also note that if you would like to specify a different output directory, edit the line that has ‘C:\DB Backups\\’ in it to a location of your choosing.

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE Name = 'sp_BackupAllDatabases' and type = 'V')
	DROP PROCEDURE sp_BackupAllDatabases
GO

CREATE PROCEDURE sp_BackupAllDatabases
as

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\DB Backups\\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),110)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
       BACKUP DATABASE @name TO DISK = @fileName

       FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

GO

Step 3 : Automating the Batch File

The next step is to automate the batch file which you just created. To do this, we will use the Windows Task Scheduler. This can be found by going to the Control Panel -> Administrative Tools. Then select Create Basic Task… (Off to the right hand side). In the first dialog, enter in a name and description for your task. Next, select the frequency you would like the backup to run. You then will have a chance to pick the day when you want your back program to run. I went with the second Monday of the month so I can easily remember. Up next is where you pick your action, telling the scheduler you want to run the batch file. Select Start a Program and hit next. You now are ready to set up where the application is ran from. Browse to the folder that contains your batch file or paste in the path if you already know it. Before you hit next on this screen however, you need to add the location of the script (without the file name) in quotations. See below for an example.

Example of Start a Program
Example of Start a Program

Hit next, you are done. You now have a working automated backup. Of course there are many ways to do this, but this was the quickest for me. Good luck!

Jacob Saylor

Software developer in Kentucky

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: