Baclup di database per SQL Server
SET NOCOUNT ON;
DECLARE
@FileName NVARCHAR(1024)
, @DBName NVARCHAR(256)
, @PathName NVARCHAR(256)
, @Message NVARCHAR(2048)
, @IsCompressed BIT
SELECT
@PathName = 'C:\sql_daily_backup\ '
, @IsCompressed = 0
DECLARE db CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
SELECT
sd.name
, file_path = @PathName + FileDate + '_' + name + '.bak'
FROM sys.databases sd
CROSS JOIN (
SELECT FileDate = + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '_')
) fd
WHERE sd.state_desc != 'OFFLINE'
AND sd.name NOT IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY sd.name
OPEN db
FETCH NEXT FROM db INTO
@DBName
, @FileName
WHILE @@FETCH_STATUS = 0 BEGIN
DECLARE @SQL NVARCHAR(MAX)
SELECT @Message = REPLICATE('-', 80) + CHAR(13) + CONVERT(VARCHAR(20), GETDATE(), 120) + N': ' + @DBName
RAISERROR (@Message, 0, 1) WITH NOWAIT
SELECT @SQL =
'BACKUP DATABASE [' + @DBName + ']
TO DISK = N''' + @FileName + '''
WITH FORMAT, ' + CASE WHEN @IsCompressed = 1 THEN N'COMPRESSION, ' ELSE '' END + N'INIT, STATS = 15;'
EXEC sys.sp_executesql @SQL
FETCH NEXT FROM db INTO
@DBName
, @FileName
END
CLOSE db
DEALLOCATE db[]
