smo恢复数据库 [英] smo restore database

问题描述

我使用SQL Server SMO将.bak还原到新数据库，但是无法正常工作。

I use SQL Server SMO to restore a .bak to a new database, but failed to work.

sql服务器是2012年，并且smo对象版本来自最新的sdk版本11.0

sql server is 2012 and smo object version is from the latest sdk version 11.0

文件.bak已创建

我得到的错误消息是：

服务器'SERVER'的还原失败。

Restore failed for Server '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解决方案，并尝试了

UPDATED: I surrended SMO solutions, and tried

 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 恢复数据库。我将分享我的代码。希望能帮助到你。尽管此解决方案有一个警告，但它认为您只有一个主数据文件。匹配日志文件和数据文件确实很棘手，并且可能在许多方面出现问题。

I successfully used SMO to restore the database. I'll share my code. Hope it helps. This solution has one caveat though, it considers that you have only one primary data file. Getting to match up the log and data files is really tricky and something can go wrong in many ways. Anyway try and let me know it this helps.

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;

        }
    }
}

这篇关于smo恢复数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！