April 14, 2015 at 10:01 am
Hi,
I hope I'm in the right forum. Apologies if I'm not. Okay - here is my problem. Can you help me please? I want to transfer everything from a Sql Server 2005 R2 Enterprise to a Sql Server 2012 R2 Standard. That means databases, data, permissions, users, indexes (if possible), constraints (if possible), logins etc...
The database servers are on separate boxes.
I believe the best way to do this is with log shipping? Is that correct? Do I initiate the log-shipping process from the source or the destination? Can anyone help me please? Any comments/suggestions/user-experiences are much appreciated.
Thanking you,
J.
April 27, 2015 at 9:12 am
You would be better off backing up the databases on the 2005 box and restoring to the new box.
script out the logins from the old box to create on the new. You will then need to fix orphaned users, ie re link server logins with db logins.
script out any jobs that need copying over.
Be mindful of going from enterprise to standard, it may be the case that you are using enterprise features. you will find out when you attempt to restore the databases to the new server.
This will be fine as long as you can get sufficient downtime to accommodate the time taken for all of the backups and restores.
If not, you can restore a full backup to the new server with no recovery, do this before you are going live. then top it of with a smaller differential backup. this will dramatically reduce the time taken.
April 27, 2015 at 9:27 am
Yeah, I'd do backup/copy/restore too if possible. It can be/feel safer than doing an in-place upgrade.
Use sp_help_revlogin to script out the logins as mentioned above.
Have a read of this: http://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/
And this has a section specifically for upgrading from 2005: https://msdn.microsoft.com/en-us/library/ms143393.aspx (
April 27, 2015 at 9:41 am
+1 for backup/restore
Main thing here is to test, test, test. I know it might be obvious, but you need to point your applications (as a test) to the test upgrade and see what works or doesn't. This will help you find holes.
Off the top of my head, these items need to be manually scripted and moved from source box(es) to the new one.
- logins
- linked servers
- jobs
- packages
- server certificates
- DDL triggers for the server
- server audits
- replication, perhaps dist dbs
- might have issues with security for any of these items
The DBs should move, and if you have the correct logins moved, likely no orphans, but need to check.
Lastly, run the upgrade advisor on each db. Make sure that you aren't going to get into issues there. MIght need to reduce compat level for databases.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply