An idiots guide to moving from sql server 2005 to sql server express 2012?

  • mike 91138

    Grasshopper

    Points: 16

    Apologies if I'm asking in the wrong place and let me firstly say I know next to nothing about SQL server!

    My situation is this: I have a transport system running on an old 2003 SBS - this sever is pretty much on its last legs and the business has no budget for replacing the server. Our software provider wants to charge us 10k to move to the lastest and greatest version of the software but its an outlay we just cannot justify. The current software whilst old works perfectly well for us. I have purchased a new desktop computer and installed SQL server Express 2012 and now need to move all the databases from the old server to the new one. I was hoping, very naively, that I would just be able to backup the databases and restore them in the new software, change the local machine ODBC's to point to the new server and everything would work. Obviously that hasn't happened and there is much more work involved but I am now out of my depth.

    So I guess my question is - is there an idiots guide that can guide me through the process? Any assistance/guidance would be greatly appreciated.

  • HanShi

    SSC-Dedicated

    Points: 33142

    Copying the databases using the SQL backup and restore method should work from SQL2005 to SQL2012. I expect the only thing to take into account during the restore is the location of the datafiles. That can be specified in the restore command. A basic restore command looks like:
    RESTORE DATABASE my_database FROM DISK = 'Z:\Backup\my_database.bak'
     WITH FILE = 1, REPLACE
     , MOVE 'my_database_datafile' TO 'D:\database\my_database.mdf'
     , MOVE 'my_database_log' TO 'D:\database\my_database_log.ldf'

    When migrating to the Express version of SQL take into account the restrictions of the version. A database within SQL2012 Express is limited to 10GB. Other limitations of the Express version can be found here

    When migrating a database to a higher version I always have used the steps below. This will set and update the database to the current compatibility level of the server. Changing the compatibility also implies depricated features could stop working. So please test your application and check your code if it uses such depricated features.

    /********************************************************************/
    -- commands to upgrade the compatibility level of a database
    -- to the current SQL instance level
    --
    /********************************************************************/

    --==========================================================================================
    --
    -- change the text "databasename" in the code below to the actual database name
    --
    --==========================================================================================

    -- step 1: change recovery level and page_verify setting
    ALTER DATABASE [databasename] SET RECOVERY SIMPLE, PAGE_VERIFY CHECKSUM

    -- step 2: change compatability level to the level of the instance itself (taken from [master])
    USE [databasename]
    DECLARE @cmpt_level INT
    SELECT @cmpt_level = compatibility_level FROM sys.databases WHERE name = 'master'
    EXEC dbo.sp_dbcmptlevel @dbname=N'databasename', @new_cmptlevel=@cmpt_level

    -- step 3: remove existing statistics
    USE [databasename]
        DECLARE @ObjectName sysname
        DECLARE @StatsName sysname
        DECLARE StatsCursor CURSOR FAST_FORWARD
        FOR
        SELECT
        '['+OBJECT_NAME(object_id)+']' as 'ObjectName',
        '['+[name]+']' as 'StatsName'
        FROM sys.stats
        WHERE
        (INDEXPROPERTY(object_id, [name], 'IsAutoStatistics') = 1
        OR INDEXPROPERTY(object_id, [name], 'IsStatistics') = 1)
        AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
        OPEN StatsCursor
        FETCH NEXT FROM StatsCursor
        INTO @ObjectName, @StatsName
        WHILE @@FETCH_STATUS = 0
        BEGIN
        EXEC ('DROP STATISTICS ' + @ObjectName + '.' + @StatsName)
        FETCH NEXT FROM StatsCursor
        INTO @ObjectName, @StatsName
        END
        CLOSE StatsCursor
        DEALLOCATE StatsCursor

    -- step 4: update usage information in the database
    DBCC UPDATEUSAGE ('databasename') WITH NO_INFOMSGS, COUNT_ROWS

    -- step 5: correct settings regarding STATISTICS
    ALTER DATABASE [databasename] SET AUTO_CREATE_STATISTICS ON
    ALTER DATABASE [databasename] SET AUTO_UPDATE_STATISTICS ON

    -- step 6: list and map users to corresponding logins
    exec sp_change_users_login 'report'
    /*
    -- use the command below to correct listed issues
    exec sp_change_users_login 'auto_fix', '{reported_user}'
    */

    -- step 7: check consistentie of the users with the logins
    --   (in case the loginnaam is empty, the mapping isn't correct)
    exec sp_helpuser
    /*
    -- remove any unwanted mapping between user and login and regrant the desired permissions
    DROP USER [username]
    exec sp_dropalias 'username'
    */

    -- step 8 (optional): change the recovery model to FULL
    -- REMARK: create a new full backup after executing the step below
    ALTER DATABASE [databasename] SET RECOVERY FULL

    -- step 9 (optional): change the database owner (alter the name "sa" below to the desired accountname)
    alter authorization on database::[databasename] TO [sa]

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • sgmunson

    SSC Guru

    Points: 110416

    One other thing to take into consideration is that I'm not sure that SQL Server 2012 Express Edition is going to be properly licensed if you have more than one user.   When SBS was purchased, it included a SQL Server license for some number of users, and I'm doubtful you'd be in compliance with MSFT software licensing by using an Express version of SQL Server.   Not sure a sizable cost is avoidable, even if you don't use the vendor to acquire it, and go directly to MSFT instead.

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • Sue_H

    SSC Guru

    Points: 89718

    sgmunson - Monday, April 30, 2018 11:02 AM

    One other thing to take into consideration is that I'm not sure that SQL Server 2012 Express Edition is going to be properly licensed if you have more than one user.   When SBS was purchased, it included a SQL Server license for some number of users, and I'm doubtful you'd be in compliance with MSFT software licensing by using an Express version of SQL Server.   Not sure a sizable cost is avoidable, even if you don't use the vendor to acquire it, and go directly to MSFT instead.

    It's not limited to 1 user though. Express doesn't have the same limitations as msde did. In theory both have always had the same limitation as all editions which is 32,767.
    MSDE had that workload governor that would fire based on 5 concurrent operations (or workload of 5, something like that) but they got rid of that and the limitations are in hardware, database size.

    Sue

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714080

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32322

    Also think about how you will schedule your native backups, if required.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714080

    There are some additions to Express and always the Windows scheduler, but good point, Beatrix

  • sgmunson

    SSC Guru

    Points: 110416

    Sue_H - Monday, April 30, 2018 12:26 PM

    sgmunson - Monday, April 30, 2018 11:02 AM

    One other thing to take into consideration is that I'm not sure that SQL Server 2012 Express Edition is going to be properly licensed if you have more than one user.   When SBS was purchased, it included a SQL Server license for some number of users, and I'm doubtful you'd be in compliance with MSFT software licensing by using an Express version of SQL Server.   Not sure a sizable cost is avoidable, even if you don't use the vendor to acquire it, and go directly to MSFT instead.

    It's not limited to 1 user though. Express doesn't have the same limitations as msde did. In theory both have always had the same limitation as all editions which is 32,767.
    MSDE had that workload governor that would fire based on 5 concurrent operations (or workload of 5, something like that) but they got rid of that and the limitations are in hardware, database size.

    Sue

    Oh...  for whatever reason, I thought Express was a single-user thing just like Developer Edition, from a licensing perspective where keeping it free or low cost is concerned.  My bad...

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

Viewing 8 posts - 1 through 8 (of 8 total)

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