which one is the fastest way of moving database form local to live server in sql server

Question

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.

Answer 1

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.

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

Answer 3

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

5 Click Add

6 Find the MDF file

7 Click OK

That should be it

Answer 4

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.