How can I copy a MS SQL database (d

[TigerWood]

How can I copy a MS SQL database (data and objects) from one server (MS SQL Server) to another?

 

[fluteplr]

Backup and Restore is what I usually use.

You can also detach the database, make a copy and attach the copy to the new server.

 

[tlbroadbent]

See the following.

INF: Moving SQL Server Databases to a New Location with Detach/Attach

http://support.microsoft.com/default.aspx?kbid=224071

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[TigerWood]

Thanks guys for your solution. What I really need is to make a copy (not move) to a new server.

I tried the following two method and both failed.

1. I made a back up of the database and tried to restore it to the new server that has the same database name and same size of the original database.

2. I made a copy of the database and replaced the files (Data file and log file) in the same directory name with the same database name. But this did not work neither.

Any other idea?

 

[fluteplr]

I do this at least 20 times a week and it works just fine.

Did you get an error message?

Are both servers the same version of SQL Server?

Did you change the drive\directory mappings for the data and log files if needed?

There does not need to be a database of the same name and size on the new server, if fact it is easier if there is not. Just delete the database on the 'new' server.

When you are doing a restore look at the 2nd page of the restore options that is where you can change the file locations. Make sure that the file locations (drives and directories) actually exist on the new server.

 

[isonlyme]

Hi,

This is Another way, you can do a backup of prod then:

1 - Do a restore database AS and write a new name

2 - after it finish do a exoprt to pass all object from the database you just "restore as" to a new database, but don't transfer any users or grants.

Just another way to do it if anything else fails.

hth

 

[TigerWood]

fluteplr, Could you provide me with the precise steps to follow? Thank you.

 

[tlbroadbent]

The link I provided gives the steps. You can replace moving in the page title with copying if you prefer. The steps are the same.

Here is another link.

HOW TO: Move (or copy) Databases Between Computers That Are Running SQL Server

http://support.microsoft.com/default.aspx?kbid=314546

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm