SQL Source Control - 1.0
Learning SQL Source Control - 1.0
Source controlling data
SQL Source Control enables you to source control your database schema.
It is also possible to keep data in source control, alongside the schema, by using SQL Source Control with SQL Data Compare Professional Edition
It is planned to support source controlling data within SQL Server Management Studio in later versions of SQL Source Control.
To source control your databases, SQL Source Control creates a folder of scripts representing your database schema.
This scripts folder shares the format used by scripts in SQL Data Compare, and so the two tools can be used to source control data.
Example: source controlling data
In this example the database WidgetDev is already in source control, and we will additionally source control its static data.
This example uses the SQL Data Compare and Subversion command line interfaces.
It is also possible to source control static data using the SQL Data Compare graphical user interface, and a source control client such as TortoiseSVN
The example has 3 stages:
- Create a local copy
- Update the local copy with data
- Commit the data to source control
1. Create a local copy
First, create a local copy of the database in source control by performing a Subversion checkout.
At a command prompt, type:
cd C:\program files\subversion\bin
svn checkout http://svn-eval.red-gate.com/svn/<project>/trunk/WidgetDev "C:\WidgetDevScripts"
Where:
- http://svn-eval.red-gate.com/svn/<project>/trunk/WidgetDev is the URL for the database in your Subversion repository
- "C:\WidgetDevScripts" is the file path for the directory where the local copy will be created
A local copy of the scripts folder is created. This is a Subversion working copy, and is associated with the subversion repository.
2. Update the local copy with data
Use SQL Data Compare to update the scripts folder with data.
In this example the table WidgetPurchases, which contains transactional data, is excluded.
At a command prompt, type:
cd C:\program files\red gate\SQL Data Compare 8
/sqldatacompare /db1:"WidgetDev"
/scr2:"C:\WidgetDevScripts"
/Exclude:table:WidgetPurchases
/sync
Where:
- /db1:WidgetDev specifies WidgetDev as the source
- /scr2:"C:\WidgetDevScripts" specifies the local copy, WidgetDevScripts, as the target for a SQL Data Compare synchronization
- /Exclude:table:WidgetPurchases excludes WidgetPurchases. All other tables will be included
- /sync performs a SQL Data Compare synchronization, making the data in WidgetDevScripts the same as WidgetDev
The local copy is updated with data.
This example assumes you are using Windows authentication to log into the database. You may need to supply a user name and password.
For more information, see Getting started with the SQL Data Compare command line
3. Commit the data to source control
The final step is to commit the changes from the local copy to source control.
At a command prompt, type:
cd C:\program files\subversion\bin
svn commit C:\WidgetDevScripts
You may be prompted to supply a comment when committing.
Source control is updated.
Automating the process
If the data you want to source control is likely to change during development, you can save these commands as a .bat file, making it easier to commit changes.
The following example re-uses the local copy we have already created, so you do not need to perform a Subversion checkout. Instead the folder is updated with the latest changes.
The example includes a commit comment.
Save the following:
cd C:\program files\subversion\bin
svn update "C:\WidgetDevScripts"
cd C:\program files\red gate\SQL Data Compare 8
/sqldatacompare /db1:"WidgetDev"
/scr2:"C:\WidgetDevScripts"
/sync
cd C:\program files\subversion\bin
svn commit C:\WidgetDevScripts
-m "updated static data"
You can run this script to automatically commit data changes, or automate it using the Scheduled Task Wizard.
See also |
Was this article helpful?
Worked example: setting up SQL Source Control with SVN for development teams
Worked example: setting up SQL Source Control with TFS for development teams
Using SQL Source ControlSetting up a local Subversion repository
Continuous integration
Source controlling data
Evaluation center
SQL Source Control
- Setting SQL Compare options within SQL Source Control
- Linking fails due to SVN pre-commit hooks
- Error: Failed to resolve no-ops after 5 tries
- Using SQL Compare or SQL Changeset scripts with SQL Source Control
all SQL products
- Compatibility of Red Gate tools in 64-bit environments
- Application has encountered an error and needs to close
- Error message after installing SQL Toolbelt - The description for Event ID ( 1 ) in Source ( nview_info ) cannot be found.
- Changing the temporary directory used by the installer
- Toolbelt Installer "hanging" while "scanning volumes"
- Login failing with "trusted SQL Server connection" error when using RunAs
all products
- Some Red Gate products identified as containing a trojan by Anti-Virus software
- Activation may fail with Unknown Error -1
- Product uses web help although a CHM file is available locally
- Argument exception resulting from missing environment variable
- Check for updates may fail when used through proxies
- 'Unidentified Publisher' error when repairing or uninstalling
- Licensing activates product as standard edition
- Moving Red Gate software products to another machine
- Red Gate tools log locations
- The application UI opening slowly when there is no internet access
SQL Source Control
- Database development models
- Release notes - version 1.0
- Release notes - version 1.1
- Release notes - version 2.0
- Release notes - version 2.1
- Release notes - version 2.2
- Requirements & prerequisites
- Technical Overview
- Release notes - version 3.0
all SQL products
all products
- Red Gate product acknowledgements
- Activating your products
- Red Gate bundle history
- Check for updates
- Troubleshooting Check for Updates errors
- Current versions
- Deactivating your products
- Installing Red Gate products from the .msi file
- Requesting additional activations
- Serial numbers for bundles
- Reactivating using a different serial number
- Extending your trial
- Finding your serial numbers
- Moving a serial number from one computer to another
- No response received for manual activation
- Licensing and activation resources
- Troubleshooting licensing and activation errors
- Licensing and activation FAQs
- Red Gate tools log file locations
- Download old versions of products
- Download product prerequisites & utilities
- Support & upgrades
- Upgrading your software
- Upgrading FAQs
- Installing Red Gate SQL Server tools across a network
