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 server 是 2012 并且 smo 对象版本来自最新的 sdk 版本 11.0

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

文件 .bak 是使用 sql management studio 2012 创建的，在同一台本地电脑上，也在同一台编码电脑上.

file .bak was created using sql management studio 2012, same local pc, on the same coding pc as well.

我得到的错误信息是:

服务器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:	emplate.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:	emplate.bak' WITH RECOVERY, MOVE 'beauty1' TO 'D:MyDataeauty01_Data.mdf', MOVE 'beauty1_log' TO 'd:Mydataeauty01_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屋！