t-sql 備份所有資料庫及壓縮檔案

SQL資料庫備份後仍有很大的壓縮空間(一般都有80%以上)，我習慣把備份檔案再上傳到dropbox以免出現黑天鵝。

begin

--  檔案儲存位置

DECLARE @Str_File_Path NVARCHAR( 256) = 'C:\Dropbox\oldboy\' 

DECLARE @db_name VARCHAR( 50) 

DECLARE @fileName VARCHAR( 256) 

DECLARE @Command VARCHAR( 256) 

--  使用cursor 所有資料庫

DECLARE db_cursor CURSOR FOR

SELECT name FROM master .dbo. sysdatabases

WHERE name NOT IN ( 'master','model' ,'msdb', 'tempdb')  -- 系統資料庫除外

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @db_name 

WHILE @@FETCH_STATUS = 0 

BEGIN 

          

           SET @fileName = @Str_File_Path + @db_name

           SET @Command = @Str_File_Path + '7z a -t7z '  + @fileName + '.7z ' + @fileName + '.bak'

           SET @fileName = @Str_File_Path + @db_name   + '.bak'          

           BACKUP DATABASE @db_name TO DISK = @fileName WITH INIT 

           exec xp_cmdshell @Command

           SET @Command = 'del ' +   @Str_File_Path + @db_name   + '.bak'

           exec xp_cmdshell @Command

          

           FETCH NEXT FROM db_cursor INTO @db_name 

END 

CLOSE db_cursor  

DEALLOCATE db_cursor

end