Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
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

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

31k questions

32.8k answers

501 comments

693 users

Browse Categories

...