January 19, 2018 at 7:18 am
Hi
I have a task to make sure that the prod and dev are in snync. How can i handle it, besides taking the full backup of Prod and restore in Dev?
Thank you
January 19, 2018 at 7:21 am
There is a tool from Redgate called SQL Compare that can do this. I'm not aware of any free products but someone else might be.
Thanks
January 19, 2018 at 7:34 am
You are a superstar!
January 19, 2018 at 7:44 am
NorthernSoul - Friday, January 19, 2018 7:21 AMThere is a tool from Redgate called SQL Compare that can do this. I'm not aware of any free products but someone else might be.Thanks
SQL Compare compares database structure, not data.
SQL Clone (also from Redgate) may be of interest.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 19, 2018 at 7:48 am
Phil Parkin - Friday, January 19, 2018 7:44 AMNorthernSoul - Friday, January 19, 2018 7:21 AMThere is a tool from Redgate called SQL Compare that can do this. I'm not aware of any free products but someone else might be.Thanks
SQL Compare compares database structure, not data.
SQL Clone (also from Redgate) may be of interest.
Thanks for clarifying Phil.
January 19, 2018 at 10:24 am
tmmutsetse - Friday, January 19, 2018 7:18 AMHi
I have a task to make sure that the prod and dev are in snync. How can i handle it, besides taking the full backup of Prod and restore in Dev?
Thank you
I would recommend using the Visual Studio SSDT and create a SQL Project for all your db objects. Not only does it include a compare tool, but it allows your code to be stored in a repository (SVN or TFS) and allows you automate your database builds and deploys using msbuild, which builds your project just like other Visual studio projects, it checks for errors.
I used to use Redgate, but now I will never look back, SSDT is the way to go!
January 19, 2018 at 10:31 am
morbius82000 - Friday, January 19, 2018 10:24 AMtmmutsetse - Friday, January 19, 2018 7:18 AMHi
I have a task to make sure that the prod and dev are in snync. How can i handle it, besides taking the full backup of Prod and restore in Dev?
Thank youI would recommend using the Visual Studio SSDT and create a SQL Project for all your db objects. Not only does it include a compare tool, but it allows your code to be stored in a repository (SVN or TFS) and allows you automate your database builds and deploys using msbuild, which builds your project just like other Visual studio projects, it checks for errors.
I used to use Redgate, but now I will never look back, SSDT is the way to go!
This method does not sync data. I guess it depends on exactly what the OP meant by 'in snync' (sic)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 19, 2018 at 11:04 am
Yes it will synch data if you use a publish script. Redgate works for quick and dirty, but if you want to do it write, use SSDT imho.
January 19, 2018 at 11:05 am
Phil Parkin - Friday, January 19, 2018 10:31 AMmorbius82000 - Friday, January 19, 2018 10:24 AMtmmutsetse - Friday, January 19, 2018 7:18 AMHi
I have a task to make sure that the prod and dev are in snync. How can i handle it, besides taking the full backup of Prod and restore in Dev?
Thank youI would recommend using the Visual Studio SSDT and create a SQL Project for all your db objects. Not only does it include a compare tool, but it allows your code to be stored in a repository (SVN or TFS) and allows you automate your database builds and deploys using msbuild, which builds your project just like other Visual studio projects, it checks for errors.
I used to use Redgate, but now I will never look back, SSDT is the way to go!
This method does not sync data. I guess it depends on exactly what the OP meant by 'in snync' (sic)
Yes it will synch data if you use a publish script. Redgate works for quick and dirty, but if you want to do it right, use SSDT imho.
January 19, 2018 at 12:15 pm
morbius82000 - Friday, January 19, 2018 11:04 AMYes it will synch data if you use a publish script. Redgate works for quick and dirty, but if you want to do it write, use SSDT imho.
I use database projects and SSDT all of the time ... have done for years.
SQL Clone is not a 'quick and dirty' solution ... have you taken the time to read about it?
The only data contained in my publish scripts is that contained in post-deployment scripts (mostly static lookup data).
If you are using publish scripts to somehow sync the data in your development database with what is contained in production, please share details of the process which you are following.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 19, 2018 at 1:07 pm
morbius82000 - Friday, January 19, 2018 11:04 AMYes it will synch data if you use a publish script. Redgate works for quick and dirty, but if you want to do it write, use SSDT imho.
I'm going to argue with you a bit on this.
We've got a VERY thorough suite of tools for doing soup-to-nuts database development and deployment. You can go from source control through all the environments using our tool set. Further, I'd say overall that we offer quite a bit more functionality than SSDT. Our scripts are safer and we have mechanisms for dealing with data breaking changes that simply causes SSDT to just stop. SSDT is also on a much slower maintenance & upgrade schedule than we are.
Then you start talking about all the other tools available in the suite such as SQL Prompt, SQL Clone, the new data privacy & protection tools, and our offering far exceeds the capabilities and capacities of SSDT.
All that said, SSDT is a very good piece of software, we're just better.
"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 19, 2018 at 1:57 pm
Phil Parkin - Friday, January 19, 2018 12:15 PMmorbius82000 - Friday, January 19, 2018 11:04 AMYes it will synch data if you use a publish script. Redgate works for quick and dirty, but if you want to do it write, use SSDT imho.I use database projects and SSDT all of the time ... have done for years.
SQL Clone is not a 'quick and dirty' solution ... have you taken the time to read about it?
The only data contained in my publish scripts is that contained in post-deployment scripts (mostly static lookup data).
If you are using publish scripts to somehow sync the data in your development database with what is contained in production, please share details of the process which you are following.
Well it's a more complicated process. I agree with you, I only change data in the static lookup data through in the post deployment script. We have several environments, DEV,QA,UAT and Production and different repository branches. We use the publish scripts to synch the database objects and make changes to lookup data. It's all automated and works great.
January 19, 2018 at 2:01 pm
morbius82000 - Friday, January 19, 2018 1:57 PMPhil Parkin - Friday, January 19, 2018 12:15 PMmorbius82000 - Friday, January 19, 2018 11:04 AMYes it will synch data if you use a publish script. Redgate works for quick and dirty, but if you want to do it write, use SSDT imho.I use database projects and SSDT all of the time ... have done for years.
SQL Clone is not a 'quick and dirty' solution ... have you taken the time to read about it?
The only data contained in my publish scripts is that contained in post-deployment scripts (mostly static lookup data).
If you are using publish scripts to somehow sync the data in your development database with what is contained in production, please share details of the process which you are following.Well it's a more complicated process. I agree with you, I only change data in the static lookup data through in the post deployment script. We have several environments, DEV,QA,UAT and Production and different repository branches. We use the publish scripts to synch the database objects and make changes to lookup data. It's all automated and works great.
And what about changes to transactional data?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 19, 2018 at 2:53 pm
Phil Parkin - Friday, January 19, 2018 2:01 PMmorbius82000 - Friday, January 19, 2018 1:57 PMPhil Parkin - Friday, January 19, 2018 12:15 PMmorbius82000 - Friday, January 19, 2018 11:04 AMYes it will synch data if you use a publish script. Redgate works for quick and dirty, but if you want to do it write, use SSDT imho.I use database projects and SSDT all of the time ... have done for years.
SQL Clone is not a 'quick and dirty' solution ... have you taken the time to read about it?
The only data contained in my publish scripts is that contained in post-deployment scripts (mostly static lookup data).
If you are using publish scripts to somehow sync the data in your development database with what is contained in production, please share details of the process which you are following.Well it's a more complicated process. I agree with you, I only change data in the static lookup data through in the post deployment script. We have several environments, DEV,QA,UAT and Production and different repository branches. We use the publish scripts to synch the database objects and make changes to lookup data. It's all automated and works great.
And what about changes to transactional data?
Those are one offs and handled seperately, either automated or manually by a DBA if small.
January 19, 2018 at 2:56 pm
Grant Fritchey - Friday, January 19, 2018 1:07 PMmorbius82000 - Friday, January 19, 2018 11:04 AMYes it will synch data if you use a publish script. Redgate works for quick and dirty, but if you want to do it write, use SSDT imho.I'm going to argue with you a bit on this.
We've got a VERY thorough suite of tools for doing soup-to-nuts database development and deployment. You can go from source control through all the environments using our tool set. Further, I'd say overall that we offer quite a bit more functionality than SSDT. Our scripts are safer and we have mechanisms for dealing with data breaking changes that simply causes SSDT to just stop. SSDT is also on a much slower maintenance & upgrade schedule than we are.
Then you start talking about all the other tools available in the suite such as SQL Prompt, SQL Clone, the new data privacy & protection tools, and our offering far exceeds the capabilities and capacities of SSDT.
All that said, SSDT is a very good piece of software, we're just better.
I will admit I have not dealt with Redgate's SQL Clone, I was just commenting on SQL Compare, which is a good tool and does have one advantage over SSDT in that it will create reports of changes. I have used it extensively and before I found SSDT it was indispensible, but if you are a VIsual Studio shop, SSDT integrates so much better using msbuild and the versioning is so much nicer than imbedding it in SSMS.
Still, to each his own, if you like Redgate, by all means use it!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply