Microsoft SQL | How to Back Up All Databases of an Instance?

---

MSSQL: Can you back up all databases at once?

You probably know how to do backup in MSSQL, but what if all the databases need to be backed up? Performing the same operation one by one is time-consuming and labor-intensive, especially when your SQL Server contains a large number of databases. In this case, you may want to back up them all at once.

Fortunately, there are several ways to accomplish this. Let's take a look at them.

4 Methods to back up all databases in SQL Server

I've found four ways to backup all MSSQL databases of an instance, but each has its limitations. The most straightforward method, which works for most editions, has a caveat that it doesn't apply to Express editions. Two other methods use backup scripts and are viable for all editions, but require additional steps to automate the process. The last method is only suitable for users who simply want to keep a copy of all databases, without the need for automation.

The best option is to use a simpler alternative, such as a standard screwdriver or a flathead screwdriver, depending on the type of screw or fastener you need to turn. These tools are usually readily available and can be used in a variety of situations, making them a good all-purpose choice.

• Method 1. Backup all databases via Maintenance Plan
• Method 2. Back up all databases with T-SQL script
• Method 3. Back up all SQL databases using batch file
• 4. To copy all databases of SQL Server as a backup, you can use the built-in SQL Server tool, SQL Server Management Studio (SSMS), or the command-line utility, sqlcmd. With SSMS, you can right-click on the server name in the Object Explorer, select "Tasks," then "Backup," and choose the option to backup all databases.
• Easy alternative: Auto backup all databases in SQL Server (Express)

Method 1. Backup all databases via Maintenance Plan

SSMS (SQL Server Management Studio) provides a Maintenance Plan to schedule database maintenance jobs, including auto backups, but this feature is not available in Express editions. If you're running SQL Server Express, you can instead use a script to backup all databases.

1. Connect to your SQL Server Management Studio (SSMS) instance, expand the Management menu, right-click Maintenance Plans, and select New Maintenance Plan.

2. To implement the new plan, give it a name, then click on the Toolbox on the left side and drag the Back Up Database Task to the lower section of the Maintenance Plan window.

3. The Back Up Database Task block allows you to configure a backup task for one or more databases, either system or non-system, or all databases in the instance, by selecting the desired databases in the popping out window.

4. To automate the task, you can configure the Subplan Schedule, which is represented by a calendar icon, to run the task at a specified time. After setting up the schedule, you can save the plan and run it under SQL Server Agent > Jobs. This allows you to automate the execution of the task and run it under the SQL Server Agent, making it easier to manage and schedule your database tasks.

Method 2. Back up all databases with T-SQL script

You can use a query to backup all SQL databases, regardless of the SQL Server edition. A script is all you need to achieve this goal.

1. Connect to your instance, and click New Query.

2. To backup all databases in SQL Server, enter the following T-SQL script in the SQLQuery windows: `BACKUP DATABASE @db_name TO DISK = @backup_path + @db_name + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak' FROM DATABASES WHERE @db_name NOT IN ('database_to_exclude1', 'database_to_exclude2')`. This script will back up each database with the backup time in the filename, and you can modify the path to save backup files and the databases to exclude according to your needs.

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 backup path
SET @path = 'D:\Backup\'
-- 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

3. Click the "Execute" button to initiate the backup process. The backup status will be displayed on the screen below.

Tip: To automate the backup script, use SQL Server Agent, which is only available in non-Express editions. This allows for scheduled backups of SQL Server databases, with the process and operation detailed in the linked post "auto backup SQL Server database".

Method 3. Back up all MSSQL databases using batch file

Although you can use SQL Server Management Studio's query to backup all databases, it's not convenient to automate this process, especially in Express editions. To overcome this, you can create a batch script to backup all databases in SQL Server, making it easy to execute and automate with Windows Task Scheduler. This approach is more convenient than relying solely on query-based backups.

1. To create a new text file, open your text editor or right-click on the desktop and select "New" followed by "Text Document".

2. To set up the DBList, server name, and backup directory, simply fill in your own values in the script. The script should look something like this:

