1. 系统备份

1.1. WEB服务器备份

略

1.2. 数据库备份

1.2.1. 维护计划

建议通过维护计划生成两个子计划，SQL SERVER 系统会自动生成代理任务

• 事务日志-->收缩日志-->全备份
• 增量备份

1.2.2. 本地备份

1.创建备份存储过程，默认C盘目录，如需要修改路径，替换下面代码中的“C:\database\backup”即可,需要手工创建路径。

use master
GO
create procedure BackupDatabase
as
begin
    declare @ident nvarchar(100) ,
    @filename nvarchar(100)
    set @ident = replace(replace(Convert(varchar,Getdate(),126) ,':','_'),'.','_')

    set @filename = N'D:\db_backups\ebs_kernel_'+@ident+N'.bak'         
    backup database ebs_kernel to disk = @filename    with init

    set @filename = N'D:\db_backups\ebs2java_'+@ident+N'.bak'
    backup database ebs2java to disk = @filename    with init

    set @filename = N'D:\db_backups\ebs20_order_'+@ident+N'.bak'
    backup database ebs20_order to disk = @filename    with init


end

然后需要备份时执行如下代码：

use master
GO
exec BackupDatabase

2.定时任务，可以将以下代码执行(用你的数据服务器用户名替换其中的“iZulw4drmbjckqZ\Administrator”)，每五小时进行自动备份。

USE [msdb]
GO

/****** Object:  Job [backup]    Script Date: 11/21/2017 17:17:53 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 11/21/2017 17:17:53 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'backup',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'无描述。',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'iZulw4drmbjckqZ\Administrator', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [backup database]    Script Date: 11/21/2017 17:17:55 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'backup database',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'exec BackupDatabase',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'twice2aday',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=8,
        @freq_subday_interval=5,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20171117,
        @active_end_date=99991231,
        @active_start_time=0,
        @active_end_time=235959,
        @schedule_uid=N'ef9cd9de-08e6-4d07-81e9-e01a98a2edd8'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

1.3. 跨主机备份

• sp_xpcmdshell实现数据部服务器非实时同步

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[backup_to_test]
as
begin

    EXEC sp_configure 'show advanced options', 1;  
    -- To update the currently configured value for advanced options.  
    RECONFIGURE;  
        -- To enable the feature.  
    EXEC sp_configure 'xp_cmdshell', 1;  
    RECONFIGURE

    declare @backupdir nvarchar(max) = 'D:\Backup\',
            @zipdir varchar(max)

    declare @ebs_kernelpath varchar(max),
            @ebs2javapath varchar(max),
            @ebs_remote_dir varchar(max),
            @ebs_kerenel_remote_dir varchar(max)

    declare @zipfilename varchar(max)
    declare @command varchar(1000), 
            @compresscommand1 varchar(1000)
    declare @password varchar(200)

    declare @remoteserverurl varchar(100) = '\\EBSCS\backfromformal',
            @remoteserveruser varchar(100) = 'ebsup\administrator',
            @remoteserverpass varchar(100) = 'JHDZ@abcd1234'


    set @ebs_kernelpath=@backupdir+'ebs_kernel.bak'
    set @ebs2javapath=@backupdir+'ebs2java.bak'

    set    @ebs_remote_dir = '  '+@remoteserverurl+'\ebs2java.bak /y'
    set @ebs_kerenel_remote_dir = '  '+@remoteserverurl+'\ebs_kernel.bak /y'


    exec xp_cmdshell 'del D:\Backup\ebs_kernel.bak /Q'
    exec xp_cmdshell 'del D:\Backup\ebs2java.bak /Q'

    BACKUP DATABASE ebs_kernel  TO DISK = @ebs_kernelpath with compression , noformat ,init;
    BACKUP DATABASE ebs2java  TO DISK = @ebs2javapath with compression , noformat ,init;
    /*如果要压缩    
    set @zipdir='C:\"Program Files"\7-Zip\7z.exe'
    --set @zipfilename='db.png'
    --set @password='-pqsydb'
    --set @command ='del '+@backupdir+'\db.png'
    --exec xp_cmdshell @command
    --set @command=@zipdir+' a -y -sdel ' +@password+' '+@backupdir+@zipfilename+' '+@ebs_kernelpath+' '+@ebs2javapath
    --exec xp_cmdshell @command
    */
    exec xp_cmdshell  'net use /DELETE z: /y'
    print 'net use /DELETE z: /y'

    set @command= 'net use z: '+@remoteserverurl+' "'+@remoteserverpass+'" /user:'+@remoteserveruser
    exec xp_cmdshell @command
    print @command

    set @command = N'copy ' + @ebs2javapath+@ebs_remote_dir
    exec xp_cmdshell @command
    print @command
    set @command = N'copy ' + @ebs_kernelpath+@ebs_kerenel_remote_dir
    exec xp_cmdshell @command
    print @command

end