Creating new database with existing mdf only mdf file

A common scenario!

To create a new database with an existing MDF file, you can use the following methods:

Method 1: Using SQL Server Management Studio (SSMS)

  1. Open SSMS and connect to the instance where you want to create the new database.
  2. Right-click on the "Databases" folder in the Object Explorer and select "New Database".
  3. In the "New Database" dialog box, enter a name for the new database and select "Attach" as the database creation method.
  4. Browse to the location of the existing MDF file and select it.
  5. Click "OK" to create the new database.

Method 2: Using T-SQL

  1. Open a new query window in SSMS and connect to the instance where you want to create the new database.
  2. Run the following T-SQL command:
    CREATE DATABASE <new_database_name> ON (NAME = '<existing_mdf_file_name>', FILENAME = '<path_to_existing_mdf_file>');

    Replace <new_database_name> with the name you want to give to the new database, <existing_mdf_file_name> with the name of the existing MDF file, and <path_to_existing_mdf_file> with the path to the existing MDF file.

For example:

CREATE DATABASE MyNewDB ON (NAME = 'MyExistingDB', FILENAME = 'C:\Path\To\MyExistingDB.mdf');

Method 3: Using the sqlcmd command-line tool

  1. Open a command prompt and navigate to the directory where you want to create the new database.
  2. Run the following command:
    sqlcmd -S <instance_name> -Q "CREATE DATABASE <new_database_name> ON (NAME = '<existing_mdf_file_name>', FILENAME = '<path_to_existing_mdf_file>');"

    Replace <instance_name> with the name of the SQL Server instance, <new_database_name> with the name you want to give to the new database, <existing_mdf_file_name> with the name of the existing MDF file, and <path_to_existing_mdf_file> with the path to the existing MDF file.

For example:

sqlcmd -S MyInstance -Q "CREATE DATABASE MyNewDB ON (NAME = 'MyExistingDB', FILENAME = 'C:\Path\To\MyExistingDB.mdf');"

After running one of these methods, the new database will be created with the existing MDF file as its primary data file.