1

I am hacking around trying to write a script to restore a backup from a known folder location. The location will only ever contain one backup file. I have come up with this so far, but I don't know how to get the value from the temp table into the restore database command.

CREATE TABLE #tempFileTable (FName VARCHAR(8000),Depth INTEGER, Files INTEGER)

INSERT INTO #tempFileTable
EXEC xp_dirtree 'S:\', 1, 1

SELECT TOP 1 FName FROM #tempFileTable

DROP TABLE #tempFileTable

DECLARE @DBName varchar(255) = 'DatabaseName'

RESTORE DATABASE @DBName FROM  DISK = ??? WITH  FILE = 1

GO
0

2 Answers 2

0

May be you can try this:

CREATE TABLE #tempFileTable (FName VARCHAR(8000),Depth INTEGER, Files INTEGER)

INSERT INTO #tempFileTable
EXEC xp_dirtree 'D:\Test1', 1, 1

SELECT TOP 1 Fname FROM #tempFileTable;

DECLARE @DBName varchar(255) = 'Test'

select 'RESTORE DATABASE ' + @DBName + ' FROM  DISK = D:\Test1\' + fname +  ' WITH  FILE = 1' 
from #tempFileTable

DROP TABLE #tempFileTable

GO

Output:

RESTORE DATABASE Test FROM  DISK = D:\Test1\ReportServer.bak WITH  FILE = 1
0

Maybe you could try something like :

declare @file varchar(50)
declare @DBName varchar(50)
declare @sql varchar(200)

CREATE TABLE #tempFileTable (FName VARCHAR(8000),Depth INTEGER, Files INTEGER)

INSERT INTO #tempFileTable
EXEC xp_dirtree 'D:\', 1, 1

select @file = FName
from #tempFileTable

set @DBName = 'MyDB'

set @sql = 'RESTORE DATABASE '+@DBName+' FROM  DISK = ''D:\'+@file+''' WITH  FILE = 1'

print @sql
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.