-->

• Aug 19, 2020 Click Start Backup. To Schedule a Backup for a Later Time or Date You may want to run a backup operation when there is low system usage. However, such times may be late at night or on weekends. You can schedule backup jobs to run on a particular day and time. NOTE: To schedule a backup operation, the Task Scheduler service must be running.
• I have a batch file in Windows 7 Professional that runs RASDIAL and creates a VPN to Windows Server 2003. The batch file then maps a drive and then I do a backup routine. This batch file runs flawlessly by double clicking it. Using Task Scheduler, the batch file executes properly except for.

This article introduces how to use a Transact-SQL script and Windows Task Scheduler to automate backups of SQL Server Express databases on a scheduled basis.

Original product version: SQL Server
Original KB number: 2019698

Summary

SQL Server Express editions do not offer a way to schedule either jobs or maintenance plans because the SQL Server Agent component is not included in these editions. Therefore, you have to take a different approach to back up your databases when you use these editions.

Check the box Verify data after backup and click Next Click Replace the existing backups and click Next Click Later and in the Job Name box type Monday Nights, click Set Schedule In Scheduled Task select Weekly and as Start time 11:00 PM (or whenever you want the backup to be scheduled).

Currently SQL Server Express users can back up their databases by using one of the following methods:

Use SQL Server Management Studio or Azure Data Studio. For more information on how to use these tools to Back up a database review the following links:

• Use a Transact-SQL script that uses the BACKUP DATABASE family of commands. For more information, see BACKUP (Transact-SQL).

This article describes how to use a Transact-SQL script together with Task Scheduler to automate backups of SQL Server Express databases on a scheduled basis.

Note

This applies to only SQL Server express editions and not to SQL Server Express LocalDB.

More information

You have to follow these four steps to back up your SQL Server databases by using Windows Task Scheduler:

Schedule Server 2003 Backup Batch File Windows 10

Step A: Create stored procedure to Back up your databases.

Connect to your SQL express instance and create sp_BackupDatabases stored procedure in your master database using the script at the following location:

Step B: Download SQLCMD tool (if applicable).

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files. In SQL Server 2014 and lower versions, the utility is shipped as part of the product. Starting with SQL Server 2016, sqlcmd utility is offered as a separate download. For more information, review sqlcmd Utility.

Step C: Create batch file using text editor.

In a text editor, create a batch file that is named Sqlbackup.bat, and then copy the text from one of the following examples into that file, depending on your scenario:

• All the scenarios below use D:SQLBackups as a place holder. The script needs to be adjusted to the right drive and Backup folder location in your environment.

• If you are using SQL authentication, ensure that access to the folder is restricted to authorized users as the passwords are stored in clear text.

Note

The folder for the SQLCMD executable is generally in the Path variables for the server after SQL Server is installed or after you install it as stand-alone tool. But if the Path variable does not list this folder, you can either add its location to the Path variable or specify the complete path to the utility.

Example1: Full backups ofalldatabases in the local named instance of SQLEXPRESS by using Windows Authentication.

Example2: Differential backups ofalldatabases in the local named instance of SQLEXPRESS by using a SQLLogin and its password.

Note

The SQLLogin should have at least the Backup Operator role in SQL Server.

Example 3: Log backups ofalldatabases in local named instance of SQLEXPRESS by using Windows Authentication

Example 4: Full backups of the database USERDB in the local named instance of SQLEXPRESS by using Windows Authentication

Similarly, you can make a differential Backup of USERDB by pasting in 'D' for the **@backupType** parameter and a log Backup of USERDB by pasting in 'L' for the **@backupType** parameter.

Step D: Schedule a job by using Windows Task Scheduler to execute the batch file that you created in step B. To do this, follow these steps:

1. On the computer that is running SQL Server Express, click Start, then in the text box type task Scheduler.

2. Under Best match, click Task Scheduler to launch it.

3. In Task Scheduler, right-click on Task Schedule Library and click on Create Basic task….

4. Enter the name for the new task (for example: SQLBackup) and click Next.

5. Select Daily for the Task Trigger and click Next.

6. Set the recurrence to one day and click Next.

7. Select Start a program as the action and click Next.

8. Click Browse, click the batch file that you created in Step C, and then click Open.

9. Check the box Open the Properties dialog for this task when I click Finish.

10. In the General tab,

    1. Review the Security options and ensure the following for the user account running the task (listed under When running the task, user the following user account:)

       The account should have at least Read and Execute permissions to launch sqlcmd utility. Additionally,

       • If using Windows authentication in the batch file, ensure the owner of the task permissions to do SQL Backups.

       • If using SQL authentication in the batch file, the SQL user should have the necessary permissions to do SQL Backups.

    2. Adjust other settings according to your requirements.

Tip

As a test, run the batch file from Step C from a command prompt that is started with the same user account that owns the task.

Be aware of the following when you use the procedure that is documented in this article:

• The Task Scheduler service must be running at the time that the job is scheduled to run. We recommend that you set the startup type for this service as Automatic. This makes sure that the service will be running even on a restart.

• There should be lots of space on the drive to which the backups are being written. We recommend that you clean the old files in the Backup folder regularly to make sure that you do not run out of disk space. The script does not contain the logic to clean up old files.

Additional references

In the first part I gave you an example of automating backup on windows 2003 server using batch files and scheduled tasks. In this second part, I will give another example on automating backup using the same technique but on windows 2008.

