Automating SQL Express Backups for Fun and Profit

Here's how to back up SQL and SQL Express databases automatically.

Phase One:

1. Create two folders on the C Drive: C:\DB_Backup and C:\ScheduledTasks. DB_Backup is the target for the backup job, and ScheduledTasks holds the SQL script that will be triggered via a scheduled task.
2. Install an appropriate version of SQL Management Studio Express (SSMS).
3. In SSMS, connect to the instance by using <ServerName>/<InstanceName>. An easy way to find the instance name is to open the services console (Start, Run, services.msc, enter) and look for your SQL service. The instance name will be within parentheses next to it.
4. Now, right click on the database and choose Tasks-->Backup
5. In the destination area, choose to back up to disk, and click the 'Add' button.
6. Navigate to the DB_Backup folder that you created, and make up a file name with a .bak extension.
7. Click on the 'Options' page on the left, and select 'Overwrite all existing backup sets'
8. Place a check mark next to "Verify backup when finished'
9. Click the down arrow next to 'Script' at the top of the window, and choose 'Script Action to New Query Window' -- This is an excellent way to learn SQL, by the way. You can do this from nearly everywhere in SSMS.
10. Now, press the cancel button on the backup window (we're not going to back it up now)

You should now see the SQL code for the backup in a query window. Here's an example:

BACKUP DATABASE [DatabaseName] TO  DISK = N'C:\temp\DatabaseName.bak' WITH NOFORMAT, INIT,  NAME = N'etl-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'DatabaseName' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DatabaseName' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''etl'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'C:\temp\DatabaseName.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO


Phase Two:

1. Click File-->Save SQLQuery1.sql As... and save the sql script file to C:\ScheduledTasks
2. You can now close SSMS
3. Open Task Scheduler
4. Right-click 'Task Scheduler Library' on the left, and choose 'Create Task'
5. Name it "<Server> SQL Express Backup"
6. Choose an appropriate user to run as
7. Select Run whether the user is logged on or not
8. Select Run with highest priveledges
9. Choose highest "configure for" level available
10. Select the Triggers tab
11. New....
12. Set the scheduled time.
13. Select the Actions tab
14. New...
15. In the program/script section, put the path to sqlcmd.exe. Mine is "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SqlCmd.exe" (if the path includes spaces, use quotes). Yours might be different depending on your version of SQL.
16. In the arguments field, put the following: -S <ServerName>\<InstanceName> -i "C:\Scheduled Tasks\<ServerName>-<DBName>.sql"
17. Click OK
18. Once your actions are completed, click on the settings tab
19. Change the "Stop the task if it runs longer than" value to 1 hour. You can change this depending on how long an actual backup takes for your database, of course.
20. Click OK to finish creating the task
21. Enter the credentials for the user account that is to run the task
22. Run the scheduled task to test it, and check C:\DB_Backup for the .bak file(s)

And that's it! Setting this up on our SQL Express databases saved us a ton of money....