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 SQL Server 2008, I have big database almost 500 MB of data there. Now I wanted to upload or deploy this DB to Live server. Currently I am generating script and then uploading script to live and by command line I am deploying database.

But this took almost 2 hours to do that very cumbersome too. and one more problem is also there I am not able to use import export database option cause its loses constrains like primary key and all and also gets error while inserting record from 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

4 Answers 4

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

 
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.