Sql-server – Upgrade SQL Server 2005 Enterprise to SQL Server 2008 R2 Standard

sql-server-2008-r2

I understand that this is not a supported path, but does anyone have any insight about how to accomplish this? I can't seem to find any documents on the subject. I need my QA (2005) to match my Production (2008R2). Just joined this company and this is one of my first tasks…

Best Answer

You cannot do an upgrade from enterprise to Standard edition. Instead you can follow below steps :

  1. Install and configure fresh SQL Server standard 2008R2 instance (same configuration as like your 2008R2 ).

  2. Run below script replacing the @destserver with the NEW Server Name on the Old Enterprise Edition instance.

Note: Test below script upfront in your environment. I have assumed that xp_cmdshell is enabled. If not, it needs to be enabled before you run this script.

Edit: As per Aaron's suggestion, you can think of doing backup restore as a good and safe option (the script can be changed to do that).

set nocount on
--  Author  :: Kin
--  Desc    :: Move Logins, Databases from one instance to another
-- Version :: 1.0 for dba.stackexchange.com
--  Date    :: 04/29/2013

  declare @datafile varchar(255),
            @logfile varchar(255),
            @dbid tinyint,
            @SQLText varchar(8000),
            @dbname varchar(255),
            @destserver varchar(255),
            @SQLText2 varchar(8000)
set @destserver ='' --Insert your Destination Server NAme in here.
--1.Transfer Logins
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@@servername+' -E -Q"execute master.dbo.sp_help_revlogin" -oD:\logs\revloginout.sql'''
print @sqltext
--exec (@sqltext)
-- Create on Destination Server.
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@destserver+' -E -iD:\logs\revloginout.sql'''
print @sqltext
--exec (@sqltext)
--2. Detach All Local Databases and prepare for Attach on dest.
if exists(select 1 from tempdb..sysobjects where name like '%#filetable%')
      begin
      drop table #filetable
      end
      create table #filetable (mdf varchar(255),ldf varchar(255),dbid tinyint,dbname varchar(100),fileid tinyint)
      --

      insert #filetable (mdf,dbid,fileid) 
      select physical_name,database_id,data_space_id from sys.master_files where data_space_id=1

      insert #filetable (ldf,dbid,fileid) 
      select physical_name,database_id,data_space_id from sys.master_files where data_space_id=0

      update u 
      set u.dbname = s.name
      from #filetable u
      inner join master..sysdatabases s 
      on u.dbid = s.dbid
select @dbid = min(dbid) from #filetable where dbid > 4
while @dbid is not null
begin

      select @SQLText = 'alter database '+ dbname from #filetable where dbid = convert(varchar,@dbid) 
      select @SQLText = @SQLText+' set single_user with rollback immediate'
      select @SQLText = @SQLText+' exec master..sp_detach_db ' + dbname from #filetable where dbid = convert(varchar,@dbid)
      print @SQLText
      Exec(@SQLText)

      select @SQLText2 = 'exec opendatasource(''SQLNCLI'',''Datasource='+@destserver+';Persist Security Info=False;Integrated Security=SSPI'').master.dbo.sp_attach_db '''+dbname+'''' from #filetable where dbid = @dbid
      select @SQLText2= @SQLText2+','''+ mdf+'''' from #filetable where dbid = @dbid and mdf is not null
      select @SQLText2=@SQLText2+','''+ ldf+''''  from #filetable where dbid = @dbid and ldf is not null
      print @SQLText2
      Exec(@SQLText)
      select @dbid = min(dbid) from #filetable where dbid > 4 and dbid > @dbid
end
select * from #filetable
drop table #filetable
--Finally Shutdown SQL Server
--shutdown with nowait
Related Question