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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am using SQL Server 2008, I have a big database of almost 500 MB. Now I wanted to upload or deploy this DB to the Live server. Currently I am generating scripts and then uploading them to live and by command line I am deploying the database.

This took almost 2 hours so that's very cumbersome to do and one more problem is also that I am not able to use import export database option cause it loses constraints like primary key and all and also gets an error while inserting record from the same process.

Can you tell me how can I do all this and with proper data transfer.

share|improve this question
If you're using backup and restore, make sure you are using the WITH COMPRESSION switch with the backup. – wBob Mar 3 '13 at 14:42

You can use backup/restore. Take a backup of your dev db and restore it on the live server. Should be a bit faster than 2 hours.

share|improve this answer

The fact that you're trying to deploy the database is the problem. Your deliverable should had been not a database, but the deployment script. Right now you probably hand crafted a database in development and are trying to copy it to production. think about what will happen when you will want to deploy the first update of your application into the live system. You won't be able to copy the database anymore because you'll loose the live data.

Re-architect your project to use migrations instead of modifying a dev database and then looking of ways to copy the changes into produciton.

share|improve this answer

If you can take the original database offline,do that and then:

  1. Copy the MDF andf LDF files
  2. Move them to the new server
  3. Attach them on the new server - right click the Databases node
  4. Select Attach

enter image description here

5 Click Add

enter image description here

6 Find the MDF file

7 Click OK

enter image description here That should be it

share|improve this answer

At your development server use this query to create backup:

backup database <dbname> to disk='full qualified of backup file.' 

for eg:

backup database library_db to disk='c:\backups\library_bak'

then upload this back file to your live server and then use this query to restore the backup:

restore database <dbname> from disk='full path of your backup file' with replace;

Note: you can even compress the backup file to minimize the upload time.

Hope this helps.

share|improve this answer

Your Answer


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.