I am using
SSMS (I am a software developer, not a DBA) and in each application release I need to release the database with its tables (some with data some with scheme only).
I tried doing 3 things (
Using Database --> Tasks -> Generate scripts...This is fine, for small databases, when the scripts is over 200 MB, users reports memory problems, both when running
SSMS. Moreover, I have to remember to script triggers and I am never sure whether the scripts generate identical database.
I tried using the
Back Upoption but I guess this option is intended for backup and not export data, it prevents me from exporting only the scheme for example. Back ups also keep histories for differential restores which is not what I intend.
I tried out
Script Database as... CREATE To... but these doesn't keep triggers and data. Moreover, this script use hardcoded paths to .mdf and .idf which changes from a server to server.
What is the correct approach to replicate a database to another server ?
P.S. I am new to this Stack Exchange website, I read several question but didn't find an answer after looking on several questions.