Restore the database without the partition file

Question

I am currently trying to restore my old sql server database, but I don't have the partition file. (I set up the partition in a different drive but didn't actually use it. The main mdf is 10GB and should contain most of the data.) )

If I try:

USE master;

CREATE DATABASE MY_DB
    ON  ( FILENAME = N'D:\DATA\MY_DB.mdf' )
    FOR ATTACH_FORCE_REBUILD_LOG;
GO

The Sql server would complain

Unable to open the physical file "D:\MY_DB\TablePartitionFile\GranularityPartitionFile1.ndf". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 5120, Level 16, State 5, Line 11
Unable to open the physical file "D:\MY_DB\TablePartitionFile\GranularityPartitionFile2.ndf". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 5120, Level 16, State 5, Line 11

I even try to create an empty partition file to trick the sql server, still failed.

If there a way to only use the main mdf to restore the database without the partition file?

Answer 1

You can do it, if you have the primary file (MDF) and the log (LDF).

Create a new database with the same file structure of the old one, including a NDF and a log file:

CREATE DATABASE MY_DB ON
primary               (name='MY_DB', FILENAME = N'D:\DATA\MY_DB.mdf' ),
filegroup partition01 (name='Partition01', filename='D:\DATA\GranularityPartitionFile1.ndf')
log on                (name='MY_DB_log', filename='D:\DATA\example.ldf')

Then set the NDF file offline, and after that, set the database offline

alter database MY_DB modify file(name='Partition01',offline)
alter database MY_DB set offline

Now copy the original primary and log files over the ones you've just created.

Then turn the database back online.

alter database MY_DB set online
select * from MY_DB .information_schema.tables

And now you can access the data, to copy it somewhere else.