select ‘备份开端时间: ‘ = getdate(卡塔尔(قطر‎declare @RCL_DATE char(8)select
@RCL_DATE = convert(char(8State of Qatar,GETDATE(卡塔尔国,112State of Qatardeclare @PATH
varchar(60卡塔尔(قطر‎–数据库 SZC 的备份–带差距备份select @PATH = ‘F:DB_Back’ +
‘XTOADB_FULL_’ + @RCL_DATE + ‘.bak’BACKUP LOG XTOADB WITH
TRUNCATE_ONLYBACKUP DATABASE XTOADB TO DISK = @PATH WITH INIT–数据库
SZCStatistics 的备份select @PATH = ‘F:DB_Back’ + ‘XTOADBStatistics’ +
@RCL_DATE + ‘.bak’–BACKUP LOG XTOADBStatistics WITH
TRUNCATE_ONLY–BACKUP DATABASE XTOADBStatistics TO DISK = @PATH–数据库
SZClog 的备份select @PATH = ‘F:DB_Back’ + ‘XTOADBlog’ + @RCL_DATE +
‘.bak’–BACKUP LOG XTOADBlog WITH TRUNCATE_ONLY–BACKUP DATABASE
XTOADBlog TO DISK = @PATHGO?

set @cmd_str='”C:\Program Files\WinRAR\WinRAR.exe” a
y:\backup\database\’+@dbname+’.rar ‘ + @local_name
print @cmd_str+char(10)
EXEC master..xp_cmdshell @cmd_str

2)选项验证备份集完整性和压缩备份尽量都选上。

declare @exec_str varchar(60)declare @exec_str_rar varchar(60)declare
@exec_str_diff varchar(60)declare @exec_str_rar_diff
varchar(60)declare @DEL_DATE_4 char(8)select @DEL_DATE_4 =
convert(char(8State of Qatar,DATEADD(dd,-4,GETDATE(State of Qatar卡塔尔,112卡塔尔–管理数据库 SZC 4天
带增备select @exec_str = ‘del F:DB_BackXTOADB_FULL_’ + @DEL_DATE_4

go

亟需在意的点:

–2. 步骤二:数据库备份管理

–互联网认证
EXEC master..xp_cmdshell ‘net use w: \\200.168.15.9\backup
“baijin830” /user:”WINDOWS-72AMTFX\administrator”‘,no_output
–互联网拷贝
set @cmd_str=’copy x:\backup\database\’+ @dbname+’.rar ‘ + ‘w:\’
print @cmd_str+char(10)
EXEC master..xp_cmdshell @cmd_str

上述正是广大的两种备份的不二秘诀,各位能够依附本人的作业场景来筛选切合自个儿的备份方式。

  • ‘.bak’select @exec_str_rar= ‘del D:DB_Back_RARXTOADB_FULL_’ +
    @DEL_DATE_4 + ‘.rar’select @exec_str_diff = ‘del
    F:DB_BackdifferentXTOADB_DIFF_’ + @DEL_DATE_4 + ‘*.bak’select
    @exec_str_rar_diff= ‘del D:DB_Back_RARdifferentXTOADB_DIFF_’ +
    @DEL_DATE_4 + ‘*.rar’exec master..xp_cmdshell @exec_str,
    no_outputexec master..xp_cmdshell @exec_str_rar, no_outputexec
    master..xp_cmdshell @exec_str_diff, no_outputexec
    master..xp_cmdshell @exec_str_rar_diff, no_output–处理数据库
    SZCStatistics 4天select @exec_str = ‘del F:DB_BackXTOADBStatistics’ +
    @DEL_DATE_4 + ‘.bak’select @exec_str_rar= ‘del
    D:DB_Back_RARXTOADBStatistics’ + @DEL_DATE_4 + ‘.rar’exec
    master..xp_cmdshell @exec_str, no_outputexec master..xp_cmdshell
    @exec_str_rar, no_output–管理数据库 SZClog 4天select @exec_str =
    ‘del F:DB_BackXTOADBlog’ + @DEL_DATE_4 + ‘.bak’select
    @exec_str_rar= ‘del D:DB_Back_RARXTOADBlog’ + @DEL_DATE_4 +
    ‘.rar’exec master..xp_cmdshell @exec_str, no_outputexec
    master..xp_cmdshell @exec_str_rar, no_outputGO

–压缩到网络驱动器上

然后创制powershell脚本,将下边语句粘贴进去并保存成xx.ps1,在通过Windows的职责调解定时推行备份就足以了。复制代码 代码如下:$dbname = ‘test’write-host
“——“$dbname& cmd /c “sqlcmd -U sa -P 123456 -S 127.0.0.1 -Q `”
pr_1 ‘$dbname’`” “

mssql server 数据库教程备份语句

–数据库备份压缩拷贝实例:前提要安装RA奇骏压缩软件
–注脚变量
declare @day varchar(10),@dbname varchar(20),@filename
varchar(100),@cmd_str varchar(500),@local_name varchar(500)
–起头变量值
set @day = convert(varchar(10),getdate(),112)
set @dbname=’database’
set @filename=@dbname+@day
set @local_name=’y:\backup\database\’+@filename
–sql备份
backup database database to disk=@local_name with init
–删除备份
set @cmd_str=’del y:\backup\database\’+@dbname +
convert(varchar(10),DATEADD(DAY,-2,getdate()),112)
EXEC master..xp_cmdshell @cmd_str
–删除RAR
set @cmd_str=’del y:\backup\database\’+@dbname+’.rar’
EXEC master..xp_cmdshell @cmd_str

1.用sqlserver的护卫安插

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注