mssql 数据库还原

 /// <summary>
 /// 数据库还原
 /// </summary>
 /// <param name="DataBaseName">数据库名</param>
 /// <param name="DataBaseRestorePath">还原文件路径</param>
 /// <returns>1还原成功；-1还原失败</returns>
public static int DataBaseRestore(string DataBaseName, string DataBaseRestorePath)
        {
            string DataBaseConn = ConfigurationManager.ConnectionStrings["DataBaseConnString"].ConnectionString;
            SqlConnection sqlConn = new SqlConnection(DataBaseConn);
            SqlCommand sqlComm = new SqlCommand();
            sqlComm.Connection = sqlConn;
            try
            {
                sqlComm.CommandText = "use [" + DataBaseName + "];select filename,groupid from sysfiles";
                sqlConn.Open();
                string db_data_filename = "", db_log_filename="";
                SqlDataReader reader = sqlComm.ExecuteReader();
                while (reader.Read()) //获取物理文件路径
                {
                    if (reader["groupid"].ToString() == "1")
                    {
                        db_data_filename = reader["filename"].ToString().Trim();
                    }
                    if (reader["groupid"].ToString() == "0")
                    {
                        db_log_filename = reader["filename"].ToString().Trim();
                    }
                }
                reader.Close();

                //获取逻辑名字
                string lj_data = "", lj_log = "";
                sqlComm.CommandText = "restore   filelistonly   from   disk='" + DataBaseRestorePath + "'";
                reader = sqlComm.ExecuteReader();
                while (reader.Read()) //获取物理文件路径
                {
                    if (reader["Type"].ToString() == "D")
                    {
                        lj_data = reader["logicalName"].ToString().Trim();
                    }
                    if (reader["Type"].ToString() == "L")
                    {
                        lj_log = reader["logicalName"].ToString().Trim();
                    }
                }
                reader.Close();
                string loginName = DataBaseName;//默认登录帐号名和数据库名相同
                string strSql = String.Format(@"
use master

declare @s nvarchar(1000)
declare tb cursor local
for
select N'kill '+cast(spid as varchar)
from master..sysprocesses
where dbid=db_id('{0}')
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb

RESTORE DATABASE [{0}] FROM DISK='{6}' WITH MOVE '{2}' TO '{4}', MOVE '{3}' TO '{5}',STATS = 10,REPLACE

use [{0}]
--添加用户和权限
exec sp_grantdbaccess N'{1}'
exec sp_addrolemember N'db_accessadmin',N'{1}'
exec sp_addrolemember N'db_securityadmin',N'{1}'
exec sp_addrolemember N'db_ddladmin',N'{1}'
exec sp_addrolemember N'db_backupoperator',N'{1}'
exec sp_addrolemember N'db_datareader',N'{1}'              
exec sp_addrolemember N'db_datawriter',N'{1}'

--替换对象所有者
EXEC sp_MSforeachtable 'exec sp_changeobjectowner ''?'',''{1}'''

DECLARE @OldOwner as NVARCHAR(128)
DECLARE @NewOwner as NVARCHAR(128)
select @OldOwner='?'
select @NewOwner='{1}'
DECLARE @Name as NVARCHAR(128)     
DECLARE @Owner as NVARCHAR(128)   
DECLARE @OwnerName as NVARCHAR(128)   
    
DECLARE curObject CURSOR FOR     
select 'Name'= name,   
'Owner'= user_name(uid)   
from sysobjects   
where user_name(uid)=@OldOwner   
order by name   
OPEN curObject   
FETCH NEXT FROM curObject INTO @Name, @Owner   
WHILE(@@FETCH_STATUS=0)   
BEGIN           
if @Owner=@OldOwner
begin   
set @OwnerName=@OldOwner+'.'+rtrim(@Name)   
exec sp_changeobjectowner @OwnerName, @NewOwner   
end
FETCH NEXT FROM curObjec INTO @Name, @Owner   
END   
close curObject   
deallocate curObject ",
                      DataBaseName, loginName, lj_data, lj_log, db_data_filename, db_log_filename, DataBaseRestorePath);

                sqlComm.CommandText = strSql;
                sqlComm.ExecuteNonQuery();

                Assistant.WriteLog(strSql);
                return 1;
            }
            catch (Exception exc)
            {
                Assistant.WriteLog("MsSql DataBaseRestore " + exc.ToString());
                return -1;
            }
            finally
            {
                sqlConn.Close();
            }
        }

写的一个测试例子，^_^,记录下