How do I copy a SQL Server database

Question

Hello,

I was wondering what my options are for copying a SQL Server database. If this is a repeat I'll delete my question but I couldn't find an answer when using the search.

This is what I already tried:

-Write the database to a sql script.
When I do this I always seem to run into a problem where I have to manually adjust stuff. Like problems with keys or collation conflicts.

Also this seems very slow. My last script file is 1.5Mb but it takes like 1 hour to run. It's also annoying to run into an error at 90%.

I mostly get it to work using a sql scripts but it feels like I am wasting a lot of time trying to fix little error and waiting for the sql to execute.

-Use the export function on Microsoft SQL Server Management Studio
If I am correct this doesn't copy store procedures, views and/or keys.

-Detach / Attach
Is it correct to think that this is mostly not possible on shared hostings where you only get one database? I ran into permission problems when trying this.

That's about everything I tried.

Thanks in advance,

Pickels

Answer 1

I find the easiest way is to use the Backup / Restore option. Backup the database, copy the file to the new server and then do a restore. You need to create a blank database first on the second server but you don't need to define any of the tables or anything. Then, right-click on the new blank database and select Restore and restore from the file you backed up on the first server.

When you do the restore, you might need to go to the Options page and set the Restore As path if your original database files were stored in a path that doesn't exist on the new server or you want to restore to a different location. By default, it will try to restore the database to the same path as it was on the original server.

Answer 2

Try Full backup and restore.

Answer 3

If you are going to migrate to a MS SQL Server 2008, you have the option to "Copy Database" you can chose to copy all or selected databases.

Right click on a database -> Tasks -> Copy Database. Go through the wizard and choose either "Detach and Attach"-method or "SQL Managment Method" (if you want the database to remain online).

But note that this only works if your destination SQL is MsSQL 2008.

Answer 4

It depends on what your hosting provider allows. Some allow attach/restore, some don't, some allow it but charge per-incident. Even when is allowed, the database version is extremly important, as the engine is capable of upgrading a database but it cannot downgrade the version, so your development version can be at most the provider's version, not newer.

But ultimately this is a problem of development process. You should never develop by modifying the database, but instead create scripts, kept under source control, to apply any application schema change (including all procedures and all catalog data). The application should be capable of upgrading the database schema from these scripts, including the initial deployment. My recommendation is to use an application defined version in the extended properties and have scripts to update the application schema version: Version Control and your Database.

But now that the cat is out of the bag and you already did the bad practice, your best bet is a good 3rd party scripting/compare utility, like SQL Compare.

Answer 5

Hey mate,

Try DBSourceTools. http://dbsourcetools.codeplex.com
Its open source, and specifically designed to script an entire database - tables, views, procs to disk.
Then all you need to do is to create a deployment target.
Click on deploy - and the tool will copy your database to the new target.
By using SQL Bulk copy to upload data, it can do 10000 records in a few seconds.

Have fun