i have website runs in azure, using azure database. development database .mdf file. because data changing in azure database, able grab snapshot of data in order have newer representation of data in local .mdf file. because code inserts data database, cannot connect production database local machine.
the reason have .mdf file because can package code in source control. way not have provide external test database when move between machines (i developer).
is possible somehow convert database in sql server database in microsoft sql server management studio .mdf file? or have query synchronizes data copying out of 1 , other database?
one of reasons want able synchronize azure database .mdf file not have package in source control (they can pretty big), rather generate 1 time need .mdf database test with. rather not have remote test database connect development if not have to.
it seems have on complicated things myself. there better approaches using .mdf database? there different approach duplicating production database testing?
this do:
- store database in ssdt database project
- to this, download ssdt (https://msdn.microsoft.com/data/hh297027) either need have visual studio or can use visual studio express
- import database, attach .mdf , use import wizard code out (right click on ssdt project , import-->database)
- if have static data or reference data use sp_generate_merge (https://github.com/readyroll/generate-sql-merge) generate merge statement each table - add these post deploy script in project
- when want deploy either use publish functionality in ssdt or sqlpackage.exe - make sure test thoroughly before pushing production database.
- when want test on new machine publish code new dev instance , start using - might want have separate script setting test data etc
doing mean can track changes in source control , free automatic deployment system.
what won't give snapshot of production data, create ssis package copies tables need - testing should able (and indeed many of do) use clean database develop / test on , pull down specific bits of data when debugging issues.
