自动备份所有sqlserver数据库脚本

使用msde/sqlserver express版本没有带代理服务可以通过计划任务定时运行

• 备份全部数据库的sql,逻辑是通过SYSDATABASES获取全部数据库名写入游标，循环执行备份到文件SQL脚本

use master
declare @DbName varchar(60)
declare @BackSql varchar(1000)
DECLARE @backupTime DATETIME = CURRENT_TIMESTAMP
declare myCursor cursor for
SELECT [name] FROM SYSDATABASES
where [name] not in ('master','model','msdb','tempdb')
order by [name]
open myCursor
fetch next from myCursor into @DbName
while(@@FETCH_STATUS = 0)
begin

select @BackSql='Backup DATABASE ['+@DbName+'] to disk=''F:\YYBAK\'+@DbName+'-'+CONVERT(VARCHAR(25), @backupTime, 112) +'.bak'' with format'

exec(@BackSql)
fetch next from myCursor into @DbName
end
close myCursor
DEALLOCATE myCursor

• 转成powershell脚本在定时任务中运行

-S后面是数据库服务器地址，非默认实例则加上 \实例名，-P是密码

sqlcmd -S localhost -U sa -P password -i ./bak.sql -o ./bak.log

• 备份/ 打包/ 清除

sqlcmd -S localhost -U sa -P CHWIT+sasa,. -i ./YXKbak.sql -o ./bak.log

set zip=C:\Program Files\7-Zip\7z.exe
set timestamp=%date:~0,10%
set dir=E:\temp\

echo "%zip%"
echo "%dir%"
echo "%timestamp%"
::explorer "%arc%\.."

"%zip%" a E:\chwRecycle\ZHGY_YXK_%timestamp%.7z "%dir%"


forfiles /p "E:\\temp" /s /m *.* /d -2 /c "cmd /c del @path" 
forfiles /p "E:\\chwRecycle" /s /m *.* /d -5 /c "cmd /c del @path"