Compatibility level change--permanent or temporary changes to db?

  • Does changing the compatibility level to 90 make permanent changes to the database, or would any changes be reversed when/if the CL were changed back to 80? If permanent, is there a list somewhere of the changes that I could use to compare both versions to see what needs to be tested?

    We have a purchased application that came with a SQL database. The original installation was to SQL2000. We have since migrated the database to SQL2005, with compatibility level 80 (SQL Server 2000).

    IIRC, we simply detached/reattached the database to migrate it, but I could be wrong. I can dig up my notes on that if needed.

    I'd like to change this to CL 90 (SQL Server 2005) for some back-end reporting I'm doing.

    I've asked the vendor, who gave their standard answer of (more or less) "it should work, but test it because it's not our problem if it breaks".

    I can make a test copy of the database and ask the users to test it. However, I'm afraid that we'd still miss something (there's lots of functionality that is only used once in while).

    If the changes aren't permanent, then I could switch it back if we ran into any problems (ideal). If they are permanent, it would help a great deal if I knew where to focus the testing.

    Thanks much for any info.

  • Create a dev/test copy of your database, change the compatibility level to 90, then run this on it:

    use [database] -- Please remember to fill this in!!

    go

    dbcc UPDATEUSAGE (0) WITH NO_INFOMSGS;

    go

    DBCC CHECKDB (0) with no_infomsgs;

    go

    -- Update all table stats

    declare Tables cursor local fast_forward

    for

    select name

    from sys.all_objects

    where type = 'u'

    declare @Table varchar(100), @SQL varchar(1000)

    open tables

    fetch next from tables into @table

    while @@fetch_status = 0

    begin

    select @sql = 'update statistics dbo.' + @table + ' with fullscan'

    exec (@sql)

    --raiserror(@table, 10, 1) with nowait

    fetch next from tables into @table

    end

    close tables

    deallocate tables;

    go

    DECLARE @sql VARCHAR(max),

    @Text VARCHAR(max),

    @ProcName VARCHAR(500),

    @ProcName1 VARCHAR(500)

    DECLARE @T TABLE

    (ProcName VARCHAR(200),

    sql VARCHAR(max),

    ErrorMessage VARCHAR(4000))

    DECLARE c Cursor FOR

    select name, definition

    from sys.all_objects

    inner join sys.sql_modules

    on all_objects.object_id = sql_modules.object_id

    where type in ('p','tf','if')

    and name not like 'dt_%'

    and name not like 'sys_%'

    Open C

    FETCH NEXT FROM c INTO @ProcName, @Text

    WHILE @@FETCH_STATUS = 0 BEGIN

    SET @text = REPLACE(@text, @ProcName, @ProcName + 'CreateTest') -- change proc name

    BEGIN TRY

    EXEC(@text) -- try to create the proc

    INSERT @T values (@ProcName, @text, ERROR_MESSAGE()) -- record procs that could be created

    END TRY

    BEGIN CATCH

    INSERT @T values (@ProcName, @text, ERROR_MESSAGE()) -- record procs that couldn't be created

    END CATCH

    if exists

    (select *

    from sys.all_objects

    where name like '%' + @procname + 'createtest'

    and type = 'p')

    begin

    set @sql = 'drop procedure ' +

    (select name

    from sys.all_objects

    where name like '%' + @procname + 'createtest'

    and type = 'p')

    exec(@sql)

    end

    if exists

    (select *

    from sys.all_objects

    where name like '%' + @procname + 'createtest'

    and type in ('if','tf'))

    begin

    set @sql = 'drop function ' +

    (select name

    from sys.all_objects

    where name like '%' + @procname + 'createtest'

    and type in ('if','tf'))

    exec(@sql)

    end

    FETCH NEXT FROM c INTO @ProcName, @Text

    END

    CLOSE c

    DEALLOCATE c

    SELECT * FROM @T where errormessage is not null

    order by procname

    go

    That will handle the main maintenance tasks you need to do, which includes updating all table statistics. It will also test each proc and function in the database to make sure it can compile under 2005. There are some things in 2000 that won't compile in 2005, and this will give you a list of any you have. It's all easy stuff to fix, most of the time, but it's important to know.

    That will allow you to create a script to make any needed changes.

    Once you change to 90, you cannot change back to 80. You can restore a backup made before the compatibility change, and that will be 80 (of course), but that means any data changes made, code changes, etc., that were after that backup, will be lost.

    I've upgraded a number of databases, and with that script, it's pretty easy and quite fast. Couple of hours for the largest one I did (and that thing was a mess written by people who should never be allowed near T-SQL).

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Bijal Parekh, thanks for the link. I did a search for old threads, I swear!

    GSquared - tremendous! That's exactly what I need. I'd read that procs could be a problem but hadn't been able to find details of what to check.

    Thanks very much!

  • hi grasshopper,

    I am still new to SQL 2005 but I try and help as much as i can. It was just an attempt to help you.. 🙂

  • Oh, I appreciate the help -- I just felt silly that I hadn't already found that thread. 🙂

    Thanks!

  • Some time ago I had the same challenge and wrote a script for this.

    Take a look at http://www.sqlservercentral.com/scripts/Compatibility/62093/

    Note that If the problem is inside of a dynamic SQL code, it won't be detected.

    Good luck,

    Luiz.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply