C#怎么备份和恢复数据库（SQL SEVER 2005)？

写个存储过程 传入备份的路径 和数据库名字 备份名字 然后再外面调用吧
----------------------------
--完整备份
Backup Database NorthwindCS
To disk='G:BackupNorthwindCS_Full_20070908.bak'
--差异备份
Backup Database NorthwindCS
To disk='G:BackupNorthwindCS_Diff_20070908.bak'
With Differential
--日志备份，默认截断日志
Backup Log NorthwindCS
To disk='G:BackupNorthwindCS_Log_20070908.bak'
--日志备份，不截断日志
Backup Log NorthwindCS
To disk='G:BackupNorthwindCS_Log_20070908.bak'
With No_Truncate
--截断日志不保留
Backup Log NorthwindCS
With No_Log
--或者
Backup Log NorthwindCS
With Truncate_Only
--截断之后日志文件不会变小
--有必要可以进行收缩
--文件备份
Exec Sp_Helpdb NorthwindCS --查看数据文件
Backup Database NorthwindCS
File='NorthwindCS' --数据文件的逻辑名
To disk='G:BackupNorthwindCS_File_20070908.bak'
--文件组备份
Exec Sp_Helpdb NorthwindCS --查看数据文件
Backup Database NorthwindCS
FileGroup='Primary' --数据文件的逻辑名
To disk='G:BackupNorthwindCS_FileGroup_20070908.bak'
With init
--分割备份到多个目标
--恢复的时候不允许丢失任何一个目标
Backup Database NorthwindCS
To disk='G:BackupNorthwindCS_Full_1.bak'
,disk='G:BackupNorthwindCS_Full_2.bak'
--镜像备份
--每个目标都是相同的
Backup Database NorthwindCS
To disk='G:BackupNorthwindCS_Mirror_1.bak'
Mirror
To disk='G:BackupNorthwindCS_Mirror_2.bak'
With Format --第一次做镜像备份的时候格式化目标
--镜像备份到本地和远程
Backup Database NorthwindCS
To disk='G:BackupNorthwindCS_Mirror_1.bak'
Mirror
To disk='\192.168.1.200BackupNorthwindCS_Mirror_2.bak'
With Format
--每天生成一个备份文件
Declare @Path Nvarchar(2000)
Set @Path ='G:BackupNorthwindCS_Full_'
+Convert(Nvarchar,Getdate(),112)+'.bak'
Backup Database NorthwindCS
To disk=@Path
--从NoRecovery或者
--Standby模式恢复数据库为可用
Restore Database NorthwindCS_Bak
With Recovery
--查看目标备份中的备份集
Restore HeaderOnly
From Disk ='G:BackupNorthwindCS_Full_20070908.bak'
--查看目标备份的第一个备份集的信息
Restore FileListOnly
From Disk ='G:BackupNorthwindCS_Full_20070908_2.bak'
With File=1
--查看目标备份的卷标
Restore LabelOnly
From Disk ='G:BackupNorthwindCS_Full_20070908_2.bak'
--备份设置密码保护备份
Backup Database NorthwindCS
To disk='G:BackupNorthwindCS_Full_20070908.bak'
With Password = '123',init
Restore Database NorthwindCS
From disk='G:BackupNorthwindCS_Full_20070908.bak'
With Password = '123'
---------------------------------------------------------
public bool ProcessRestoreDB(string strFileNamePCRBackup, string strFileNameFiscalBackup)
{
bool bResult = true;
DBAccess dbAccess = null;
OdbcCommand cmd = null;
try
{
if (File.Exists(strFileNamePCRBackup) == false || File.Exists(strFileNameFiscalBackup) == false)
{
bResult = false;
}
if (bResult == true)
{
dbAccess = new DBAccess(this.m_objTerminalConfig);
bResult = dbAccess.OpenConnection();
}
if (bResult == true)
{
string strDataBaseName = dbAccess.Connection.Database;
string strAlter = "USE MASTER ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
strAlter = string.Format(strAlter, strDataBaseName);
cmd = new OdbcCommand(strAlter, dbAccess.Connection);
cmd.ExecuteNonQuery();
strAlter = "USE MASTER ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
strAlter = string.Format(strAlter, CDefine.DATABASE_NAME_FISCAL_DB);
cmd = new OdbcCommand(strAlter, dbAccess.Connection);
cmd.ExecuteNonQuery();
string strSQLBackup = "RESTORE DATABASE [{0}] FROM DISK = N'{1}' WITH REPLACE";
strSQLBackup = string.Format(strSQLBackup, strDataBaseName, strFileNamePCRBackup);
strSQLBackup += Environment.NewLine;
strSQLBackup += "RESTORE DATABASE [{0}] FROM DISK = N'{1}' WITH REPLACE";
strSQLBackup = string.Format(strSQLBackup, CDefine.DATABASE_NAME_FISCAL_DB, strFileNameFiscalBackup);
cmd = new OdbcCommand(strSQLBackup, dbAccess.Connection);
cmd.ExecuteNonQuery();
strAlter = "USE MASTER ALTER DATABASE {0} SET MULTI_USER WITH ROLLBACK IMMEDIATE";
strAlter = string.Format(strAlter, strDataBaseName);
cmd = new OdbcCommand(strAlter, dbAccess.Connection);
cmd.ExecuteNonQuery();
strAlter = "USE MASTER ALTER DATABASE {0} SET MULTI_USER WITH ROLLBACK IMMEDIATE";
strAlter = string.Format(strAlter, CDefine.DATABASE_NAME_FISCAL_DB);
cmd = new OdbcCommand(strAlter, dbAccess.Connection);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
ExceptionLogWrite("ProcessRestoreDB", ex);
bResult = false;
}
finally
{
if (dbAccess != null)
{
dbAccess.CloseConnection();
}
}
return bResult;
}
-----------------------------------------------------------
其实这个很简单，SQL本来就可以备份，有SQL 指令的，但是，这个只能把备份文件放在SQL SERVER所在的那台服务器。
backup database DBNAME to disk='d:akfilename.bak'
在SQL 指行这条就可以了
如果要保存到其它的电脑要先建立连接，在SQL里执行这个就好了
master..xp_cmdshell 'net use　\电脑名称共享文件夹名 密码/user:用户名'
其实就是在SQL里执行NET USE(DOS命令)
然后备份的路径就可以用
disk='\电脑名称共享文件夹名akfilename.bak'
SQL2005 的 master..xp_cmdshell 默认是不能用的，BAIDU一把就知道怎么打开了。