SQL Server Upgrade to 2k8 R2

(since we do this kind of thing sooo regularly … )

Preparation:

Update server (OS & SQL to latest SP – not ‘necessary but appears to be a common practice*)
Db -> single user mode
(user) Db -> full backups
Db -> script users/permissions/jobs et. al.
Record the account under which each service runs
Update engine
(should not have to) Restore user Db’s
(should not have to) Restore users and permissions, jobs et. al. ‘should’ still be there
Test …

* Adam – 2k8 R2 from 2k5 requires 2k5 SP2 or greater

Errors & causes: “The database cannot be opened because it is version 661. This server supports version 662 and earlier. A downgrade path is not supported.”
 Resolution is supposed to be to set the engine instance to the correct sp level but even when I tried all three (RTM, 1, 2) it did not work.

“cannot create db … “
 Check permission on target ldf & mdf dirs for sql server user

“process cannot access *.dll”
 Use “Process Explorer” to find who has a lock on the dll and kill the PID. MOM is one of the likely culprits




From: Kelly, Joe
Sent: Tuesday, September 27, 2011 12:35 PM
To: Joe, Adam; Kelly, Joe
Subject: FYI - Error Attaching Db's

Per our con, this is the error that I was receiving when trying to reattach the db’s:

“The database cannot be opened because it is version 661. This server supports version 662 and earlier. A downgrade path is not supported.”

Resolution is supposed to be to set the engine instance to the correct sp level but even when I tried all three (RTM, 1, 2) it did not work.

Lesson learned:

Db -> single user mode
(user) Db -> full backups
Db -> script users/permissions/jobs et. al.
Update engine
Restore user Db’s
Restore users and permissions, jobs et. al. ‘should’ still be there