locked
Migration from SQL server 2000 to SQL server 2008 R2 RRS feed

  • Question

  • Hello,

     

    We are going to migrating from SQL server 2000 to SQL server 2008 R2 .
    How can I move SQL jobs , maintenance tasks and Logins from SQL server 2000 to SQL server 2008 R2 ?

    What steps should I take to make everything is moved to new server ?

     

    Thanks in advance .

     

    Daizy

    Monday, September 12, 2011 9:19 PM

Answers

  • Hi Daizy,

    In order to move SQL Jobs, Just Right click on the job and choose script from SQL Server 2000 and execute those scripts in R2.

    In ordet to migrate Maintenance plans: http://msdn.microsoft.com/en-us/library/bb153838.aspx

    For login transfer use this link: http://www.sqlmag.com/article/query-analyser/sql-server-login-transfer

     

     


    Thanks, Raj Lanka
    • Proposed as answer by Peja Tao Wednesday, September 14, 2011 7:59 AM
    • Marked as answer by Peja Tao Thursday, September 22, 2011 2:04 PM
    Monday, September 12, 2011 9:30 PM
  • and this:

     

     


    --FOR SQL2000

    CREATE PROCEDURE sp_help_revlogin

    -- NULL per tutte le utenze, nome utente per specificare un singolo utente

    @login_name SYSNAME = NULL,

    -- 1 per eliminare pwd policy

    @PwdPolicy bit = 1

    AS

    DECLARE @name SYSNAME

    DECLARE @xstatus INT

    DECLARE @binpwd VARBINARY (256)

    DECLARE @txtpwd SYSNAME

    DECLARE @tmpstr VARCHAR (256)

    DECLARE @SID_varbinary VARBINARY(85)

    DECLARE @SID_string VARCHAR(256)

    IF (@login_name IS NULL)

    DECLARE login_curs CURSOR FOR

    SELECT sid, name, xstatus, password FROM master..sysxlogins

    WHERE srvid IS NULL AND name <> 'sa'

    ELSE

    DECLARE login_curs CURSOR FOR

    SELECT sid, name, xstatus, password FROM master..sysxlogins

    WHERE srvid IS NULL AND name = @login_name

    OPEN login_curs

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd

    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 ''

    PRINT 'DECLARE @pwd sysname'

    WHILE (@@fetch_status <> -1)

    BEGIN

    IF (@@fetch_status <> -2)

    BEGIN

    PRINT ''

    SET @tmpstr = '-- Login: ' + @name

    PRINT @tmpstr

    IF (@xstatus & 4) = 4

    BEGIN -- NT authenticated account/group

    IF (@xstatus & 1) = 1

    BEGIN -- NT login is denied access

    SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''

    PRINT @tmpstr

    END

    ELSE BEGIN -- NT login has access

    SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''

    PRINT @tmpstr

    END

    END

    ELSE BEGIN -- SQL Server authentication

    IF (@binpwd IS NOT NULL)

    BEGIN -- Non-null password

    EXEC sp_hexadecimal @binpwd, @txtpwd OUT

    IF (@xstatus & 2048) = 2048

    SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'

    ELSE

    SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'

    PRINT @tmpstr

    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

    SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name

    + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '

    END

    ELSE BEGIN

    -- Null password

    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

    SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name

    + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '

    END

    IF (@xstatus & 2048) = 2048

    -- login upgraded from 6.5

    SET @tmpstr = @tmpstr + '''skip_encryption_old'''

    ELSE

    SET @tmpstr = @tmpstr + '''skip_encryption'''

    PRINT @tmpstr

    IF @PwdPolicy = 1

    PRINT 'ALTER LOGIN [' + @name + '] WITH CHECK_POLICY = OFF'

    END

    END

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd

    END

    CLOSE login_curs

    DEALLOCATE login_curs

    RETURN 0

     

    GO

     

    • Proposed as answer by Peja Tao Wednesday, September 14, 2011 7:59 AM
    • Marked as answer by Peja Tao Thursday, September 22, 2011 2:05 PM
    Monday, September 12, 2011 11:25 PM
  • Moving DTS packages from one server to another.

    http://www.sqldts.com/204.aspx

    I believe this still works going to SQL Server 2008, however to run the packages, you will need to install the support for these.

    Microsoft SQL Server 2005 Backward Compatibility Components

    X86 Package (SQLServer2005_BC.msi) - 11273 KB
    X64 Package (SQLServer2005_BC.msi) - 18569 KB

    Also, if you still need to edit your DTS packages you will need the DTS Designer.

    http://blogs.msdn.com/b/sqlserverfaq/archive/2009/07/09/error-sql-server-2000-dts-designer-components-are-required-to-edit-dts-packages-install-the-special-web-download-sql-server-2000-dts-designer-components-to-use-this-feature-microsoft-sqlserver-dtsobjectexplorerui-ssms.aspx

    RLF

    • Marked as answer by Peja Tao Thursday, September 22, 2011 2:05 PM
    Monday, September 19, 2011 8:06 PM

