July 5, 2012 at 1:46 pm
Hi,
I am moving a 75GB database from 2005 to 2012. The actual database has only 1 data files and 1 log file. I would like to restore the database on SQL2012 where the database is configure with 6 data files and 3 log files.
Is there a way to achieve that?
Thanks
July 5, 2012 at 1:57 pm
No. A restore will recreate the database exactly as it was at time of backup. You'll have to split the files later.
Why do you want more than one log file? Multiple data files, sire, but there's little point in multiple log files.
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
July 5, 2012 at 2:04 pm
Thanks,
As of now, we have a log file of 30GB. I was thinking of creating 3 of 10GB, for better performance. This is not a good idea?
When I will do the restore, only 1 data file will be created. When done, I will create the new datafiles. Is there a way to balance the data between the datafiles?
THanks again.
July 5, 2012 at 2:21 pm
Rem70Rem (7/5/2012)
As of now, we have a log file of 30GB. I was thinking of creating 3 of 10GB, for better performance. This is not a good idea?
No. The log is used serially, not in parallel. So if you create 3, SQL will use them one at a time.
When I will do the restore, only 1 data file will be created. When done, I will create the new datafiles. Is there a way to balance the data between the datafiles?
Best way is to create multiple filegroups then decide which tables you want on which filegroups. How you do that depends on the reason for splitting the database into multiple files and will need quite a bit of thought, investigation and design before you make any changes.
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
July 5, 2012 at 2:37 pm
I see. For the log, the best is to keep only 1 file, even if it's a 30+GB file?
For the data file, first step will be to restore the database as is. The data files of the new database will resident on a SAN. I was told by our admin that by creating multiple data files, I will have a better performance, is it the case? Should I keep only 1 huge file?
I was thinking of making a logical split of the tables by types (staging tables vs reporting tables). So might create 2 filesgroups. I am not to familiar with the filegroup architecture. Everything will be transparent for users? At the creation of the tables and/or indexes I will need to specified the filegroup right?
I will be able to backup my database the same way I am doing it now?
Mucha gracia.
July 5, 2012 at 3:21 pm
Rem70Rem (7/5/2012)
I see. For the log, the best is to keep only 1 file, even if it's a 30+GB file?
Sure. I've had 200GB+ log files before. There's no performance advantage in splitting log files.
For the data file, first step will be to restore the database as is. The data files of the new database will resident on a SAN. I was told by our admin that by creating multiple data files, I will have a better performance, is it the case?
Not automatically, no. It's possible, but improving performance by splitting files requires analysis (where the IO load is, whether the application is IO bottlenecked), etc) and careful design
Everything will be transparent for users? At the creation of the tables and/or indexes I will need to specified the filegroup right?
I will be able to backup my database the same way I am doing it now?
Yes, yes and yes.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply