I would like to write an automation script that restores a SQL Server DB from its backup file. However, doing this is not a straightforward procedure in SQL because the main query needs additional inputs which can actually be obtained using another query. Can I do this in one query?
There is a question already on SO; but the solution is not very flexible. RESTORE FILELISTONLY
's definition changes very frequently. Even otherwise, the solution seems very verbose.
Isn't there a simpler way to store the results of a query into a variable and use them? This is a cakewalk in any programming language.
Get the Logical Name:
RESTORE FILELISTONLY FROM DISK = 'D:SourceBackUpFile.bak' GO
Restore the DB:
RESTORE DATABASE DBName FROM DISK = 'D:SourceBackUpFile.bak' WITH RECOVERY MOVE 'SourceMDFLogicalName' TO 'D:TargetMDFFile.mdf', MOVE 'SourceLDFLogicalName' TO 'D:TargetLDFFile.ldf'
Best Answer
I wrote a blog post at SQLServerScience.com that shows how to obtain the details you're after, and is compatible with all versions of SQL Server since 2008+
This is the main code from that blog post:
The generated
RESTORE DATABASE
command looks like:This code has also been tested on the Linux version of SQL Server 2017.
You asked:
The requirement here is not adding a value to a variable. We need to extract the contents of a set of disparate data into a table. It might be conceptually similar to loading an array from an object. However, in SQL Server the only way you can store the results of command output like the
RESTORE HEADERONLY
command, is to first insert it into a table, then grab the specific values from the desired table.