CODEWITHSUNDEEP
c#sql-server-2005smo
dellbingham
dellbingham

Reputation: 59

Backup and Restore SQL Database

I need to backup (using C#) a SQL 2005 Database to a *.bak file. From that file, I am trying to restore into a new Database on the same exact server.

The backup works fine and then my C# code calls the restore method and it appears to be working until it finally "timesout". I checked the "innerExceptions" and they reveal that the RESTORE made it to 90 percent and then returns the exception.

Below is the code I am using to BACKUP AND RESTORE.

Can someone please let me know where I am going wrong? It must be a timeout parameter somewhere that I can set but I do not know what it is or how to do it.

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();
    }

}

Upvotes: 3

Views: 4782

Answers (2)

Chris Pfohl
Chris Pfohl

Reputation: 19074

This is something that'd be pretty darn easy to do in plain sql...

Have you considered trying it that way? You just have to write the sql script or procedure to do it and then run it. This is what we do (for a different situation, but similar):

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

Upvotes: 0

Bryan Crosby
Bryan Crosby

Reputation: 6554

Have you tried setting a value with ServerConnection.StatementTimeout to see what happens?

MSDN reference here.

Upvotes: 1

Related Questions