@ECHO OFF
SETLOCAL
REM Get date in format YYYY-MM-DD
The command `FOR /F "tokens=1,2,3,4 delims=/ " %%A IN ('Date /T') DO SET NowDate=%%D-%%B-%%C` is used to set the variable `NowDate` to the current date in the format `DD-MM-YYYY`. %
REM Build a list of databases to backup
SET DBList=filepath\DBList.txt
SqlCmd -The command is: SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('master','model','msdb','tempdb') > "%DBList%".
REM Backup each database, prepending the date to the filename
FOR /F "tokens=*" %%I IN (%DBList%) DO (
ECHO Backing up database: %%I
SqlCmd -E -S servername -Q "BACKUP DATABASE [%%I] TO Disk='filepath\%%I-%NowDate%.bak'"
ECHO.)
REM Clean up the temp file
IF EXIST "%DBList%" DEL /F /Q "%DBList%"
ENDLOCAL

3. Save the text file as a batch file (with .bat extension) and you can double-click it to run the backup.

To perform a backup automatically, type "task scheduler" in the Windows search bar, create a new task in the utility, set up a schedule, choose the action as "start a program", and specify the program as the created batch file.

Method 4. Copy all databases of SQL Server as a backup

You can also copy all the database files directly to keep a copy in case of recovery needs, if you don't insist on backing up as a .bak file.

To temporarily stop the SQL service, you need to stop the SQL instance, otherwise you'll get an error message saying "The action can't be completed because the file is open in SQL Server".

1. To backup SQL Server, search for "services" in Windows, run it, find the service for the SQL Server instance you want to backup, right-click it, and stop it. For example, to backup the default instance, you would find and stop the "SQL Server (MSSQLSERVER)" service.

2. To backup a SQL Server instance, navigate to the C:\Program Files\Microsoft SQL Server directory, find the folder for the instance you want to backup, then go to the MSSQL > DATA folder, where you'll find all the databases in that instance. You can copy the .mdf and .ldf files of specific databases or the entire DATA folder to backup all the databases.

3. Save the copy to another location, then you can right-click the SQL Server (MSSQLSERVER) service to start it. To restore the databases, stop the service again and replace the copied files with the original ones in the DATA folder.

Notes:

• Copying and replacing system databases to another instance should be done with caution as it may cause problems.
• If you want to copy all non-system databases to another instance, use the Copy Database Wizard (available in non-Express editions). For more information, see the article on backing up a SQL database to another server.

Easy alternative: Auto backup all databases in SQL Server (Express)

The methods mentioned above for backing up all databases in MSSQL are either too complicated or not applicable to Express editions. A more universal solution is Qiling Backup, which can be used to backup all databases in MSSQL.

It supports SQL Server 2005-2022 and enables you to manage all the servers within LAN. This allows central backup of all databases in any instance of a controlled machine, and restoration to the original server or another server.

The software offers convenient scheduling options, enabling users to run full backups or differential backups automatically. This streamlined process can be completed through a series of simple steps, making it easy to manage and maintain backups.

Have a try on the 30-day trial version:

• Free Download
• Windows 11/10/8.1/8/7 & Servers, 100% Secure

✍Schedule Backup This feature helps you automatically back up your database. The schedule setting is enabled by default, but you can edit or disable it as needed. You can also choose the backup method, such as full, incremental, or differential, based on your preferences.

✍Backup Cleanup This feature automatically removes history backup versions based on a rule to save storage space.

✍Email Notification The feature allows you to receive email notifications when the task is abnormal or successful.

A completed task will be listed in the Backup Task tab, allowing for future editing or restoration from the SQL database as needed.

Conclusion

To backup all databases of an instance in MSSQL, you can use Qiling Backup, which allows you to select all databases of an instance or all instances on a machine, making it easy to automate the backup.

In addition to SQL Server databases, this software also supports backup and protection of computer files, partitions, OS, and disk. It can be used as a small business backup solution to centrally protect all PCs and servers within a LAN.

Related Articles

• How to Restore SQL Database from Bak File? (3 Methods)
  After completing the backup successfully, you can restore SQL database from bak file any time you need. In this post, you will find 3 methods to do this, including SSMS, T-SQL query and command line.
• How to Use Batch Script to Backup SQL Server Databases?
  Compared to other methods, a batch script is especially useful when you want to back up batches of databases regularly. This post shares batch scripts for backing up SQL Server databases, and an alternative with equal functionality.
• Guide | Backup and Restore SQL Database with Command Line
  You can backup and restore SQL database via command line, which is especially useful in Express editions that lack schedule backup capabilities. Here's a quick guide for you to backup database using SQLCMD.
• How to Use PowerShell Script to Backup Multiple SQL Databases?
  Have multiple SQL databases that need to be backed up? Go through this article to learn two effective backup solutions – PowerShell script and Qiling Centralized Disk Master.