Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
Why are you "upgrading" from enterprise to standard ? Even some features from SQL 2005 standard are not available on 2008 standard. – IvanP Apr 29 '13 at 18:11
Why don't you just do a side-by-side and then migrate all databases and server objects? – Thomas Stringer Apr 29 '13 at 18:12

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
--  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%')
      drop table #filetable
      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 =
      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

      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

      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
      select @dbid = min(dbid) from #filetable where dbid > 4 and dbid > @dbid
select * from #filetable
drop table #filetable
--Finally Shutdown SQL Server
--shutdown with nowait

share|improve this answer
detach/attach is a very, very, very dangerous way to do this. Why would you recommend this over backup/restore? If something goes wrong, you now have ZERO backups. Also, what happens when an Enterprise feature is being used? That attach might not work so well... – Aaron Bertrand Apr 29 '13 at 19:18
I presumed that the OP might be having good backups before starting moving the databases around .. (my bad that I should have pointed it out upfront ). Also, the OP wanted to go from enterprise to standard edition, so he is already aware that no enterprise feature would be available in standard edition. I agree with you that backup/restore is safer way, but attach/detach is more faster. – Kin Apr 29 '13 at 19:42
How is it "more faster"? If you presumed the OP already has good backups, they can just restore them, right? – Aaron Bertrand Apr 29 '13 at 19:43
fair enough ... but just out of curiosity, what enterprise feature do you think that 2005 will have that will not work in 2008R2 standard ? – Kin Apr 29 '13 at 19:45
Well a feature that will break outright, like table partitioning, or one that will fail later, like index maintenance with ONLINE = ON, or one that will "fail" silently, e.g. relying on indexed views without the NOEXPAND hint. – Aaron Bertrand Apr 29 '13 at 19:47

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.