如何每天备份SqlServer-2008数据库 [英] How to take backup of a SqlServer-2008 database on every day

问题描述

程序员

我需要使用Sql Jobs每天在00:01 AM备份我的Sql-Server-2008数据库.
情况就像:
需要在过去24小时内完成所有与数据库的交易.像添加的列，添加的表，插入到表中的数据等.

请帮助我在SqlServer中编写查询或在C#中编写代码

在此先感谢...

Hi, Programmers

I need to take a backup of my Sql-Server - 2008 database on everyday at 00:01AM by using Sql Jobs.
The Scenario is like:
Need to Get all the transactions done to the DB on last 24 hrs. Like added Columns,Added Tables,Inserted data in to the tables etc..

Please Help me to write a Query in SqlServer or Code in C#

Thanks in Advance...

推荐答案

以下脚本备份数据库并将其计划到12.00AM

Below script backups the database and schedules it to 12.00AM

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'Back Up Database - databaseName', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=2, 
		@notify_level_netsend=2, 
		@notify_level_page=2, 
		@delete_level=0, 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'Back Up Database - databaseName', @server_name = N'servername\instance'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'Back Up Database - databaseName', @step_name=N'1', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_fail_action=2, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'BACKUP DATABASE [DatabaseName] TO  DISK = N''D:\DbFilepath\dbFileName.bak'' WITH NOFORMAT, NOINIT,  NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
', 
		@database_name=N'master', 
		@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'Back Up Database - databaseName', 
		@enabled=1, 
		@start_step_id=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=2, 
		@notify_level_netsend=2, 
		@notify_level_page=2, 
		@delete_level=0, 
		@description=N'', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', 
		@notify_email_operator_name=N'', 
		@notify_netsend_operator_name=N'', 
		@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'Back Up Database - databaseName', @name=N'DailyBackup', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20111024, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO

更改脚本中的值

change the values from the script

databaseName - database to backup
servername\instance - servername and sqlserver instance name
D:\DbFilepath\dbFileName.bak - backup file path

这篇关于如何每天备份SqlServer-2008数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！