How can I find out the location of my (localdb) SQL Server 2012 database and back it up?

Question

I am using VS2012 and I have a database created:

(localdb)v11.0 (SQL Server 11.0.2100 - T61Alan)

How can I find out the physical location of this database. How can I back this up? Can I just make a copy of the files, move these to another location and start the database again.

Here is my connection string:

<add name="DB1Context" connectionString="Data Source=(LocalDb)v11.0;Initial Catalog=DB1;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />

question from:https://stackoverflow.com/questions/18202383/how-can-i-find-out-the-location-of-my-localdb-sql-server-2012-database-and-bac

Answer 1

Try this one -

DECLARE 
      @SQL NVARCHAR(1000)
    , @DB_NAME NVARCHAR(100) = 'AdventureWorks2008R2'

SELECT TOP 1 @SQL = '
    BACKUP DATABASE [' + @DB_NAME + '] 
    TO DISK = ''' + REPLACE(mf.physical_name, '.mdf', '.bak') + ''''
FROM sys.master_files mf
WHERE mf.[type] = 0
    AND mf.database_id = DB_ID(@DB_NAME)

PRINT @SQL
EXEC sys.sp_executesql @SQL

Output -

BACKUP DATABASE [AdventureWorks2008R2] 
TO DISK = 'D:DATABASESQL2012AdventureWorks2008R2.bak'