February 19, 2014 at 7:45 am
I'm working with a vendor who has designed a customer database. Couldn't help but notice it has both mdf and ndf files. Got to thinking, does a full backup of a database via SSMS backup both mdf and ndf files into 1 bak file? The main reason for the question, I ran a full backup of the database, it looks as if only the mdf is backed up (bak size 4.6 GB, but ndf 22 GB and mdf 3.7 GB don't match).
That also brings to mind the restore, do you reference both mdf and ndf via script? I've been googling this subject and will continue but am looking for insights. I'm more concerned about the backup right now.
Any comments / urls would be appreciated - thanks.
February 19, 2014 at 7:51 am
A Full database backup is just that, a backup of the full database, all data files.
When restoring, unless you need to move the files to a new location it's just RESTORE DATABASE <db name> FROM DISK = <backup location>. If you do need to move the files, then you treat the secondary data files exactly the same way as the primary data file
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2014 at 8:23 am
Backup usually takes less space, and significantly less if it's a compressed backup.
When restoring via SSMS you should see all the .mdf and .ndf files. You have to specify locations for them.
Regards,
Igor
Igor Micev,
My blog: www.igormicev.com
February 19, 2014 at 8:43 am
Thank you guys - much appreciated.
February 19, 2014 at 10:33 am
You can always look at the backup files themselves to understand the structure of the database that was backed up. I have an article on that on Simple-Talk[/url].
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply