locked
How to move SQL standard database to SQL Express RRS feed

  • Question

  • I am trying to move a samll group of databases from SQL Standard 2005 to SQL Express on a machine that is not part of the network.

    The SQL Standard is setup for Windows Authentication Only.

    When I try to restore the databases in Express I get an Access denied.

    Do I need to change the Standard to mixed mode first & set up an 'sa'? Sorry I no prior experience moving databases in SQL and this is my 1st time using Express.

    If someone can step me through this I would be muchly grateful!!
    Saturday, December 19, 2009 5:00 PM

Answers

  • Are you trying to move the database by backup / restore or detach / attach option ?

    Do you have enough permissions on express instance to create a new database (The account with which you are trying to restore / attach should have sysadmin role or db_creator role at the server level) ?

    1. Backup db from standard edition.
    2. Move the backup files to express instance.
    3. restore the backup file with a login that has enough permissions
    4 . Now fix the mismatch logins / users using the stored proc sp_change_users_login

    NOTABLE POINT: Express only supports db upto 4 gb , therefore you cannot move the databases that are greater in size (more than 4gb to an expres edition instance )
    Thanks, Leks
    • Marked as answer by MarshC Saturday, December 19, 2009 10:25 PM
    Saturday, December 19, 2009 5:10 PM

All replies

  • Are you trying to move the database by backup / restore or detach / attach option ?

    Do you have enough permissions on express instance to create a new database (The account with which you are trying to restore / attach should have sysadmin role or db_creator role at the server level) ?

    1. Backup db from standard edition.
    2. Move the backup files to express instance.
    3. restore the backup file with a login that has enough permissions
    4 . Now fix the mismatch logins / users using the stored proc sp_change_users_login

    NOTABLE POINT: Express only supports db upto 4 gb , therefore you cannot move the databases that are greater in size (more than 4gb to an expres edition instance )
    Thanks, Leks
    • Marked as answer by MarshC Saturday, December 19, 2009 10:25 PM
    Saturday, December 19, 2009 5:10 PM
  • Leks,

    Thank you for your help.

    The expess login acct has sysadmin.
    Largest database is 2gb.

    The steps I am performing using Mngt Studio are:

    Restore Files & Filegoroups

    To Database: DbName (does not exist)
    From device, file, add DbName.bak

    Restore: Checked Full but not the Trans log (have tried both with and without the log)

    Pop-up tells me I can not restore the DbName_log  ????

    Thank you
    Marsh

    Saturday, December 19, 2009 6:59 PM
  • Check that the account that your sql server express runs under has read/write permissions on the folder(s) you are trying to restore that sql server backup to, and that the location is different to the current location, of course.


    ajmer dhariwal || eraofdata.com
    Saturday, December 19, 2009 9:40 PM
    Answerer
  • Check whether the backup is a valid backup (not a corrupted one) using restore verifyonly option of sql server . Google for restore verifyonly and you will find documentation for it. ALso make sure you are satisfying the point mentioned by Ajmer.
    Thanks, Leks
    Saturday, December 19, 2009 9:57 PM
  • Checked the permissions, they were good. Still no luck using backup/restore.

    BUT, you were helpfull just the same. I ended up using the Detach/Attach method you mentioned in your original response. It worked!!

    Thank you very much.
    Marsh
    Saturday, December 19, 2009 10:25 PM
  • To create the users you could try the blog from Wayne Sheffield search for sp_help_revlogin (script below) it creates a sp on the source server (master db) when you run this command it creates a new script that you run at the destination server where it will create the dbusers you need.

    IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
      DROP PROCEDURE sp_hexadecimal
    GO
    CREATE PROCEDURE sp_hexadecimal
        @binvalue VARBINARY(256),
        @hexvalue VARCHAR (514) OUTPUT
    AS
    /*******************************************************************************
    Cloned from http://support.microsoft.com/kb/918992
    ********************************************************************************
                                    MODIFICATION LOG
    ********************************************************************************
    2012-10-17 WGS Initial creation.
    *******************************************************************************/
    DECLARE @charvalue VARCHAR (514)
    DECLARE @i INT
    DECLARE @length INT
    DECLARE @hexstring CHAR(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
    WHILE (@i <= @length)
    BEGIN
      DECLARE @tempint INT
      DECLARE @firstint INT
      DECLARE @secondint INT
      SELECT @tempint = CONVERT(INT, SUBSTRING(@binvalue,@i,1))
      SELECT @firstint = FLOOR(@tempint/16)
      SELECT @secondint = @tempint - (@firstint*16)
      SELECT @charvalue = @charvalue +
        SUBSTRING(@hexstring, @firstint+1, 1) +
        SUBSTRING(@hexstring, @secondint+1, 1)
      SELECT @i = @i + 1
    END
     
    SELECT @hexvalue = @charvalue
    GO
     
    IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
      DROP PROCEDURE sp_help_revlogin
    GO
    CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
    /*******************************************************************************
    Cloned from http://support.microsoft.com/kb/918992
    ********************************************************************************
                                    MODIFICATION LOG
    ********************************************************************************
    2012-10-17 WGS Added check for login and default database existing before creating.
    *******************************************************************************/
    DECLARE @name sysname
    DECLARE @type VARCHAR (1)
    DECLARE @hasaccess INT
    DECLARE @denylogin INT
    DECLARE @is_disabled INT
    DECLARE @PWD_varbinary  VARBINARY (256)
    DECLARE @PWD_string  VARCHAR (514)
    DECLARE @SID_varbinary VARBINARY (85)
    DECLARE @SID_string VARCHAR (514)
    DECLARE @tmpstr  VARCHAR (1024)
    DECLARE @is_policy_checked VARCHAR (3)
    DECLARE @is_expiration_checked VARCHAR (3)
     
    DECLARE @defaultdb sysname
     
    IF (@login_name IS NULL)
      DECLARE login_curs CURSOR FOR
     
          SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
    sys.server_principals p LEFT JOIN sys.syslogins l
          ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
    ELSE
      DECLARE login_curs CURSOR FOR
     
          SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
    sys.server_principals p LEFT JOIN sys.syslogins l
          ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
    OPEN login_curs
     
    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
    IF (@@FETCH_STATUS = -1)
    BEGIN
      PRINT 'No login(s) found.'
      CLOSE login_curs
      DEALLOCATE login_curs
      RETURN -1
    END
    SET @tmpstr = '/* sp_help_revlogin script '
    PRINT @tmpstr
    SET @tmpstr = '** Generated ' + CONVERT (VARCHAR, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
    PRINT @tmpstr
    PRINT ''
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      IF (@@FETCH_STATUS <> -2)
      BEGIN
        PRINT ''
        SET @tmpstr = '-- Login: ' + @name
        PRINT @tmpstr
        IF (@type IN ( 'G', 'U'))
        BEGIN -- NT authenticated account/group
     
          SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' 
          FROM WINDOWS 
          WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
        END
        ELSE BEGIN -- SQL Server authentication
            -- obtain password and sid
                SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS VARBINARY (256) )
            EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
            EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
     
            -- obtain password policy state
            SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
            SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
     
                SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' 
                WITH PASSWORD = ' + @PWD_string + ' HASHED, 
                     SID = ' + @SID_string + ', 
                     DEFAULT_DATABASE = [' + @defaultdb + ']'
     
            IF ( @is_policy_checked IS NOT NULL )
            BEGIN
              SET @tmpstr = @tmpstr + ', 
                     CHECK_POLICY = ' + @is_policy_checked
            END
            IF ( @is_expiration_checked IS NOT NULL )
            BEGIN
              SET @tmpstr = @tmpstr + ', 
                     CHECK_EXPIRATION = ' + @is_expiration_checked
            END
        END
        IF (@denylogin = 1)
        BEGIN -- login is denied access
          SET @tmpstr = @tmpstr + '; 
        DENY CONNECT SQL TO ' + QUOTENAME( @name )
        END
        ELSE IF (@hasaccess = 0)
        BEGIN -- login exists but does not have access
          SET @tmpstr = @tmpstr + '; 
        REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
        END
        IF (@is_disabled = 1)
        BEGIN -- login is disabled
          SET @tmpstr = @tmpstr + '; 
        ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
        END
        SET @tmpstr = 'IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE NAME = ''' + @name + ''')
        AND DB_ID(''' + @defaultdb + ''') IS NOT NULL
    BEGIN
        ' + @tmpstr + ';
    END;';
        PRINT @tmpstr
      END
     
      FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
       END
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
    GO

    Thursday, June 27, 2013 10:32 AM
  • Please post the error details from the sql error logs here, that would help us to understand why it fails to restore. Just a guess, do you have a dbname_log with identical name ? Use Restore  with filelistonly option to get the file details, and query sysaltfile table to see if the name is already in use.

    Thank you
    Hemantgiri S. Goswami | SQL Server Consultant
    SQL Server Citation| Twitter | Facebook
    Author of book - SQL Server 2008 High Availability

    Friday, June 28, 2013 2:36 AM