[SOLVED] How to copy a SQL 2005 database to another computer?

Question

I'm trying to rebuild a web server in a virtual pc. Installed required software and Microsoft SQL Server 2005. I've had full backups of my databases but it is not possible to restore from those backups in this new installation. By the way, backups are fine, i can restore from them on original server.

In summary, how can I copy whole database to another computer? How can I backup a database and restore is fully on another computer?

Regards, Burak

PS: The database I'm trying to restore is called "Some_db" and it does not exist on new server. I also tried to create a new one with defaults and restore on it but that gave an error on new db. I don't know any details of the database.

Answer 1

1. Backup file
2. Copy .bak file to other server
3. Restore .bak file.

Works every time for me. You have to make sure the new instance of SQL is of a sufficient level to be able to host the database. You also have to recreate the users on the new server and remove and readd them to each database.

Answer 2

You have 3 Choises:

1) Restore DB as you tried

• This failed for you for some reason
• Here a tutorial: http://msdn.microsoft.com/en-us/library/ms177429.aspx

2) Detach DB and Atach it to the new server

• This is easy: Rightclick on the DB -> Detach.
• Rightclick on the new Server -> Attach
• Tutorial: http://www.databasedesign-resource.com/moving-the-database.html

3) Create Create-Scripts of the table schema and use insert into statements for the data. For this, there are a bunch of tools, my favourit is "Redgate SQL Compare". It creates you all nessecary scripts.

• The link: http://www.red-gate.com/products/SQL_Compare/index.htm

Answer 3

Or take offline, copy files, attach database, bring online..

Answer 4

The alternative to the backup and restore approach is to detach the db, copy the mdf and ldf files over and then attach them on the new server

Answer 5

open your current query browser window and run the query .The restore will happen automatically

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AdventureWorks.bak'
GO

for more details

This will create a .bak file after that copy the .bak to server.

restore the data base by right click on DataBase .Select restore database .Give the database name and location .And restore it .