Как восстановить резервную копию базы данных SQL Server без знания пути назначения или имени файла?

Мне нужно программно (T-SQL) восстановить резервную копию базы данных в SQL Server 2008. Эта резервная копия поступает с другого сервера, и исходное имя базы данных может быть и другим. (Фактически, я копирую базу данных с некоторого сервера в новую базу данных на другом сервере.)

По-видимому, я должен использовать RESTORE DATABASE ... WITH MOVE, который работает, но мне нужно знать, где должны быть восстановлены файлы и как их следует назвать. Несмотря на поиск, я не нашел то, что казалось бы самой основной задачей: просто используйте путь по умолчанию и имена файлов, т.е. Делайте то, что делает SQL Server при выпуске CREATE DATABASE - вам не нужно указывать путь для этой команды, так почему это необходимо для RESTORE?

Неужели я забыл какое-то тривиальное решение, или мне действительно нужно будет перечислить файлы, как-нибудь найти каталог базы данных из конфигурации SQL Server и использовать эту информацию для создания команды RESTORE? Спасибо.

(Сравнивая с PostgreSQL, где при использовании pg_restore вы указываете уже созданную базу данных назначения, а когда вы ее создавали, у вас была опция - не обязательно - указать нестандартное табличное пространство, но если вы не сделали этого, t, вам все равно, где файлы физически сохранены.)

Ответы

Ответ 1

Для будущих гуглеров; от SQL Server 2012 и далее вы можете использовать:

SELECT ServerProperty(N'InstanceDefaultDataPath') AS default_file
     , ServerProperty(N'InstanceDefaultLogPath') AS default_log
;

Это читает пути к папкам, которые отображаются в разделе "Свойства сервера" > "Параметры базы данных" > "Расположение базы данных по умолчанию"

Ответ 2

Команда CREATE DATABASE создаст базу данных с путями по умолчанию, поэтому вы можете создать базу данных, а затем восстановить ее, используя синтаксис replace (режим sqlcmd):

:setvar DatabaseName MyDatabase
declare @fileName varchar(255)
set @fileName = 'c:\backup\mybackup.bak'

if db_id('$(DatabaseName)') is null
            CREATE DATABASE [$(DatabaseName)]

RESTORE DATABASE [$(DatabaseName)]
FROM DISK = @fileName
WITH REPLACE

Ответ 3

Вам нужно прочитать регистр, например

DECLARE @Result NVARCHAR(4000) 
EXECUTE [master].[dbo].xp_instance_regread 
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', 
@Result OUTPUT,  
'NO_OUTPUT' 
SELECT @Result

Ответ 4

Я написал функцию некоторое время назад, которая делает это для вас. Он поддерживает все версии SQL Server, включая установки с экземплярами экземпляра по умолчанию и именами. См. здесь код и пример

Чтобы выполнить восстановление, вы вызовете функцию, как показано ниже.

declare @sql nvarchar(2000), 
        @data varchar(260), 
        @log varchar(260); 

select @data = dbo.fn_get_default_path(0),
       @log = dbo.fn_get_default_path(1)

SELECT @sql= 'RESTORE DATABASE DefaultLocationDB 
FROM  DISK = N''c:\backups\DemoDB.bak'' WITH  FILE = 1,  
MOVE N''demo_data_device'' TO N''' + @data + '\DemoDb.ldf'',  
MOVE N''demo_log_device'' TO N''' +  @log + '\DemoDb.ldf'',  
NOUNLOAD, REPLACE'

exec (@sql)

Ответ 5

Я должен был сделать это сам, и в итоге я собрал несколько ответов, чтобы создать единственный script, который восстанавливает базу данных (любую базу данных) и перенастраивает файлы. Все, что вам нужно знать, это путь к файлу резервной копии и новое имя базы данных. К сожалению, не похоже, что есть какие-то краткие решения этой проблемы.

DECLARE @Source nvarchar(max) = 'C:\backup.bak'
DECLARE @RestoreDestination nvarchar(max) = 'NewDatabaseName'

