0

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?

2
  • 3
    If you were doing an actual restore then it would create the file for you, but since you are trying to attach an existing MDF file, you need all the files (and to specify all of them) Oct 28, 2021 at 14:18
  • Did you use a different filegroup for the GranularityPartitionFilen.ndf files ? If not the round robin fill algorithm means that SQL Server will have been putting data there anyway. Even if you were using a different filegroup I don't think you will be able to restore / attach the database, but it might be easier for someone to recover the data Oct 28, 2021 at 14:43

1 Answer 1

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.