我自己的项目的sqlserver 2008 r2 express的自动备份的脚本

来源：互联网发布：nginx 集群配置编辑：程序博客网时间：2024/05/18 02:08

参见上一篇文章

backup_database.bat

C:
cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn
SQLCMD -S .\sqlexpress -U sa -P Dayawan123 -i J:\Database_Backup\backup.sql

backup.sql

GO

DECLARE
@backupTime VARCHAR(20)
DECLARE
@fileName VARCHAR(1000)
SELECT
@backupTime=(CONVERT(VARCHAR(8), GETDATE(), 112) +REPLACE(CONVERT(VARCHAR(5), GETDATE(), 114), ':', ''))
SELECT
@fileName='J:\Database_Backup\DB_'+@backupTime+'.bak'
backup database SafetyValve to disk=@fileName
GO

然后是创建的计划任务

safetyValve backup.xml

<?xml version="1.0" encoding="UTF-16"?>
<Task version="1.2" xmlns="http://schemas.microsoft.com/windows/2004/02/mit/task">
<RegistrationInfo>
<Date>2015-05-11T16:50:27.0939616</Date>
<Author>WINDOWS-XCBQ5Y7\Administrator</Author>
<Description>backup every 2 am</Description>
</RegistrationInfo>
<Triggers>
<CalendarTrigger>
<StartBoundary>2015-05-11T02:00:00</StartBoundary>
<Enabled>true</Enabled>
<ScheduleByDay>
<DaysInterval>1</DaysInterval>
</ScheduleByDay>
</CalendarTrigger>
</Triggers>
<Principals>
<Principal id="Author">
<UserId>WINDOWS-XCBQ5Y7\Administrator</UserId>
<LogonType>InteractiveToken</LogonType>
<RunLevel>LeastPrivilege</RunLevel>
</Principal>
</Principals>
<Settings>
<MultipleInstancesPolicy>IgnoreNew</MultipleInstancesPolicy>
<DisallowStartIfOnBatteries>true</DisallowStartIfOnBatteries>
<StopIfGoingOnBatteries>true</StopIfGoingOnBatteries>
<AllowHardTerminate>true</AllowHardTerminate>
<StartWhenAvailable>false</StartWhenAvailable>
<RunOnlyIfNetworkAvailable>false</RunOnlyIfNetworkAvailable>
<IdleSettings>
<Duration>PT10M</Duration>
<WaitTimeout>PT1H</WaitTimeout>
<StopOnIdleEnd>true</StopOnIdleEnd>
<RestartOnIdle>false</RestartOnIdle>
</IdleSettings>
<AllowStartOnDemand>true</AllowStartOnDemand>
<Enabled>true</Enabled>
<Hidden>false</Hidden>
<RunOnlyIfIdle>false</RunOnlyIfIdle>
<WakeToRun>false</WakeToRun>
<ExecutionTimeLimit>P3D</ExecutionTimeLimit>
<Priority>7</Priority>
</Settings>
<Actions Context="Author">
<Exec>
<Command>J:\Database_Backup\backup_database.bat</Command>
</Exec>
</Actions>
</Task>

0 0