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 masterdeclare @s nvarchar(1000)declare tb cursor localforselect N'kill '+cast(spid as varchar)from master..sysprocesseswhere dbid=db_id('{0}')open tbfetch next from tb into @swhile @@fetch_status=0beginexec(@s)fetch next from tb into @sendclose tbdeallocate tbRESTORE DATABASE [{0}] FROM DISK='{6}' WITH MOVE '{2}' TO '{4}', MOVE '{3}' TO '{5}',STATS = 10,REPLACEuse [{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=@OldOwnerbegin   set @OwnerName=@OldOwner+'.'+rtrim(@Name)   exec sp_changeobjectowner @OwnerName, @NewOwner   endFETCH 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();            }        }

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