c - 使用 SQL Server 管理对象还原 1 个 SQL Server .bak 文件中包含的多个数据库

Question

我已经搜索了很长时间，这是我的第一个 StackOverFlow 问题，所以我希望没有我应该找到的已发布答案!

SQL Server .bak 文件可以包含多个数据库。我需要以编程方式恢复文件中的每个数据库。

我发现的所有示例都使用 .bak 文件和一个数据库。例如:

http://www.sqlmusings.com/2009/06/01/how-to-restore-sql-server-databases-using-smo-and-powershell/

我正在使用 powershell，但这不相关:

$restore = new-object("Microsoft.SqlServer.Management.Smo.Restore")
$restore.NoRecovery = $false;
$restore.ReplaceDatabase = $true;
$restore.Action = "Database"

$backup_device = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($bak_file_full_path, "File")
$restore.Devices.Add($backup_device)
$datatable_header = $restore.ReadBackupHeader($sql_server);

$datatable_header 包含有关 bak 文件中存储的每个底层数据库的信息。

foreach($backup_row in $datatable_header.Rows)
{
    $backup_name = $backup_row["BackupName"];
    $database_name = $backup_row["DatabaseName"];
    $backup_description = $backup_row["BackupName"];
    $backup_position = $backup_row["Position"];

    $restore_as_name = $restore_as;
    if ( ! [string]::IsNullOrEmpty($backup_name) )
    {
        $restore_as_name += "_" + $backup_name;
    }

    $restore_as_name = $restore_as_name.ToLower();

    Write-Host "Restore $restore_as_name";        

    $restore_file_data = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
    $restore_file_data.LogicalFileName = $database_name;
    $restore_file_data.PhysicalFileName = $data_path + $restore_as_name + "_data.mdf"

    $restore_file_log = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
    $restore_file_log.LogicalFileName = $database_name + "_Log";
    $restore_file_log.PhysicalFileName = $data_path + $restore_as_name + "_log.ldf"


    #the logical file names should be the logical filename stored in the backup media
    $restore.Database = $database_name;


    $restore.RelocateFiles.Add($restore_file_data)
    $restore.RelocateFiles.Add($restore_file_log)

    $restore.SqlRestore($sql_server)                      
    $restore.RelocateFiles.Clear();
    #BackupName, BackupDescription, BackupType, ExpirationDate, Compressed, Position              
}

缺少的部分是使用“Position”，它是一个标识文件引用中的数据库的数字:

https://msdn.microsoft.com/en-us/library/ms178536.aspx

如果您在 T-SQL 中执行此操作，一些 sudo 代码将是:

RESTORE DATABASE [%DBNEW%] FROM DISK = `[%PATHBACKUP%]` WITH FILE = [%POSITION%]  

那么，有谁知道如何使用SQL管理对象和位置/文件属性吗？

Answer 1

我想通了 - 这是我的稍微具体的 power shell 代码，用于从一个 .bak 文件恢复所有数据库 - 包括一些辅助函数。

function database_restore_bak($server, $username, $password, $restore_as, $bak_file_full_path)
{

        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended')  | out-null

        try
        {
                Write-Host "Details:";
                Write-Host ;
                Write-Host "SQL Server: $server";
                Write-Host "Username: $username";
                Write-Host "Password: $password";
                Write-Host "Restore as: $restore_as";

                #restore LIB from the backup set

                $sql_server_connection_info =  new-object ('Microsoft.SqlServer.Management.Common.ServerConnection') 

                $sql_server_connection_info.LoginSecure = $false;   # set to true for Windows Authentication
                $sql_server_connection_info.Login = $username;
                $sql_server_connection_info.Password = $password;
                $sql_server_connection_info.ServerInstance = $server;

                $sql_server = new-object ('Microsoft.SqlServer.Management.Smo.Server') -ArgumentList @($sql_server_connection_info);

                Write-Host "Connected to $server, version v($($sql_server.Information.Version))"

                # get the file default paths, we will restore to theses
                $data_path = $sql_server.Settings.DefaultFile
                if ( [string]::IsNullOrEmpty($data_path) )
                { $data_path = $sql_server.Information.MasterDBPath; }
                $data_path = (full_path $data_path);

                Write-Host "Data path: $data_path";

                $log_path = $sql_server.Settings.DefaultLog
                if ( [string]::IsNullOrEmpty($log_path) )
                { $log_path = $sql_server.Information.MasterDBLogPath; }
                $log_path = (full_path $log_path);

                Write-Host "Log path: $log_path";


                # Create restore object and specify its settings
                $restore = new-object("Microsoft.SqlServer.Management.Smo.Restore")
                $restore.NoRecovery = $false;
                $restore.ReplaceDatabase = $true;
                $restore.Action = "Database"

                $backup_device = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($bak_file_full_path, "File")
                $restore.Devices.Add($backup_device)


                $datatable_header = $restore.ReadBackupHeader($sql_server);

                foreach($backup_row in $datatable_header.Rows)
                {
                        $backup_name = $backup_row["BackupName"];
                        $database_name = $backup_row["DatabaseName"];
                        $backup_position = $backup_row["Position"];

                        $restore_as_name = $restore_as;

                        if ( ! [string]::IsNullOrEmpty($backup_name) )
                        { $restore_as_name += "_" + $backup_name; }

                        $restore_as_name = $restore_as_name.ToLower();

                        Write-Host "Restore $restore_as_name";

                        $restore.FileNumber = $backup_position;
                        $file_list = $restore.ReadFileList($sql_server);

                        #data table, change to detect file type to not reply on 0 / 1
                        $logical_file_name_data = $file_list.Rows[0]["LogicalName"]; 
                        $logical_file_name_log = $file_list.Rows[1]["LogicalName"];

                        $restore_file_data = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
                        $restore_file_data.LogicalFileName = $logical_file_name_data;
                        $restore_file_data.PhysicalFileName = $data_path + $restore_as_name + "_data.mdf"

                        $restore_file_log = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
                        $restore_file_log.LogicalFileName = $logical_file_name_log;
                        $restore_file_log.PhysicalFileName = $data_path + $restore_as_name + "_log.ldf"


                        $restore.RelocateFiles.Add($restore_file_data)
                        $restore.RelocateFiles.Add($restore_file_log)

                        #many examples have this step, I did not find it was needed
                        #if (!$sql_server.Databases.Contains($restore_as_name))
                        #{
                        #    $database = New-Object Microsoft.SqlServer.Management.Smo.Database($sql_server, $restore_as_name)
                        #    $database.Create();
                        #}

                        $restore.Database = $restore_as_name;            
                        $sql_server.KillAllProcesses($restore_as_name);

                        $restore.SqlRestore($sql_server)                      
                        $restore.RelocateFiles.Clear();
                        Write-Host "Restored $restore_as_name";
                }
        }
        catch [System.Exception]
        {  error_message "Failed to restore database" $_.Exception; }          
}


