当前位置：Gxlcms > 数据库问题 > C#.NET SQL数据库备份与还原解决方案

C#.NET SQL数据库备份与还原解决方案

时间：2021-07-01 10:21:17 帮助过：34人阅读

=‘‘, --要备份的数据库名称,不指定则备份当前数据库 @BKPATH NVARCHAR(260)=‘‘, --备份文件的存放目录,不指定则使用SQL默认的备份目录 @BKFNAME NVARCHAR(260)=‘‘,--备份文件名,文件名中可以用\DBNAME\代表数据库名,\DATE\代表日期,\TIME\代表时间 @BKTYPE NVARCHAR(10)=‘DB‘,--备份类型:‘DB‘备份数据库,‘DF‘ 差异备份,‘LOG‘ 日志备份 @APPENDFILE BIT=1 --追加/覆盖备份文件 AS BEGIN /* p_BackupDB ‘CSFramework22.Normal‘,‘C:\db_backup\‘ p_BackupDB ‘CSFramework22.Test‘,‘C:\db_backup\‘ p_BackupDB ‘CSFramework22.System‘,‘C:\db_backup\‘ */ DECLARE @SQL VARCHAR(8000) IF ISNULL(@DBNAME,‘‘)=‘‘ SET @DBNAME=DB_NAME()--当前数据库 IF ISNULL(@BKPATH,‘‘)=‘‘ SET @BKPATH=dbo.f_GetDBPath(NULL) IF ISNULL(@BKFNAME,‘‘)=‘‘ SET @BKFNAME=‘\DBNAME\_\DATE\_\TIME\.BAK‘ SET @BKFNAME=REPLACE(REPLACE(REPLACE(@BKFNAME,‘\DBNAME\‘,@DBNAME) ,‘\DATE\‘,CONVERT(VARCHAR,GETDATE(),112)) ,‘\TIME\‘,REPLACE(CONVERT(VARCHAR,GETDATE(),108),‘:‘,‘‘)) SET @SQL=‘BACKUP ‘+CASE @BKTYPE WHEN ‘LOG‘ THEN ‘LOG ‘ ELSE ‘DATABASE ‘ END +‘[‘+@DBNAME+‘]‘ +‘ TO DISK=‘‘‘+@BKPATH+@BKFNAME +‘‘‘ WITH ‘+CASE @BKTYPE WHEN ‘DF‘ THEN ‘DIFFERENTIAL,‘ ELSE ‘‘ END +CASE @APPENDFILE WHEN 1 THEN ‘NOINIT‘ ELSE ‘INIT‘ END PRINT @SQL EXEC(@SQL) IF @@ERROR=0 BEGIN PRINT ‘备份日志‘ INSERT INTO dbo.sys_BackupHistory(DBName,BackupFileName,BackupPath,BackupTime) VALUES (@DBNAME,@BKFNAME,@BKPATH+@BKFNAME,GETDATE()) END END

