What is a good approaching for tracking incremental changes to database tables, stored procedures, etc for Azure SQL Data Warehouse? I am in the process of moving a large database over to Azure SQL Data Warehouse. The prior approach for change tracking was using a 'Database Project' in Visual Studio 2015. This allows easy source control integration with TFS or Git or whatever. When you want to publish, you just target the destination database and it generates a change script. This functionality does not at all work for Azure SQL Data Warehouse. Visual Studio (and the latest SSDT) simply can't target SQL DW. This means the process of publishing is extremely tedious, entirely manual and extremely error prone. Is there another comparable approach you are using for this type of project?

There has recently been [a lot of talk](https://curiousaboutdata.com/2024/08/18/t-sql-tuesday-177-roundup-managing-database-code/) about putting database code in source control. I have read widely on this topic and there appears to be an unstated assumption that using SMO through PowerShell makes it very easy to do the following simultaneously: 1. Connect to a Microsoft SQL Server. 2. Grab every unit of code in a database (e.g. every table, index, stored procedure, function, view, permission, database config, ... *but not data*) 3. Save them in separate files, so that they're useful for source control. Despite my best efforts, I can find no off-the-shelf SMO code that does this. It really is as if there is a secret SMO script that I cannot find. To me, this suggests that such a script is so easy to write that nobody has bothered publicising it. dbatools has a handful of specialised functions for certain types of object (e.g. Get-DbaDbStoredProcedure), but finding every required dbatools function for this and correctly piping it to Export-DbaScript would be a big job. I'm also aware of some Redgate tools, **but I'm specifically asking about SMO**. So, to put my question in an answerable form: What parts of the SMO make it very easy to write a script that simultaneously satisfies all of my three numbered points above?

We have an instance of SQL Server (MSSQL) version 2016, and the database contains programming scripts, e.g., stored procedures and functions. For the programming scripts of MSSQL, we wonder how to do version control on the source code. - For example, dump or export the script in text format SQL and check in to the Git repository. - Or please kindly point out if there is a better way of doing the job. We highly appreciate hints and suggestions.

I was trialing Apex source control - trial ended. Uninstalled Apex (well I thought I did). Today I deleted the Apex database. Now I get Msg 208, Level 16, State 1, Procedure ApexSQL_SourceControl_DDLTrigger, Line 72 [Batch Start Line 7] Invalid object name 'ApexSQL.ApexSQL_SourceControl.Objects'. Can not find any triggers at all on the server - how can I get rid of this error. Won't let me alter any procedures.

I have a folder of scripts that contain multiple objects and jobs that I roll out every time I deploy a new SQL Server Instance. The scripts utilize scripting variables, as an example, here is an abridged example of a job creation script: DECLARE @Owner SYSNAME = (SELECT [name] FROM sys.server_principals WHERE [sid] = 0x01) DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Myjob', @enabled=1, @notify_level_eventlog=0, @notify_level_email=2, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'blah', @category_name=N'Database Maintenance', @owner_login_name=@Owner, @notify_email_operator_name=N'$(AlertOperator)', @job_id = @jobId OUTPUT Note that @notify_email_operator_name will be set to whatever value is passed to $(AlertOperator) These scripts are usually run through a Powershell script which loops through the folder and passes values to the $(AlertOperator) variable. This approach allows a suite of scripts to be kept which can be rolled out to a new server easily. I was wondering if there was a GUI tool where I can open one or more .sql files and it would automatically recognize the scripting variables in those files and prompt for their values before running the files against one or more defined servers? This feature of SSMS Almost does what I want, the only problem is that the parameters must be in the ` format rather than $(variableName) making it incompatible with the current Powershell setup. Another issue with it (which I could live with) is that it only really does a find and replace, rather than executing the script. I could live with that but the non support for $()` is unfortunately a deal breaker

What is a good way to migrate DB changes from Development to QA to Production environments? Currently we: 1. Script the change in a SQL file and attach that to a TFS work item. 2. The work is peer-reviewed 3. When the work is ready for testing then the SQL is run on QA. 4. The work is QA tested 3. When the work is ready for production then the SQL is run on the production databases. The problem with this is that it is very manual. It relies on the developer remembering to attach the sql or the peer-reviewer catching it if the developer forgets. Sometimes, it ends up being the tester or QA deployer who discovers the problem. A secondary problem is that you sometimes end up needing to manually coordinate changes if two separate tasks change the same database object. This may just be the way it is but it still seems like there should be some automated way of "flagging" these issues or something. Our setup: Our development shop is full of developers with a lot of DB experience. Our projects are very DB oriented. We are mainly a .NET and MS SQL shop. Currently we are using MS TFS Work Items to track our work. This is handy for code changes because it links the changesets to the work items so I can find out exactly what changes I need to include when migrating to QA and Production environments. We are not currently using a DB project but may switch to that in the future (maybe that is part of the answer). I am very used to my source control system taking care of things like this for me and would like to have the same thing for my SQL.

I was wondering if any of you can share some experience regarding integrating Flyway and Git. We're currently developing a project, each sprint we need to make database changes for new features of course. Once every two weeks we merge changes made in dev DB to prod DB (the code reside on different branches in Git and we merge every two weeks). We want to start using Flyway for DB version control. Right now the idea is to create sql scripts on dev branch when we need a change, and run migrate on dev for every change we need. Then every two weeks when merge happens, the sql scripts will be merged to prod branch and then be run (using flyway migrate) using pipeline. Does that sound like a good approach? Can anyone share some experience about it or suggest other approaches?

What is the proper approach when we add a new column into an existing table? For example, I already have columns like, Foo1, Foo2, Bar1, Bar2. Now I want to add a new column called Foo3. What is standard approach (if that is such thing) when I want to add a column with similar name? I see 2 choices: 1. Create temp table with new structure where new column is next to columns with same name, copy data to new table from existing table and drop existing table and rename temp table. Bit complex process but makes database fields more readable. 2. Add new column in end. More simpler operation. But column names *may not* be clearly understood if they in very end. To get some reference, we are using Database Projects to source control database changes and have a nicer GUI for app developers to make database changes. And we are using some kind of ORM to interact with database so nobody is querying database using database object names. **Update:** I have couple of indexes on some of existing columns. But columns that have similar names (including one that I want to add) are not part of any index.

How do you install a TFS plugin for SQL Server 2016 Management Studio? The source control option is no longer in the option list so the existing workarounds no longer appear to work.

**tl;dr:** I know a few SQL semantics and I know how databases work (*basically*) but I never created one that had a considerable size. I lack of knowledge concerning workflow, SQL-code management and very basic SQL programming practices (write by hand or use e.g. pgAdminIII). I need some "*instructions*" on how to manage my source code and state of my database. ---------- Okay, this sounds like a very basic question and in fact it is a *very basic question*. Five years of computer science and I never really had to develop a database by myself and now I am here and have no idea how to do that. Now, as a software developer writing Java or C or whatever the workflow is pretty clear of course. Thinking about design, creating some files, write code, use version control and commit/push. Next day repeat. My problem is that I can't get my head around that *incremental* nature of creating a database. What I mean is that I don't know how my SQL script should look like if I commit it to my repository and if I use e.g. something like FlywayDB. At the beginning I'd have something like this e.g. DROP TABLE IF EXISTS company_employee; CREATE TABLE company_employee ( id BIGSERIAL PRIMARY KEY ); and I need that DROP TABLE IF EXISTS because while I am developing I might change that table all the time and therefore drop it each time before I recreate it. But do I want to commit that script? I mean if somebody accidentally runs it on my production server it would drop all my tables and re-create them. So one question I'm asking is e.g. how do my scripts look like that I am actually committing to my e.g. git repository? Do I have different versions of that file? One that is e.g. a "*development*"-version and another is a "*I am confident it doesn't break anything*"-version that I commit? Another option could be that I e.g. just create the tables in the first step: -- V1__create-company-tables.sql CREATE TABLE company ( id BIGSERIAL PRIMARY KEY ); CREATE TABLE company_employee ( id BIGSERIAL PRIMARY KEY ); and in a second file I add the foreign keys: -- V2__adding-company-tables-foreign-keys.sql ALTER TABLE company_employee ADD COLUMN company_id BIGSERIAL, ADD CONSTRAINT fk_company_employee_company FOREIGN KEY (company_id) REFERENCES company(id), But that would mean that I'll never have all in one file - just fragments of my database in different files V1 to Vn. Another thing I could do is to just use pgAdminIII and add everything I need, then reverse engineer my database, run it with FlywayDB and commit that *generated* file to my database. But I don't like that idea since pgAdminIII uses deprecated stuff like WITH ( OIDS=FALSE ). So well, I would be glad if someone could bring me on track somehow and give me a scratch of how the actual development process of a database looks like. Should I use pgAdminIII or write my whole database by hand? How do I version my .sql files? Where do I store them on my local machine? pgAdminIII doesn't even seem to have something like a "Database Project" - it just offers access to modify my database but there is no source code management. I am really confused about how my setup would/should/could look like.

Does SQL Server SSDT Visual Studio 2017 Database Projects support Source control data for lookup tables, or do we still have to utilize messy Post-Deployment Merge Scripts for multiple tables? Redgate has a more finesse, solution. Wondering when MS will catch up. ### Related - Manage lookup data using SQL Server Data Tools (SSDT) (Benjamin Day Consulting) - Pre-Deployment And Post-Deployment Script - SQL Database Project (C# Corner)

Our SQL Agent jobs execute different DTSX packages at various steps. e.g:

\\DEVLserver\DTSX\package1.dtsx

I want to keep the agent job script in TFS but when I promote it is there any way to change or publish it as:

\\TESTserver\DTSX\package1.dtsx
\\PRODserver\DTSX\package1.dtsx

by using variables?

When creating a TFS Visual Studio 2017 solution with SQL Server 2016, should the solution contain 1. one database project per solution, or 2. multiple database projects in a single solution? Our system has databases which are interlinked/cooperate in creating one service. I believe the correct answer is "multiple databases in one TFS Visual Studio solution"; please validate. Thanks,

I am trying to figure out a way to have all of our random SQL scripts that we write under source control. I am already using SSDT for the database project. This is not for these sort of scripts. It is for all the other random queries that we run over time. Before we put them in a stored procedure. Or the other scripts that just don't really fit as a SP. How can I bring my non SSDT Sql scripts under source control using Visual Studio?

I have generated an EXPDAT.DMP file of an Oracle database with the command: exp userid=usr/pass@db owner=own rows=n compress=n I then ran the following command to generate a file containing the 'Create Table...' statements: imp userid=usr/pass@db full=y indexfile=output.sql This is great for the Create Table statements, but I also want the Create Procedure / Create Function / Create View source code. I can see them there in the .DMP file, but I haven't found a way to extract them. Is it possible to extract this information from the EXPDAT.DMP file? Or will I need to use another method? The purpose is to have the source exportable to files that can be source-controlled.

I am currently using Tortoise SVN to source control a .NET Web Application. What would be the best way to bring our SQL Server stored procedures into Source Control? I am currently using VS 2010 as my development environment and connecting to an off-premise SQL Server 2008 R2 database using SQL Server Data Tools (SSDT). What I have been doing in the past is saving the procs to a .sql file and keeping this files under source control. I'm sure there must be a more efficient way than this? Is there an extension I can install on VS2010, SSDT or even SQL Server on the production machine?

SSMS 2012 does not work with VSS 6. We installed modern source control, TFS 2010 server. Then I Installed Team Foundation Server MSSCCI Provider 2010. However the only choice for Source control plug-in in SSMS 2012 is still VSS6. My VS2010 works just fine, I could connect to TFS in a team explorer. How to add TFS as a second source code control plugin in SSMS 2012?

We are overhauling the way that we store our database in source control and keep a change log of it. I was reading the following article: http://thedailywtf.com/articles/Database-Changes-Done-Right , and in the Short Section of "The Taxonomy of Database Scripts" it describes the three types of scripts (QUERY, OBJECT, and CHANGE). I like the idea of generalizing scripts into these three categories but I'm wondering about the QUERY type. Questions: - Why would someone want to put a SELECT statement into source control outside of an object? - The database will change afterwards and make the QUERY script unusable, what then? - The data may change returning a different result set, this would defeat purpose of source control, what then? - Would the original result set have to be saved to solve the 2nd and 3rd issues? - What is an example of a SELECT statement that might be put into source control? - Wouldn't a INSERT statement work better and store the results in a table as in baselines? I just can't see the purpose of storing SELECT statements into source control. If there is a purpose could someone please answer the above questions and maybe state the pros and cons of storing a SELECT statement into source control?

The database our developers are working on is too large (have a lot of database objects). We have to control db objects changes (change management). Our company cannot have a person who would be responsible for db changes only. So we need a source safe for database objects, something like version control for standard code, but more related to database, that can synchronize database and scripts. What is the best one. Reliable, Cheap, Functional - choose the two ones :)

We have on several databases on same instances, some stored procedures have references to tables on other databases. We are in the middle of a big project to take our database under source control. Creating the baselines is not an problem. But after baseline creation, when trying to deploy on other environments, say a local developer instance or staging or alike, this cross reference issue is causing us several headaches. After doing some research and checking on possible approaches I still can't find a reliable way of managing this issues. One approach proposed is to split baseline creation into "modules" that could be run individually so the cross referenced objects remain in one single script and be run alone. But what if the referenced objects on the other database has changed, then how I can track those changes? Maybe an index was added on referenced table and directly affects performance on the current sp? Or for whatever reason the referenced table changed column name, even deleted that column that is referenced and added a different one? What a nightmare. So, question is, is there a recommended solution for this issue? For solution I mean, methodology, step-by-step approach, or whatever else that helps getting close to a solution with the deploying issue when having cross reference between database objects. And for the moment, removing the database cross references is a no go. Way to much application dependent stuff to be corrected/updated and can't be done for the moment. Not completely sure if this question follows the standards, as it can maybe trigger some opinion based answers. But I don't have more information to throw in for the moment. If you feel it should be flagged, then go.