Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (6.1k points)

I am using the VS2012 and I have created a database:

(localdb)\v11.0 (SQL Server 11.0.2100 - T61\Alan)

How to get the physical location of this database. How can I back this up? Can I simply make a copy of the files, move these to a different location and start the database again.

Following is my connection string:

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

1 Answer

0 votes
by (12.7k points)

You can try the following code:

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:\DATABASE\SQL2012\AdventureWorks2008R2.bak'

Interested to Learn SQL in detail? Come and Join the SQL Certification course by Intellipaat.

You can check out the below SQL Tutorial video for better understanding.

Related questions

Browse Categories

...