Monday, September 3, 2012

A SQL template for attaching Sitecore DBs

I've been doing some comparison analysis of different builds of Sitecore, and found myself creating a large number of throw-away installs locally. Attaching databases and giving rights was a bit of a nuisance, so I wrote a SQL Server Management Studio template. You can add this to your local SQL Server Management Studio by clicking Ctrl-T to open Template Explorer, and clicking New Template.

Once it's installed, you can attach Master, Core, and Web, and optionally Analytics, in a single SQL Server command. Click Ctrl-Shift-M to pull up the following prompt:
Supply the prefix to use when attaching the databases, the full path of the Databases folder, the SQL login account used by Sitecore, and a 1 or 0 to indicate whether to attach Analytics.

The script attaches the databases as "prefix_web", etc., and gives db_owner rights to the account specified.

Note: Double clicking on the code sample will Select All.
USE MASTER
CREATE DATABASE <prefix, , >_master 
 ON(FILENAME='<dbpath, , >\Sitecore.Master.mdf'),
 (FILENAME='<dbpath, , >\Sitecore.Master.ldf')
 FOR ATTACH;
 
CREATE DATABASE <prefix, , >_web
 ON(FILENAME='<dbpath, , >\Sitecore.Web.mdf'),
 (FILENAME='<dbpath, , >\Sitecore.Web.ldf')
 FOR ATTACH;
 
CREATE DATABASE <prefix, , >_core 
 ON(FILENAME='<dbpath, , >\Sitecore.Core.mdf'),
 (FILENAME='<dbpath, , >\Sitecore.Core.ldf')
 FOR ATTACH; 
GO

USE <prefix, , >_web;
CREATE USER <login, ,sitecore> FROM LOGIN <login, ,sitecore>;
exec sp_addrolemember db_owner, <login, ,sitecore>

USE <prefix, , >_master;
CREATE USER <login, ,sitecore> FROM LOGIN <login, ,sitecore>;
exec sp_addrolemember db_owner, <login, ,sitecore>

USE <prefix, , >_core;
CREATE USER <login, ,sitecore> FROM LOGIN <login, ,sitecore>; 
exec sp_addrolemember db_owner, <login, ,sitecore>
 
IF (<analytics, bit, 1>=1)
BEGIN
USE MASTER
CREATE DATABASE <prefix, , >_analytics 
 ON(FILENAME='<dbpath, , >\Sitecore.Analytics.mdf'),
 (FILENAME='<dbpath, , >\Sitecore.Analytics.ldf')
 FOR ATTACH; 
GO
USE <prefix, , >_analytics;
CREATE USER <login, ,sitecore> FROM LOGIN <login, ,sitecore>;
exec sp_addrolemember db_owner, <login, ,sitecore>
END

6 comments:

  1. Dan, have you seen Sitecore Installer? It's awesome to use when standing up instances of any version quickly. It use it all the time to test modules, etc.. http://trac.sitecore.net/SitecoreInstaller

    ReplyDelete
  2. Thanks for the tip, Mark. I will check it out. I known that Alex Shyba is doing some pretty cool stuff with PowerShell as well (http://sitecoreblog.alexshyba.com/2011/12/installing-sitecore-with-powershell.html)

    ReplyDelete
  3. Dan, have you looked at the Install Sitecore task on the Sitecore Rocks start sage in Visual Studio? I find this a massive time saver as it installs the version of Sitecore zip file that I feed it and allows me to bootstrap my site with whatever packages I choose in one action.

    ReplyDelete
  4. Another tool to check out. I feel a blog post coming... Thanks, Kevin and Mark!

    ReplyDelete
  5. Great article! Very informative and simple easy to understand. Keep posting it really help for those people that needs this. I will refer your blog to my friends. Thanks a lot!

    ReplyDelete
  6. A blog post from the dark ages, before I knew about SIM. https://marketplace.sitecore.net/en/Modules/Sitecore_Instance_Manager.aspx

    ReplyDelete