CODEWITHSUNDEEP
sql-server
Alan2
Alan2

Reputation: 24572

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

I am using VS2012 and I have a database created:

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

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" />

Upvotes: 52

Views: 126007

Answers (9)

Vinko
Vinko

Reputation: 1

Switch to SSMS or Azure Studio and then do the following:

SSMS: right-click the database and click properties. enter image description here Once there, click Files and observe the information under Path (if you can't see it, expand the window or click in the path cell and CTRL A + CTRL C, and then CTRL P into a text editor, Word, ... It should look something like this:

C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA (If you back up your database, your Backup folder will be in the MSSQL folder together with the DATA folder.)

Azure Studio: right-click the database and click properties. enter image description here Once there, click Files and observe the information under Path (if you can't see it, expand the column or hover over the cell to see the information).

Upvotes: 0

Paul McLean
Paul McLean

Reputation: 3560

In SQL Server Management Studio, Right click on DataBase -> Properties -> Files will tell you where on your hard disk it lives. If you backup the mdf, be sure to back up the ldf too.

enter image description here

Alternatively, you can right click on the DB, and choose Tasks -> Backup. This will make a a single .bak file for you, and you don't need to worry about the mdf/ldf.

Upvotes: 38

cytek04
cytek04

Reputation: 596

I tried everything here and could not find them anywhere. I finally found them by searching *.mdf in file explorer. They were in C:\Users\user\source\repos\CallNote\App_Data.

Upvotes: 0

code4j
code4j

Reputation: 4656

It is quite confusing for people who touch with Entity Framework the first time.

If you use Code First, an mdf file is generated at %USERPROFILE% (e.g. C:\Users\<username>).

If you use Database First, you create a database under SQL Server Object Explorer (not Server Explorer!), an mdf file will be generated at %LOCALAPPDATA%\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB.

Upvotes: 58

Hung Vu
Hung Vu

Reputation: 5924

By default, LocalDB database creates “*.mdf” files in the C:/Users/"username" directory.

Link ref: https://docs.asp.net/en/latest/tutorials/first-mvc-app/working-with-sql.html

Upvotes: 52

Jon List
Jon List

Reputation: 1512

This PowerShell script, will give you the default location for localdb .mdf files:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
(New-Object Microsoft.SqlServer.Management.Smo.Server("(localdb)\$instancename")).DefaultFile

where $instancename is the name of the localdb instance you want to check for. You can get a list of localdb instances by running

sqllocaldb i

Upvotes: 3

John K
John K

Reputation: 28869

Open Windows registry editor and navigate to key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server Local DB\Installed Versions. Look beneath the version key of the SQL Server instance being targeted e.g. 11.0 for SQL 2012, and see its InstanceAPIPath value for file system location of the localdb's.

Note that at full list of SQL server versions mapped to release name and year can be found here

Upvotes: 3

Devart
Devart

Reputation: 121952

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

Upvotes: 13

Jonathan
Jonathan

Reputation: 5993

http://technet.microsoft.com/en-us/library/hh510202.aspx

The system database files for the database are stored in the users' local AppData path which is normally hidden. For example C:\Users\--user--\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\LocalDBApp1. User database files are stored where the user designates, typically somewhere in the C:\Users\\Documents\ folder.

Upvotes: 15

Related Questions