2

I have a database called Admin. It has only one default filegroup called primary.

File group: primary
files in the file group: Admin_data.mdf

Now I have taken a full filegroup backup.

Backup file name: fg_full.bak

Then created a new ndf in the same file group called sec_file.ndf and created few tables, inserted 1000 rows on each table.

After an hour took a file backup of my MDF and NDF files

MDF backup: Admin_Data.bak
NDF backup: sec_file_ndf.bak

File Backup using SSMS enter image description here

Here somehow my sec_file get deleted so I want to restore latest NDF backup, how can I restore it?

Output of restore headeronly

enter image description here

Restore filelistonly from disk = 'D:\sec_file_ndf.bak'

enter image description here

  • After the backup. SQL Server was running during the file backup – Bhuvanesh Aug 23 '17 at 9:37
  • @Randolph West >>> Also, when you say you took a file backup, was SQL Server running when this took place?<<< Sorry, but how could SQL Server take a file backup if it was not running? – sepupic Aug 23 '17 at 9:43
  • @Randolph West, of course it's possible to do a file backup. Certainly ONLY if SQL Server service IS RUNNING. Because if the service is stopped it just cannot do anything – sepupic Aug 23 '17 at 9:46
  • @Randolph West, docs.microsoft.com/en-us/sql/relational-databases/… – sepupic Aug 23 '17 at 9:48
  • @Randolph - Question updated with snapshot of backup file – Bhuvanesh Aug 23 '17 at 9:52
1

Query the backup file with

RESTORE HEADERONLY FROM DISK = 'D:\sec_file_ndf.bak'

and

RESTORE FILELISTONLY FROM DISK= 'D:\sec_file_ndf.bak' 

...to verify the contents of your D:\sec_file_ndf.bak file.

You might have multiple backups in one *.BAK file. If so add the option FILE = 2 to your RESTORE command to restore your most recent backup of the *.NDF file.

1

Assuming that your database is in FULL recovery model (as you confirmed in comments) it's possible to restore just the second file.

The sequence is this one:

  • backup log admin to disk = '...\tailofthelog.trn' with norecovery;

  • restore database admin file = 'sec_file' from disk = '...' with norecovery;

  • restore database admin from disk = '...\tailofthelog.trn' with recovery;

UPDATE

Here is my repro code:

if db_id('test') is not null drop database test;
go

CREATE DATABASE test ON  PRIMARY 
( NAME = N'prime1', 
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test.mdf' , 
  SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) 
 LOG ON 
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test_log.ldf' , 
SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%);
go

alter database test set recovery FULL;
go

backup database test filegroup = 'PRIMARY' 
to disk = 'Z:\backups\default\test_primaryFG1.bak';
go

alter database test add file
( NAME = N'prime2', 
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test_prime2.ndf' , 
  SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB );
go 

-- second connection:
use test;
go  
create table dbo.t(id int);
go

-- first connection:  
backup database test file = 'prime1' 
to disk = 'Z:\backups\default\test_prime1.bak';
go

-- second connection:
create table dbo.t1(id int);
insert into dbo.t1(id) values(1);
go


-- first connection:
backup database test file = 'prime2' 
to disk = 'Z:\backups\default\test_prime2.bak'

-----------------------------------
backup log test 
to disk = 'Z:\backups\default\test_tail.trn' with norecovery;

restore database test file = 'prime2'
from disk = 'Z:\backups\default\test_prime2.bak'
with norecovery;

restore database test
from disk = 'Z:\backups\default\test_tail.trn' with recovery;

-----------------------------------
-- just another restore starting with FD restore as OP did:
restore database test 
from disk = 'Z:\backups\default\test_primaryFG1.bak' with replace, norecovery;

restore database test file = 'prime2' 
from disk = 'Z:\backups\default\test_prime2.bak' with norecovery


restore database test
from disk = 'Z:\backups\default\test_tail.trn' with recovery;
  • Thanks for your answer, In my case database, is in FULL recovery mode only, and yes I didn't take any tail backup. So my question is if I took a tail backup then How can I restore the sec_file alone? other files are fine. – Bhuvanesh Aug 23 '17 at 10:32
  • I need time to verify it because in your particular case the file is a part of PRIMARY FG. I'm afraid the minimum unit to be restored in case of PRIMARY FG is the whole group, not just 1 file – sepupic Aug 23 '17 at 10:37
  • it's just a Testing database. So im ready to run any queries – Bhuvanesh Aug 23 '17 at 10:39
  • ok, I've tested it right now and I'll update my answer – sepupic Aug 23 '17 at 12:16
  • 1
    If you want I can just update my answer with my code that I executed successfully on my server. – sepupic Aug 23 '17 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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