Guide & Example | Backup Script for SQL Server Database

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 SQLQuery 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 Centralized Backupper Database. 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 try:

1. After finishing the setup of Centralized Backupper Database, you can use it to install Agent package on client-side, and Request Control over the computers you want to manage.

Then, switch to Home tab and choose SQL Server Backup to get started.

2. In Step 1, click + Add Computers to detect the controlled client computers with SQL Server, and select the one you want to backup.

3. In Step 2, and hit + Add to detect the instances on the select machine. In the popping out window, you can choose multiple databases or even multiple instances to backup.

4. In Step 3, click Add Storage to specify a target path. The added network share or NAS will be saved for your latter use.

5. Optionally, you can set up a Schedule to automate the backup, and choose to perform full or differential backup. Then click Start Backup to execute the task. Once a backup is completed, you can directly choose the task to restore.

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 Centralized Backupper Database Edition, the centralized data management solution.

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