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.

jcolebrand
  • 6,376
  • 4
  • 43
  • 67

3 Answers3

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

gbn
  • 70,237
  • 8
  • 167
  • 244
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 :-).

Marian
  • 15,741
  • 2
  • 62
  • 75
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.
john.da.costa
  • 565
  • 1
  • 3
  • 14