linux版本sql server数据库一键备份脚本分享

1、创建备份目录并设置权限

sudo mkdir -p /var/opt/mssql/backups
sudo chown mssql:mssql /var/opt/mssql/backups
sudo chmod 700 /var/opt/mssql/backups

2、编写脚本

vi bk.sql

DECLARE @DBNAME NVARCHAR(100)
DECLARE @BackupPath NVARCHAR(100) = '/var/opt/mssql/backups/'
DECLARE @SQL NVARCHAR(MAX)

BEGIN TRY
    -- 测试路径写入权限
    EXEC xp_create_subdir @BackupPath;
    
    DECLARE CurDBName CURSOR FOR
    SELECT name FROM sys.databases 
    WHERE name NOT IN ('master','tempdb','model','msdb')

    OPEN CurDBName
    FETCH NEXT FROM CurDBName INTO @DBNAME
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @FileName NVARCHAR(500) = @BackupPath + @DBNAME + '_test.bak'
        
        -- 使用最小化参数测试
        SET @SQL = N'BACKUP DATABASE [' + @DBNAME + '] 
                    TO DISK = N''' + @FileName + ''' 
                    WITH INIT, STATS = 5'
        
        PRINT 'Executing: ' + @SQL
        EXEC(@SQL)
        
        FETCH NEXT FROM CurDBName INTO @DBNAME
    END
    
    CLOSE CurDBName
    DEALLOCATE CurDBName
END TRY
BEGIN CATCH
    PRINT 'Error: ' + ERROR_MESSAGE()
END CATCH

3、执行备份

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'zxcvbnm@123' -i bk.sql

4、删除超过30天的备份

crontab -l
0 2 * * * find /var/opt/mssql/backups/ -name "*.bak" -mtime +30 -exec rm {} \;