I ran across someone that was building a restore script to automated their restores. This person wanted their script to work with any instance, and that means they’d need to find the path for the database files, if the database already existed.

It was interesting to me, and I decided to give a solution a try, and I ended up using dynamic SQL, which I don’t love, but it worked. As I was digging through, I realized that all the database data is in sys.master_files. However I’d started this and it was an intriguing problem. I don’t love this solution, and I wouldn’t use it here, but there might be a place where you can use it.

To find out if a database exists, you can easily use the sys.databases view to find it.

USE master
GO
DECLARE @path VARCHAR(500)
   , @db VARCHAR(200)
;
SELECT @db = 'AdventureWorks2008'
IF EXISTS (SELECT name
         FROM sys.databases
 WHERE name = @db
 )

This is a snippet, and you need more code for this to work, but it does work if you include the “then” and “else” blocks. If the database doesn’t exist, you can grab the default file paths from the registry if you want to build the restore script, but if it does, then what.

You want to find the database files, but these aren’t stored in master. If you query sys.database_files, you’ll get this:

I have 8 or 10 databases on this instance, but none appear. However if I query a specific database, I get the files.

How do I get this data, in a script, given that I can’t execute a “use” statement easily at runtime.

There are probably a few ways, but for me, I decided dynamic SQL might make sense her. This is an administrative task, so it’s not likely to allow for SQL Injection as I wouldn’t expose this for users to run.

The first step is to build my query. In this case, I want to execute this query:

 select physical_name
  from AdventureWorks2008.sys.database_files
   where file_id = 1

This isn’t ideal, in that I could have many files for this database, but for now I’m concerned with just getting the primary data file.

I can build this string dynamically like this. Note that I’ve assigned the result to a variable for now.

DECLARE @sqlCommand nvarchar(1000)
DECLARE @db varchar(75)
DECLARE @file VARCHAR(500)
SET @db = 'AdventureWorks2008'
SET @sqlCommand = 'select @f = physical_name from ' + @db + '.sys.database_files where file_id = 1'
SElect @sqlCommand

However now I need to run this command and return a value. sp_executesql is a function that allows you to execute a string, pass in parameters, and assign them back. I can do this with this script.

DECLARE @sqlCommand nvarchar(1000)
DECLARE @db varchar(75)
DECLARE @file VARCHAR(500)
SET @db = 'AdventureWorks2008'
SET @sqlCommand = 'select @f = physical_name from ' + @db + '.sys.database_files where file_id = 1'
EXECUTE sp_executesql @sqlCommand, N'@f varchar(500) OUTPUT', @f=@file OUTPUT
select @file

If I run this, I’ll get the file path from the AdventureWorks2008 primary data file.

That’s the first step in this process. If I wanted to complete it, I’d have to make sure I did this for each data file, probably using some temporary table instead of a variable, and storing all the physical paths and logical names, and using those to build a dynamic restore script.

Or I could download this script: SQL 2005 Restore Script Generator

Filed under: Blog Tagged: syndicated, T-SQL