Restore db from SSC from command line

A SQL Server Management Studio add-in to source control your database in Subversion or Team Foundation Server.

Moderators: Chris Auckland, David Atkinson, sherr, PhilScrace, andy.campbell.smith

Restore db from SSC from command line

Postby kraades » Fri Nov 30, 2012 10:45 am

At the moment we restore our test database from a SQL backup file.

We would like to store the test database (schema and data) in TFS using SSC and restore it using the command line or using an API.

What are the possibilities?

Current:
SQL backup file --> SQL database

Requirement:
SSC --> SQL backup file --> SQL database
or if this is not possible:
SSC --> SQL database
kraades
 
Posts: 5
Joined: Tue Oct 23, 2012 8:34 am

Postby Brian Donahue » Mon Dec 03, 2012 4:27 pm

You can use SQL Compare to check out the database scripts from TFS and create a new test database from that.

Before that can happen, though, you'll probably want to use SQL Source Control to get the database *into* TFS as a set of SQL scripts.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby david.proctor » Tue Dec 04, 2012 2:16 pm

Like Brian says, your best option is probably to check the scripts out and run a command like the following:

Code: Select all
SQLCompare.exe /scripts1:"C:\\ScriptsFolder" /s2:targetserver /db2:targetdatabase /include:staticdata /sync


However, you can also get SQL Compare to check it out of SQL Source Control for you using the
Code: Select all
/version1
switch. Using
Code: Select all
/version1:HEAD
checks out the most recent copy of the database.

Code: Select all
SQLCompare.exe /s1:sourceserver /db1:SourceDatabase /version1:HEAD /s2:targetserver /db2:targetdatabase /include:staticdata /sync


Just to be clear, it doesn't actually compare against the source database, it compares against the scripts folder of the source database that it checks out.

To store the data as well as the schema you'll want to make sure you've linked all the tables as static data. More about this is here: http://www.red-gate.com/supportcenter/c ... lling_Data

Let me know if this is useful and if you need any more help.
David Proctor
Technical Author,
Red Gate Software
david.proctor
 
Posts: 2
Joined: Fri Nov 23, 2012 3:30 pm


Return to SQL Source Control 3

Who is online

Users browsing this forum: newastrums and 0 guests