function error_message($message, $ex = $null)
{
    sub_heading "Error in the script";
    Write-Host $message -ForegroundColor Red;
    if ( $ex -ne $null )
    {
        $line = $_.InvocationInfo.ScriptLineNumber;
        $line_offset = $_.InvocationInfo.ScriptLineNumber;
        Write-Host "line number: $line, line offset: $line_offset"  -ForegroundColor Red;       
    }   
    while ( $ex -ne $null )
    {
        Write-Host ($ex.Message) -ForegroundColor Red; 
        $ex = $ex.InnerException;
    }  
}


function heading($message)
{
    $char = "-";
    Write-Host -nonewline ($char * (console_width )) -ForegroundColor White;
    Write-Host -nonewline ($char * (console_width )) -ForegroundColor White;
    Write-Host -nonewline ($char * (console_width )) -ForegroundColor White;
    $s = center_text $message $char;
    Write-Host -nonewline $s -ForegroundColor White;
    Write-Host -nonewline ($char * (console_width )) -ForegroundColor White;
    Write-Host -nonewline  ($char * (console_width )) -ForegroundColor White;
    Write-Host ($char * (console_width )) -ForegroundColor White;
}

function sub_heading($message)
{
    $char = "-";
    Write-Host
    Write-Host -nonewline ($char * (console_width )) -ForegroundColor White;
    $s = center_text $message $char;
    Write-Host -nonewline $s -ForegroundColor White;
    Write-Host ($char * (console_width )) -ForegroundColor White;    
}

function console_width()
{
    return [int]$Host.UI.RawUI.BufferSize.width;
}


function center_text($text, $pad_char = " ")
{
    $s = ""
    $s1 = ""

    $console_character_width = console_width;
    $length = [int]$text.length;

    $complete_lines = [System.Math]::floor($length / $console_character_width);
    $chars_just_emit = $complete_lines * $console_character_width;
    $chars_left = $length - ($chars_just_emit);

    if ( $chars_just_emit -gt 0 )
    {
        $s = $text.SubString(0,$chars_just_emit);        
    }
    if ( $chars_left -gt 0 )
    {
        $pad_left = ([int]$console_character_width - [int]$chars_left) / [int]2;

        $s1 = ($pad_char * ($pad_left - 1)) + " " + $text.SubString($chars_just_emit) +  " " + ($pad_char * ($pad_left - 1));    
        if ( $s1.length -gt $console_character_width )
        {
            $s1 = $s1.SubString(1);  
        }
    }
    $ret = $s + $s1;
    return $ret;
}


function full_path($path, $path_separator = "\", $front = $false)
{
    if ( $path -eq $null )
    { return $null; }
    $path = $path.Trim();
    if ( $path.Length -eq 0 )
    {
        return $path_separator;
    }
    if ( ! $front )
    {
        if ( $path[$path.Length - 1] -ne $path_separator )
        {
            $path = ($path + $path_separator);     
        }
    }
    else
    {
         if ( $path[0] -ne $path_separator )
        {
            $path = ($path_separator + $path);     
        }
    }

    return $path;
}