By: Edwin Sarmiento | Last Updated: 2018-06-02 | Comments (43) | Related Tips: More >Express Edition
Problem
As a lot of line-of-business applications are being built with SQL Server Express Edition as their backend database, we need to make sure that we backup the system and the user databases running on these instances. Unfortunately, SQL Server Express Edition does not come with SQL Agent which we would normally use to create a database maintenance plan to backup all the databases. How do we perform a backup of our system and user databases in SQL Server Express Edition similar to how we do it in other editions?
Solution
We can use a combination of VBScript and TSQL with Task Manager in Windows to automate the creation of user and system database backups in SQL Server Express Edition.
Note: All files should be saved in folder E:SQL_Backupscripts. This can be changed, but this example is setup for this folder. If you save to a different folder you will need to update the scripts accordingly.
Step 1 - Create the TSQL script
The TSQL script below generates a database backup similar to the formatting generated by the database maintenance plan, taking into account the date and time the backup files were generated. We save the script as a .sql file, E:SQL_BackupscriptsbackupDB.sql, which we will call from a batch file using sqlcmd.
Step 2 - Create the VBScript file
Next, we will need to create a VBScript file which will be responsible for cleaning up old copies of the database backups. The script also writes to a log file which records the database backup files.
- You do need to create an empty file named E:SQL_BackupscriptsLOG.txt to save a log of the deleted files.
- Also copy the below script and save as E:SQL_BackupscriptsdeleteBAK.vbs
On Error Resume Next
Dim fso, folder, files, sFolder, sFolderTarget
Set fso = CreateObject('Scripting.FileSystemObject')
'location of the database backup files
sFolder = 'E:SQL_Backup'
Set folder = fso.GetFolder(sFolder)
Set files = folder.Files
'used for writing to textfile - generate report on database backups deleted
Const ForAppending = 8
'you need to create a folder named 'scripts' for ease of file management &
'a file inside it named 'LOG.txt' for delete activity logging
Set objFile = fso.OpenTextFile(sFolder & 'scriptsLOG.txt', ForAppending)
objFile.Write ' & VBCRLF & VBCRLF
objFile.Write ' DATABASE BACKUP FILE REPORT ' & VBCRLF
objFile.Write ' DATE: ' & FormatDateTime(Now(),1) & ' & VBCRLF
objFile.Write ' TIME: ' & FormatDateTime(Now(),3) & ' & VBCRLF & VBCRLF
objFile.Write ' & VBCRLF
'iterate thru each of the files in the database backup folder
For Each itemFiles In files
'retrieve complete path of file for the DeleteFile method and to extract
'file extension using the GetExtensionName method
a=sFolder & itemFiles.Name
'retrieve file extension
b = fso.GetExtensionName(a)
'check if the file extension is BAK
If uCase(b)='BAK' Then
'check if the database backups are older than 3 days
If DateDiff('d',itemFiles.DateCreated,Now()) >= 3 Then
'Delete any old BACKUP files to cleanup folder
fso.DeleteFile a
objFile.WriteLine 'BACKUP FILE DELETED: ' & a
End If
End If
Next
objFile.WriteLine ' & VBCRLF & VBCRLF
objFile.Close
Set objFile = Nothing
Set fso = Nothing
Set folder = Nothing
Set files = Nothing
Step 3 - Create the batch file that will call the TSQL script and the VBScript file
We need to create the batch file which will call both the TSQL script and the VBScript file. The contents of the batch file will be a simple call to the sqlcmd.exe and a call to the VBScript file using either wscript.exe or simply calling the file. Save the file as E:SQL_BackupscriptsdatabaseBackup.cmd and save it in the scripts subfolder
Step 4 - Create a task in Windows Task Scheduler
Create a daily task in Windows Task Scheduler that will call the batch file created in the previous step. This can be found in the Control Panel > Scheduled Tasks or under Start > All Programs > Accessories > System Tools > Scheduled Tasks.
Since we are using Windows authentication to run the TSQL script, use a Windows account that is a member of the db_backupoperator role of all the databases Mass effect 3 master and commander.
- Launch 'Scheduled Tasks'
- Click on Add Scheduled Task
- Browse to the 'E:SQL_Backupscripts' folder and select databaseBackup.cmd
- Pick the frequency and time for the backups to run
- Lastly, enter a Windows account that has at least db_backupoperator role privileges for all of the databases
- See screenshots below
Next Steps
- Implement this solution for your SQL Server Express implementations
Last Updated: 2018-06-02
About the author
Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.
View all my tips
View all my tips
-->
This topic describes how to create a full database backup in SQL Server 2017 using SQL Server Management Studio, Transact-SQL, or PowerShell.
For information on SQL Server backup to the Azure Blob storage service, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service and SQL Server Backup to URL.
Limitations and Restrictions
- The BACKUP statement is not allowed in an explicit or implicit transaction.
- Backups created by more recent version of SQL Server cannot be restored in earlier versions of SQL Server.
- For an overview of, and deeper dive into, backup concepts and tasks, see Backup Overview (SQL Server) before proceeding.
Recommendations
- As a database increases in size full database backups take more time to complete, and require more storage space. For a large database, consider supplementing a full database backup with a series of differential database backups. For more information, see SQL Server Backup to URL.
- Estimate the size of a full database backup by using the sp_spaceused system stored procedure.
- By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you back up frequently, these success messages will accumulate quickly, resulting in huge error logs! This can make finding other messages difficult. In such cases, you can suppress these backup log entries by using trace flag 3226 if none of your scripts depend on those entries. For more information, see Trace Flags (Transact-SQL).
Security
TRUSTWORTHY is set to OFF on a database backup. For information about how to set TRUSTWORTHY to ON, see ALTER DATABASE SET Options (Transact-SQL).
Beginning with SQL Server 2012 (11.x) the PASSWORD and MEDIAPASSWORD options are discontinued for creating backups. You can still restore backups created with passwords.
Permissions
BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.
Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions. However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. Such problems on the backup device's physical file may not appear until the physical resource is accessed when the backup or restore is attempted.
Using SQL Server Management Studio
When you specify a back up task by using SQL Server Management Studio, you can generate the corresponding Transact-SQL BACKUP script by clicking the Script button and selecting a script destination.
Back up a database
- After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
- Expand Databases, and either select a user database or expand System Databases and select a system database.
- Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.
- Select the Database from the drop-down list.
- In the Backup type drop-down list, select Full.
- Under Backup component, select Database.
- In the Destination section, use the Back up to drop-down list to select the backup destination. Click Add to add additional backup objects and/or destinations. To remove a backup destination, select it and click Remove. To view the contents of an existing backup destination, select it and click Contents.
- (Optionally) Review the other available settings under the Media Options and Backup Options pages. For more information about the various backup options, see General page, Media options page, and Backup options page.
Additional information
- After creating a full database backup, you can create a differential database backup; for more information, see Create a Differential Database Backup (SQL Server).
- Optionally, you can select the Copy-only backup checkbox to create a copy-only backup. A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. For more information, see Copy-Only Backups (SQL Server). A copy-only backup is not available for the Differential backup type.
- The Overwrite media option may be disabled on the Media Options page if you are backing up to URL.
SSMS Examples
For the following examples, create a test database with the following Transact-SQL code:
A. Full back up to disk to default location
In this example, the
SQLTestDB
database will be backed up to disk at the default backup location. A backup of SQLTestDB
has never been taken.- In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
- Expand Databases, right-click
SQLTestDB
, point to Tasks, and then click Back Up... - Select OK.
B. Full back up to disk to non-default location
In this example, the
SQLTestDB
database will be backed up to disk at F:MSSQLBAK
. Previous back ups of SQLTestDB
have been taken.- In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
- Expand Databases, right-click
Sales
, point to Tasks, and then click Back Up... - On the General page in the Destination section select Disk from the Back up to: drop-down list.
- Select Remove until all existing backup files have been removed.
- Select Add and the Select Backup Destination dialog box will open.
- Enter
F:MSSQLBAKSales_20160801.bak
in the file name text box. - Select OK.
- Select OK.
C. Create an encrypted backup
In this example, the
SQLTestDB
database will be backed up with encryption to the default backup location.- In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
- Open a New Query window and execute the following commands to create a database master key and a certificate within your
SQLTestDB
database. - In Object Explorer, Expand Databases, right-click
SQLTestDB
, point to Tasks, and then click Back Up... - On the Media Options page, in the Overwrite media section select Back up to a new media set, and erase all existing backup sets.
- On the Backup Options page in the Encryption section select the Encrypt backup check box.
- From the Algorithm drop-down list, select AES 256.
- From the Certificate or Asymmetric key drop-down list select
MyCertificate
. - Select OK.
D. Back up to the Azure Blob storage service
The three examples below perform a full database backup of
Sales
to the Microsoft Azure Blob storage service. The storage Account name is mystorageaccount
. The container is called myfirstcontainer
. For brevity, the first four steps are listed here once and all examples will start on Step 5.- In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
- Expand Databases, right-click
Sales
, point to Tasks, and then click Back Up... - On the General page in the Destination section select URL from the Back up to: drop-down list.
- Click Add and the Select Backup Destination dialog box will open.
Striped backup to URL and a SQL Server credential already exists
A stored access policy has been created with read, write, and list rights. The SQL Server credential,
https://mystorageaccount.blob.core.windows.net/myfirstcontainer
, was created using a Shared Access Signature that is associated with the Stored Access Policy.- Select
https://mystorageaccount.blob.core.windows.net/myfirstcontainer
from the Azure storage container: text box - In the Backup File: text box enter
Sales_stripe1of2_20160601.bak
. - Click OK.
- Repeat Steps 4 and 5.
- In the Backup File: text box enter
Sales_stripe2of2_20160601.bak
. - Click OK.
- Click OK.
A shared access signature exists and a SQL Server Credential does not exist
- Enter
https://mystorageaccount.blob.core.windows.net/myfirstcontainer
in the Azure storage container: text box - Enter the shared access signature in the Shared Access Policy: text box.
- Click OK.
- Click OK.
A shared access signature does not exist
- Click the New container button and the Connect to a Microsoft Subscription dialog box will open.
- Complete the Connect to a Microsoft Subscription dialog box and then click OK to return the Select Backup Destination dialog box. See See Connect to a Microsoft Azure Subscription for additional information.
- Click OK at the Select Backup Destination dialog box.
- Click OK.
Using Transact-SQL
Sql Server Express 2012 Backup Script
Create a full database backup
- Execute the BACKUP DATABASE statement to create the full database backup, specifying:
- The name of the database to back up.
- The backup device where the full database backup is written.
The basic Transact-SQL syntax for a full database backup is:
BACKUP DATABASE database
TO backup_device [ ,..n ]
[ WITH with_options [ ,..o ] ] ;Option Description database Is the database that is to be backed up. backup_device [ ,..n ] Specifies a list of from 1 to 64 backup devices to use for the backup operation. You can specify a physical backup device, or you can specify a corresponding logical backup device, if already defined. To specify a physical backup device, use the DISK or TAPE option:
{ DISK | TAPE } =physical_backup_device_name
For more information, see Backup Devices (SQL Server).WITH with_options [ ,..o ] Optionally, specifies one or more additional options, o. For information about some of the basic with options, see step 2. - Optionally, specify one or more WITH options. A few basic WITH options are described here. For information about all the WITH options, see BACKUP (Transact-SQL).
Basic backup set WITH options:
- { COMPRESSION | NO_COMPRESSION }: In SQL Server 2008 Enterprise and later only, specifies whether backup compression is performed on this backup, overriding the server-level default.
- ENCRYPTION (ALGORITHM, SERVER CERTIFICATE |ASYMMETRIC KEY): In SQL Server 2014 or later only, specify the encryption algorithm to use, and the Certificate or Asymmetric key to use to secure the encryption.
- DESCRIPTION= { 'text' | @text_variable }: Specifies the free-form text that describes the backup set. The string can have a maximum of 255 characters.
- NAME = { backup_set_name | @backup_set_name_var }: Specifies the name of the backup set. Names can have a maximum of 128 characters. If NAME is not specified, it is blank.
By default, BACKUP appends the backup to an existing media set, preserving existing backup sets. To explicitly specify this, use the NOINIT option. For information about appending to existing backup sets, see Media Sets, Media Families, and Backup Sets (SQL Server).
Alternatively, to format the backup media, use the FORMAT option:
FORMAT [ , MEDIANAME**=** { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { text | @text_variable } ]
Use the FORMAT clause when you are using media for the first time or you want to overwrite all existing data. Optionally, assign the new media a media name and description.
Use the FORMAT clause when you are using media for the first time or you want to overwrite all existing data. Optionally, assign the new media a media name and description.
Important
Use extreme caution when you are using the FORMAT clause of the BACKUP statement because this destroys any backups that were previously stored on the backup media.
Transact-SQL Examples
For the following examples, create a test database with the following Transact-SQL code:
A. Back up to a disk device
The following example backs up the complete
SQLTestDB
database to disk, by using FORMAT
to create a new media set.B. Back up to a tape device
The following example backs up the complete
SQLTestDB
database to tape, appending the backup to the previous backups.C. Back up to a logical tape device
The following example creates a logical backup device for a tape drive. The example then backs up the complete SQLTestDB database to that device.
Sql Backup Master Review
Using PowerShell
Use the Backup-SqlDatabase cmdlet. To explicitly indicate that this is a full database backup, specify the -BackupAction parameter with its default value, Database. This parameter is optional for full database backups.
Powershell Examples
A. Full local backup
The following example creates a full database backup of the
MyDB
database to the default backup location of the server instance ComputerInstance
. Optionally, this example specifies -BackupAction Database.B. Full backup to Microsoft Azure
The following example creates a full backup of the database
Sales
on the MyServer
instance to the Microsoft Azure Blob Storage service. A stored access policy has been created with read, write, and list rights. The SQL Server credential, https://mystorageaccount.blob.core.windows.net/myfirstcontainer
, was created using a Shared Access Signature that is associated with the Stored Access Policy. The PowerShell command uses the BackupFile parameter to specify the location (URL) and the backup file name.To set up and use the SQL Server PowerShell provider