May 28, 2009 at 12:02 pm
I have a database with 20 tables and a total of 1.2 billion rows of data. I need to move all of these tables to a new SQL server. The problem is that the current server and the new server are not connected (nor can they be) so I need to manual transfer the data via external hard drive.
I know I could simply export each table into flat text files and then move those. However, I was hoping someone would know a better way. Is there a way I can export the entire database as a single file and import that? Or at least a way that I can automate the exporting of the tables without having to select each one individually? I don't need to move the indexes, SPs, log files, etc and there are no foreign key relations to maintain. I only need to move the data and table structures. Thanks
May 28, 2009 at 12:14 pm
I would take a full backup of "source" database,
move the dump file to an external disk drive,
ship the external drive to "target" location,
plug the external disk drive on "target" server,
restore backup of "source" database in "target" server.
Don't forget to script logins and jobs on source server then apply those scripts on target server.
We just did that, it worked like a charm. 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 28, 2009 at 12:14 pm
Can you backup to and restore from the external hard drive? If so, that seems like the safest and least painful way of doing this.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 28, 2009 at 1:09 pm
David Benoit (5/28/2009)
Can you backup to and restore from the external hard drive? If so, that seems like the safest and least painful way of doing this.
:w00t: flashback!
Brilliant post! mmhhh... I'm almost certain I've already heard that in some place... Oops... was it one post on top of yours? 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 28, 2009 at 1:30 pm
Okay. A followup question then: when I go to restore the files to the new server can I change where the tables are stored (which file group)? Right now, all of the data is in one file on a SAN (Primary file group). On the new server, we want to spread the tables out across the SAN. Is it possible to move tables to different file groups?
Thanks
May 28, 2009 at 2:48 pm
Yes!
Here is how you do it.
1- Pre-create the database in your "target" server -so you make all decisions about storage.
2- Force-restore over the pre-created database -so datafiles go to the new locations.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 28, 2009 at 3:04 pm
PaulB (5/28/2009)
David Benoit (5/28/2009)
Can you backup to and restore from the external hard drive? If so, that seems like the safest and least painful way of doing this.:w00t: flashback!
Brilliant post! mmhhh... I'm almost certain I've already heard that in some place... Oops... was it one post on top of yours? 😀
Yeah, I'm thinking if the site flagged milliseconds for the post time it would have been really close. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 28, 2009 at 4:26 pm
PaulB (5/28/2009)
Yes!Here is how you do it.
1- Pre-create the database in your "target" server -so you make all decisions about storage.
2- Force-restore over the pre-created database -so datafiles go to the new locations.
Sorry - this is not correct. Restoring a database from a backup uses whatever the backup has stored for the files. If the original database has one data file and one log file - that is what will be restored, regardless of the storage layout in a pre-created database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2009 at 4:51 pm
Jeffrey Williams (5/28/2009)
PaulB (5/28/2009)
Yes!Here is how you do it.
1- Pre-create the database in your "target" server -so you make all decisions about storage.
2- Force-restore over the pre-created database -so datafiles go to the new locations.
Sorry - this is not correct. Restoring a database from a backup uses whatever the backup has stored for the files. If the original database has one data file and one log file - that is what will be restored, regardless of the storage layout in a pre-created database.
Correct, you will need to use the "WITH MOVE" syntax in the restore options. Check it out in BOL and post back with questions.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 28, 2009 at 5:09 pm
PaulB (5/28/2009)
David Benoit (5/28/2009)
Can you backup to and restore from the external hard drive? If so, that seems like the safest and least painful way of doing this.:w00t: flashback!
Brilliant post! mmhhh... I'm almost certain I've already heard that in some place... Oops... was it one post on top of yours? 😀
Hey Paul... give David a break here. He posted at the same time as you did.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply