CODEWITHSUNDEEP
sql-serverdatabaset-sqlazure
Serguei Fedorov
Serguei Fedorov

Reputation: 7923

Synchronize .MDF File With Server Database T-SQL

I have a website which runs in Azure, using an Azure database. My development database however is an .mdf file. Because the data is constantly changing in the Azure database, I would like to be able to grab an snapshot of the data in order to have a newer representation of the data in my local .mdf file. Because my code also inserts data into the database, I cannot simply connect to the production database from my local machine.

The reason I have an .mdf file is because I can package it with the code in source control. That way I do not have to provide an external test database when I move between machines (I am the only developer).

Is it possible to somehow convert a database in SQL Server database in Microsoft SQL Server Management Studio to a .mdf file? Or have a query which synchronizes the data by copying it out of one and into the other database?

One of the reasons I want to be able to synchronize the Azure database with my .mdf file is so that I do not have to package in source control (they can get pretty big), but rather generate one any time I need an .mdf database to test with. I would rather not have a remote test database to connect to for development if I do not have to.

It seems like I have over complicated things for myself. Are there better approaches to this than using an .mdf database? Is there a different approach to duplicating a production database for testing?

Upvotes: 1

Views: 594

Answers (1)

Ed Elliott
Ed Elliott

Reputation: 6856

This is what I would do:

  • Store your database in an SSDT database project
  • To do this, download ssdt (https://msdn.microsoft.com/data/hh297027) you will either need to have visual studio or can use visual studio express
  • Import your database, you could attach your .mdf and use the import wizard to get your code out (right click on the ssdt project and import-->Database)
  • If you have any static data or reference data then use sp_generate_merge (https://github.com/readyroll/generate-sql-merge) to generate a merge statement for each table - add these to a post deploy script in the project
  • When you want to deploy either use the publish functionality in SSDT or sqlpackage.exe - I would make sure you test this thoroughly before pushing to your production database.
  • When you want to test on a new machine just publish your code to your new dev instance and start using it - you might want to have a separate script for setting up some test data etc

Doing this will mean that you can actually track changes in source control and you get a FREE automatic deployment system.

What this won't give you is the snapshot of the production data, to get that you could create an ssis package that copies all the tables you need - but for testing you should be able to (and indeed many of us do) just use a clean database to develop / test on and just pull down specific bits of data when we are debugging issues.

Upvotes: 3

Related Questions