Smo恢复数据库

本文关键字：数据库 恢复 Smo | 更新日期: 2023-09-27 17:53:46

我使用SQL Server SMO将.bak恢复到新数据库，但失败了。

sql server是2012,smo对象版本来自最新SDK版本11.0

文件。bak是使用SQL管理工作室2012创建的，在相同的本地pc上，在相同的编码pc上。

我得到的错误信息是:

服务器' Server '恢复失败。

我的代码有什么问题?

string dbPath = Path.Combine(@"d:'my data", dbName + "_db" + ".mdf");
string logPath = Path.Combine(@"d:'my data", dbName + "_db" + "_Log.ldf");
Restore restore = new Restore();
BackupDeviceItem deviceItem = new BackupDeviceItem("d:'template.BAK", DeviceType.File);
restore.Devices.Add(deviceItem);
restore.Database = dbName + "_db";
RelocateFile relocateDataFile = new RelocateFile("Data", dbPath);
RelocateFile relocateLogFile = new RelocateFile("Log", logPath);
restore.RelocateFiles.Add(relocateDataFile);
restore.RelocateFiles.Add(relocateLogFile);
restore.Action = RestoreActionType.Database;
restore.ReplaceDatabase = true;
restore.SqlRestore(server);

更新:我放弃SMO解决方案，并尝试

 using (SqlConnection connection = new SqlConnection("Data Source=server;user id=sa;password=xxxxx;"))
        {
            using (SqlCommand command = new SqlCommand(@"RESTORE DATABASE beauty01 FROM DISK = 'd:'template.bak' WITH RECOVERY, MOVE 'beauty1' TO 'D:'MyData'beauty01_Data.mdf', MOVE 'beauty1_log' TO 'd:'Mydata'beauty01_Log.ldf', REPLACE", connection))
            {
                connection.Open();
                // Add the parameters for the SelectCommand.

                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery();
            }
        }  >> work good.

谢谢。

Smo恢复数据库

我成功使用SMO恢复数据库。我将分享我的代码。希望能有所帮助。但是这个解决方案有一个警告，它认为您只有一个主数据文件。匹配日志和数据文件确实很棘手，而且在很多方面都可能出错。不管怎样，让我知道这是否有帮助。

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Text;
using System.Threading;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.Win32;
namespace DatabaseUtility
{
    public class BackupRestore
    {
        static Server srv;
        static ServerConnection conn;
        public static void BackupDatabase(string serverName, string databaseName, string filePath)
        {
            conn = new ServerConnection();
            conn.ServerInstance = serverName;
            srv = new Server(conn);
            try
            {
                Backup bkp = new Backup();
                bkp.Action = BackupActionType.Database;
                bkp.Database = databaseName;
                bkp.Devices.AddDevice(filePath, DeviceType.File);
                bkp.Incremental = false;
                bkp.SqlBackup(srv);
                conn.Disconnect();
                conn = null;
                srv = null;
            }
            catch (SmoException ex)
            {
                throw new SmoException(ex.Message, ex.InnerException);
            }
            catch (IOException ex)
            {
                throw new IOException(ex.Message, ex.InnerException);
            }
        }
        public static void RestoreDatabase(string serverName, string databaseName, string filePath)
        {
            conn = new ServerConnection();
            conn.ServerInstance = serverName;
            srv = new Server(conn);
            try
            {
                Restore res = new Restore();
                res.Devices.AddDevice(filePath, DeviceType.File);
                RelocateFile DataFile = new RelocateFile();
                string MDF = res.ReadFileList(srv).Rows[0][1].ToString();
                DataFile.LogicalFileName = res.ReadFileList(srv).Rows[0][0].ToString();
                DataFile.PhysicalFileName = srv.Databases[databaseName].FileGroups[0].Files[0].FileName;
                RelocateFile LogFile = new RelocateFile();
                string LDF = res.ReadFileList(srv).Rows[1][1].ToString();
                LogFile.LogicalFileName = res.ReadFileList(srv).Rows[1][0].ToString();
                LogFile.PhysicalFileName = srv.Databases[databaseName].LogFiles[0].FileName;
                res.RelocateFiles.Add(DataFile);
                res.RelocateFiles.Add(LogFile);
                res.Database = databaseName;
                res.NoRecovery = false;
                res.ReplaceDatabase = true;
                res.SqlRestore(srv);
                conn.Disconnect();
            }
            catch (SmoException ex)
            {
                throw new SmoException(ex.Message, ex.InnerException);
            }
            catch (IOException ex)
            {
                throw new IOException(ex.Message, ex.InnerException);
            }
        }
        public static Server Getdatabases(string serverName)
        {
            conn = new ServerConnection();
            conn.ServerInstance = serverName;
            srv = new Server(conn);
            conn.Disconnect();
            return srv;
        }
    }
}
相关文章：