C#.NET SQL数据库备份与还原解决方案
C#.NET SQL数据库备份与还原解决方案开发框架V2.2(快速开发版)系统管理模块已集成数据库的备份与还原功能。
数据库列表：帐套数据库与系统数据库列表。
备份文件列表：数据源来自master.dbo.sys_BackupHistory表。
首先在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 还原数据库存储过程
ALTER 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用户。
2. 还原错误
正常情况下设置好第一步有读写文件权限了，一般不会报错。如果仍有错误，将当前用户指定dbcreator角色。
3. 写备份历史记录时出错
The SELECT permission was denied on the object 'sys_BackupHistory', database 'master', schema 'dbo'.
是因为当前用户没有master数据库读写权限。将csframework用户的User Mapping勾上master,设为dbo。
backup_restore_db.sql
下载：