--https://stackoverflow.com/a/27893494/5734516
--https://stackoverflow.com/a/4018782/5734516
DECLARE @fileListTable TABLE (
    [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]       INT,
    [FileGroupID]           INT,
    [LogGroupGUID]          UNIQUEIDENTIFIER,
    [DifferentialBaseLSN]   NUMERIC(25,0),
    [DifferentialBaseGUID]  UNIQUEIDENTIFIER,
    [IsReadOnly]            BIT,
    [IsPresent]             BIT,
    [TDEThumbprint]         VARBINARY(32) -- remove this column if using SQL 2005
)

DECLARE @QueryCommand nvarchar(max) =
 'RESTORE FILELISTONLY FROM DISK = N''' + REPLACE(@Source, '''', '''''') + ''' WITH NOUNLOAD'

INSERT INTO @fileListTable 
EXEC(@QueryCommand)


--https://stackoverflow.com/a/2014673/5734516
DECLARE @filepath NVARCHAR(260)
EXEC master.dbo.xp_instance_regread 
        N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', 
        N'DefaultData', 
        @filepath output, 'no_output'


DECLARE @Command nvarchar(max) 
SET @Command = 'RESTORE DATABASE ' + QUOTENAME(@RestoreDestination)  + ' '
                + ' FROM DISK=N''' + REPLACE(@Source, '''', '''''') + ''' '
                + 'WITH FILE=1 '
SELECT 
    @Command = @Command + ',MOVE N''' + REPLACE(LogicalName, '''', '''''') + '''' 
    + ' TO N'''  
        + REPLACE(@filepath + '\' + @RestoreDestination + case when FileID = 1 then '.mdf' when Type = 'L' then '_' + CAST(FileID as varchar(max)) + '.ldf' else '_' + CAST(FileID as varchar(max)) + '.ndf' end, '''', '''''')
    + '''' 
FROM @fileListTable 

SELECT @Command
EXEC(@Command)

Примечание. Я знаю, что это старый пост, но, хотя он может быть полезен для кого-то в будущем.

Ответ 6

Если вы имеете в виду использование параметра "Новая база данных" в Studio Management Studio, то он по умолчанию выполняет некоторые значения. Но T-SQL для CREATE DATABASE по-прежнему требует путей. Вы можете увидеть это, перейдя в студию управления, выберите "Новая база данных", введите новое имя и нажмите кнопку Script. Эта кнопка предоставит вам T-SQL, который будет запускаться SQL Server. Этот процесс фактически не запускается, поэтому вы можете отказаться от мастера создания новой базы данных.

При восстановлении есть аналогичная кнопка. Поэтому, если у вас уже есть вся информация и вам нужен только T-SQL, перейдите в студию управления, чтобы выполнить восстановление, но вместо нажатия OK, чтобы запустить процесс, нажмите кнопку Script, которая будет генерировать T-SQL с помощью значения по умолчанию.

Надеюсь, что это поможет.

Ответ 7

Как уже упоминалось, CREATE DATABASE создает файлы по умолчанию. Затем вы можете найти пути этих файлов и использовать их в RESTORE part

-- Database will be created in default location
CREATE DATABASE [MyRestoredDatabase] 

DECLARE @mdfFile VARCHAR(MAX)
DECLARE @ldfFile VARCHAR(MAX)

SELECT @mdfFile = physical_name
FROM sys.master_files
WHERE database_id = DB_ID('MyRestoredDatabase')
AND file_id = 1

SELECT @ldfFile = physical_name
FROM sys.master_files
WHERE database_id = DB_ID('MyRestoredDatabase')
AND file_id = 2

-- Overwrite known files of the new database from restore 
RESTORE DATABASE [MyRestoredDatabase] 
FROM DISK='C:\Path\To\OriginalDatabase.bak'
WITH REPLACE,
    MOVE 'OriginalDatabase' TO @mdfFile,
    MOVE 'OriginalDatabase_Log' TO @ldfFile

Если вам нужно найти логические имена, используйте следующий SQL

RESTORE FILELISTONLY
FROM DISK='C:\Path\To\MyRestoredDatabase.bak'

Полностью автоматизированный script можно найти здесь