主页 / dba / 问题 / 9565
Accepted
shamim
Asked: 2011-12-22 12:28:59 +0800 CST

如何仅通过 dbName 和物理路径恢复 SQL Server 数据库

  • 772

我有几个数据库备份，我想将它们全部还原到服务器。

有人可以用 SQL 脚本为我指出正确的方向，将逻辑文件名修改为正确的路径，而不是数据库中的路径吗？

Restore FILELISTONLY FROM DISK='E:\New folder\Thursday - DB_Order.bak'

RESTORE DATABASE Business_Data_TSQL
FROM DISK='E:\New folder\Thursday - DB_Order.bak'
WITH
MOVE 'DBOrder_Data' TO 'E:\New folder\Business_Data.mdf',
MOVE 'DBOrder_Log' TO 'E:\New folder\Business_Data_log.ldf'

使用查询 1 我得到逻辑名称，然后需要为查询 2 手动设置这些名称，但是当有多个数据库时，这真的很困难。

请帮我写一个脚本，我只传递备份文件的物理路径和新的数据库名称，然后恢复该数据库。

如果有任何问题，请询问。提前致谢。

sql-server

2 个回答

  1. Best Answer

    这是您可以使用的东西。它不是完全自动化的，但这绝对是一个非常有用的脚本。

    SET NOCOUNT ON;

DECLARE 
    @DBName NVarchar(100),
    @BackupFile NVarchar(1000),
    @DeviceFrom NVarchar(1000),
    @DeviceTo NVarchar(1000),
    @LogicalName NVarchar(1000),
    @PhysicalName NVarchar(1000),
    @SQL NVarchar(MAX),
    @RowsToProcess integer,
    @CurrentRow integer,
    @Comma NVarchar(25);

SET @DBName = 'DB_Order'; --  Change this for each database
SET @BackupFile = 'E:\New folder\Thursday - DB_Order.bak'; --  Change this for each database

SELECT @DeviceFrom = SUBSTRING(physical_name, 1,
CHARINDEX(@DBName + '.mdf',
physical_name) - 1) 
FROM master.sys.master_files
WHERE name = @DBName AND FILE_ID = 1;

SET @DeviceTo = 'E:\New folder\'; -- Change this if you are changing your restore location
SET @SQL = 'RESTORE DATABASE ' + @DBName + ' FROM DISK = ''' + @BackupFile + ''' WITH ';
SET @CurrentRow = 0;
SET @Comma = ',';

DECLARE @FileList TABLE (
    RowID int not null primary key identity(1,1)
    ,LogicalName NVARCHAR(128) 
    ,PhysicalName NVARCHAR(260) 
    ,Type CHAR(1) 
    ,FileGroupName NVARCHAR(128) 
    ,Size numeric(20,0) 
    ,MaxSize numeric(20,0) 
    ,FileId BIGINT 
    ,CreateLSN numeric(25,0) 
    ,DropLSN numeric(25,0) 
    ,UniqueId uniqueidentifier 
    ,ReadOnlyLSN numeric(25,0) 
    ,ReadWriteLSN numeric(25,0) 
    ,BackupSizeInBytes BIGINT 
    ,SourceBlockSize BIGINT 
    ,FilegroupId BIGINT 
    ,LogGroupGUID uniqueidentifier 
    ,DifferentialBaseLSN numeric(25) 
    ,DifferentialBaseGUID uniqueidentifier 
    ,IsReadOnly BIGINT 
    ,IsPresent BIGINT
    ,TDEThumbprint VARBINARY(32) -- Remove this line for SQL Server 2005
    );

INSERT INTO @FileList
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @BackupFile + '''')
SET @RowsToProcess = @@RowCount;

WHILE @CurrentRow < @RowsToProcess
BEGIN
    SET @CurrentRow= @CurrentRow + 1;
    BEGIN
    IF @CurrentRow = @RowsToProcess
        SET @Comma = ',STATS=1';
    END
    SELECT @LogicalName = LogicalName,@PhysicalName = PhysicalName FROM @FileList WHERE RowID=@CurrentRow;
    SET @PhysicalName = Replace(@PhysicalName,@DeviceFrom,@DeviceTo);
    SET @SQL = @SQL + 'MOVE ''' + @LogicalName + ''' TO ''' + @PhysicalName + '''' + @Comma + '';
END

SELECT @SQL;
--EXEC(@SQL); -- Execute when you are ready.

    您可以使用 SQLCMD / Batch 文件传入您的数据库名称和物理文件名的参数并尝试将其自动化。

    • 3

  2. 我使用这样的 PowerShell 脚本将备份还原到新目标，重置路径和文件名：

    param ($bakpath, $dbname, $instance, [switch]$force, [switch]$test)

# Restore latest backup from wildcard path, optionally forcing out open connections

# Set local SQL Server file directories here:
$mdfpath = "D:\sqldata"
$ldfpath = $mdfpath

if ((get-pssnapin sqlserverprovidersnapin100 -ErrorAction "SilentlyContinue") -eq $NULL) { add-pssnapin sqlserverprovidersnapin100 }
if ((get-pssnapin sqlservercmdletsnapin100 -ErrorAction "SilentlyContinue") -eq $NULL) { add-pssnapin sqlservercmdletsnapin100 }

if (($bakpath -eq $null) -or ($dbname -eq $null)) {
    Write-Error "Parameters required: -bakpath(file/wildcard) -dbname [-instance] [-force] [-test]"
    exit
}

if ($instance -eq $null) { $instance = "." }

$bak = dir $bakpath | sort -prop LastWriteTime | select -last 1

$go = [Environment]::NewLine + "GO" + [Environment]::NewLine

$sql = "RESTORE FILELISTONLY FROM DISK = '$bak'"

$files = Invoke-Sqlcmd -Query $sql -ServerInstance $instance

$sql = ""
if ($force) { $sql += "ALTER DATABASE [$dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE" + $go }
$sql += "RESTORE DATABASE [$dbname] FROM DISK = '$bak' WITH "

foreach ($file in $files) {
    $lname = $file.LogicalName
    $pname = $file.PhysicalName.Split("\")[-1]
    $ext = $pname.Split(".")[-1]
    $ftype = $file.Type
    " -> {0} ({1}: {2})" -f $lname, $ftype, $pname

    if ($ftype -eq "D") { $sql += "MOVE N'$lname' TO N'$mdfpath\$dbname.$ext', " }
    elseif ($ftype -eq "L") { $sql += "MOVE N'$lname' TO N'$ldfpath\$dbname.$ext', " }
}
""

$sql += "REPLACE" + $go
if ($force) { $sql += "ALTER DATABASE [$dbname] SET MULTI_USER" + $go }

if (!$test) { Invoke-Sqlcmd -Query $sql -Verbose -ServerInstance $instance }
    • 3

相关问题