16 08 2014
My SQL Migration Tool Box
As I have mentioned in a previous post I have been migrating databases from old hardware that has gone out of support, up to new clustered servers in our collocation data center. Part of the migration has included bringing the databases from SQL 2000 up to SQL 2012; doing so requires all sorts of changes to the databases/jobs/etc to end up with a safe and working environment.
I have knocked together a little tool box that I use in these scenarios..
--Alter the compatibility level up to 2012
SELECT'ALTER DATABASE '+ name +' SET compatibility_level = 110' FROM sys.databases WHERE compatibility_level != 110
-- Disable AutoShrink on any databases that have it enabled.
SELECT'ALTER DATABASE '+ name +' SET AUTO_SHRINK OFF' FROM sys.databases WHERE is_auto_shrink_on = 1
--Correct any DBOwners (in my case I don't want specific owners)
SELECT 'ALTER AUTHORIZATION ON DATABASE::' + name + ' TO sa'
WHERE name NOT IN ( 'master' , 'model' , 'msdb' , 'tempdb' )
-- Change all the databases to a specified file growth (I update it later)
SELECT 'ALTER DATABASE ' + d.name + ' MODIFY FILE (NAME=' + f.NAME + ',FILEGROWTH=32MB)'
FROM SYS.SYSALTFILES f
JOIN SYS.DATABASES d ON d.database_id = f.dbid
WHERE f.status & 0x100000 = 1048576
AND d.name NOT IN ( 'master' , 'model' , 'msdb' , 'tempdb' )
-- Fix any orphaned users
EXEC sp_change_users_login 'Auto_Fix', 'USERNAME', NULL, 'PASSWORD'
-- Update to PageVerify Checksum (2000 didn't have this)
SELECT 'ALTER DATABASE ' + name + ' SET PAGE_VERIFY CHECKSUM' FROM sys.databases WHERE page_verify_option_desc != 'checksum'
As always if you wish to use some of this SQL please ensure that you understand what you are running (I take no responsibility).