sqlserver定时备份
时间: 2024-01-01 11:05:37 浏览: 27
以下是使用SQL Server代理进行定时备份的步骤:
1. 使用SQL Server代理创建作业:
```sql
USE msdb;
GO
EXEC dbo.sp_add_job
@job_name = N'BackupJob',
@enabled = 1,
@description = N'Daily backup of MyDatabase';
GO
```
2. 为作业添加备份任务:
```sql
USE msdb;
GO
EXEC sp_add_jobstep
@job_name = N'BackupJob',
@step_name = N'BackupStep',
@subsystem = N'TSQL',
@command = N'BACKUP DATABASE MyDatabase TO DISK = ''D:\Backup\MyDatabase.bak''',
@retry_attempts = 5,
@retry_interval = 5;
GO
```
3. 创建作业计划:
```sql
USE msdb;
GO
EXEC dbo.sp_add_schedule
@schedule_name = N'DailyBackupSchedule',
@freq_type = 4,
@freq_interval = 1,
@active_start_time = 220000;
GO
```
4. 将作业和计划绑定在一起:
```sql
USE msdb;
GO
EXEC sp_attach_schedule
@job_name = N'BackupJob',
@schedule_name = N'DailyBackupSchedule';
GO
```
5. 启动作业:
```sql
USE msdb;
GO
EXEC dbo.sp_start_job N'BackupJob';
GO
```
这样,就可以实现每天22点自动执行备份任务,将MyDatabase数据库备份到D盘的Backup文件夹中。如果需要修改备份路径、备份时间等参数,可以在作业和计划中进行修改。