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

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

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

Это считывает пути к папкам, которые отображаются в разделе «Свойства сервера»> «Настройки базы данных»> «Местоположения базы данных по умолчанию».

Недавно я написал функцию, которая делает это за вас. Он поддерживает все версии 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.mdf'',  
MOVE N''demo_log_device'' TO N''' +  @log + '\DemoDb.ldf'',  
NOUNLOAD, REPLACE'

exec (@sql)

Команда CREATE DATABASE создаст базу данных с путями по умолчанию, поэтому вы можете создать базу данных, а затем восстановить ее, используя синтаксис замены (режим 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

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

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

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

-- 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'

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

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

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)

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

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

Так же есть аналогичная кнопка при восстановлении. Поэтому, если у вас уже есть вся информация и вам просто нужен T-SQL, перейдите в студию управления, чтобы выполнить восстановление, но вместо того, чтобы нажать «ОК», чтобы запустить процесс, нажмите кнопку «Сценарий», которая сгенерирует вам T-SQL со значением по умолчанию. ценности.

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