skreebydba

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.