Windows2008服务器sqlserver2008自动备份数据库到指定目录以日期命名。

  • A+
输入正文标题广告代码(非移动端)

备份脚本sqlbackup.bat:

@echo off
md "E:\sqlserver-backup\database-bak\%date:~0,4%%date:~5,2%%date:~8,2%"
osql -S "127.0.0.1" -U "sa" -P "asd456" -i "E:\sqlserver-backup\shell\sqlervervackup.sql"
pause

%date:~0,4%%date:~5,2%%date:~8,2%  创建文件夹以日期为名。格式为:20151105osql:cmd中链接sql的工具。-S:为主机-U:用户名-P:密码-i:执行数据库命令的文件地址

更多参数cmd中输入osql -?查看

Windows2008服务器sqlserver2008自动备份数据库到指定目录以日期命名。

数据库命令文件sqlervervackup.sql:
DECLARE @strPath NVARCHAR(200)

set @strPath = convert(NVARCHAR(19),getdate(),112)

set @strPath = REPLACE(@strPath, ':' , '.')

set @strPath = 'E:\sqlserver-backup\database-bak\'+@strPath + '\'+ 'canyinnet' +@strPath + '.bak'

BACKUP DATABASE [canyinnet] TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

convert(NVARCHAR(19),getdate(),112):112的含义为设定日期格式为20151105这种。与脚本中的格式对应。'E:\sqlserver-backup\database-bak\'+@strPath + '\'+ 'canyinnet' +@strPath + '.bak':设定备份目录为E:\sqlserver-backup\database-bak\20151105(随备份日期改变),备份的文件名为数据库名+日期。如:canyinnet20151105.bat

附录:GETDATE()日期格式一览

Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16 Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AMSelect CONVERT(varchar(100), GETDATE(), 1): 05/16/06Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AMSelect CONVERT(varchar(100), GETDATE(), 10): 05-16-06Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16Select CONVERT(varchar(100), GETDATE(), 12): 060516Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AMSelect CONVERT(varchar(100), GETDATE(), 23): 2006-05-16Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AMSelect CONVERT(varchar(100), GETDATE(), 101): 05/16/2006Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AMSelect CONVERT(varchar(100), GETDATE(), 110): 05-16-2006Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16Select CONVERT(varchar(100), GETDATE(), 112): 20060516Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM

参考文章:

【脚本】Sqlserver批处理备份数据库到指定目录

输入正文底部广告代码(非移动端)
输入评论上方广告代码(非移动端)

发表评论

:?::razz::sad::evil::!::smile::oops::grin::eek::shock::???::cool::lol::mad::twisted::roll::wink::idea::arrow::neutral::cry::mrgreen: