SQLServer自动备份还原

思路：使用powershell脚本将生产环境数据库的全备和差备文件拷贝至沙箱环境，再使用SQLServer定时任务执行还原备份的存储过程。

• 1.首先将生产库的备份路径共享，沙箱环境将此路径映射网络驱动器Z

• 2.创建拷贝备份文件的powershell脚本，此处命名为SAPPRD.ps1

#New-PSDrive -name Z -PSProvider FileSystem -root \\10.10.10.10\backup2
Copy-Item (get-childitem Z:\full -File | Sort-Object -Property LastWriteTime -Descending )[0].FullName -Destination "D:\PRD_bak_tmp"
Copy-Item (get-childitem Z:\diff -File | Sort-Object -Property LastWriteTime -Descending )[0].FullName -Destination "D:\PRD_bak_tmp"
#remove-psdrive -name Z

• 3.使用windows任务计划程序定时调用脚本
  现在powershell中执行：
  Set-ExecutionPolicy RemoteSigned
  然后打开windows计划任务
  其中在操作一栏设置程序或脚本为：

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

后经测试直接输入脚本C:Users\SQLAdmin\SAPPRD.ps1也可以

添加参数填脚本位置：

C:Users\SQLAdmin\SAPPRD.ps1

起始位置：

C:Users\SQLAdmin

• 4.创建还原数据库的存储过程，存储过程示例如下(其中全备文件和差备文件名、路径、库名、文件等视情况而定，全备每周一次，差备每日一次)：

USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Restore_PRD]
AS

DECLARE @FullFileName VARCHAR(100)
DECLARE @DiffFileName VARCHAR(100)
DECLARE @FileFlag1 VARCHAR(20)
DECLARE @FileFlag2 VARCHAR(20)
DECLARE @SQLStr1 VARCHAR(1000)
DECLARE @SQLStr2 VARCHAR(300)

SET @FileFlag1='Full_20'+CONVERT(char(6),DATEADD(wk,DATEDIFF(wk,0,DATEADD(dd,-1,getdate())),0)-1,12)
SET @FileFlag2='Diff_20'+convert(char(6),getdate(),12)

SET @FullFileName='D:\PRD_bak_tmp\PRD'+@FileFlag1+'.BAK'
SET @DiffFileName='D:\PRD_bak_tmp\PRD'+@FileFlag2+'.BAK'

SET @SQLStr1 ='RESTORE DATABASE PRD FROM DISK = '+ char(39)+@FullFileName+char(39)+ ' with NORECOVERY,'
SET @SQLStr1 = @SQLStr1 + 'MOVE' +char(39)+'PRDDATA1'+char(39)+' TO '+char(39)+ 'E:\data\PRDDATA1.mdf' +char(39)+','
SET @SQLStr1 = @SQLStr1 + 'MOVE' +char(39)+'PRDDATA2'+char(39)+' TO '+char(39)+ 'E:\data\PRDDATA2.ndf' +char(39)+','
SET @SQLStr1 = @SQLStr1 + 'MOVE' +char(39)+'PRDDATA3'+char(39)+' TO '+char(39)+ 'E:\data\PRDDATA3.ndf' +char(39)+','
SET @SQLStr1 = @SQLStr1 + 'MOVE' +char(39)+'PRDDATA4'+char(39)+' TO '+char(39)+ 'E:\data\PRDDATA4.ndf' +char(39)+','
SET @SQLStr1 = @SQLStr1 + 'MOVE' +char(39)+'PRDDATA5'+char(39)+' TO '+char(39)+ 'E:\data\PRDDATA5.ndf' +char(39)+','
SET @SQLStr1 = @SQLStr1 + 'MOVE' +char(39)+'PRDDATA6'+char(39)+' TO '+char(39)+ 'E:\data\PRDDATA6.ndf' +char(39)+','
SET @SQLStr1 = @SQLStr1 + 'MOVE' +char(39)+'PRDDATA7'+char(39)+' TO '+char(39)+ 'E:\data\PRDDATA7.ndf' +char(39)+','
SET @SQLStr1 = @SQLStr1 + 'MOVE' +char(39)+'PRDDATA8'+char(39)+' TO '+char(39)+ 'E:\data\PRDDATA8.ndf' +char(39)+','
SET @SQLStr1 = @SQLStr1 + 'MOVE' +char(39)+'PRD_log'+char(39)+' TO '+char(39)+ 'E:\log\PRD_log.ldf' +char(39)+';'
SET @SQLStr2 = 'RESTORE DATABASE PRD FROM DISK = '+ char(39)+@DiffFileName+char(39)+ ' with RECOVERY;'

DECLARE @s NVARCHAR(1000);
DECLARE tb CURSOR LOCAL
FOR
    SELECT s = 'kill ' + CAST(spid AS VARCHAR)
    FROM master..sysprocesses
    WHERE dbid = DB_ID('PRD');
OPEN tb;
FETCH NEXT FROM tb INTO @s;
WHILE @@fetch_status = 0
    BEGIN
      EXEC(@s);
      FETCH NEXT FROM tb INTO @s;
    END;
CLOSE tb;
DEALLOCATE tb;

DROP DATABASE PRD
EXEC (@SQLStr1)
EXEC (@SQLStr2)

GO

• 5.创建SQLServer计划作业
  在作业步骤中:
  step1：–restore–
  执行我们刚才的存储过程，进行还原

exec Restore_PRD

step2：–grant–
进行授权

USE [PRD]
GO
CREATE USER [bigdata]  FOR LOGIN  [bigdata] 
GO
USE [PRD]
GO
ALTER USER [bigdata] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [PRD]
GO
ALTER ROLE [db_owner] ADD MEMBER  [bigdata] 
GO

以上步骤数据库都选master