SqlServer备份数据库的4种方式

超、凢脫俗 2024-03-23 18:17 80阅读 0赞

![c1f53deffa6334bf90bfdf5b66889e15.jpeg][]

本文主要介绍了SqlServer备份数据库的4种方式介绍,本文讲解了用sqlserver的维护计划、通过脚本+作业的方式备份数据库（非xp\_cmdshell和xp\_cmdshell）、用powershell调用sqlcmd来执行备份命令几种方式,需要的朋友可以参考下

**1.用sqlserver的维护计划**

在这里我就不给截图演示了，这个比较简单，无非就是通过sqlserver自己的维护计划拖拽出2个一个‘备份数据库'任务和一个‘清除维护'任务。

需要注意的点：

1）有备份任务里边选择备份的库尽量选‘所有用户数据库'这项，以免选择了特定数据库备份以后某天添加了新数据库却忘了勾选导致丢备份。

2）选项验证备份集完整性和压缩备份尽量都选上。

3）备份的路径尽量别选磁盘根目录。

**2.通过脚本+作业的方式备份数据库（非xp\_cmdshell）。**如果是多个库的话自己写个游标用动态sql的方式即可实现多库备份，我这里就提供个思路偷懒就不整理了。

代码如下:

DECLARE @filename VARCHAR(500)  
　　DECLARE @date DATETIME  
　　DECLARE @OLD\_DATE DATETIME  
　　SET @date=GETDATE()  
　　SET @OLD\_DATE=GETDATE()-5 --超过5天的备份即将被删除  
　　SET @FILENAME = 'E:\\存放位置\\数据库名称-'+CAST(DATEPART(YYYY,@DATE) AS VARCHAR(10))+'-'+CAST(DATEPART(MM,@DATE) AS VARCHAR(10))+'-'+CAST(DATEPART(DD,@DATE) AS VARCHAR(10))+'.BAK'  
　　BACKUP DATABASE \[数据库名称\] TO DISK = @filename WITH COMPRESSION  
　　EXECUTE master.dbo.xp\_delete\_file 0,N'E:\\存放位置',N'bak',@OLD\_DATE,1

**3.通过脚本+作业的方式备份数据库（用xp\_cmdshell），**如果你觉得你们服务器的网络、代码、以及防火墙足够安全可以开启xp\_cmdshell的方式来备份。

如果数据库没开启xp\_cmdshell的功能需要用以下脚本开通此功能，开通以后记得关闭sp\_configure。

代码如下:

USE Master  
　　GO  
　　EXEC sp\_configure 'show advanced options', 1;  
　　GO  
　　RECONFIGURE WITH OVERRIDE;  
　　GO  
　　EXEC sp\_configure 'xp\_cmdshell',1;  
　　GO  
　　RECONFIGURE WITH OVERRIDE;  
　　GO

下边是备份的脚本

代码如下:

DECLARE @DBNAME VARCHAR(128)  
DECLARE @PATH VARCHAR(50)  
DECLARE @SQL NVARCHAR(MAX)  
DECLARE @DDATE VARCHAR(8)

SET @PATH = 'E:\\BackUp'  
SET @DDATE = convert(char(8),getdate(),112)

\--删除超过1天的备份  
SET @SQL ='xp\_cmdshell '' forfiles /p "'+@path+'" /d -0 /m \*.bak /c "cmd /c echo deleting @file.... && del /f @file"'''  
EXEC (@SQL)

SET @SQL = ''  
SELECT @SQL = @SQL + '  
BACKUP DATABASE \['+NAME+'\] TO DISK = '''+@PATH+'\\'+REPLACE(name,'.','')+@DDATE+'.bak '''  
FROM master..sysdatabases  
WHERE NAME NOT IN ('master','tempdb','model','msdb')  
EXEC (@SQL)

**4.用powershell调用sqlcmd来执行备份命令。**

在master库下边写好备份的存储过程pr\_1

然后创建powershell脚本，将下边语句粘贴进去并保存成xx.ps1，在通过Windows的任务调度定时执行备份就可以了（我就是举个例子过期删除备份的语句也可以通过powershel实现，在家没有环境就随便写个思路没写全各位见谅）。

代码如下:  
$dbname = 'test'  
write-host "------"$dbname  
& cmd /c "sqlcmd -U sa -P 123456 -S 127.0.0.1 -Q \`" pr\_1 '$dbname'\`" "

以上就是常见的几种备份的方法，各位可以根据自己的业务场景来选择适合自己的备份方式。

转自：[微点阅读][Link 1] [https://www.weidianyuedu.com][https_www.weidianyuedu.com]

[c1f53deffa6334bf90bfdf5b66889e15.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/e661650e99f345fc90ab514532aa90b1.png
[Link 1]: https://m.weidianyuedu.com/
[https_www.weidianyuedu.com]: https://www.weidianyuedu.com