# 自动备份所有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"