BACKUP RESTORE FILELISTONLY

BACKUP RESTORE FILELISTONLY

                                                   Today my friend ask me about backup and restore related question. While talking with him about the issue, I have decided to share this in my web log.
The Question was, 

“Is there have a method to RESTORE database with using T-SQL?, but you didn’t have any idea about how many data files or file groups available in given backup file.”

For my Answer was very simple,

Just open SSMS and then select database backup and change new database for your needed name and next select “Script -> New Query Editor Window” then it will generate T-SQL code for you given database backup. 

But again he says, nope I have to publish T-SQL script and it should be work any database backup. Well… well now I have understand his need and given him following T-SQL script and hope it will help others as well. 

USE master
GO
SET NOCOUNT ON

DECLARE @BackupPath varchar(1000)='E:\Backups\Database_user_group_19JUNE2015.bak';
DECLARE @NewDBLocation varchar(1000)='E:\MSSQL\2014\DATABASE_USER_GROUP\';
DECLARE @NewDBName varchar(1000)='[DATABASE_USER_GROUP]';

-------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @tblDataFiles TABLE (LogicalName varchar(128),[PhysicalName] varchar(2000), [Type] varchar(4),
                        [FileGroupName] varchar(128), [Size] bigint, [MaxSize] bigint,
                        [FileId] smallint, [CreateLSN] varchar(128), [DropLSN] varchar(128), [UniqueId] varchar(128),
                        [ReadOnlyLSN] varchar(128), [ReadWriteLSN] varchar(128), [BackupSizeInBytes] varchar(128),
                        [SourceBlockSize] varchar(128), [FileGroupId] smallint, [LogGroupGUID] varchar(128),
                        [DifferentialBaseLSN] varchar(128), [DifferentialBaseGUID] varchar(128),
                        [IsReadOnly] bit, [IsPresent] bit, [TDEThumbprint] varchar(128));


INSERT INTO @tblDataFiles
EXEC('RESTORE FILELISTONLY FROM DISK=''' +@BackupPath+ '''');

--SELECT * FROM @tblDataFiles;

DECLARE @maxFileId smallint=0;
SELECT @maxFileId=MAX([FileId]) FROM @tblDataFiles;

DECLARE @ssql    nvarchar(max)='';
SELECT @ssql=@ssql+CASE WHEN [FileId]=1 THEN 'RESTORE DATABASE '+@NewDBName+'  FROM  DISK =N'''+@BackupPath+''' WITH  FILE = 1,  ' ELSE '' END +CHAR(13)+CHAR(10)+
'MOVE N'''+LogicalName+''' TO N'''+@NewDBLocation+LogicalName+CASE [FileId]    WHEN 1 THEN '_'+CAST([FileId] as varchar(4))+'.mdf'
                WHEN 2 THEN '_'+CAST([FileId] as varchar(4))+'.ldf'
                ELSE '_'+CAST([FileId] as varchar(4))+'.ndf' END+''','+CASE WHEN [FileId]=@maxFileId THEN '  NOUNLOAD,  STATS = 5;' ELSE '' END
FROM @tblDataFiles ORDER BY [FileId];         

PRINT @ssql;
EXEC (@ssql);
GO

Cheers...
and Most welcome your comments and ideas...