SQL Server / Update DB?

I have a db on a 2012 server. I have the same db on a 2016 server. I made changes to the one on the 2016 server. Now I want to update the 2012 with my 2016.
I have rights to this specific db on the 2012 but I'm not sure if I remove this db that I would right to create it again. On the 2016 I have full rights for everything.
How do I do this?
LVL 1
Jess31Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
What do you want to update?
Whole database? Some stored procedure or function? Some tables? Data only?
Jess31Author Commented:
any stored procedures or function that have been added or changed, same with tables
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you want to perform this only once, then best thing to do is to script the changed objects and run the script in the SQL Server 2012 instance.
If you want to perform this in a regular way, set a Replication between SQL Server 2016 database and the respective DB in SQL Server 2012. When you have a new object just add it to the articles so it can be replicated.
CEOs need to know what they should worry about

Nearly every week during the past few years has featured a headline about the latest data breach, malware attack, ransomware demand, or unrecoverable corporate data loss. Those stories are frequently followed by news that the CEOs at those companies were forced to resign.

Read more
Jess31Author Commented:
I tried using the script database. But when I run it on the 2012 I get these errors "There is already an object named..." Is there away to have the script override any existing object?
Dung DinhDBA and Business Intelligence DeveloperCommented:
If you script changed objects from SQL Server Management Studio, you should script with DROP and CREATE option. But if an object is existing on 2012 and some users are granted permissions on the object, you will need to grant again after updating.

If 2012 database is still used for LIVE environment, you should have a backup strategy for the objects before modifying them.
Jess31Author Commented:
Is it possible to create bacpac on 2016 and import it in 2012?
Anthony PerkinsCommented:
Or you could simply purchase SQL Compare from Red-Gate and let that app take care of all the dependencies.
Vitor MontalvãoMSSQL Senior EngineerCommented:
I tried using the script database. But when I run it on the 2012 I get these errors "There is already an object named..." Is there away to have the script override any existing object?
You can configure the script to bypass or drop existing objects. What do you prefer?
Jess31Author Commented:
I recreated the db on the target server. I created the scripts and ran it on the target. The Tables and stored procedures/functions seems to have been creted.

But I got errors on these lines in the script and the diagram didn't get created

ALTER DATABASE [ShipBill] SET TRUSTWORTHY OFF
ALTER DATABASE [ShipBill] SET HONOR_BROKER_PRIORITY OFF
ALTER DATABASE [ShipBill] SET DB_CHAINING OFF
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
Vitor MontalvãoMSSQL Senior EngineerCommented:
Seems to me those commands aren't compatible with lower versions of SQL Server. You can just remove them from the script.
Or if you wish, when generating the script, click the Advanced button and then choose the target SQL Server version so the script will run without error.
Generate-Scripts.PNG
Jess31Author Commented:
the script I created I had set to version 2012. I also set my db to compatibly 120 which is for 2012
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's strange because for example, this command only exists in SQL Server 2016 as you can state by the MSDN article.
Jess31Author Commented:
I just tried generate scripts again, using 2012 target, but this time only fore Schema and it put this scopped configuration in as well.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Might be some kind of bug then. Can't you just remove those lines from the script?
Jess31Author Commented:
I had done that and all seemed fine. But I'm wondering why the Diagrams didn't get created. Is it not suppose to be?
Vitor MontalvãoMSSQL Senior EngineerCommented:
You are only script the objects and not importing the data, right?
Check if table sysdiagrams has records or it is empty.
Jess31Author Commented:
I generated scripts for both Schema and Data. Both seemed to import fine despite the errors mentioned about.
sysdiagrams in my source contains 5 rows, in the target 0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Only sysdiagrams didn't import the data? The rest of table has imported data? That's weird.
You can always import the data manually using the Import/Export wizard:
ExportData.PNG
Jess31Author Commented:
Yes, only sysdiagrams didn't import.
The Export data you show - can I use it to just export diagrams?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, you can. Just choose the table instead of all objects.
Jess31Author Commented:
Export Data wants me to specify another db or such like to export it to. But the db to which I am looking to copy it is on another non connected computer.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, it needs to know the target. If you can't reach the other database then export it to a .csv or .txt file and then copy the file to the other computer and use the same wizard to import from the file into the database.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.