Were doing an automated restore for running integration tests in a project I’m working on. To make the script easy to use we only wanted to use database name and path to file. Here is the script we ended up with.
Just replace MyDatabase and C:\backups\MyDatabase.bak. Here is the script we ended up with.
IF EXISTS (SELECT * FROM sys.databases WHERE NAME='MyDatabase')
BEGIN
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
END
declare @logicalName nvarchar(128)
declare @logLogicalName nvarchar(128)
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,
IsReadOnl bit,
IsPresent bit,
TDEThumbprint varbinary(32)
)
insert into @fileListTable exec('RESTORE FILELISTONLY FROM DISK= N''C:\backups\MyDatabase.bak''')
select @logLogicalName = LogicalName from @fileListTable WHERE Type = 'L'
select @logicalName = LogicalName from @fileListTable WHERE Type = 'D'
declare @query varchar(max)
select @query =
'RESTORE DATABASE [MyDatabase]
FROM DISK = N''C:\backups\MyDatabase.bak'' WITH FILE = 1,
MOVE N''' + @logicalName + ''' TO N''C:\localdata\MyDatabase.mdf'',
MOVE N''' + @logLogicalName + ''' TO N''C:\localdata\MyDatabase.ldf'',
NOUNLOAD,
REPLACE,
STATS = 10'
exec (@query)
ALTER DATABASE MyDatabase SET MULTI_USER