CODEWITHSUNDEEP
sql-server-2005backuprestore
Ali Burak Kulakli
Ali Burak Kulakli

Reputation: 562

How to copy a SQL 2005 database to another computer?

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.

Upvotes: 2

Views: 7922

Answers (5)

anishMarokey
anishMarokey

Reputation: 11387

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 .

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147374

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

Upvotes: 1

Peter
Peter

Reputation: 49008

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

Upvotes: 0

PassionateDeveloper
PassionateDeveloper

Reputation: 15158

You have 3 Choises:

1) Restore DB as you tried

2) Detach DB and Atach it to the new server

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.

Upvotes: 6

cjk
cjk

Reputation: 46485

  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.

Upvotes: 1

Related Questions