Migrating a SQL Server test database from development to test environment with a script?

Question

I'm doing a lot of development with a weekly demo to client. I have a development environment and a test environment.

Every week I have to backup the database on development and then use remote desktop to restore it on the test server.

I've tried using MSdeploy to move the database but haven't been able to get the dropDestinationDatabase switch working. See dropDestinationDatabase option on Web Deployment Commandline Tool

Does anyone know a good, simple technique to use here?

score 2 · Accepted Answer · answered Nov 09 '10 at 00:19

2

Red Gate's Sql Source Control allows you to automate promotes and connect your database to SVN.

I've not done it myself but I reckon that if the database is in svn then you could hook in a Team City build trigger to not only automate sql changes but monitor them in the same place as code changes.

answered Nov 09 '10 at 00:19

amelvin

8,919
4
38
59

Yes, you're right. Once in source control, it's possible to set up automation using appropriate command line tools as described in this paper: http://downloads.red-gate.com/HelpPDF/ContinuousIntegrationForDatabasesUsingRedGateSQLTools.pdf – David Atkinson Nov 14 '10 at 19:25

score 1 · Answer 2 · answered Nov 08 '10 at 11:06

1

RedGate Tools (SQL Compare, SQL Data Compare)

If you are using Visual Studio 2008 onwards, the GDR R2 database project is also a good solution.

answered Nov 08 '10 at 11:06

Mitch Wheat

295,962
43
465
541

I'd prefer a script that I can just execute, can I create one with these tools? – Rob Stevenson-Leggett Nov 08 '10 at 12:24
@Rob Stevenson-Leggett: The redgate tools can create a TSQL synch script; GDR uses a cmd line tool and manifest. – Mitch Wheat Nov 08 '10 at 12:33
@Rob Stevenson-Leggett, may I ask why do you want script for this? – Gennady Vanin Геннадий Ванин Nov 08 '10 at 14:44
Because I want a repeatable process that doesn't take half an hour. – Rob Stevenson-Leggett Nov 08 '10 at 15:12
If you're looking to just copy a database, Red Gate SQL Packager might work for you as it does both schema and data. However, it lacks the sophistication of SQL Compare and SQL Data Compare. – David Atkinson Nov 14 '10 at 19:27

Migrating a SQL Server test database from development to test environment with a script?

2 Answers2