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
sqlcmd -S (local) -U Username -PPassword -i sp_BackupAllDatabases.sqlsqlcmd -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.
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!