January 19, 2014 at 6:25 am
Hi,
I have become a bit stuck with this script I am building, any help would be appreciated.
DECLARE @Date nvarchar (100),
@SQL nvarchar (4000),
@SQL2 nvarchar (4000)
Set @Date = 'IRR_HBDE_PRD'+'_db_' + convert(varchar, getdate(), 102)
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL DROP TABLE #DirectoryTree;
CREATE TABLE #DirectoryTree (id int IDENTITY(1,1),subdirectory nvarchar(512),depth int,isfile bit);
INSERT #DirectoryTree (subdirectory,depth,isfile)EXEC master.sys.xp_dirtree 'O:\MSSQL10_50.LIVE910MSSQL\MSSQL\Backup\SQLRestores\IRR_HBDE_PRD',1,1;
set @SQL = 'SELECT subdirectory FROM #DirectoryTree WHERE isfile = 1 AND RIGHT(subdirectory,4) = ''.BAK''
AND subdirectory LIKE '''+@Date+'%'''
PRINT @SQL
SET @SQL2 = 'RESTORE DATABASE [IRR_HBDE_PRD1] FROM DISK = N''O:\MSSQL10_50.LIVE910MSSQL\MSSQL\Backup\SQLRestores\IRR_HBDE_PRD\'+@SQL+''' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10'
PRINT @SQL2
The output I get is below but the part in bold should be the backup file name, how can I correct this?:
SELECT subdirectory FROM #DirectoryTree WHERE isfile = 1 AND RIGHT(subdirectory,4) = '.BAK'
AND subdirectory LIKE 'IRR_HBDE_PRD_db_2014.01.19%'
RESTORE DATABASE [IRR_HBDE_PRD1] FROM DISK = N'O:\MSSQL10_50.LIVE910MSSQL\MSSQL\Backup\SQLRestores\IRR_HBDE_PRD\SELECT subdirectory FROM #DirectoryTree WHERE isfile = 1 AND RIGHT(subdirectory,4) = '.BAK'
AND subdirectory LIKE 'IRR_HBDE_PRD_db_2014.01.19%'' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
January 19, 2014 at 6:42 am
Bit hard for me to check if this works, but what you're looking to do is populate a variable with the returned result from the query. At the moment you're just populating the variable with the statement
DECLARE @Date nvarchar (100),
@SQL nvarchar (4000),
@SQL2 nvarchar (4000)
Set @Date = 'IRR_HBDE_PRD'+'_db_' + convert(varchar, getdate(), 102)
--Add wildcard to end of variable
SELECT @Date = RTRIM(@Date) + '%';
SELECT @Date;
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL DROP TABLE #DirectoryTree;
CREATE TABLE #DirectoryTree (id int IDENTITY(1,1),subdirectory nvarchar(512),depth int,isfile bit);
INSERT #DirectoryTree (subdirectory,depth,isfile)EXEC master.sys.xp_dirtree 'O:\MSSQL10_50.LIVE910MSSQL\MSSQL\Backup\SQLRestores\IRR_HBDE_PRD',1,1;
--set @SQL = 'SELECT subdirectory FROM #DirectoryTree WHERE isfile = 1 AND RIGHT(subdirectory,4) = ''.BAK''
--AND subdirectory LIKE '''+@Date+'%'''
--Populate variable with result not statement
set @sql = (SELECT subdirectory FROM #DirectoryTree WHERE isfile = 1 AND RIGHT(subdirectory,4) = '.BAK'
AND subdirectory LIKE @Date)
PRINT @SQL
SET @SQL2 = 'RESTORE DATABASE [IRR_HBDE_PRD1] FROM DISK = N''O:\MSSQL10_50.LIVE910MSSQL\MSSQL\Backup\SQLRestores\IRR_HBDE_PRD\'+@SQL+''' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10'
PRINT @SQL2
This get you roughly where you want?
January 19, 2014 at 6:51 am
you my friend are a hero!
For some reason I couldn't get my head around it.
Thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply