Backup Database pubs to Disk='D:\DataSQL\pubs.bak' --->动态备份所有数据库

备份数据库

在项目实施时，备份恢复数据库还是有必要的，自动或傻瓜式的操作比较方便，
未测试，失业了，现在静不下心来，有机会要求再做这类操作时实现它，此处先收藏备用

/*

   《Dynamic SQL Applications, Performance, and Security in Microsoft SQL Server》, were tested most heavily with AdventureWorks 2016

*/

BACKUP DATABASE pubs

TO DISK='E:\SQLBackups\pubs.bak'

WITH COMPRESSION;

-- Dynamic SQL Built to Back Up All Databases Starting with “AdventureWorks”

DECLARE @database_list TABLE

      (database_name SYSNAME);

INSERT INTO @database_list

      (database_name)

SELECT

      name

FROM sys.databases

WHERE name LIKE 'AdventureWorks%';

DECLARE @sql_command NVARCHAR(MAX);

DECLARE @database_name SYSNAME;

DECLARE database_cursor CURSOR LOCAL FAST_FORWARD FOR

SELECT database_name FROM @database_list

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @database_name;

WHILE @@FETCH_STATUS = 0

BEGIN

      SELECT @sql_command = '

      BACKUP DATABASE [' + @database_name + ']

      TO DISK="E:\SQLBackups\' + @database_name + '.bak"

      WITH COMPRESSION;'

      EXEC (@sql_command);

      FETCH NEXT FROM database_cursor INTO @database_name;

END

CLOSE database_cursor;

DEALLOCATE database_cursor;