gpt4 book ai didi

c# - smo 恢复数据库

转载 作者:可可西里 更新时间:2023-11-01 08:05:12 27 4
gpt4 key购买 nike

我使用 SQL Server SMO 将 .bak 恢复到新数据库,但没有成功。

sql server是2012,smo object版本来自最新的sdk version 11.0

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

我得到的错误信息是:

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 解决方案,并尝试了

 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 恢复了数据库。我会分享我的代码。希望能帮助到你。不过此解决方案有一个警告,它认为您只有一个主要数据文件。匹配日志文件和数据文件确实很棘手,并且在很多方面都可能出错。不管怎样,试着让我知道这有帮助。

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;

}
}
}

关于c# - smo 恢复数据库,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14376615/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com