February 6, 2011 at 4:31 am
Hi, all
how to use one .ndf file in database. If i newly created one .ndf file in primary filegroup , how the database will use this secondary data file.Is it a automated process or manually we need to move. PLease help
February 6, 2011 at 5:33 am
If it's in the primary filegroup, SQL will automatically use it. It will spread data across all files in a filegroup.
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 6, 2011 at 10:43 pm
Thanx gila for your reply. But then what if i want to split my data around dirrerent data. should i create separate .mdf file or .ndf file.
for example if i have one .mdf file in certain database and i want my data file should not exceed say 4 GB because many times we need to burn the data files into DVDs. In that case should i use a .mdf data file or .ndf data file . Please help
February 6, 2011 at 10:52 pm
The naming of the file is utterly irrelevant. You could call the data files .myDataFile and SQL wouldn't care.
If you want SQL to automatically spread the data among the files, create them all in the same filegroup. If you want to be able to explicitly say which file a specific table goes into, use separate filegroups.
p.s. burning to DVD is a pretty poor reason to create multiple files. On larger databases that will result in a huge number of files and could cause problems. Architect your database files for the database requirements, for HA/DR requirements. To burn to DVD you can always take a backup and use RAR's ability to split compressed files into specific sizes
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 7, 2011 at 7:10 am
I am curious, though. Why would anyone burn data files on DVD? There are more reliable and much better storage options available that allow for bigger files. These options will save your time and they are usually much more secure than a pile of DVDs.
I also hope you're not copying these files onto DVD while the database is online. If so, you've just wasted a whole lot of time and money as nothing you've burned is usable in any format.
February 7, 2011 at 7:43 am
Burning data to DVDs is somewhat irrelevant. That's a file copy operation, and it can be a good way to transfer data. Since you cannot burn from SQL Server, the idea of linking this with separate files for data doesn't make sense.
As Gail mentioned, you want to architect separate filegroups (or files) for specific reasons. If you are considering archiving out, or sending specific data in specific tables to someone, then you could create a filegroup backup and then move THAT backup to DVD. Or you could export the data using SSIS/BCP to some other format and back that up. However a backup needs the full backup to restore, along with log backups, so be careful about what you do here.
Is this for an archive? Or transfer to some other machine/application?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply