CODEWITHSUNDEEP
sql-serverdatabase
user2726975
user2726975

Reputation: 1353

Multiple .mdf files for SQL Server database

I created a database with 3 .mdf files and one .ndf file and a log file by mistake.

CREATE DATABASE [Test] ON  PRIMARY 
( NAME = N'Test_Data01', FILENAME = N'C:\MSSQLDSS\PROD01\TestDB\Test_Data01.mdf' , SIZE = 16724800KB , MAXSIZE = 30818304KB , FILEGROWTH = 524288KB ), 
( NAME = N'Test_Data02', FILENAME = N'C:\MSSQLDSS\PROD01\TestDB\Test_Data02.mdf' , SIZE = 15676224KB , MAXSIZE = 30818304KB , FILEGROWTH = 524288KB ), 
( NAME = N'Test_Data03', FILENAME = N'C:\MSSQLDSS\PROD01\TestDB\Test_Data03.mdf' , SIZE = 15781120KB , MAXSIZE = 20578304KB , FILEGROWTH = 524288KB ), 
( NAME = N'Test_Data04', FILENAME = N'C:\MSSQLDSS\PROD01\TestDB\Test_Data04.ndf' , SIZE = 16305408KB , MAXSIZE = 20578304KB , FILEGROWTH = 524288KB )
 LOG ON 
( NAME = N'Test_Log01', FILENAME = N'C:\MSSQLDSS\PROD01\TestDB\Test_Log01.ldf' , SIZE = 544704KB , MAXSIZE = 68706304KB , FILEGROWTH = 262144KB )
GO

I created many tables and realised my mistake. Is it possible to update the database to have only one .mdf and change the other 2 .mdf files to .ndf?

Thanks

Upvotes: 0

Views: 996

Answers (2)

Thom A
Thom A

Reputation: 95924

Alternatively, to the Method Dan shows, you can detach the database, rename, and then reattach with the correct names. This is for my environment (which is running on Linux), but you get the idea:

Initial Create:

CREATE DATABASE [Test] ON PRIMARY 
( NAME = N'Test_Data01', FILENAME = N'/mnt/WDBlue/Test_Data01.mdf' , SIZE = 16724800KB , MAXSIZE = 30818304KB , FILEGROWTH = 524288KB ), 
( NAME = N'Test_Data02', FILENAME = N'/mnt/WDBlue/Test_Data02.mdf' , SIZE = 15676224KB , MAXSIZE = 30818304KB , FILEGROWTH = 524288KB ), 
( NAME = N'Test_Data03', FILENAME = N'/mnt/WDBlue/Test_Data03.mdf' , SIZE = 15781120KB , MAXSIZE = 20578304KB , FILEGROWTH = 524288KB ), 
( NAME = N'Test_Data04', FILENAME = N'/mnt/WDBlue/Test_Data04.ndf' , SIZE = 16305408KB , MAXSIZE = 20578304KB , FILEGROWTH = 524288KB )
 LOG ON 
( NAME = N'Test_Log01', FILENAME = N'/mnt/WDBlue/Test_Log01.ldf' , SIZE = 544704KB , MAXSIZE = 68706304KB , FILEGROWTH = 262144KB )
GO

Detach database:

ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

EXEC sys.sp_detach_db N'Test';

Rename files:

mv /mnt/WDBlue/Test_Data02.mdf /mnt/WDBlue/Test_Data02.ndf
mv /mnt/WDBlue/Test_Data03.mdf /mnt/WDBlue/Test_Data03.ndf

Reattach with new file names:

CREATE DATABASE [Test]
    ON PRIMARY (NAME = N'Test_Data01', FILENAME = N'/mnt/WDBlue/Test_Data01.mdf'), 
               (NAME = N'Test_Data02', FILENAME = N'/mnt/WDBlue/Test_Data02.ndf'), 
               (NAME = N'Test_Data03', FILENAME = N'/mnt/WDBlue/Test_Data03.ndf'), 
               (NAME = N'Test_Data04', FILENAME = N'/mnt/WDBlue/Test_Data04.ndf')
    LOG ON (NAME = N'Test_Log01', FILENAME = N'/mnt/WDBlue/Test_Log01.ldf')
    FOR ATTACH;

Upvotes: 0

Dan Guzman
Dan Guzman

Reputation: 46261

You can rename database files by following the same procedure as moving database files to a new location.

Execute ALTER DATABASE...MODFY FILE for each file:

USE master;

ALTER DATABASE Test 
    MODIFY FILE (NAME = N'Test_Data02', FILENAME = N'C:\MSSQLDSS\PROD01\TestDB\Test_Data02.ndf');
ALTER DATABASE Test 
    MODIFY FILE (NAME = N'Test_Data03', FILENAME = N'C:\MSSQLDSS\PROD01\TestDB\Test_Data03.ndf');
ALTER DATABASE Test 
    MODIFY FILE (NAME = N'Test_Data04', FILENAME = N'C:\MSSQLDSS\PROD01\TestDB\Test_Data04.ndf');
GO

Set database OFFLINE (requires exclusive database access):

ALTER DATABASE Test SET OFFLINE;
GO

Rename physical files with extension ndf.

Set database ONLINE:

ALTER DATABASE Test SET ONLINE;
GO

GO

Upvotes: 2

Related Questions