January 9, 2014 at 6:32 pm
I am a developer(an inexperienced one) trying to understand what is the best way to deploy a brand new database with the exact same schema as an existing DB on the same server, but with a different name. I want to preserve the data in all reference tables which I can do by having the relevant post-deployment scripts run after the deployment.
What I can't understand is how the project should be set up to deploy database Customer_2 if database Customer_1 exists on this server - the Visual Studio project contains DB Customer and I need to find a way to be able to deploy Customer_N (but without creating N identical DB's in the project). I checked a ton of articles and blogs, and feel dumb asking this question but haven't been able to understand it yet. Any help would be appreciated.
January 9, 2014 at 6:49 pm
This is a shortcut, but why not create a copy of the database and truncate all non-reference tables?
January 10, 2014 at 4:07 am
It's a tough problem to solve. There are two sets of tools that could help you out. Microsoft has a deployment tool called SQL Server Data Tools (SSDT). You can download it through Visual Studio. It's going to allow you to get your data structures and check them into source control and then do builds and deployments to different databases or different servers. It's going to allow you to automate most of what you're looking for with the exception of lookup data which is no longer supported by SSDT (an older version of the tool did support it).
Another alternative is to look to Red Gate Sofware. We have a couple of tools, SQL Source Control[/url] and Deployment Manager which allow you to automate everything SSDT does, plus more, including lookup data.
Either toolset will get you moving in the direction you're interested in. There's no real way to do this natively from within SQL Server Management Studio. While there are functional differences, the main issue is down to do you want to do your database work from within SSMS (then Red Gate) or through Visual Studio and SSMS (then Microsoft).
DISCLAIMER: I do work for Red Gate.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 10, 2014 at 10:20 am
Thank you for responding! I will do more research on that. I think this could probably be done via SSIS as well. Another thing I found while going through the VS project documentation is that it could possibly be done by a few additions to the project files.
January 10, 2014 at 10:25 am
Thank you All!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply