Home > Administration, SQL Server, T-SQL > Downgrading SQL Server Enterprise to Standard – With Scripts!

Downgrading SQL Server Enterprise to Standard – With Scripts!

In my new job, I am getting to do a number of things I’ve never done before. To do those things, I am trying to write reusable scripts whenever possible. This post by Ted Krueger made me want to post some of them.

One of the things I’ve done here is downgrade an instance from Enterprise to Standard Edition. Starting with this post by Bru Medishetty (b) I put together the following checklist:

  1. Disable the SQL Server Agent.
  2. Back up all user databases.
  3. Copy data and log files for system databases.
  4. Script out all logins.
  5. Generate script to attach all user databases.
  6. Detach all user databases.
  7. Uninstall SQL Server 2005 Enterprise Edition.
  8. Install SQL Server Standard.
  9. Apply any Service Packs.
  10. Stop SQL Server services and copy the system database data and log files from the new install.
  11. Restart SQL Server and attach user databases, using the script generated in step 5.
  12. Confirm that the user databases are accessible.
  13. Stop SQL Server service and overwrite the existing system database files from the Enterprise Edition install.
  14. Restart SQL Server service.
  15. Run the login creation script.

You can find the script to generate the logins for an instance at PASS President Bill Graziano’s blog. NOTE: Before running this script, go to Options -> Query Results -> Results to Text and change the Maximum number of characters displayed in each column to 8192. This will prevent painful truncation. Run the script and send the results to text.

Here are the scripts I wrote to generate the backup, detach and attach statements. Run the results to text, copy and paste them into a new query and save them.

Backup Script

/**********************************************************************
Description: Script to generate backup statements for all user databases on an instance
Author: Frank Gill
Date: 11/2/2012
**********************************************************************/

-- SET NOCOUNT ON to prevent row counts from showing up in the output
SET NOCOUNT ON

-- Create temp table to hold the database list
CREATE TABLE #backup
(dbname VARCHAR(255))

--Declare local variables
DECLARE @dbname VARCHAR(255)
DECLARE @sqlstr VARCHAR(2000)
DECLARE @loopcount INT
DECLARE @looplimit INT

-- Initialize the loop counter
SET @loopcount = 1

-- Insert a list of databases on the instance into your temp table, excluding tempdb
INSERT INTO #backup
SELECT name FROM sys.databases
WHERE name <> 'tempdb'

-- Set the loop limit to the count of databases selected in the previous step
SET @looplimit = @@ROWCOUNT

--SELECT * FROM #backup

-- Run the while loop once for each database selected above
WHILE @loopcount <= @looplimit
BEGIN

	-- Pull the first database from the temp table
	SET @dbname = (SELECT TOP 1 dbname FROM #backup)

	-- Build the dynamic SQL statement into @sqlstr
	SET @sqlstr =  'BACKUP DATABASE [' + @dbname + '] TO  DISK = N''C:\Backup\' + @dbname + '_Your_File_Name.bak'' WITH NOFORMAT, NOINIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM
GO'

	-- Print @sqlstr to build a script for later execution
	-- If you want to execute the dynamic SQL within the loop, replace the PRINT statement with EXEC(@sqlstr)
	PRINT @sqlstr

	-- Delete the current database from the temp table
	DELETE FROM #backup WHERE dbname = @dbname

	-- Increment the loop counter
	SET @loopcount = @loopcount + 1

END

-- Clean up the temp table
DROP TABLE #backup

Detach Script

/**********************************************************************
Description: Script to generate detach statements for all user databases on an instance
Author: Frank Gill
Date: 11/2/2012
**********************************************************************/

-- SET NOCOUNT ON to prevent row counts from showing up in the output
SET NOCOUNT ON

-- Create a temp table to hold the list of databases on the instance
CREATE TABLE #backup
(dbname VARCHAR(255))

-- Declare local variables
DECLARE @dbname VARCHAR(255)
DECLARE @sqlstr VARCHAR(2000)
DECLARE @loopcount INT
DECLARE @looplimit INT

-- Initialize loop counter
SET @loopcount = 1

-- Insert a list of user databases into the temp table
INSERT INTO #backup
SELECT name FROM sys.databases
WHERE name NOT IN ('tempdb', 'master', 'msdb', 'model')

-- Set the loop limit to the count of user databases on the instance
SET @looplimit = @@ROWCOUNT

--SELECT * FROM #backup

-- Loop through the list of databases
WHILE @loopcount <= @looplimit
BEGIN
	
	-- Pull the first database out of the temp table
	SET @dbname = (SELECT TOP 1 dbname FROM #backup)
	
	-- Build the dynamic SQL string, including a SET SINGLE_USER statement to allow the detach
	-- DO NOT RUN THIS ON AN ACTIVE SYSTEM
	-- Make sure that all user activity has stopped prior to executing any detach activity
	SET @sqlstr = 'USE master' + CHAR(10) + 'GO' + CHAR(10) + 'ALTER DATABASE ' + @dbname + 
	' SET SINGLE_USER WITH ROLLBACK IMMEDIATE' + CHAR(10) + 'GO' + CHAR(10)
	
	-- Append the detach statement to the SET SINGLE_USER
	SET @sqlstr =  @sqlstr + 'EXEC sp_detach_db ''' + @dbname + ''', ''true'';
GO'
	
	-- Print @sqlstr to generate a script for use later
	-- If you want to execute @sqlstr within the loop, replace the PRINT with EXEC(@sqlstr)
	-- You will need to remove the GO from the end of the dynamic SQL string
	PRINT @sqlstr
	
	--  Delete the current database from the temp table and increment the loop counter
	DELETE FROM #backup WHERE dbname = @dbname

	SET @loopcount = @loopcount + 1

END

--Clean up the temp table
DROP TABLE #backup

Attach Script

/**********************************************************************
Description: Script to generate attach statements for all user databases on an instance
Author: Frank Gill
Date: 11/2/2012
**********************************************************************/


USE master
GO

-- SET NOCOUNT ON to exclude row counts from printed results
SET NOCOUNT ON

-- Create a temp table to hold database and file information
CREATE TABLE #dbattach
(database_name SYSNAME
,[file_name] VARCHAR(1000))

-- Declare local variables
DECLARE @loopcount INT
DECLARE @looplimit INT
DECLARE @loopcount2 INT
DECLARE @looplimit2 INT
DECLARE @database_name SYSNAME
DECLARE @sqlstr VARCHAR(2000)
DECLARE @filename VARCHAR(2000)

-- Initialize both loop counters
SET @loopcount = 1
SET @loopcount2 = 1

-- Use sp_MSforeachdb to insert a list of database names and files into #dbattach
-- You can find more information on sp_MSforeachdb at this link
-- http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

declare @RETURN_VALUE int
declare @command1 nvarchar(2000)
set @command1 = 'use [?] INSERT INTO #dbattach SELECT DB_NAME(), physical_name FROM sys.database_files ORDER BY DB_NAME()'
exec @RETURN_VALUE = sp_MSforeachdb @command1 = @command1

-- Run a select of distinct database names to populate the @looplimit with the @@ROWCOUNT
(SELECT DISTINCT(database_name) FROM #dbattach
WHERE database_name NOT IN ('tempdb', 'master', 'msdb', 'model'))

SET @looplimit = @@ROWCOUNT

-- Begin looping through the list of databases
WHILE @loopcount <= @looplimit
BEGIN

	-- Pull the first user database name out of the table
	SET @database_name = (SELECT TOP 1 database_name FROM #dbattach WHERE database_name NOT IN ('tempdb', 'master', 'msdb', 'model'))

	-- Set the limit for the inner loop to the count of files for that database
	SELECT @looplimit2 = COUNT(*) FROM #dbattach WHERE database_name = @database_name

	-- Begin building the dynamic SQL string with the call to sp_attach_db and database name
	SET @sqlstr = 'EXEC sp_attach_db @dbname = N''' + @database_name + '''' + ','
 
	-- Loop through each file for the database
	WHILE @loopcount2 <= @looplimit2
	BEGIN

		-- Select the top file name for the database into @filename
		SET @filename = (SELECT TOP 1 file_name FROM #dbattach WHERE database_name = @database_name)
		
		-- Append @sqlstr using the @filename parameter plus the current loop count to adhere to the syntax of sp_attach_db 
		SET @sqlstr = @sqlstr + '@filename' + CONVERT(VARCHAR(2), @loopcount2) + ' = N''' + @filename + ''','
		
		-- Delete the current row from the temp table and increment the inner loop counter
		DELETE  FROM #dbattach WHERE file_name = @filename

		SET @loopcount2 = @loopcount2 + 1
		--PRINT @sqlstr
	
	END
	
	-- Because I want to generate a series of attach statements for future use, I am printing @sqlstr
	-- If I wanted to run the statement in the loop I would change the PRINT to EXEC(@sqlstr)
	-- Print @sqlstr using the SUBSTRING to remove the last comma
	-- Include a line return and the GO to delimit the attach statements
	PRINT SUBSTRING(@sqlstr,1,(LEN(@sqlstr) - 1)) + CHAR(10) +	'GO'

	-- Increment the outer loop counter and intitial the inner loop counter
	SET @loopcount = @loopcount + 1
	SET @loopcount2 = 1

END

-- Clean up the temp table.
DROP TABLE #dbattach

Please test all of these scripts, parsing the results prior to uninstalling an instance of SQL Server. I hope you find them useful.

  1. Ervin
    May 29, 2013 at 08:02

    Hi.
    Is this for SQL 2012 or 2008?

    Thanks.

    • June 6, 2013 at 22:13

      Ervin,

      This is for 2005, but I the concept should work for 2008 or 2012. I can’t guarantee the scripts will work without modificaiton.

  2. Francesco
    October 24, 2013 at 09:59

    Hey,
    We are running these script on an instance but it seem that the Attach script cant handle dashes “-” … I have a Sharepoint database namen called “SharePoint_AdminContent_0d8cdb05-a2f7-4c48-837a-ce01db154c31” and I get an error Database ‘SharePoint_AdminContent_0d8cdb05’ does not exist. Make sure that the name is entered correctly. And you can see that its just before the dash.

    Also, I tried to run the extract logins from the blog you talked about but it doesnt output it as a text, i get it as a single column on several rows starting with “IF NOT EXISTS (SELECT * FROM master.sys.server….” and on a message tap I get:

    Msg 2714, Level 16, State 3, Procedure fn_hexadecimal, Line 32
    There is already an object named ‘fn_hexadecimal’ in the database.
    —————————————————————————–
    — Script created on Oct 24 2013 4:43PM
    —————————————————————————–

    —————————————————————————–
    — Create the windows logins
    —————————————————————————–
    —————————————————————————–
    — Create the SQL Logins
    —————————————————————————–
    —————————————————————————–
    — Disable any logins
    —————————————————————————–
    —————————————————————————–
    — Assign groups
    —————————————————————————–

    • October 24, 2013 at 10:18

      Francesco, thanks for the comment. The SharePoint database name needs to be wrapped in square brackets. Replace this line:
      SET @sqlstr = ‘EXEC sp_attach_db @dbname = N”’ + @database_name + ”” + ‘,’

      With this:
      SET @sqlstr = ‘EXEC sp_attach_db @dbname = N”’ + ‘[‘ + @database_name + ‘]’ + ”” + ‘,’

      That should solve your problem. I will post an update tonight.

      Thanks for reading.

      • Francesco
        October 24, 2013 at 10:41

        Hey,

        Thanks for a quick respond.. it says the same thing:

        Msg 911, Level 16, State 1, Line 1
        Database ‘SharePoint_AdminContent_0d8cdb05’ does not exist. Make sure that the name is entered correctly.

        SET @sqlstr = ‘EXEC sp_attach_db @dbname = N”’ + ‘[‘ + @database_name + ‘]’ + ”” + ‘,’

        I had to translate your ‘ ” ‘ because I couldnt copy the whole string. Is there any need to allow signes like the dash? because I guess it look at it like a charachter that is used by SQL script so we need to like exchange it somehow so it doesnt read it as a SQL query variable. I dont know, not a scripter.

        I have 20 instances and a couple of hundred databases before im done with this.. these script will help alot!

      • October 24, 2013 at 10:52

        Francesco, let me take a closer look and get back to you. Wrapping a SharePoint database name in square brackets will usually fix that issue. Look for an update tomorrow.

      • October 24, 2013 at 12:28

        Francesco, sometimes the answer is too simple to see. Replacing this line of code:

        set @command1 = ‘use ? INSERT INTO #dbattach SELECT DB_NAME(), physical_name FROM sys.database_files ORDER BY DB_NAME()’

        with this:

        set @command1 = ‘use [?] INSERT INTO #dbattach SELECT DB_NAME(), physical_name FROM sys.database_files ORDER BY DB_NAME()’

        should take care of your issue. Let me know if that works and I’ll update the page.

        Thanks again for reading.

      • Francesco
        October 25, 2013 at 02:16

        Hey yes, it worked! it outputs it like this now.. i left the [] from before, is that good or should I remove them? I dont have the possibility to test the attaching and dettaching right now.

        EXEC sp_attach_db @dbname = N'[SharePoint_Config]’,@filename1 = N’X:\SharePoint_Config.mdf’,@filename2 = N’W:\SharePoint_Config_log.LDF’
        GO

  3. Francesco
    October 25, 2013 at 05:58

    I got the user script to work aswell..but one small problem though, when I use your script it presents everything nice and tidy in a Messages tab for me to just copy. But when I use the export logins script it create two tabs. One called Results which has rows and columns, and one tab called Messages where this text ends up. I guess the purpose is that what ever is presented under Results should be presented under Messages, but its not doing it for me like your scripts. Any idea?

    —————————————————————————–
    — Script created on Oct 25 2013 12:45PM
    —————————————————————————–

    —————————————————————————–
    — Create the windows logins
    —————————————————————————–
    —————————————————————————–
    — Create the SQL Logins
    —————————————————————————–
    —————————————————————————–
    — Disable any logins
    —————————————————————————–
    —————————————————————————–
    — Assign groups
    —————————————————————————–

    • October 25, 2013 at 15:26

      Francesco,

      That is because the commands in the login are being built using a SELECT where I am using a PRINT statement. Hit CTRL+T before running the query and it will output to text, which should be easier to copy and paste. CTRL+D will get you back to grid.

      Frank

      • Francesco
        October 28, 2013 at 02:32

        Excellent! Worked like a charm..

  1. November 23, 2012 at 06:21

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: