如何一次在MS SQL Server中备份所有数据库

In this article, I will show you how to backup all the Databases at once that you have in the MS SQL Server using a SQL Store Procedure.

在本文中，我将向您展示如何使用SQL存储过程一次备份MS SQL Server中的所有数据库。

Lots of people who work with MS SQL Server is facing this problem when they try to migrate the DBs to a new server from an old server. SQL Server has no option to backup all the Databases that we have in the current instance at once. So I have to face the same problem and found a solution for that.

当尝试将DB从旧服务器迁移到新服务器时，许多使用MS SQL Server的人都面临此问题。 SQL Server无法选择一次备份我们在当前实例中拥有的所有数据库。 因此，我不得不面对同样的问题，并找到了解决方案。

After going so many forums on the internet I have finally managed to develop a Stored Procedure to run in the Server instance and it will automatically backup all the Databases you have in the Server to the location you will provide.

在互联网上经过了如此多的论坛之后，我终于设法开发了一个存储过程以在Server实例中运行，它将自动将Server中拥有的所有数据库备份到您将提供的位置。

So this is the Stored Procedure that you need to use.

这就是您需要使用的存储过程。

DECLARE @databaseName VARCHAR(20); 
DECLARE @PATH VARCHAR(MAX); 
DECLARE @DBcount INT = 0; 
DECLARE @DBNames TABLE (ID INT IDENTITY(1,1) primary key, name nvarchar(20));
DECLARE @LoopCount INT = 1; 


SELECT @DBcount = (select COUNT(name) from sys. databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')) 


INSERT INTO @DBNames 


SELECT name from sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') 


WHILE @LoopCount <= @DBcount 
BEGIN  


    SET @databaseName = (SELECT name FROM @DBNames WHERE ID = @LoopCount) 
    SELECT @PATH = N'C:\backup\ALL DB Backups\' + @databaseName + '.bak' 


    BACKUP DATABASE @databaseName TO  DISK = @PATH  WITH NOINIT,NOUNLOAD, NOSKIP, STATS = 10,NOFORMAT 
    SET @LoopCount = @LoopCount + 1 
    
END; 


GO

Basically this is a normal SP that you can understand easily. And there is a place that you need to change before you run this on your SQL Server Management Studio.

基本上，这是一个您可以轻松理解的常规SP。 在SQL Server Management Studio上运行之前，需要更改一个地方。

See line number 17. That is the place that we are going to store these backups. So you need to give the folder path that you are going to save the backups in your server /machine. just replace the first part that starts with “C:\” with the path to your folder. And it will create the backups with the database names in that directory.

参见第17行。这就是我们要存储这些备份的地方。 因此，您需要提供要将备份保存在服务器/计算机中的文件夹路径。 只需将以“ C：\”开头的第一部分替换为文件夹的路径即可。 然后它将使用该目录中的数据库名称创建备份。

This will take some time if you have many databases. and after all, you will get the Database backups that you want in the directory you selected.

如果您有许多数据库，这将需要一些时间。 毕竟，您将在所选目录中获得所需的数据库备份。

翻译自: https://medium.com/@sithummeegahapola/how-to-backup-all-databases-at-once-in-ms-sql-server-88ed2b6cbee9