Coding for the Weekend

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

Data Center

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

 

 

Please Share This:

Share on facebook
Share on twitter
Share on linkedin

You may also like:

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow:

Subcribe to Blog Via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

What I'm Saying on Twitter

Subscribe via feedburner

%d bloggers like this: