SQL Server FAQs

SQL Server is Relational Database Management System developed by Microsoft. It mainly has these two files - an MDF file which is primary database file where all the database information is stored, and an LDF file which contains the logging informations.

Default Database File Location in SQL Server

E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\

Master Database Files(MDF) are the primary database files in SQL Server which contains the schema and data. It stores all the data in the database objects like tables, stored procedures, views, triggers etc. Every database has one MDF file which is the starting point of the database.

NDF file is secondary database file which works with SQL server and basically stored on a different location other than MDF file. We can say that it is user-defined database file as it can be used to save data in case if MDF file is getting large or disk space is not available on same location where the MDF file is saved. In this case we stored the data in NDF file which will be associated with MDF file and file name will be same as MDF as lib.mdf and lib.ndf.

Yes, you can restore your database backup file (.bak) to a different database by providing a new database name. Further, it is possible to change the Data and Transaction log file location as desired.

Use the following script,

RESTORE DATABASE
MyDB
FROM DISK = '\\MyShare\MyBackup.bak'
WITH
MOVE 'DataFile' TO 'D:\myNewDBLocation\DataFile.mdf',
MOVE 'LogFile' TO 'E:\\myNewDBLocation\LogFile.ldf', REPLACE