SQJ Server: How do I retrieve database files paths with Transact sql?

midfde
midfde used Ask the Experts™
on
I need a T-SQL statement that returns paths of (.MDF, .LDF) files for a given database name. I need to be able to use them PROGRAMMATICALLY in
RESTORE DATABASE ... WITH MOVE ... TO <retrieved path>
I need this to prevent an automated C#, ADO.NET procedure from  throwing exception:
<SQL_ErrMessage>
'Directory lookup for the file "..." failed with the operating system error 3(The system cannot find the path specified.).'
and
"File 'such-n-such' cannot be restored..."
</SQL_ErrMessage>
because of organizational mess (somebody moved files to a new location), and aforementioned procedure fails with its hard-coded paths.

cmd = new SqlCommand("RESTORE DATABASE " + dbName + " FROM DISK='" + backupFN + "'"
                        + withMove
                        , conn);
                    cw("\n" + cmd.CommandText + "\n");
                    cmd.CommandTimeout = 60 * 60 * 3; //expected RESTORE time as ~1 hour (07/2008)
                    /****************************************************************/
                    /**/            cmd.ExecuteNonQuery();                        /**/
                    /****************************************************************/
 
This is an excerpt from the automated procedure output of today.
 
Database restore - BEGIN: 11/10/2008 4:22:35 AM
Decompressed backup [\\newmail\igor$\MondayBackup.bak] file size is 7,314,078,208
connString = "Server=pac4\sql2005;Database=tempdb;Integrated Security=SSPI;"
 
RESTORE DATABASE [ContinuumDB] FROM DISK='\\newmail\igor$\MondayBackup.bak' WITH 
move 'ContinuumDev' TO 'D:\program files\microsoft SQL server\MSSQL\data\ContinuumDev.mdf',
move 'ContinuumLog' TO 'D:\program files\microsoft SQL server\MSSQL\data\ContinuumLog.ldf'
 
Directory lookup for the file "D:\program files\microsoft SQL server\MSSQL\data\ContinuumDev.mdf" failed with the operating system error 3(The system cannot find the path specified.). 
File 'ContinuumDev' cannot be restored to 'D:\program files\microsoft SQL server\MSSQL\data\ContinuumDev.mdf'. Use WITH MOVE to identify a valid location for the file. 
Directory lookup for the file "D:\program files\microsoft SQL server\MSSQL\data\ContinuumLog.ldf" failed with the operating system error 3(The system cannot find the path specified.). 
File 'ContinuumLog' cannot be restored to 'D:\program files\microsoft SQL server\MSSQL\data\ContinuumLog.ldf'. Use WITH MOVE to identify a valid location for the file. 
Problems were identified while planning for the RESTORE statement. Previous messages provide details. 
RESTORE DATABASE is terminating abnormally.
 
Call stack for the current thread:at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) 
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) 
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) 
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) 
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) 
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) 
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() 
at CU_Restore.Program.Main(String[] args) 
 
Database restore - END: 11/10/2008 4:22:40 AM
Elapsed Time - 00:00:04 (01:22:38)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2008
Awarded 2008
Commented:
select * from sys.database_files

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial