October 23, 2014 at 12:14 pm
I am unable to find a method to do a restore operation on a database if the database have more than two data files and more than one log file situation (automated). Can anyone please reply with a good query...
Note: If this is something like a database with 2 dB files and one log file, my below script works:
*********************************************************************************
use master
go
DECLARE @Table TABLE (LogicalName varchar(128),[PhysicalName] varchar(128), [Type] varchar, [FileGroupName] varchar(128), [Size] varchar(128),
[MaxSize] varchar(128), [FileId]varchar(128), [CreateLSN]varchar(128), [DropLSN]varchar(128), [UniqueId]varchar(128), [ReadOnlyLSN]varchar(128), [ReadWriteLSN]varchar(128),
[BackupSizeInBytes]varchar(128), [SourceBlockSize]varchar(128), [FileGroupId]varchar(128), [LogGroupGUID]varchar(128), [DifferentialBaseLSN]varchar(128), [DifferentialBaseGUID]varchar(128), [IsReadOnly]varchar(128), [IsPresent]varchar(128), [TDEThumbprint]varchar(128)
)
DECLARE @Path nvarchar(1000)='C:\BKP\adventureworks2012.Bak'
DECLARE @LogicalNameData varchar(128),@LogicalNameLog varchar(128)
DECLARE @PhysicalDatapath varchar(128),@PhysicalLogPath varchar(128)
--Declare @datafile_count INT
--Declare @logfile_count INT
INSERT INTO @table
EXEC('
RESTORE FILELISTONLY
FROM DISK=''' +@Path+ '''
')
/*
select * from @table
select @datafile_count = count(*) from @table
where type = 'D'
select @logfile_count = count(*) from @table
where type = 'L'
*/
SET @LogicalNameData=(SELECT LogicalName FROM @Table WHERE Type='D')
SET @LogicalNameLog=(SELECT LogicalName FROM @Table WHERE Type='L')
SET @PhysicalDatapath = (SELECT PhysicalName FROM @Table WHERE Type='D')
SET @PhysicalLogPath = (SELECT PhysicalName FROM @Table WHERE Type='L')
SELECT @LogicalNameData "DB_Data_Logical_Name",@LogicalNameLog "DB_Log_Logical_Name"
SELECT @PhysicalDatapath "DB_Data_Physical_Name",@PhysicalLogPath "DB_Log_Physical_Name"
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'adventureworks2012')
BEGIN
ALTER database [adventureworks2012] set single_user with rollback immediate
RESTORE DATABASE [adventureworks2012] FROM DISK = @Path
WITH
MOVE @LogicalNameData TO @PhysicalDatapath,
MOVE @LogicalNameLog TO @PhysicalLogPath,
replace, stats = 10
ALTER database [adventureworks2012] set multi_user with rollback immediate
END
ELSE
BEGIN
ALTER database [adventureworks2012] set single_user with rollback immediate
RESTORE DATABASE [adventureworks2012] FROM DISK = @Path
WITH
MOVE @LogicalNameData TO @PhysicalDatapath,
MOVE @LogicalNameLog TO @PhysicalLogPath,
replace, stats = 10
ALTER database [adventureworks2012] set multi_user with rollback immediate
END
Thanks.
October 23, 2014 at 12:57 pm
I suggest gen'ing the MOVE clauses directly from @tables, ignoring the count. Just concatenate all the results into a RESTORE statement, and run that statement dynamically. For example:
...existing code...
DECLARE @sql nvarchar(max)
DECLARE @db_name_to_restore sysname
DECLARE @db_with_clauses nvarchar(max)
SET @db_name_to_restore = 'adventureworks2012'
SELECT @db_with_clauses = STUFF((
SELECT
', MOVE ''' + LogicalName + ''' TO ''' + PhysicalName + ''''
FROM @Table
ORDER BY FileId
FOR XML PATH('')
), 1, 2, '')
--SELECT @db_with_clauses
SET @sql =
'RESTORE DATABASE [' + @db_name_to_restore + '] ' +
'FROM DISK = ''' + @Path + ''' ' +
'WITH REPLACE, STATS = 10, ' +
@db_with_clauses
PRINT @sql
--EXEC(@sql)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 23, 2014 at 1:05 pm
Many thanks Scott.
I just now added one more Log file too, to test and the query picks up. Thanks.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply