• You can query sys.databases to see if the database exists.

    If so, then you'd have to query sys.database_files in that database to get the file location. You can do that like this:

    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

    The problem is that you might have multiple files. You'll have at least 2, but you could have more. It's not necessarily simple to get all these and construct a restore statement.

    If it doesn't exist, then you just restore. You don't need the default path to do the restore.

    However the thing you do need to do is read the backup file to determine how many files you need. A RESTORE FILELIST ONLY will do this, with the backup path. From there, you can reconstruct the WITH MOVE commands. It's not hard to do, but it takes a little work. This script can help there:

    http://www.sqlservercentral.com/scripts/T-SQL/63271/