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 do this. Let's take a look at them.

4 Methods to back up all databases in SQL Server

I compiled 4 methods to backup all MSSQL databases of an instance. Among them, Method 1 is the most intuitive one, but does not apply to Express editions. Method 2 & Method 3 use backup scripts and work for all the editions, but you need to do some extra operations to automate the task. Method 4 is only suitable for users who simply want to keep a copy of all databases.

You can pick one as you need it, or try a simpler alternative.

• 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
• Method 4. Copy all databases of SQL Server as a backup
• Easy alternative: Auto backup all databases in SQL Server (Express)

Method 1. Backup all databases via Maintenance Plan

SSMS (SQL Server Management Studio) provides Maintenance Plan to schedule a range of database maintenance jobs. Normally you can use this feature to auto backup multiple databases, but it’s not included in Express editions. If you are running SQL Server Express, please backup all databases with a script (next method).

1. Launch SSMS and connect to your instance. Expand the menu of Management and right click Maintenance Plans to choose New Maintenance Plan…

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

3. Double-click the block of Back Up Database Task. Configure the backup task in the popping out window. You can easily select multiple specific databases, system databases, non-system databases or all databases of the instance.

4. Then you can configure the Subplan Schedule (calendar icon) to automate the task. After completing the setup, you can save the plan, and run it under SQL Server Agent > Jobs.

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

No matter in which edition of SQL Server, you can use query to achieve this goal. All you need is a script to backup all the SQL databases.

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

2. In SQLQuery windows, enter this T-SQL script to backup all databases in SQL Server. It will back up each database with backup time in filename. You can modify the path to save backup files, and the database 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 on Execute button to perform the backup. You can see the backup status in the screen below.

Tip: If you want to automate the backup script, please use SQL Server Agent, which is only applied to non-Express editions. Learn the operation of SQL Server Agent in this post: auto backup SQL Server database

Method 3. Back up all MSSQL databases using batch file

Although you can use query to backup all databases, it is not very convenient to automate it (especially in Express editions). Therefore, you can consider creating a batch script to backup all databases in SQL Server. Thus you can execute the backup by double-clicking, and automate the batch file with Windows Task Scheduler.

1. Open text editor, or you can right-click the desktop, choose New > Text Document to create a new text file.

2. Fill in your own path to set DBList, your server name, your backup directory (after “TO DISK”), and copy the script into text editor.

@ECHO OFF
SETLOCAL
REM Get date in format YYYY-MM-DD
FOR /F "tokens=1,2,3,4 delims=/ " %%A IN ('Date /T') DO SET NowDate=%%D-%%B-%%C

REM Build a list of databases to backup
SET DBList=filepath\DBList.txt
SqlCmd -E -S servername -h-1 -W -Q "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). Then you can double-click it to execute the backup.

To perform the backup automatically, type “task scheduler” in Windows search bar and create a new task in the utility. More specifically, you can 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

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

Before starting, you need to temporarily stop the service of corresponding SQL instance, otherwise you will receive “The action can’t be completed because the file is open in SQL Server”.

1. Search for “service” in Windows and run it. Find the service of the SQL Server instance you want to backup. For example, I want to copy all databases in the default instance, then I need to find SQL Server (MSSQLSERVER), right-click it and Stop it.

2. Navigate to C:\Program Files\Microsoft SQL Server, you will find separate folders for each instance. Go to the one you want to backup > MSSQL > DATA, here are all the databases in this instance. You can copy the .mdf and .ldf files of specific databases, or directly copy the entire DATA folder to backup all these databases.

3. Save the copy to another location, then you can right-click the SQL Server (MSSQLSERVER) service to Start it. When you want to restore these databases, you need to Stop the service again and put the copied files back to DATA folder with replacement.

Notes:

• Copying and replacing system databases to another instance may cause problem. Please be cautious.
• If you want to copy all the non-system databases to another instance, it’s recommended to use Copy Database Wizard (only in non-Express editions). Details can be found in backup SQL database to another Server.

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

As you can see, the above methods are either too complicated or not applicable to Express editions. To backup all databases in MSSQL, AOMEI Cyber Backup is a more universal solution.

It supports SQL Server 2005-2022 and enables you to manage all the servers within LAN. That is, you can centrally back up all the databases in any instance of a controlled machine, and restore to original server or another server.

It provides convenient schedule options allowing you to run full backup or differential backup automatically. And all the operations can be done through several simple steps.

Have a try on the 30-day trial version:

How to back up all SQL databases of an instance:

1. Install the the downloaded .exe file and launch the main interface of AOMEI Cyber Backup, click Source Device -> Microsoft SQL -> Add Microsoft SQL.

2. Download proxy program and install it on the device with SQL Server installed. Then, click Already installed proxy and select the device with SQL installed. Next, click  -> Authentication to validate the database instance. 

3. Click Backup Task on the left menu bar, select Backup Task -> Create New Task, and choose the backup type as Microsoft SQL Backup.

4. Click on Device Name to specify the SQL instances and databases you need to back up. You can select one/multiple database flexibly, or select all the databases of an instance at once.

5. Select Target to store the backup. You can specify a local or network path, the added path will be saved in Added storage.

6. Click Start Backup to create and execute the task. In daily use, you can also use some useful features to optimize the backup:

✍Schedule Backup helps you backup your database automatically. The schedule setting is enabled by default. You can edit or disable it on your needs. Also choose the backup method (Full/Incremental/Differential) as you like.

✍Backup Cleanup is an advanced feature that can automatically remove history backup versions based on a rule to save your storage space.

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

A completed task will be listed in the Backup Task tab. Then you can edit or restore from SQL database from it when you need.

Conclusion

In MSSQL, how to backup all databases of an instance? There are several methods you can try, but each of them has some limitations. To perform the task smoothly and conveniently, AOMEI Cyber Backup is a considerable alternative. It enables you to select all the databases of an instances or even all the instances on a machine, and makes it very easy to automate the backup.

In addition to SQL Server databases, this software also works on computer files/partitions/OS/disk. You can use it as a small business backup solution to centrally protect all the PCs and servers within LAN.