Create new SQL Database in different location using SSMS

When you create new SQL Server Database, both Primary data file and Log file of the database will be created under default sql server location (C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATA). Since most of the applications are using C drive location to store their required files, we can not ensure the availability of free space in future for growing database. So better idea is create new SQL Database files in a dedicated location that has more free space availability without any other disturbance. You can easily create new sql server database in different location using SQL Server Management Studio.

Steps to create new SQL Database in specified location using SSMS

1. Connect SQL Server Management Studio by entering SQL Server details.

Create new SQL Database in different location using SSMS

2. Right-click on the node Databases, and click New Database…

Create new SQL Database in different location using SSMS

3. Enter the new Database name and drag the below horizontal scroll bar into right-side direction as shown in below image.

Create new SQL Database in new location using SSMS

4. Now, you can see the column Path which holds the default locations for Primary database file and Log file.

Create new SQL Database in different location using SSMS

5. Either you can manually type the new database location for primary database file and log file or you can use browse (…) button to select the database location where you want to create new database files.

Create new SQL Database in specified location using SSMS

6. Click OK to create new sql database in the specified location. Now, you can see new database files are created under the specified location D:SQLDB instead of default sql primary storage location.

Create new SQL Server Database in different location using SSMS

Morgan,
Thanks
Software Developer

Advertisement

Leave a Comment