Задача сделать бэкапы баз на 50 серверах в одно файловой хранилище в разные папки
DECLARE @name NVARCHAR(500) DECLARE @cmd NVARCHAR(500) DECLARE @Cursor CURSOR DECLARE @nameser NVARCHAR(500) SET @nameser = @@SERVERNAME SET @Cursor = CURSOR FOR SELECT NAME FROM [master].[sys].[databases] WHERE NOT NAME IN ('master' ,'tempdb' ,'model' ,'msdb' ,'SES' ,'SES_ARCHIVE' ,'SES_SERVICE') AND NAME LIKE '%2013%' OR NAME LIKE '%2014%' OR NAME LIKE '%2015%' OPEN @Cursor FETCH NEXT FROM @Cursor INTO @name WHILE (@@FETCH_STATUS=0) BEGIN --print @nameser SET @cmd = ' EXEC xp_cmdshell ''MD \\192.168.0.38\Backup\cristal_arhiv\'+@nameser+'\'' BACKUP DATABASE ['+@name+'] TO DISK = N''\\192.168.0.38\Backup\cristal_arhiv\'+@nameser+'\'+@nameser+'_'+ @name+'.BAK'' WITH NOFORMAT, NOINIT, NAME = N'''+@name+ '-Полная База данных Резервное копирование'', SKIP, NOREWIND, NOUNLOAD, STATS = 10' -- PRINT @cmd EXEC (@cmd) FETCH NEXT FROM @Cursor INTO @name END
Скрипт я запускал в зарегистрированных серверах.
После того как сделался бэкап удалил эти базы
DECLARE @name NVARCHAR(500) DECLARE @cmd NVARCHAR(500) DECLARE @Cursor CURSOR SET @Cursor = CURSOR FOR SELECT NAME FROM [master].[sys].[databases] WHERE NOT NAME IN ('master' ,'tempdb' ,'model' ,'msdb','SES','SES_ARCHIVE','SES_SERVICE') AND name LIKE '%2013%' or name LIKE '%2014%' or name LIKE '%2015%' OPEN @Cursor FETCH NEXT FROM @Cursor INTO @name WHILE (@@FETCH_STATUS = 0) BEGIN --print @name SET @cmd = --'BACKUP DATABASE ['+ @name +'] TO DISK = N''\\192.168.0.38\Backup\cristal arhiv\'+@name+'.BAK'' WITH NOFORMAT, NOINIT, NAME = N'''+@name+'----Полная База данных Резервное копирование'', SKIP, NOREWIND, NOUNLOAD, STATS = 10' 'EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'''+@name+''' GO USE [master] GO ALTER DATABASE ['+ @name +'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO USE [master] GO /****** Object: Database ['+ @name +'] Script Date: 11/29/2016 08:23:42 ******/ DROP DATABASE ['+ @name +'] GO' PRINT @cmd -- exec (@cmd) FETCH NEXT FROM @Cursor INTO @name END
Результат бэкапа