Steve Jones - SSC Editor (5/8/2013)
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:
Thanks Steve...
I'm looking for all the files..i got the script here...
declare @db varchar(100)
set @db = 'test_restore'
select physical_name from sys.master_files where DB_NAME(database_id) = @db
but we need to frame something like this....
check for database , if it exists execute above code else get the default path( we alreayd have script for this )
Fyi, we dont do sql restore directly..we use commvault ( third party tool ) ..because of this we dont have much flexibility and we cannot see the backup path...
please let me know your thoughts
thanks in advance..