March 25, 2015 at 5:46 am
I am trying to figure out the best method for transferring SQL 2005 databases from 2 different physical servers to a new server running windows server 2008 64bit.
I own a fully registered copy of Microsoft SQL 2005 32bit Standard Edition. However all of my SQL databases together are only 4GB in size thereby making 2008 SQL Express an option.
Here is the breakdown of the servers:
1) (Old Server1) Windows 2003 Server 32bit - SQL Express 2005 32bit
2) (Old Server2) Windows 2003 Server 32bit - SQL Server 2005 32bit Standard
3) (New Server) Windows 2008 Server 64bit - (Not sure which version to install)
**The objective is to migrate 2 SQL databases from Old Server1 and 1 SQL database from Old Server 2 to the New Server. What version should I install on the new server??? Do I go with 2008 express or install the 2005 Standard Edition?
Pending your response to which version to install, what are the correct steps to migrate (which I have never done before) the databases from both of these servers to the new server?
I have researched this online and I think this particular situation is unique in that I am transferring from both SQL Express and Standard from 2 different physical servers to a 2008 server that is 64bit. I cannot find anything online that I feel confident in trying. Please help me, as I have this migration deadline of this weekend. Any help is appreciated. Thanks.
March 25, 2015 at 6:25 am
bradh 14842 (3/25/2015)
I am trying to figure out the best method for transferring SQL 2005 databases from 2 different physical servers to a new server running windows server 2008 64bit.I own a fully registered copy of Microsoft SQL 2005 32bit Standard Edition. However all of my SQL databases together are only 4GB in size thereby making 2008 SQL Express an option.
Here is the breakdown of the servers:
1) (Old Server1) Windows 2003 Server 32bit - SQL Express 2005 32bit
2) (Old Server2) Windows 2003 Server 32bit - SQL Server 2005 32bit Standard
3) (New Server) Windows 2008 Server 64bit - (Not sure which version to install)
**The objective is to migrate 2 SQL databases from Old Server1 and 1 SQL database from Old Server 2 to the New Server. What version should I install on the new server??? Do I go with 2008 express or install the 2005 Standard Edition?
Pending your response to which version to install, what are the correct steps to migrate (which I have never done before) the databases from both of these servers to the new server?
I have researched this online and I think this particular situation is unique in that I am transferring from both SQL Express and Standard from 2 different physical servers to a 2008 server that is 64bit. I cannot find anything online that I feel confident in trying. Please help me, as I have this migration deadline of this weekend. Any help is appreciated. Thanks.
the only recommendation i think I can say is backup and restore of the databases, and using sp_helprev_login to migrate users from the other servers to the new servers.
a full backup is an exact copy of the database, and is the fastest and most reliable way to move data.
but if a user simply changes his connection to point to the "new" server, it would fail, becuas ethe logins have to be migrated as well.
the script sp_helprev_logins will script out the logins, with encrypted passwords, for easy migrations.
there might be additional things you need to migrate from the other servers, as well.
this is my quick list of migration items, if it helps; many might not applybut it's a decent enough list:
• special objects or functions in master database? (sp_whoisactive, Ola hollgren's objects, etc)
• Script out Alerts
• Script out Operators
• Script out Jobs
• Script out SQL Agent config
• Script out Database Mail
• are there any Trace Flags DBCC TRACESTATUS
• Script out Linked Servers
Lowell
March 26, 2015 at 8:05 am
Thanks for the suggestions.
March 26, 2015 at 8:13 am
Definitely backup/restore to move the databases. The backup files aren't affected by whether the server is x86 or x64 (neither are the database files for that matter)
If you have a licensed copy of SQL 2008 R2 Standard edition, use that. While the database sizes are small enough for Express, it's limited on memory and CPU and may well have poor performance compared with the 2005 Standard edition.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply