- 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.
- Install an appropriate version of SQL Management Studio Express (SSMS).
- 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.
- Now, right click on the database and choose Tasks-->Backup
- In the destination area, choose to back up to disk, and click the 'Add' button.
- Navigate to the DB_Backup folder that you created, and make up a file name with a .bak extension.
- Click on the 'Options' page on the left, and select 'Overwrite all existing backup sets'
- Place a check mark next to "Verify backup when finished'
- 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.
- Now, press the cancel button on the backup window (we're not going to back it up now)
BACKUP DATABASE [DatabaseName] TO DISK = N'C:\temp\DatabaseName.bak' WITH NOFORMAT, INIT, NAME = N'etl-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
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
- Click File-->Save SQLQuery1.sql As... and save the sql script file to C:\ScheduledTasks
- You can now close SSMS
- Open Task Scheduler
- Right-click 'Task Scheduler Library' on the left, and choose 'Create Task'
- Name it "<Server> SQL Express Backup"
- Choose an appropriate user to run as
- Select Run whether the user is logged on or not
- Select Run with highest priveledges
- Choose highest "configure for" level available
- Select the Triggers tab
- Set the scheduled time.
- Select the Actions tab
- 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.
- In the arguments field, put the following: -S <ServerName>\<InstanceName> -i "C:\Scheduled Tasks\<ServerName>-<DBName>.sql"
- Click OK
- Once your actions are completed, click on the settings tab
- 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.
- Click OK to finish creating the task
- Enter the credentials for the user account that is to run the task
- Run the scheduled task to test it, and check C:\DB_Backup for the .bak file(s)