Moving Database to new server.
-
Currently, my old server is getting obsolete and slow. Thus, an exercise to move my database over to a new server. The old database is poorly structered as well.
Files in old database:
Logical drive Physical drive size
db_data g:\db1.mdf 5GB
db_data2 h:\db2.ndf 10GB
db_data3 i:\db3.ndf 3GB
db_data4 j:\db4.ndf 10GB
I am hoping to structure up the new database with the following file structure:
Logical drive Physical drive size
db_data e:\db1.mdf 12GB
db_data2 f:\db2.ndf 10GB
db_data3 g:\db3.ndf 10GB
I have made a backup of the database and attempted restore which did not work as I realised (later) that restore place back the physical files to it's original place, failing my restore. Is there a way to restore my database to the server placing it on a new set of logical files which has a new set of physical files? Is there a work around on this? i.e reducing logical files and all?
fyi, i attempted DTS as an option to move the records from the old server to the new one. I hit a number of failures on this method, this resorted to backup and restore, which in turn cause a different set of problem.
-
We ALMOST have exactly the same problem mine is that i have 2 mdf and 2 ldf and after transferring the database it must be 1 mdf and 1 ldf DTS did the trick for me. Yes i encountered errors but those errors are just errors stating that 1-2 view,sp is not copied to the new server. So for my case 2 mdf and 2ldf to only 1 a piece DTS can do the trick
"-=Still Learning=-"
Lester Policarpio
-
To end up with data in different files, you will need to do some sort of data transfer process. Either DTS or BCP will suit your needs.
Another option is to keep the same number of files and restore the database with the MOVE option to move the data files to new locations.
--------------------
Colt 45 - the original point and click interface
-
you want to go from 4 files to 3 files, so use restore database...with move as Phill said to place 4 files on drives you want them. Then use dbcc shrinkfile(filename, emptyfile) to move the data in the 4th file to the other 3.
Then use alter database remove file to drop the 4th file
then use alter database filesize to set the 3 files to the size you want
In fact it is best to size all 3 files the same if you can, that way you get an even spread of data.
---------------------------------------------------------------------
-
thank you Lester Policarpio & philcart. looks like i have to resort to dts to solve my problem here.
-
KayAge (12/19/2007)
thank you Lester Policarpio & philcart. looks like i have to resort to dts to solve my problem here.well ok, restore method gotta be easier though.
---------------------------------------------------------------------
-
THANK YOU george sibbald!!! and merry christmas to you!! you saved my day.. and i was thinking that i need to perform dts already!! will try what you suggested after christmas and come back here to post the result!
btw, Merry Christmas to Lester Policarpio & philcart as well 🙂
-
If you want to size you data files the same across 3 drives for optimal I/O. You can create a new database with the same name as your old db with the new datafile structure. Script out all tables, views, procs, functions, create then in the new db and then use the Import/Export wizard to move the data into the new tables.
If you aren't too concerned about the datafiles be exactly the same size, then George's method is the simplest.
-
Edogg (12/19/2007)
If you want to size you data files the same across 3 drives for optimal I/O. You can create a new database with the same name as your old db with the new datafile structure. Script out all tables, views, procs, functions, create then in the new db and then use the Import/Export wizard to move the data into the new tables.If you aren't too concerned about the datafiles be exactly the same size, then George's method is the simplest.
This process is what i did when i needed to reduce from 2 mdf and 2 ldf to 1 a piece i just created a new database (when creating a new database there will be only 1 mdf and ldf) then use dts to transfer out tables, views, stored procedures, triggers, log-ins etc then i created a comparison chart to see if all are copied exactly the same. But for 3-4 mdf/ndf reducing not to 1 but to 2-3 mdf/ndf i think the MOVE command will do it although as of now i only experienced from 2 to 1. I will also test the MOVE command to see the result.
Advance Happy New Year to all SQL Server Central Members!!!!
"-=Still Learning=-"
Lester Policarpio
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply