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

首先在master数据库建立3个存储过程及一个备份历史表：
1. p_BackupDB
2. p_RestoreDB
3. p_CreateJob
4. sys_BackupHistory --备份历史表

1. p_BackupDB 备份数据库存储过程

ALTER PROC [dbo].[p_BackupDB] @DBNAME SYSNAME=\'\', --要备份的数据库名称,不指定则备份当前数据库 @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 还原数据库存储过程

CREATE PROC [dbo].[p_RestoreDB] @BKFILE NVARCHAR(1000), --定义要恢复的备份文件名 @DBNAME SYSNAME, --定义恢复后的数据库名,默认为备份的文件名 @RETYPE NVARCHAR(10)=\'DB\',--恢复类型:\'DB\'完整恢复数据库,\'DBNOR\' 为差异恢复,日志恢复进行完整恢复,\'DF\' 差异备份的恢复,\'LOG\' 日志恢复 @FILENUMBER INT=1, --恢复的文件号 @OVEREXIST BIT=1 --是否覆盖已经存在的数据库,仅@RETYPE为 AS BEGIN /* --还原数据库 p_RestoreDB \'C:\db_backup\CSFramework22.Normal_20110924_213838.BAK\',\'CSFramework22.Normal\' --查看备份文件的内容 RESTORE FILELISTONLY FROM DISK=\'C:\db_backup\CSFramework22.Normal_20110924_213838.BAK\' */ DECLARE @SQL VARCHAR(8000) --得到恢复后的数据库名 IF ISNULL(@DBNAME,\'\')=\'\' SELECT @SQL=REVERSE(@BKFILE) ,@SQL=CASE WHEN CHARINDEX(\'.\',@SQL)=0 THEN @SQL ELSE SUBSTRING(@SQL,CHARINDEX(\'.\',@SQL)+1,1000) END ,@SQL=CASE WHEN CHARINDEX(\'\\',@SQL)=0 THEN @SQL ELSE LEFT(@SQL,CHARINDEX(\'\\',@SQL)-1) END ,@DBNAME=REVERSE(@SQL) --生成数据库恢复语句 SET @SQL=\'RESTORE \'+CASE @RETYPE WHEN \'LOG\' THEN \'LOG \' ELSE \'DATABASE \' END +\'[\'+@DBNAME+\']\' +\' FROM DISK=\'\'\'+@BKFILE+\'\'\'\' +\' WITH FILE=\'+CAST(@FILENUMBER AS VARCHAR) +CASE WHEN @OVEREXIST=1 AND @RETYPE IN(\'DB\',\'DBNOR\') THEN \',REPLACE\' ELSE \'\' END +CASE @RETYPE WHEN \'DBNOR\' THEN \',NORECOVERY\' ELSE \',RECOVERY\' END --设当前数据库离线状态 EXEC(\'ALTER DATABASE [\'+@DBNAME+\'] SET OFFLINE WITH ROLLBACK IMMEDIATE\') --恢复数据库 EXEC(@SQL) --设当前数据库连线状态 EXEC(\'ALTER DATABASE [\'+@DBNAME+\'] SET ONLINE\') END

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用户。