0

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.

1
  • If you're using backup and restore, make sure you are using the WITH COMPRESSION switch with the backup.
    – wBob
    Commented Mar 3, 2013 at 14:42

4 Answers 4

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.

2

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.

1

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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