August 29, 2008 at 9:15 am
We've got about 8 production databases on one of our servers. Our system administrator is interested in moving our SQL Server databases to another server. I've created the databases using last weekend's full backups. Now we can test our applications for database connectivity. However, I don't want to go through that process again. Now that I've got the databases on the new server, what can I use to get the current data off of the current production database, onto the new database server? It has been suggested that we use replication. Will that work? I've never used replication before.
Rod (a neophyte DBA - really I'm a programmer who has to do the DBA tasks)
Kindest Regards, Rod Connect with me on LinkedIn.
August 29, 2008 at 9:47 am
Setting up replication to move a database server is very painful and not something I would recommend. I would however state that using backup / restore is probably the safest and most likely the fastest way to go. A couple options as follows;
1. Backup and restore as mentioned above and as you have already done for testing.
2. Stop the one instance (MSSQL services) on the old server and then move the database files across the network to the new server and then attach them using the right click attach method or sp_attach_db method.
Both of these are pretty straightforward and low risk.
Make sure you have BACKUPS before you do anything please. I can't stress this enough lately. I swear I have responded to at least 4 posts over the last couple of days where people have had corruption or lost databases and no backups. Don't get caught like that please.
Also, make sure that you script out permissions on the old server and apply them on the new server prior to restoring or attaching the databases. Search this site for script examples (sp_help_revlogin).
As always, please post if you have specific questions.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 29, 2008 at 11:37 am
David, it sounds like, given what you've said, I would be better off either dettaching the files and attachning them after I've copied them to the new server, or doing a full backup of all of the databases and restoring to the new server.
Thank you.
Kindest Regards, Rod Connect with me on LinkedIn.
August 29, 2008 at 11:48 am
If you wanted to do it automatically, you could do either log shipping or mirroring.
In you situation, I think log shipping would be easier to implement, but I think both would work far better than replication.
Hope that helps.
August 29, 2008 at 1:25 pm
I vote for Logshipping !
* Noel
August 29, 2008 at 1:47 pm
Not trying to be argumentative but by the time you set up log shipping, work out all the kinks, and then convert that server to be the online server, you might as well have stopped the old instance, copied the files and attached them to the new server. Don't forget you still have to do a full backup / restore on the new server in order to get log shipping functioning. What have you gained.
Sorry, I like things simple, and clean.
Obviously this is a matter of preference but the less you have to do the better off you will be. So, back to your response to my earlier post, I would say yes, either of those choices would work well.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 29, 2008 at 1:51 pm
No offense to you Rod, but my point exactly. No need to learn "new to you" technology to accomplish your goal, and ultimately it really won't buy you anything.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 29, 2008 at 1:57 pm
Log shipping is a high availablity method. Essentially, you continually run log backup at specified intervals on you primary server and then recover those logs automatically on the secondary server.
As David said above, you will have to do an intial backup and then restore to start it, but once that is done, and you work out the kinks, you just automatically recover the log backup to the secondary server and you know have all of your data.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply