SQL Server Backup Script: Using T-SQL [Example]

Advantages of backing up SQL Server with T-SQL script

It may not be a bad idea to back up one or two databases with SSMS GUI. But if you have tons of databases in SQL Server, or have specific requirements for backup results (e.g. including date/time in file names), then T-SQL can be a life saver to process repetitive tasks.

In the following article, I will share the SQL Server backup scripts I use to perform basic database backup, and some more advanced tasks.

Basic database backup script in SQL Server

Before moving on to more complex scripts, let's take a glance at the basic DATABASE BACKUP commands, which is the foundation to backup SQL Server with script.

Connect to your server, click New Query to open the SQL Query window, and enter the command to Execute it.

Make a full backup of database:

BACKUP DATABASE databasename
TO DISK = 'path\name.bak'
GO

Make a differential backup of database:

BACKUP DATABASE databasename
TO DISK = 'path\name.bak'
WITH DIFFERENTIAL
GO

*Creating a differential backup requires a previous full backup.

Make a transaction-log backup of database:

BACKUP LOG databasename
TO DISK = 'path\filename.trn'
GO

For database recovery, you could refer to restore SQL database from bak file.

Script to backup SQL Server database with date

Sometimes you may want to back up a database frequently and keep different versions for recovery needs, and it would be very convenient if the bak file name can be composed of database name and backup date.

Here’s an example of SQL Server backup script with date:

DECLARE @FileName varchar(1000)
SELECT @FileName = (SELECT 'filepath\databasename' + convert(varchar(500), GetDate(),112) + '.bak')
BACKUP DATABASE databasename TO DISK = @FileName

Fill in your own backup path and database name, you will get a bak file with date.

Script to backup all databases of an instance

An appropriate T-SQL script can be a great relief when you need to backup all databases of SQL Server. With it, you don’t need to specify the database you want to backup, just exclude those ones you don’t want to backup (such as system databases). And the created backup files will be automatically named as database name plus date.

Here’s an example:

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

-- specify database backup directory e.g. 'D:\backup\'
SET @path = 'backuppath'

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping

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

How to automate a SQL Server backup script?

Now you’ve got the SQL Server backup database script, you can execute it any time you want. However, if you need to perform it frequently, it’s better to automate the MSSQL backup script with SQL Server Agent.

Note: This function is only available in non-Express editions. If you are a SQL Express user, please try using batch file to backup SQL database automatically.

1. Right-click SQL Server Agent, choose New > Job…

2. In the General tab of New Job window, enter the name and description for the job.

3. Click on Steps tab, hit New… and give it a Step name, then copy your backup script into Command section and click OK.

4. Move to Schedules tab, and you can click New… to set up a schedule to auto execute the script.

5. Then, click OK to create the job. If there’s nothing wrong, you can find the new job under SQL Server Agent > Jobs. It will auto perform the operation at the time you set, but you can also right-click it and choose Start Job at Step… to execute it immediately.

Note: If you want to change the destination path of stored backups without changing the script, you can specify a logical backup device in sql server before the backup, and then redefine it when you want to change it.

Powerful alternative to backup SQL databases with ease

Once you’ve got an effective script, the backup job for SQL Server database will become much easier. However, this method has some limitations as well:

• It’s not very convenient to automate the backup task.
• Any minor error in the script will cause the operation to fail.
• You still need a restore script to restore databases of SQL Server…

To make up for these shortcomings, you can use SQL database backup software like AOMEI Cyber Backup. It is very easy to operate, and has the abilities to backup multiple databases at once, schedule automatic backup, restore multiple databases, etc.

Download the 30-day free trial to have a try:

✏ Run the downloaded .exe file to install AOMEI Cyber Backup, and launch its main interface. Click Source Device -> Microsoft SQL -> Add Microsoft SQL.

✏ Choose Download proxy program or Copy link to download the Agent program and install it on the device with SQL Server installed. Then, switch to Already installed proxy to select the device.

✏ Next, click -> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication.

How to backup SQL Server automatically via AOMEI Cyber Backup

1. Click Backup Task -> Create New Task, and choose the Backup Type as Microsoft SQL Backup.

2. Click on Device Name to specify the SQL instance and databases for backup. You can select one or multiple databases as you need.

3. Select a Target storage to save your SQL backups. You can speicfy a local path or network path.

4. Configure the Schedule to execute the SQL database backup daily, weekly or monthly, and choose the backup method as Full, Incremental or Differential Backup.

5. Click Start Backup to create and execute the task. Once it's finished, you will find it in the Backup Task tab.

✍More useful features:
* Archive helps you to store database backups to cloud storage such as Amazon S3.
* Backup Cleanup helps you to delete older backup version automatically and therefore save storage space.
* Email Notification enables you to receive email notifications when the task is abnormal or successful.

6. To restore SQL databases from the backup created with AOMEI Cyber Backup, you just need to click Backup Task on the left menu bar, locate the task you want to restore, and click -> Restore.

☞ You can then select a backup version that you want to restore SQL databases from, and choose to restore to original location or new location.

Conclusion

A SQL Server backup script allows for some more complex and customized backup operations compared to SSMS GUI. But if you are not familiar with T-SQL commands, or want to perform backup & restore more intuitively, you can also use AOMEI Cyber Backup, the centralized data management solution.

It works not only on the local device, but all the SQL Servers within LAN. So you can arrange the backup centrally, and restore SQL database to another server as you like.