|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
--Script 3: Backup all non-system databases --1. Variable declaration DECLARE @path VARCHAR(500) DECLARE @name VARCHAR(500) DECLARE @filename VARCHAR(256) DECLARE @time DATETIME DECLARE @year VARCHAR(4) DECLARE @month VARCHAR(2) DECLARE @day VARCHAR(2) DECLARE @hour VARCHAR(2) DECLARE @minute VARCHAR(2) DECLARE @second VARCHAR(2) -- 2. Setting the backup path SET @path = 'C:\Temp\OneDrive\DevAndy\Backups Kingside\Database Backup\' -- 3. Getting the time values SELECT @time = GETDATE() SELECT @year = (SELECT CONVERT(VARCHAR(4), DATEPART(yy, @time))) SELECT @month = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mm,@time),'00'))) SELECT @day = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(dd,@time),'00'))) SELECT @hour = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(hh,@time),'00'))) SELECT @minute = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mi,@time),'00'))) SELECT @second = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(ss,@time),'00'))) -- 4. Defining cursor operations DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') -- system databases are excluded --5. Initializing cursor operations OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN -- 6. Defining the filename format SET @fileName = CONCAT(@path, @name, '_backup_', @year, '_', @month, '_', @day, '_', @hour, '_', @minute, '_', @second, '.bak') BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor |
Quelle: https://www.sqlshack.com/multiple-methods-for-scheduling-a-sql-server-backup-automatically/
Login