SQLTeam.com | Weblogs | Forums

Move SQL 2005 databases to SQL 2012


#1

Hey all, I'm a non-SQL guy who is in charge of what few SQL databases we have here at work. One of them is running SQL 2005 and I'm going to move it over to SQL 2012. I've read a couple of times that all I have to do is backup the database from 2005 and restore it in 2012. That sounds way too easy. Is that really just two steps and done???

Thanks,

Joe B


#2

SQL Server has very good tools to help you upgrade. Start with the upgrade advisor which will analyze your database and installed components to detect incompatibilities or deprecated features that may cause problems after upgrade.


#3

Hi JamesK and thanks for the link. I'm in a similar situation as the first "comment" in that I'm going to be moving from SQL 2005 on Server 2003 to SQL 2012 on a much more recent Windows server (in my case Server 2012R2).

And of course another gotcha I found out about -- I have HUGE transaction log files. A couple of examples:
DB1Size=298MB TranLogSize=8308MB
DB2Size=155MB TranLogSize=7205MB
DB3Size=149MB TranLogSize=6098MB
DB4Size=393MB TranLogSize=5413MB

I'm guessing I need to shrink these down before doing any moving across boundaries.

Thanks,

Joe B


#4

You don't have to shrink them down in order to move the databases, but you can. I would check why they grew to that size though. What is the recovery model of those databases? If it's not SIMPLE, then how often are log backups are occurring? I'm guessing that those 4 databases are set to FULL with NO log backups. That would explain the log sizes.