I’ve mentioned that my company is doing a data center move in the near future and I’ve been building out some PowerShell scripts to get ready for that. Before we get there though we are having to rebuild one of our test environments from the ground up. When I told my coworker Shyam of my approach he slightly disagreed and said he thought he had a better way.
So today Shyam and I are holding a little contest, We’re each going to put together some code to detach all of the databases on one server, copy them to a new server, and then attach them all. Shyam is using an approach where he uses a lot of SMO calls, I am using an approach where I do a bunch of SQL queries. I’ll keep all of you posted on who wins, why, and give you the code once we’re done (minus any edits for security but it will be working code).
It’s really great to have a coworker like Shyam to hold a friendly little competition like this with. I can’t wait to see how he ends up solving a couple of the problems that we’ve already come across. Here’s some of the P. of C. code that we’re both using to get us started; it’s basic ‘code that writes code’ but what we’re going after is ‘code that writes AND executes code’.
This generates the list of databases and files including file# to use with the sp_attach_db command. I know that in 2005+ you should use ALTER DATABASE … WITH ATTACH but we have a mix of SQL 2000 and 2005 in this project. (which means I’m actually running this command through a linked server connection on the 2000 boxes.)
SELECT CASE WHEN ROW_NUMBER() OVER(PARTITION BY db.name ORDER BY sf.filename) = 1 THEN ‘GO
sp_attach_DB @dbname=”’+SUBSTRING(db.NAME, 1, LEN(db.NAME))+”’,’ ELSE ‘, ‘ END+‘
@filename’+CONVERT(VARCHAR, ROW_NUMBER() OVER(PARTITION BY db.name ORDER BY sf.filename))+‘=”’+SUBSTRING(sf.filename, 1, LEN(sf.filename))+””
FROM master..sysaltfiles sf
JOIN master..sysdatabases db
ON sf.dbid = db.dbid
WHERE db.sid !=0x01
ORDER BY db.name, sf.filename
This just generates the copy file statements in PowerShell that we will need to move the database files.
SELECT ‘
copy-item “‘+SUBSTRING(
saf.filename, 1, LEN(saf.filename))+‘” -destination “\\DestinationServerName\Drive$\’+SUBSTRING(SUBSTRING(saf.filename, 1, LEN(saf.filename)), 4, LEN(saf.filename))+‘”‘
FROM master.dbo.sysaltfiles saf
JOIN master.dbo.sysdatabases db
ON saf.dbid = db.dbid
WHERE db.sid !=0x01
ORDER BY db.name, saf.filename
