February 26, 2008 at 4:46 pm
I have a database that are approx 800 GB, and all data are store in one file abc.MDF (forget the logfile).
I want to create a backup and then restore the database again (under another dbname), and I want to split the file into two or more:
1) abc.MDF
2) ABC1.NDF
3) ABC2.NDF
(and so on)
How can I split the database into these AND how can I reorganize the files, so that they are equally sized (approx 200-250 gb. pr. file).
Best regards
Søren,
MCITP SQL 2008 (administration / programming), MCITP SQL 2005 (BI), MCT
February 26, 2008 at 4:51 pm
You can't do that with a restore.
A restore will create files that are exactly the same size as when the backup was run.
February 26, 2008 at 4:58 pm
Do you know any way I can do it?
Søren,
MCITP SQL 2008 (administration / programming), MCITP SQL 2005 (BI), MCT
February 26, 2008 at 7:39 pm
There are a number of considerations:
- number of secondary files
- use of filegroups
- placement of files on physical disks
- placement of data within the files (e.g. splitting indexes from tables, splitting historical data)
- backup strategies (full, differential, combinations)
Start with why you are splitting the files. Is it because of performance or because the single data file is too damn big (of course, one can lead to the other, but anyway...)?
The way you split the data up can depend on what edition of SQL Server you have. Enterprise Edition supports table partitioning, useful for splitting old, infrequently accessed data from newer data. Otherwise, you will just have to go through your tables & indexes and decide which ones to put on what files/filegroups. This could be based on frequency of access, size, performance etc. Don't forget to allow for growth as well.
It's a big job & there's no quick & dirty way to do it.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 27, 2008 at 11:40 am
As mentioned by the previous user you can't do that wiwth a restore. You need to create a database, filegroups in it and move them one by one. This should be simple one.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 27, 2008 at 11:57 am
Hi thx, for your reply, but can you give me a hint, how it should be done?
My problem is that I have this very large DB, and I want to put in on multiple discs (don't ask me why :-). Each disk is 200 GB, and my database is 4 times bigger. I therefore want to split this 800 GB database out on 8 disc with 8 db-files (one .mdf and 7 .ndf) that is 100 gb big.
When new data is added I want to have them written on all 8 files (so that 1/8 is on disc 1, 1/8 on disc 2 and so on), so that the 8 files are approximately equally in size.
My problem is to divide the current 800 GB .MDF into 8 100 GB files.
I hope that you can help, and thx in advance.
Søren
Søren,
MCITP SQL 2008 (administration / programming), MCITP SQL 2005 (BI), MCT
February 28, 2008 at 3:41 pm
I want to create a backup and then restore the database again (under another dbname), and I want to split the file into two or more:
1) abc.MDF
2) ABC1.NDF
3) ABC2.NDF
(and so on)
You cannot do this in one step, but it is very plausible to accomplish this task. First you have to backup the database and restore it under a different name. Make sure you have enough disk space as this is 1.6 terabytes. Once the database is restored, create your filegroups. You will have to investigate how you want to divide your big tables. This will help balance the storage distribution. You will have to alter your tables to set the new filegroup.
Your next step is to add more files to the database. You can do this step from SSMS or using TSQL code. Obviously it is much easier to do this step from the GUI, especially if you are new or not comfortable using TSQL.
The tsql command:
USE [master]
GO
ALTER DATABASE [ABC]
ADD FILE ( NAME = N'ABC1', FILENAME = N'C:\ABC1.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [MyFileGroup]
GO
February 28, 2008 at 3:47 pm
Thx. for your reply. The tasks you have described is no problem. The problem is then afterwards to take the big file and split it out on more files, and do it so that all files are approx. equally sized.
Søren
Søren,
MCITP SQL 2008 (administration / programming), MCITP SQL 2005 (BI), MCT
February 28, 2008 at 4:51 pm
Create the database, create the datafiles and script the tables and place them in the filegroups and then move the data. hope this should be an easy solution for you.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 29, 2008 at 3:28 am
You need to plan this very carefully, as making any changes for a database of your size is time consuming.
One approach is to add additional files to the existing filegroup, and place these files on the various disks. You will then need to do a SHRINKFILE on the original file to redistribute your data. If you take this approach, expect the SHRINKFILE to run for many hours and result in poor end-user performance while it is running.
Having done all of this, you may find the resulting database is difficult to manage. What do you do when the files get full? If you add another disk and data file then you need to re-balance everything to get optimal performance.
If may be far better to make all your disks Dynamic volumes, and get Windows to treat them as a single drive letter. Or better still get a proper RAID configuration set up.
If this seems to beyond your skills then I suggest you persuade your manager to buy some consultancy time. This may cost 2 - 3K, but the cost to your business of getting things wrong will be a few orders of magnitude larger.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 29, 2008 at 9:14 am
Other option is that you can move your data to flat files and then move them to the databases.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply