Can you back up all databases in MSSQL 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 SQL 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
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 databases.
1. Connect to your instance, and click New Query.
2. Enter this T-SQL script in SQLQuery window to 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
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
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
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.
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
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'"
REM Clean up the temp file
IF EXIST "%DBList%" DEL /F /Q "%DBList%"
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.
- 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 Centralized Backupper Database is a more universal solution.
It supports SQL Server 2005-2019 and enables you to manage all the Windows PCs and 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:
Install the software and its client packages properly, gain control over those computers you want to manage. You can also register the central computer as a client at the same time.
How to centrally backup all databases in MSSQL:
1. Launch Centralized Backupper Database, select Home > SQL Server Backup.
2. In Step 1, click + Add Computer to detect the client computers with SQL Server databases, and choose the one you want to backup.
3. Move to Step 2, click + Add to detect all the SQL Server instances on the selected machine. Here you can select one or more instances in their entirety, or specifically select the databases you want to back up.
4. Finally click on Step 3, hit Add Storage to specify a network share or NAS path as the backup destination.
5. Click Schedule to set up an automatic backup (once/daily/weekly/monthly), full backup an d differential backup is also supported in Advanced tab. After the setup, you can Start Backup.
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 Centralized Backupper Database Edition 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.