TB Database on SQL Server

Question

TB Database on SQL Server

Viking-204480

SSC Eights!

Points: 970
More actions
July 27, 2005 at 12:31 am

#168083

We have a 600 GB database sitting on a production box which has performance issues. We were planning to move this database to a cluster instance (Active /Active) with better hardware conifgurations (higher RAM, higher CPU, etc..). This cluster instance would be connected to a SAN.
Here are my points:
(1) My colleague wanted to restore this database from a .BAK file of 600 GB size, onto a new database having better placement of files in different filegroups. I advised him, this is not possible where you can restore a database having just a single file in a single filegroup to a new database having multiple files and filegroups?
Just need to check with you all in the forum, was I right or is there something I should know to make my colleagues point possible.
(2) If, the above restore is not possible per my advise, I propose restoring the backup file of 600 GB in one of the new instance of the cluster, create datafiles, filegroups, move tables, move indexes, images & text datatypes into respective filegroups.
Would somebody second me on point (2), with more inputs for better execution of my plan.
Thanks!
Viking

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 1

You are correct in saying that no 1 is not possible, even if the db is pre configured, the foot print etc comes from the backup. You would need to restore the back up and the create seperate datafiles and move everything around.

I have found it is eassiest to script everything out, and then drop all my indexes, and move tables that way with the shrinkfile with the empty option to migrate the date and then recreate the uindexes.

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Viking-204480 SSC Eights! Points: 970 More actions · Answer 2

Fair, thanks for your input.

Any other thoughts from the forum?

Thanks!

Viking

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 3

I agree with Ford that the best would be 3, script the existing server, and transfer to the New server after tweaking the scripts for the desired re-organization.

I would also like to add to perform the transfer begining with the SQL Server Role/Logins, that way as each object is created it can also transfer the permissions.

I find that the INSERT tablename SELECT * FROM LinkedOldserver.database.dbo.tablename will perform the data transfer, however with 600GB of data maybe you should use BCP to transfer the data.

Andy

Viking-204480 SSC Eights! Points: 970 More actions · Answer 4

OK! That sounds great. But, to do inserts or BCP data I need to have thorough knowledge of relationships of parent & child tables in the database.

Unfortunately, I am strained with time and my management wants me to complete the job at the earliest. Even, if I convince that I need to do by BCP or any other methods what would be the shortest time I can completing the data loading.

I finally feel, that I need to restore whole database and do the re-organization. Now, can you put your thoughts how quick can I do a restore if I resort to this method?

Further inputs would be helpful!

Thanks!

Viking

ChrisMoix-87856 SSCertifiable Points: 7288 More actions · Answer 5

If you have some space left on your current server, you could add filegroups/files, and start moving tables, indexes, etc. to their new filegroups. That way, when you restore onto the new server, you can use the WITH MOVE option to place those new files onto their new drives.

Regardless, it is going to take a LONG time to restore a 600GB database. Good Luck.