Windows 2008 already comes with a predefined template for scheduling backup using task scheduler but using this simple method will only have the option of backing up the whole disk (taking an image of the disk), but fortunately there is an ability to select what to backup using the wbadmincommand through the command prompt.

You must run wbadminfrom an elevated command prompt. Click Start, right-click Command Prompt, and then click Run as administrator.

First we have to prepare the backup destination, in our example here it will be a shared folder, as if you will backup to another disk, wbadmin will format it to place the image. For example the share path will be server01winbackup

Create a batch file “DailyBackup.bat” which will have the following commands:

set backupshare=server01winbackup << This will assign the share name to the a variable to be used in backup script

Specify the volumes to be included in the backup, remember in windows 2008 you can only select the entire volume (C: D: …) but you cannot select individual files or folders. If you use 2008 R2 you can do so and I will explain it in Part 3.

set include=c:,d: <<volume names separated by comma, volume drive letters, volume mount points, or GUID-based volume names

Make sure that you have created the three empty folders inside share1 (Today, Yesterday and Old) and rotate backup among them. Please refer to part 1 to understand why I used these three folders.

del %backupshare%old*.* /q
rd %backupshare%old
ren %backupshare%yesterday old
ren %backupshare%today yesterday
md %backupshare%today

Executing the wbadmin command:

wbadmin start backup -backuptarget:%backupshare%today -include:%include% -allCritical –quiet

This will execute the image backup of the selected drives. Remember as you must take backup of the entire volume and you can not select certain items, but you can restore individual files and folders or selected items. Each time you execute this batch file will delete the oldest backup and will rename today & yesterday’s back then create new backup in today’s folder.

Scheduling the Backup operation:

Open the Task Scheduler: Control Panel > Administrator tools > Task Scheduler

From the menu select Create Task.

Schedule Batch File

In the General Tab give a name to the task and select Run whether the user is logged on or not

In the Triggers tab, click New, then Select the daily time to execute the task, make sure it is enabled.

On Actions tab, click new, then select the batch file you created (DailyBackup.bat)

Click OK. A new window will open to enter the username & password of the user the task will use when executed. (e.g. username member of the backup operators and permitted to access the backup share folder)

About Restoring Backup in windows 2008:

You have to restore the backup using wbadmin, below are steps to consider:

1- Get the version number of the backup you will restore from:

wbadmin get versions -backupTarget:server01winbackuptoday -machine:server01

The output of the command is something similar to the following:

wbadmin 1.0 – Backup command-line tool
(C) Copyright 2004 Microsoft Corp.

Backup time: 8/12/2011 3:44 PM
Backup target: Network Share labeled server01winbackupToday
Version identifier: 08/12/2011-13:44 ß this is the version number you need
Can Recover: Volume(s), File(s), Application(s), Bare Metal Recovery, System State

2-Get the Items that you can restore:

wbadmin get items -version:08/12/2011-13:44 -backupTarget:server01winbackuptoday -machine:server01

The output of the command is something similar to the following:

wbadmin 1.0 – Backup command-line tool
(C) Copyright 2004 Microsoft Corp.

Volume Id = {f711229f-b5cb-11e0-a3f1-806e6f6e6963}
Volume ‘<Unlabeled Volume>’, mounted at C:
Application = FRS
Component = 8C0936D1-A5B0-4F05-99E7-A79FC7873694-C018CBC4-8D91-4BAD-B92A-6E91D97AA640 (SYSVOL8C0936D1-A5B0-4F05-99E7-A79FC7873694-C018CBC4-8D91-4BAD-B92A-6E91D97AA640)

Application = AD
Component = ntds (C:_Windows_NTDSntds)

Application = Registry
Component = Registry (Registry)

3- Now you can perform one of the following tasks:

Restore the entire drive:

wbadmin start recovery -version:08/12/2011-13:44 -BackupTarget:server01winbackuptoday -itemType:Volume -items:c: -machine:server01

Restore System Recovery (Bare Metal)

wbadmin start sysrecovery -version:08/12/2011-13:44 -BackupTarget:server01winbackuptoday -machine:server01

Restore System State Recovery

wbadmin start systemstaterecovery -version:08/12/2011-13:44 -BackupTarget:server01winbackuptoday -authsysvol -machine:server01

Restore Registry

wbadmin start recovery -version:08/12/2011-13:44 -BackupTarget:server01winbackuptoday -itemType:App -items:Registry -machine:server01

Schedule Server 2003 Backup Batch Files

Restore Files or Folders (e.g. folder1)

Schedule Server 2003 Backup Batch File Opener

wbadmin start recovery -version:08/12/2011-13:44 -BackupTarget:server01winbackuptoday -itemType:File -items:c:folder -recursive

Remember

1. In most cases you will recover the system (restore the backup) from a connected media (volume) such as a hard Disk, DVD drive, USB drive and so on… Usually when recovering a system it rearly to restore a backup from a shared foler. Accordingly, your -backuptarget option will be a volume (e.g. -backuptarget:F:). In that case the backup folder ” WindowsImageBackup ” should be on the root of that volume.
2. When berforming the backup and your backuptarget is a volume, then the backup program will format that volume and if there were old backups, they will be lost. That’s why the shared folder is used for backups in windows 2008.

In the Part 3 I will explain some differences between 2008 & 2008 R2 in executing the wbadmin.