120 private links
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
-- specify database backup directory
SET @path = 'E:\Backup\'
---- specify filename format yyyy-mm-ddhh.mm.ss
--SELECT @fileDate = CONVERT(VARCHAR(10), GETDATE(), 120) +''+ REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','.')
-- specify filename format yyyy-mm-ddhh.mm
SELECT @fileDate = CONVERT(VARCHAR(10), GETDATE(), 120) +''+ REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),':','.')
print REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),':','.')
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCHSTATUS = 0
BEGIN
SET @fileName = @path + @name + '' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName;
FETCH NEXT FROM db_cursor INTO @name;
END
CLOSE db_cursor
DEALLOCATE db_cursor