14

We've got a simple database instance with just enough data in the databases to keep it useful, and we can use it for demonstration purposes, and I want to put a copy of it on my laptop so I can use it for dev work when I'm not at the office. (The regular dev database instances are about 150GB, this one instance is 3GB)

What's the most repeatable/scriptable method for recreating the instance on my laptop? I'm sure I'm going to have to run through the installer again for SqlServer2008, and set up an instance, but after that ... I'm thinking I want to be able to drop my old databases every month or so and copy all the new stuff over to my laptop, as my instance won't change on either the one demo system or the laptop. But the databases themselves will update, with refreshes on the sprocs and the like.

I've considered scripting all the "drop database-files; add database-files {path}" and keeping them in a runnable batch file of some sort, and then I can "drop, delete, copy, add" but wondered if there was a better way than just xcopy and batch?

I'm trying to do this on the cheap (don't we always try and do things on the cheap) so I'm not particularly interested in RedGate products or the like. I want something I can maintain easily in a text editor for myself.

Improve this question
5
  • is it single database or sql server instance with more than one database? I assume you have sql server installed in your laptop and development machine and you have one database, in that case you can use backup and restore. Backup development machine db, copy it to laptop and restore, vice versa... does that make sense?
    – Sandeep Kumar M
    Mar 3, 2011 at 12:15
  • is it SQL Server Express Edition? or Standard? SSIS is a good option for scenarios like this, unfortunately its not available in Express and Web editions.
    – Sandeep Kumar M
    Mar 3, 2011 at 12:31
  • Sorry @Sandy ~ It's SQL Dev 2008. I've got access to pretty much everything that you could guess at tho, on account of MSDN.
    – jcolebrand ♦
    Mar 3, 2011 at 23:43
  • Question title is little confusing "What's the most effective way to duplicate an |instance| on SQL 2008?" - instance or database?
    – Sandeep Kumar M
    Mar 4, 2011 at 8:51
  • @Sandy ~ "Instance" ... indeed, I want the whole thing.
    – jcolebrand ♦
    Mar 4, 2011 at 15:05

3 Answers 3

Reset to default
4

You've hit the nail on the head: copy the files. I've used this to good effect.

I'd say you have to define "cheap" to include investment of time to prepare a solution that doesn't involve xcopy

Improve this answer
3

Umm, not sure if this helps, but we're using a set of scripts to "clean" a production database and prepare it for development abuse. We're taking all the big tables that have a datetime column and we're rebuilding the table with only the last year of data in it. From a full db of about 200 GB we get to about a 40 GB db for development. Mainly we're generating dynamic scripts for creating bcp files for each table, recreate the table (including any related constraints) and then bulk insert only last year data.

Maybe there are tools to do this, but our shop goes cheap :-).

Improve this answer
5
  • Yeah, we go the BCP route for a lot of stuff, so I get what you're saying, and truncating on some of this would be good, but I already have the roughly 3GB of data I need, so I'm not really looking to add more there. I just want to copy the whole instance and figure there's a way to keep doing it repeatedly. But otherwise I think I'm on the same tack as what you're talking about, just smaller sacle.
    – jcolebrand ♦
    Mar 3, 2011 at 0:12
  • So repeat all steps in batches :). SQL has batch parameters and you can combine batch files with sql files and get a nice package. I've created a custom Log Shipping (dumb) package that is working on different domains and all db preparation is done in cmd/sql files. You can create a named instance only for your use and there start/stop the service, restore the db, clean it, prepare it, backup it and ship to some specific folder. Maybe some work could be done faster in powershell.. but I'm not familiar with it.
    – Marian
    Mar 3, 2011 at 0:20
  • yep that's the idea ;)
    – jcolebrand ♦
    Mar 3, 2011 at 13:36
  • @jcolebrand - Maybe a tool like Norton Ghost or Clonezilla would help? You'd need it to copy the prod backup folders somewhere on your playing ground, then back there do the all fun (restore..trim...etc). You'd not need to script the copy actions and you'll handle only the sql part :).
    – Marian
    Mar 4, 2011 at 21:09
  • oooooh that could be useful. New avenues of consideration.
    – jcolebrand ♦
    Mar 4, 2011 at 21:23
0

The easiest way to upgrade your schema to match those systems is to use a tool like Red Gate SQL Compare or Embarcadero DB Change Manager.

There are a bunch of other tools available that can probably do the same schema comparison and even provide you the DDL to upgrade your database schema.

Options

  1. Use a 3rd Party Tool
  2. Database Backup & Restore - you may need to restore to a staging instance to delete data (due to space restrictions on laptop)
  3. Write your own database scripter using SMO and simply create a job that scripts out the objects, then recreate your schema each month / time you need it, then use SSIS to load data into the new objects.
Improve this answer
3
  • How does "Database backup and restore" work for the instance? I want to keep the data in these tables because it's a full data set, just smaller than production.
    – jcolebrand ♦
    Mar 3, 2011 at 23:35
  • SSIS would probably let me do what I need but I don't know enough about that. I should investigate further.
    – jcolebrand ♦
    Mar 3, 2011 at 23:35
  • @jcolebrand - I suggested backup restore, by simply would have to consider how to move data across from your live system to your demo system when you do stuff such as adding columns. This does however have the drawback requiring a restore then data purge before another backup and restore / detach and attach.
    – john.da.costa
    Mar 4, 2011 at 5:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.