All replies

  • Hi Daizy,

    In order to move SQL Jobs, Just Right click on the job and choose script from SQL Server 2000 and execute those scripts in R2.

    In ordet to migrate Maintenance plans: http://msdn.microsoft.com/en-us/library/bb153838.aspx

    For login transfer use this link: http://www.sqlmag.com/article/query-analyser/sql-server-login-transfer

     

     


    Thanks, Raj Lanka
    • Proposed as answer by Peja Tao Wednesday, September 14, 2011 7:59 AM
    • Marked as answer by Peja Tao Thursday, September 22, 2011 2:04 PM
    Monday, September 12, 2011 9:30 PM
  • For Logins use this:

     CREATE PROCEDURE sp_hexadecimal
        @binvalue varbinary(256),
        @hexvalue varchar(256) OUTPUT
    AS

    DECLARE @charvalue varchar(256)
    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

    Monday, September 12, 2011 11:24 PM
  • and this:

     

     


    --FOR SQL2000

    CREATE PROCEDURE sp_help_revlogin

    -- NULL per tutte le utenze, nome utente per specificare un singolo utente

    @login_name SYSNAME = NULL,

    -- 1 per eliminare pwd policy

    @PwdPolicy bit = 1

    AS

    DECLARE @name SYSNAME

    DECLARE @xstatus INT

    DECLARE @binpwd VARBINARY (256)

    DECLARE @txtpwd SYSNAME

    DECLARE @tmpstr VARCHAR (256)

    DECLARE @SID_varbinary VARBINARY(85)

    DECLARE @SID_string VARCHAR(256)

    IF (@login_name IS NULL)

    DECLARE login_curs CURSOR FOR

    SELECT sid, name, xstatus, password FROM master..sysxlogins

    WHERE srvid IS NULL AND name <> 'sa'

    ELSE

    DECLARE login_curs CURSOR FOR

    SELECT sid, name, xstatus, password FROM master..sysxlogins

    WHERE srvid IS NULL AND name = @login_name

    OPEN login_curs

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd

    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 ''

    PRINT 'DECLARE @pwd sysname'

    WHILE (@@fetch_status <> -1)

    BEGIN

    IF (@@fetch_status <> -2)

    BEGIN

    PRINT ''

    SET @tmpstr = '-- Login: ' + @name

    PRINT @tmpstr

    IF (@xstatus & 4) = 4

    BEGIN -- NT authenticated account/group

    IF (@xstatus & 1) = 1

    BEGIN -- NT login is denied access

    SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''

    PRINT @tmpstr

    END

    ELSE BEGIN -- NT login has access

    SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''

    PRINT @tmpstr

    END

    END

    ELSE BEGIN -- SQL Server authentication

    IF (@binpwd IS NOT NULL)

    BEGIN -- Non-null password

    EXEC sp_hexadecimal @binpwd, @txtpwd OUT

    IF (@xstatus & 2048) = 2048

    SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'

    ELSE

    SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'

    PRINT @tmpstr

    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

    SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name

    + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '

    END

    ELSE BEGIN

    -- Null password

    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

    SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name

    + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '

    END

    IF (@xstatus & 2048) = 2048

    -- login upgraded from 6.5

    SET @tmpstr = @tmpstr + '''skip_encryption_old'''

    ELSE

    SET @tmpstr = @tmpstr + '''skip_encryption'''

    PRINT @tmpstr

    IF @PwdPolicy = 1

    PRINT 'ALTER LOGIN [' + @name + '] WITH CHECK_POLICY = OFF'

    END

    END

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd

    END

    CLOSE login_curs

    DEALLOCATE login_curs

    RETURN 0

     

    GO

     

    • Proposed as answer by Peja Tao Wednesday, September 14, 2011 7:59 AM
    • Marked as answer by Peja Tao Thursday, September 22, 2011 2:05 PM
    Monday, September 12, 2011 11:25 PM
  • Thank both of you .

    I was able to move logins , SQL jobs ,alerts and Operators but I could not able to move DTS package and Database maintenace Plans to SQL server 2008.

    There is not DTS migration wizard install on SQL server 2000 server .I do not have migration option when I right click on Database maintenance plans .

    Please help me Do we have another way to transfer SQL server 2000 Database maintenance plans and DTS package to SQL server 2008 ?

    Your Help will be apprecited .

    Daizy

     

     

    Friday, September 16, 2011 1:19 PM
  • Hi Daizy

    see if these can somehow help, I do not use them since ages so I do not guarantee, test them before (they do not do any damage anyway).

    Question is what you want to do with DTS in 2008, migrate to DTSX ? Keep them as 2000 ? (you need backward compatibily tools to be installed)       If you want ot upgrade you can export with the second procedure, it should create file systems.

    Regards,

         Marco

     

    this works with dts 2000 (your case), but you must be able to fix it for DTSX as well if you need, see the dtxs table on 2005 or 2008, should be sysdtspackages90 or something (can't remember now)

    sp_addlinkedserver 'lk', ' ', 'SQLOLEDB', NULL, NULL,
    'Driver={SQL Server};Database=msdb;Server=XXX;UID=X;PWD=X;'
    insert into sysdtspackages SELECT * FROM OPENQUERY(lk, 'SELECT * FROM dbo.sysdtspackages')

     

    this should save packages to file system

     

    Create procedure s_SavePackages
    @Path varchar(128)
    as
    /*

    */

     set nocount on

    declare @objPackage int
    declare @PackageName varchar(128)
    declare @rc int
    declare @ServerName varchar(128)
    declare @FileName varchar(128)
    declare @FilePath varchar(128)
    declare @cmd varchar(2000)
     
     select  @ServerName = @@ServerName ,
      @FilePath = @Path
     
     if right(@Path,1) <> '\'
     begin
      select @Path = @Path + '\'
     end
     
     -- create output directory - will fail if already exists but ...
     select @cmd = 'mkdir ' + @FilePath
     exec master..xp_cmdshell @cmd
     
     
    create table #packages (PackageName varchar(128))
     insert  #packages
      (PackageName)
     select  distinct name
     from msdb..sysdtspackages
     
     select @PackageName = ''
     while @PackageName < (select max(PackageName) from #packages)
     begin
      select @PackageName = min(PackageName) from #packages where PackageName > @PackageName

      select @FileName = @FilePath + @PackageName + '.dts'

      exec @rc = sp_OACreate 'DTS.Package', @objPackage output
      if @rc <> 0
      begin
       raiserror('failed to create package rc = %d', 16, -1, @rc)
       return
      end

      exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null,
       @ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName
      if @rc <> 0
      begin
       raiserror('failed to load package rc = %d, package = %s', 16, -1, @rc, @PackageName)
       return
      end
      
      -- delete old file
      select @cmd = 'del ' + @FileName
      exec master..xp_cmdshell @cmd, no_output
      
      exec @rc = sp_OAMethod @objPackage, 'SaveToStorageFile', null, @FileName
      if @rc <> 0
      begin
       raiserror('failed to save package rc = %d, package = %s', 16, -1, @rc, @PackageName)
       return
      end
      
      exec @rc = sp_OADestroy @objPackage
     end
    go

     

     


    Monday, September 19, 2011 7:52 PM
  • Moving DTS packages from one server to another.

    http://www.sqldts.com/204.aspx

    I believe this still works going to SQL Server 2008, however to run the packages, you will need to install the support for these.

    Microsoft SQL Server 2005 Backward Compatibility Components

    X86 Package (SQLServer2005_BC.msi) - 11273 KB
    X64 Package (SQLServer2005_BC.msi) - 18569 KB

    Also, if you still need to edit your DTS packages you will need the DTS Designer.

    http://blogs.msdn.com/b/sqlserverfaq/archive/2009/07/09/error-sql-server-2000-dts-designer-components-are-required-to-edit-dts-packages-install-the-special-web-download-sql-server-2000-dts-designer-components-to-use-this-feature-microsoft-sqlserver-dtsobjectexplorerui-ssms.aspx

    RLF

    • Marked as answer by Peja Tao Thursday, September 22, 2011 2:05 PM
    Monday, September 19, 2011 8:06 PM
  • Before you do the migration 

    have you  ran the upgrade advisor toll against your 2000 box..

     

    Transferring logins

    http://support.microsoft.com/kb/246133

    SQL Jobs

    script it.. and run on the new box..

     if you are using any custom errors, make sure you transfer them as well.. 

     

    or 

    you could create SSIS package to transfer the database to the new server.. Transfer Database task..see the link bellow

    http://technet.microsoft.com/en-us/library/ms141204.aspx

     

    What method of upgrade you going to use.. Side by Side or Migration..

    if its side by side and database detach attach method.. then make sure to run the statistics update after the upgrade..

     

    vt 

    Tuesday, September 20, 2011 1:27 PM