Sql Server question - how do you copy a database?

[notfred]

This seems like it should be easy, but I can't figure out how to do it. I have a database set up on a development machine, I want to copy the entire thing - data, definitions, stored procedures, etc. To another machine running the same version (2000) of SQL Server.

How do I do that?

 

[quikah]

http://support.microsoft.com/default.aspx?scid=kb;en-us;314546

 

[znaps]

It is kinda funky, but there are a couple of ways to do it. The easiest way is to detach the Db on the source, then copy the MDF and LDF files to your dest, and attach.

 

[shutterFly]

The easiest way I've found is to use the Import and Export Data Wizard that comes with SQL. It basically takes you through the steps of copying data from one DB to another. Selec the source, destination, the objects (tables, stored procedures, functions, etc.) and it does everything for you.

 

[HeroOfPellinor]

Backup the database, create the new database, then restore the backup to the new database. There are some quirks, like having to rename the paths to make sure it doesn't overwrite the old database, but it's covered on Microsoft's site.

 

[znaps]

Originally posted by: shutterFly
The easiest way I've found is to use the Import and Export Data Wizard that comes with SQL. It basically takes you through the steps of copying data from one DB to another. Selec the source, destination, the objects (tables, stored procedures, functions, etc.) and it does everything for you.

That's messed up on me more times that I can remember..may have been due to network issues, but it's slower than the detach/copy/attach method for me. The advantage in yours is that you don't need access to the filesystem of the machine.

 

[Snapster]

If you have access to the file system detach, copy, attach like znaps said although remember that you may need to redo the logins if moving to a different machine, but that's a small price to pay.

If you don't you're going to have to use the data transfer wizard, which pretty much requires all things to be perfectly defined (stored procs definitely!) before it'll successfully copy, also you'll need to be able to access the database you are copying from on new machine for you to be able to copy it.