[Webinar] Streamline your web hosting managementRegister Today


Upgrading SQL Server 2005 to SQL Server 2008

Posted on 2010-03-31
Medium Priority
Last Modified: 2012-05-09
We are trying to upgrade SQL Server 2005 to SQL Server 2008 which is an in-place upgrade. We have a database which is in OFFLINE mode. Can someone tell me whether we need to change the status of this database to ONLINE before we start the upgrade or it will be upgraded without any issues eventhough it is in OFFLINE mode or it will be upgraded only when the database is made ONLINE after the upgrade?
Question by:Srinivas_Vengala
  • 3
  • 2

Accepted Solution

blandyuk earned 1125 total points
ID: 29211756
Shouldn't make any difference as the databases will still be in compatibility 90 (SQL 2005) after the upgrade anyway. To change it after, simply right-click DB, "Properties", goto "Options" and change compatibility to 10 (SQl 2008).

Author Comment

ID: 29214682
But for the other user databases which are ONLINE, when the upgrade runs, it upgrades those databases to SQL Server 2008 though the compatibility level is still 90 after the upgrade. It records the following in the SQL Error LOG.

Converting database 'Test1' from version 611 to the current version 655.

It will not record this for the database which is OFFLINE. Instead it records the below message.

Cannot open database [Test2]. The upgrade of replication security meta-data could not be performed. Run sp_vupgrade_replsecurity_metadata again from the [master] database when the [Test2] is accessible.

After upgrading the SQL Server ,when I changed the OFFLINE database to ONLINE and compatibility level to 100, it is not recording anything about the upgrading that database (like converting the database...).

Any further information on this highly appreciated.

Assisted Solution

blandyuk earned 1125 total points
ID: 29215674
If you are worried about this database, simply bring it online and restrict acces to "SINGLE_USER" mode. Then preform your update.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions


Author Comment

ID: 29217020
I am not worried about a single database. But my question is, will the OFFLINE database be upgraded when it is made ONLINE (after the SQL Server upgrade) without any further steps or does it require some special attention?

Assisted Solution

blandyuk earned 1125 total points
ID: 29219340
I have attached a database from an SQL Server 2005 server instance to a SQL Server 2008 instance with no issues what-so-ever. On this basis, I doubt you'll have any issues BUT having never performed an upgrade on an SQL server where a database if OFFLINE, (mainly because I'd bring them ONLINE as part of the upgrade), I don't know.

Assisted Solution

dethbylt earned 375 total points
ID: 29334451
Not necessarily.  You could:
1. Backup the database.
2. Save the data files to a different location.
3. Detach the database prior to the upgrade.
4. Upgrade to SQL 2008.
5. Restore or attach it to the SQL 2008 server, post-upgrade.

If you have problems, you could bring the db online and then put it in single user mode (like bland said).  When it is in single user mode, it is fully functional for administrative purposes...

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

613 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question