2. p_RestoreDB 还原数据库存储过程 

  1. <strong><span style="color: #000000">CREATE PROC [dbo].[p_RestoreDB] 
  2.     @BKFILE NVARCHAR(</span><span style="color: #800080">1000</span>),   --<span style="color: #000000">定义要恢复的备份文件名
  3.     @DBNAME SYSNAME,          </span>--<span style="color: #000000">定义恢复后的数据库名,默认为备份的文件名
  4.     @RETYPE NVARCHAR(</span><span style="color: #800080">10</span>)=<span style="color: #800000">‘</span><span style="color: #800000">DB</span><span style="color: #800000">‘</span>,--恢复类型:<span style="color: #800000">‘</span><span style="color: #800000">DB</span><span style="color: #800000">‘</span>完整恢复数据库,<span style="color: #800000">‘</span><span style="color: #800000">DBNOR</span><span style="color: #800000">‘</span> 为差异恢复,日志恢复进行完整恢复,<span style="color: #800000">‘</span><span style="color: #800000">DF</span><span style="color: #800000">‘</span> 差异备份的恢复,<span style="color: #800000">‘</span><span style="color: #800000">LOG</span><span style="color: #800000">‘</span><span style="color: #000000"> 日志恢复
  5.     @FILENUMBER INT</span>=<span style="color: #800080">1</span>,        --<span style="color: #000000">恢复的文件号
  6.     @OVEREXIST BIT</span>=<span style="color: #800080">1</span>          --<span style="color: #000000">是否覆盖已经存在的数据库,仅@RETYPE为   
  7. AS 
  8. BEGIN
  9. </span><span style="color: #008000">/*</span><span style="color: #008000">
  10. --还原数据库
  11. p_RestoreDB ‘C:\db_backup\CSFramework22.Normal_20110924_213838.BAK‘,‘CSFramework22.Normal‘
  12. --查看备份文件的内容
  13. RESTORE FILELISTONLY FROM DISK=‘C:\db_backup\CSFramework22.Normal_20110924_213838.BAK‘
  14. </span><span style="color: #008000">*/</span><span style="color: #000000">
  15.     DECLARE @SQL VARCHAR(</span><span style="color: #800080">8000</span><span style="color: #000000">) 
  16.     </span>--<span style="color: #000000">得到恢复后的数据库名
  17.     IF ISNULL(@DBNAME,</span><span style="color: #800000">‘‘</span>)=<span style="color: #800000">‘‘</span><span style="color: #000000"> 
  18.        SELECT @SQL</span>=<span style="color: #000000">REVERSE(@BKFILE)
  19.        ,@SQL</span>=CASE WHEN CHARINDEX(<span style="color: #800000">‘</span><span style="color: #800000">.</span><span style="color: #800000">‘</span>,@SQL)=<span style="color: #800080">0</span><span style="color: #000000"> THEN @SQL 
  20.        ELSE SUBSTRING(@SQL,CHARINDEX(</span><span style="color: #800000">‘</span><span style="color: #800000">.</span><span style="color: #800000">‘</span>,@SQL)+<span style="color: #800080">1</span>,<span style="color: #800080">1000</span><span style="color: #000000">) END 
  21.        ,@SQL</span>=CASE WHEN CHARINDEX(<span style="color: #800000">‘</span><span style="color: #800000">\‘,@SQL)=0 THEN @SQL </span>
  22.        ELSE LEFT(@SQL,CHARINDEX(<span style="color: #800000">‘</span><span style="color: #800000">\‘,@SQL)-1) END </span>
  23.        ,@DBNAME=<span style="color: #000000">REVERSE(@SQL) 
  24.     </span>--<span style="color: #000000">生成数据库恢复语句
  25.     SET @SQL</span>=<span style="color: #800000">‘</span><span style="color: #800000">RESTORE </span><span style="color: #800000">‘</span>+CASE @RETYPE WHEN <span style="color: #800000">‘</span><span style="color: #800000">LOG</span><span style="color: #800000">‘</span> THEN <span style="color: #800000">‘</span><span style="color: #800000">LOG </span><span style="color: #800000">‘</span> ELSE <span style="color: #800000">‘</span><span style="color: #800000">DATABASE </span><span style="color: #800000">‘</span><span style="color: #000000"> END
  26.        </span>+<span style="color: #800000">‘</span><span style="color: #800000">[</span><span style="color: #800000">‘</span>+@DBNAME+<span style="color: #800000">‘</span><span style="color: #800000">]</span><span style="color: #800000">‘</span>
  27.        +<span style="color: #800000">‘</span><span style="color: #800000"> FROM DISK=</span><span style="color: #800000">‘‘‘</span>+@BKFILE+<span style="color: #800000">‘‘‘‘</span> 
  28.        +<span style="color: #800000">‘</span><span style="color: #800000"> WITH FILE=</span><span style="color: #800000">‘</span>+<span style="color: #000000">CAST(@FILENUMBER AS VARCHAR) 
  29.        </span>+CASE WHEN @OVEREXIST=<span style="color: #800080">1</span> AND @RETYPE IN(<span style="color: #800000">‘</span><span style="color: #800000">DB</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">DBNOR</span><span style="color: #800000">‘</span>) THEN <span style="color: #800000">‘</span><span style="color: #800000">,REPLACE</span><span style="color: #800000">‘</span> ELSE <span style="color: #800000">‘‘</span><span style="color: #000000"> END 
  30.        </span>+CASE @RETYPE WHEN <span style="color: #800000">‘</span><span style="color: #800000">DBNOR</span><span style="color: #800000">‘</span> THEN <span style="color: #800000">‘</span><span style="color: #800000">,NORECOVERY</span><span style="color: #800000">‘</span> ELSE <span style="color: #800000">‘</span><span style="color: #800000">,RECOVERY</span><span style="color: #800000">‘</span><span style="color: #000000"> END 
  31.     </span>--<span style="color: #000000">设当前数据库离线状态
  32.     EXEC(</span><span style="color: #800000">‘</span><span style="color: #800000">ALTER DATABASE [</span><span style="color: #800000">‘</span>+@DBNAME+<span style="color: #800000">‘</span><span style="color: #800000">] SET OFFLINE WITH ROLLBACK IMMEDIATE</span><span style="color: #800000">‘</span><span style="color: #000000">)  
  33.     </span>--<span style="color: #000000">恢复数据库
  34.     EXEC(@SQL) 
  35.     </span>--<span style="color: #000000">设当前数据库连线状态
  36.     EXEC(</span><span style="color: #800000">‘</span><span style="color: #800000">ALTER DATABASE [</span><span style="color: #800000">‘</span>+@DBNAME+<span style="color: #800000">‘</span><span style="color: #800000">] SET ONLINE</span><span style="color: #800000">‘</span><span style="color: #000000">)
  37. END</span></strong>

3.  p_CreateJob创建作业存储过程

(因SQLExpress 2005 没有代理服务，可以创建作业，但无法运行作业。SQL2008版本可以)
4. sys_BackupHistory 备份历史记录表

常见错误及解决方法：
1. 备份错误

技术分享

SQL报错：

BACKUP DATABASE CSFrameworkPermission TO DISK=‘C:\CSFrameworkPermission_20110923_104732.BAK‘ WITH NOINIT
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘C:\CSFrameworkPermission_20110923_104732.BAK‘. Operating system error 5(拒绝访问。).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

提示操作系统错误（拒绝访问），是因为当前建立连接的用户(CSFramework)没有服务器建立文件的权限。
其它提示：
类似于NT AUTHORITY\SYSTEM权限不够的问题，请在服务管理中将SQL Server (MSSQLSERVER) 和 SQL Server Agent (MSSQLSERVER).的两个服务的登录（Log On）改为在 192.168.0.2上有权限的域帐号
解决方法：

打开SQL Server配置管理工具，在Log On页面有两种方式登录服务器。
1. Build-in account，选择Local System。
2. This account，指定一个登录用户，可以使用Administrator用户。

技术分享

2. 还原错误

正常情况下设置好第一步有读写文件权限了，一般不会报错。如果仍有错误，将当前用户指定dbcreator角色。

技术分享

3. 写备份历史记录时出错

The SELECT permission was denied on the object ‘sys_BackupHistory‘, database ‘master‘, schema

技术分享 技术分享

补充：

如果遇到如下情况

技术分享

遇到这种情况别急，查看控制面板，卸载

技术分享

本图为已处理过的，卸载2012的Client和LocalDB

技术分享

OK，解决

C#.NET SQL数据库备份与还原解决方案

标签：

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！