Archive

Archive for November, 2012

Determining Estimated Completion Time from sys.dm_exec_requests

November 21, 2012 2 comments

According to Books Online, the following list of commands will generate a percent complete and an estimated completion time in the sys.dm_exec_requests DMO:

Percent Complete Commands

You can use this to determine approximately how long a backup, restore or DBCC command will run. This query will get you a nicely formatted time value.

[sourcecode language=”SQL”]
USE master
GO

SELECT
CASE
WHEN estimated_completion_time < 36000000
THEN ‘0’
ELSE ”
END
+ RTRIM(estimated_completion_time/1000/3600)
+ ‘:’ + RIGHT(‘0’ + RTRIM((estimated_completion_time/1000)%3600/60), 2)
+ ‘:’ + RIGHT(‘0’ + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining],
percent_complete,
*
FROM sys.dm_exec_requests
WHERE percent_complete > 0
[/sourcecode]

Categories: DMO, , T-SQL, Uncategorized

Downgrading SQL Server Enterprise to Standard – With Scripts!

November 16, 2012 12 comments

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

[sourcecode language=”SQL” wraplines=”false”]
/**********************************************************************
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
[/sourcecode]

Detach Script
[sourcecode language=”SQL” wraplines=”false”]
/**********************************************************************
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
[/sourcecode]

Attach Script

[sourcecode language=”SQL” wraplines=”false”]
/**********************************************************************
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
[/sourcecode]

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