Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 (SSMS):

  1. 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 sqlcmd.exe and from SSMS. Moreover, I have to remember to script triggers and I am never sure whether the scripts generate identical database.
  2. I tried using the Back Up option 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.

  3. 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.

share|improve this question

3 Answers 3

up vote 2 down vote accepted

If you want the schema and the data, the best and safest way is a copy_only backup. Don't like the backup history that comes along in the MSDB tables? Ok, delete it, then start a new backup chain by performing a full backup once the copy is restored on the new server.

If you just want schema, there are plenty of options. I prefer RedGate's SQL Compare, but there are plenty of alternative tools, including SSDT which (I think with the most recent update) has schema compare functionality.

share|improve this answer

Since you are a programmer, not a DBA, I would recommend the correct programmer's way to do such things.

Don't update the database directly - never. Maintain all your schema changes, and base data changes in files which are then pushed with your release.

I don't know what your language of choice is, but there are many tools to do this, Ruby has it's own tool - rake - with which you do that. And here are two others:

share|improve this answer
    
Do you know any solution for C# ? –  IvanP Apr 20 '13 at 7:00
1  
These would technically work as they don't particularly relate to your code. flywaydb for example does not care what language you write your app in. Your SQL datatabase updates are a separate matter. –  ETL Apr 20 '13 at 16:12

If you prefer using the GUI, you could handle this problem with SSIS (Integration Services). Create a new package with a single task, the Database Transfer Task. Once done and saved, you can automate this process with the Agent. You could launch this package every night for example and keep your different environments up to date.

There are also many other ways to do so... I have made the assumption that your environments do not have to be in synch all day long (for example, a dev and production platform).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.