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;
OnionYang@