This post covers how to use T-SQL commands for SQL database backup with date in file name, and how to execute the task with a schedule. If they are not convenient enough, you can also use professional SQL backup management tool.
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.
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.
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.
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
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.
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:
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
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
β 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.
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.