备份和还原SQL数据库
本文关键字:数据库 SQL 还原 备份 | 更新日期: 2023-09-27 18:25:24
我需要(使用C#)将SQL 2005数据库备份到*.bak文件中。从那个文件中,我正试图恢复到同一台服务器上的新数据库中。
备份工作正常,然后我的C#代码调用恢复方法,它似乎一直在工作,直到它最终"超时"。我检查了"innerExceptions",它们显示RESTORE使其达到90%,然后返回异常。
以下是我用于BACKUP AND RESTORE的代码。
有人能告诉我哪里出了问题吗?它必须是一个超时参数,我可以在某个地方设置,但我不知道它是什么或如何做
public class JRBackupRestoreDB
{
public static void BackupDatabase(String databaseName, String userName, String password, String serverName, String destinationPath)
{
Backup sqlBackup = new Backup();
sqlBackup.Action = BackupActionType.Database;
sqlBackup.BackupSetDescription = "ArchiveDataBase:" + DateTime.Now.ToShortDateString();
sqlBackup.BackupSetName = "Archive";
sqlBackup.Database = databaseName;
BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath, DeviceType.File);
ServerConnection connection = new ServerConnection(serverName, userName, password);
Server sqlServer = new Server(connection);
Database db = sqlServer.Databases[databaseName];
sqlBackup.Initialize = true;
sqlBackup.Checksum = true;
sqlBackup.ContinueAfterError = true;
sqlBackup.Devices.Add(deviceItem);
sqlBackup.Incremental = false;
sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
sqlBackup.FormatMedia = false;
sqlBackup.SqlBackup(sqlServer);
}
public static void RestoreDatabase(String databaseName, String filePath,
String serverName, String userName, String password,
String dataFilePath, String logFilePath)
{
Restore sqlRestore = new Restore();
BackupDeviceItem deviceItem = new BackupDeviceItem(filePath, DeviceType.File);
sqlRestore.Devices.Add(deviceItem);
sqlRestore.Database = databaseName;
ServerConnection connection = new ServerConnection(serverName, userName, password);
Server sqlServer = new Server(connection);
Database db = sqlServer.Databases[databaseName];
sqlRestore.Action = RestoreActionType.Database;
String dataFileLocation = dataFilePath + databaseName + ".mdf";
String logFileLocation = logFilePath + databaseName + "_Log.ldf";
db = sqlServer.Databases[databaseName];
RelocateFile rf = new RelocateFile(databaseName, dataFileLocation);
System.Data.DataTable logicalRestoreFiles = sqlRestore.ReadFileList(sqlServer);
sqlRestore.RelocateFiles.Add(new RelocateFile(logicalRestoreFiles.Rows[0][0].ToString(), dataFileLocation));
sqlRestore.RelocateFiles.Add(new RelocateFile(logicalRestoreFiles.Rows[1][0].ToString(), logFileLocation));
sqlRestore.SqlRestore(sqlServer);
db = sqlServer.Databases[databaseName];
db.SetOnline();
sqlServer.Refresh();
}
}
您是否尝试过用ServerConnection.StatementTimeout
设置一个值,看看会发生什么?
MSDN参考资料。
这是在普通sql中非常容易做到的事情。。。
你考虑过那样做吗?你只需要编写sql脚本或过程来完成它,然后运行它。这就是我们所做的(针对不同的情况,但类似):
backup database {{DATABASE NAME HERE}}
to disk = N'{{FILE_NAME_HERE}}'
with
name = N'{{BACKUP_DATABASE_NAME_HERE}}'
GO
restore database {{NEW_DATABASE_NAME}}
from disk = N'{{FILE_NAME_HERE}}'
with
file = 1
go