How to restore a DB to a differenet SQL server

Question

Post reply

How to restore a DB to a differenet SQL server

techzone12

SSCommitted

Points: 1798
More actions
December 12, 2010 at 6:59 pm

#230431

What is the easiest/fastest way to restore a 20 GB database residing on one server to a different server?
Should I use backup/restore. Or detach/attach, or..?
I am new to this. I appreicate any advice..

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Dave Klug SSCertifiable Points: 5811 More actions · Answer 1

Dave Klug

SSCertifiable

Points: 5811

December 13, 2010 at 6:58 am

#1261676

Use backup/restore...

bjhogan SSC Eights! Points: 820 More actions · Answer 2

I believe this will grow to be a personal preference. We often use the backup/restore method as it doesn't create any downtime. However, you do have to have the extra space on the server to create the backup file.

The detach/attach method will be faster because you don't have to worry about the time to take a backup. In this case there will be downtime when you detach the database and copy the files over to the new server.

This will completely depend on your SLAs and your infrastructure. Just take a few minutes to test each scenario to see which one works best for you.

techzone12 SSCommitted Points: 1798 More actions · Answer 3

Thanks for the feedback. Yes we can afford few hours of downtime

So possibly the detach/attach is the way to go. I am going to

Try.

krypto69 SSChampion Points: 13528 More actions · Answer 4

I would use the backup/restore method...less risky...no chance of the file not re-attaching.

techzone12 SSCommitted Points: 1798 More actions · Answer 5

restore database mydb

from disk = 'd:\data\backup\mydb.bak' with replace

will overwite database at destination server..

bjhogan SSC Eights! Points: 820 More actions · Answer 6

Typically I would just give the answer, however, I think this is a good learning opportunity. Just checkout the RESTORE DATABASE syntax and the REPLACE option.

Meet George Jetson SSChampion Points: 11241 More actions · Answer 7

Try something like the following:

Use Master

Alter Database zReports

SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE mydb FROM DISK = 'x:\backup\mydb.bak'

WITH REPLACE

GO

Chris Powell

George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.

techzone12 SSCommitted Points: 1798 More actions · Answer 8

yes using "with replace " did the trick..

By the way, the speed I get answers that wrok at this forum is amazing..

Thanks everyone..