Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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? –  Coder Hawk Mar 3 '11 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. –  Coder Hawk Mar 3 '11 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 '11 at 23:43
    
Question title is little confusing "What's the most effective way to duplicate an |instance| on SQL 2008?" - instance or database? –  Coder Hawk Mar 4 '11 at 8:51
    
@Sandy ~ "Instance" ... indeed, I want the whole thing. –  jcolebrand Mar 4 '11 at 15:05
add comment

3 Answers

up vote 3 down vote accepted

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

share|improve this answer
add comment

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

share|improve this answer
    
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 '11 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 '11 at 0:20
    
yep that's the idea ;) –  jcolebrand Mar 3 '11 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 '11 at 21:09
    
oooooh that could be useful. New avenues of consideration. –  jcolebrand Mar 4 '11 at 21:23
add comment

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.
share|improve this answer
    
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 '11 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 '11 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 DaCosta Mar 4 '11 at 5:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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