Easiest way to copy an MSSQL database

komodosp

What is the handiest way to do this? I have been trying to do it in SQL Server Management Studio for ages and something is always standing in my way...

Anything like mysql's mysqldump > followed by mysql < to read back in the dump?

Right-clicking on Database and using "Generate Scripts" exports the structure only.

When I try to use "Export Data", I get various errors (e.g. Duplicate Object) during the actual export process, as if there's errors with the data (which I don't get as it's copying from an existing database!). Then it exceeds the MaximumErrorCount and I can't find where to increase that...

The best I can think of to do is do it again skipping all tables processed so far and sort of continue where I left off. Not really a decent solution since I am surely missing stuff doing it this way.

Edit

I've just tried to create a new database using the script generated by "Generate Scripts" and I get a syntax error? How can this happen? I'm using the script that SQL Management Studio generated!
It says "Incorrect Syntax near '90'" on Line 1 - the only reference to "90" I can find is on line 9,
ALTER DATABASE [MyCopy] SET COMPATIBILITY_LEVEL = 90

*shakes head*

amen

Why not just backup the database ?

Right click on the database and select Tasks->Backup ?

I've just tried to create a new database using the script generated by "Generate Scripts" and I get a syntax error? How can this happen? I'm using the script that SQL Management Studio generated!
It says "Incorrect Syntax near '90'" on Line 1 - the only reference to "90" I can find is on line 9,
ALTER DATABASE [MyCopy] SET COMPATIBILITY_LEVEL = 90

Are you running the script on the same instance of SQL Server or an instance on a different machine ?

Open a query window in each instance of sql server and run the following:

[PHP]SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')[/PHP]

this will you the server version.

If they are the same we can try and help.

Have you created the database in which to run the script i.e. if the script refers to a database MyCopy does that database actually exists ?

If no then the error is correct as you are trying to alter an object that does not exist.

There are many options to select when scripting a database and you may not have selected all of the correct one or understood what you are selecting.

jester77

I presume you are trying to copy a DB from one machine to another?

I can't remember but I think you need to create an empty DB on the new server beforehand.

mysqldump -u user -p DBNAME | ssh loginuser@newserveraddress mysql -u user -p DBNAME

ozmo

As already suggested - backup is the way to go - Or if you can stop the MSSQL server - detach the database - locate the .mdf * .ldf files (see properties on the database) and just copy these files to the new machine and use SQL Server management Studio on the other side to reattach it. Quickest way.

Procasinator

As others have mentioned, a backup or re-attaching the DB to the new server is probably what you are after.

However, it is possible to use Generate Scripts to and export DML for Schema and data.

On the 3rd page of the Generate Scripts, you are on a pane called Set Scripting Options. Click the Advanced button, and under the General heading you'll find a "Types of data to script" option. By default this is Schema only, but you can change it to "Schema and data".

stevenmu

As mentioned, backup/restore and file copy/attach are the best ways.

Another good way worth knowing is DTS and exporting/importing the DB. You can right-click on the source database and choose export, this will give you a wizard that lets you pick how and where to export to, or equally you can start an import at the destination server. If the two SQL Servers can see each other, you can do a direct export/import from one to the other. If not you can use export to intermediary files (text, csv, excel etc) and import at the other end. If this is something you need to repeat in the future, you can save the package and run it whenever, or even schedule it to run at set times. And if you need more advanced functionality you can even export it as an SSIS solution and do any advanced transformations you might need.

It might not be as quick and easy as a backup/restore or a file copy and attach, but it's an often overlooked but very useful and powerful piece of functionality.

L8rdude

Not sure if you got sorted, but I use

In sql server mgnt studio, this script

backup database <dbName> to disk = 'c:\myFolder\myDbName.bak'

Back in sql server mgnt studio

Right click Database > restore Database > Set the Source to Device ( 'c:\myFolder\myDbName.bak' ) and complete the rest of the details.

[-0-]

I have always dumped the database to a .csv file and imported it on the other machine.

seamus

[-0-] wrote: »

I have always dumped the database to a .csv file and imported it on the other machine.

Doesn't preserve indexes and stored procedures and other such stuff though.

Proper backup/restore or copy/attach are the most efficient ways. Backup/Restore has more compatibility as you will often not be able to attach a database to a different version of SQL Server, but you can almost always restore a backup, provided the destination is >= the version of the source.