SqlServer backs up multiple databases in batches and deletes the backups three days ago.

SqlServer backs up multiple databases in batches and deletes the backups three days ago.

If you don't talk much about it, paste the Code directly. The specific code is as follows:

/*************************************** ************************* * ****************/DECLARE @ backupfile VARCHAR (1024) DECLARE @ backdesc VARCHAR (1024) DECLARE @ filename VARCHAR (1024) DECLARE @ path VARCHAR (1024) DECLARE @ dbname VARCHAR (1024) DECLARE @ extension_name VARCHAR (16) -- backup parameter DECLARE tmp_Cur cursor for select name from [sys]. [databases] where name not in ('master ',' Model ', 'msdb', 'tempdb') SET @ path = n'd: \ Backup \ Autoback \'; SET @ extension_name = n'bak '; -- generate the file name SET @ filename = CONVERT (VARCHAR (1024), GETDATE (), 120) SET @ filename = REPLACE (@ filename ,':','') SET @ filename = REPLACE (@ filename, '-', '') SET @ filename = REPLACE (@ filename ,'','') SET @ filename = @ filename + '_' + CONVERT (VARCHAR (3), DATEPART (MS, GETDATE () + N '. '+ @ extension_name OPEN tmp _ Cur; fetch next from tmp_Cur INTO @ dbname; WHILE @ FETCH_STATUS = 0 BEGIN -- get the complete target file, the database backs up data to this file. SET @ backupfile = @ path + @ dbname + @ filename -- SELECT @ backupfile SET @ backdesc = @ dbname + N'-full database backup '-- starts backup., COMPRESSION parameter indicates COMPRESSION, saving DISK space backup database @ dbname to disk = @ backupfile with noformat, NOINIT, NAME = @ backdesc, SKIP, NOREWIND, NOUNLOAD, STATS = 10, compression fetch next from tmp_Cu R INTO @ dbname end close tmp_Cur; DEALLOCATE tmp_Cur; -- delete the backup file DECLARE @ olddate datetime select @ olddate = DATEADD (d,-3, GETDATE () three days ago ()) -- EXECUTE Delete (SQL 2008) EXECUTE master. dbo. xp_delete_file 0, @ path, @ extension_name, @ olddate, 1 [SQL] view plain copy print? -- The job's scheduled compression script supports multiple databases DECLARE @ DatabaseName NVARCHAR (50) DECLARE @ ExecuteSql NVARCHAR (MAX) SET @ ExecuteSql = ''DECLARE name_cursor cursor for select name FROM master .. sysdatabases WHERE name not in ('master', 'model', 'msdb', 'tempdb', 'northwind ', 'pubs', 'agentsys', 'dttimedtask ', 'alidificgv2') OPEN name_cursor; fetch next from name_cursor INTO @ DatabaseName; WHILE @ FETCH_STATUS = 0 begin set @ ExecuteSql = ''set @ ExecuteSql + = 'use ['+ @ DatabaseName +']; DECLARE @ Error int set @ Error = (select top 1 size/128.0-CAST (FILEPROPERTY ([NAME], ''spaceused '') AS int)/128.0 AS AvailableSpaceInMB FROM sys. database_files order by [NAME] DESC) -- PRINT @ Error IF (@ Error> 1) begin alter database ['+ @ DatabaseName +'] -- database name set recovery simple; -- set the simple recovery mode dbcc shrinkfile ([yidi1_gv2], 1); -- (M) cannot be less than 1 M, dbcc shrinkfile ([yidi1_gv2_log], 1); -- (M) cannot be less than 1 m alter database ['+ @ DatabaseName +'] set recovery full; -- restore to the original FULL mode END 'print @ ExecuteSql; -- print exec (@ ExecuteSql) -- execute fetch next from name_cursor INTO @ DatabaseName; END; CLOSE name_cursor; DEALLOCATE name_cursor;

Summary

The above section describes how SqlServer backs up multiple databases in batches and deletes the backups three days ago. I hope this will help you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!