Script-based promotions in SQL Server

This is one of those “well duh” things that I SHOULD remember and have it burned in my brain – but then again, I could TOTALLY see me three months from now going, ‘how the hell did I get that to work again?’

It’s not too hard to move a smaller database from QA to PROD (for example) using only scripts. Here’s a low-impact way of doing this:

  1. Go into SQL Server and right click on the database. Select Tasks -> Generate scripts.

  2. In the Advanced button, under Advanced Scripting Options select “Schema only”.
    1. At this point you may also want to turn off descriptive headers too (since I find them annoying – honestly don’t I KNOW what a “DROP DATABASE” statement does?) – but it’s not necessary.
    2. I usually don’t select the DROP AND CREATE option since it creates much messier SQL. We’re going on the assumption that you’re populating an empty database, from scratch.

  3. Select the “Save to new query window” option. Click next and watch it populate away.
  4. Follow the same steps as above, only this time for Data only. Save each of these since you’ll want to come back to these. Call the first one “Database Creation Scripts – Schema Only” and the second one “… – Data Only.sql”.
  5. OK, see all that fancy crap in the header creating the database? You don’t need this and it’ll just get in the way. Blow it away – right down to where the stored procedures start dropping in.
  6. Go to your target SQL database, and manually create an empty database. Set up permissions as you’d like it (including creating any necessary users and passwords. You are using a strong password on prod no?)
  7. An interesting bug here. See that “CREATE SCHEMA” statement? Make sure this is run manually, first… otherwise SQL will try executing the entire script as a batch (in no particular order, in parallel) – and upchuck because it won’t find the schema. This is one step you definitely want to run as part of a series.
  8. Run the entire CREATE script. If this works – and it should – then run the data creation script. Easy peasy. You may want to think about kicking off the data creation script locally on the box versus using SSMS and a remote location – just for the sake of throughput times.

This is not too shabby a way of populating a database if you don’t have SSIS set up or just want a quick and dirty way of moving tables around. I’ve done this with Azure and as my British friends would say – it works a treat.

Another little gotcha – if you add your user, and set up permissions correctly (I typically set the user account up with ddladmin, datareader, datawriter access vs individual EXEC perms on sprocs only – for EF purposes) – you may still have connection issues. The new SQL account shows up great, all looks good – but you can’t log into SSMS locally using that account or using your app. Simple fix in some cases – just right click on the server and make sure its set up for Mixed mode authentication. You may have to restart, but this will give you the application access you need.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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