Restore .BAK to a new server

Question

Restore .BAK to a new server

chandrika5

SSCrazy

Points: 2734
More actions
October 26, 2006 at 1:06 pm

#121190

I have db backups taken from the maintenance plans ( sqlserver 2000 + windows 2003). The db is set to simple so the log file is not backed up. As part of move to cluster, I need to restore a backup .BAK of production db to the cluster. Which is the best way ? This is sqlserver 2000 on windows 2003. Cannot copy across the live MDF and LDf files as I cannot bring the production to single server/detach and re-attach.
Thanks,

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

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Look up RESTORE DATABASE in BOL and refer to the WITH MOVE clause. This will allow you to use your backup file to create a new database. Either that or create a new blank database configured how you want, and restore your backup file (with override) from EM.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

chandrika5 SSCrazy Points: 2734 More actions · Answer 2

As I have to move the files across the network. Will copy the .BAK, create blank db abd attach the file. I'll see how this goes. The backup is about 2 GB.

John Rowan SSC Guru Points: 56440 More actions · Answer 3

When you say 'files', I assume that you are only referring to the backup file correct? There is no need to 'attach' any files. Can you clarify your last statement for me?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Ewan Hampson SSCrazy Points: 2780 More actions · Answer 4

We have recently been upgrading servers, and found it very convenient to schedule backup across the network to the new server. Then simply restore from the local .BAK to the new server, and your database is on its new platform. (Just got to sort out those pesky user accounts...)

You need server network connections that are fast enough (100Mb/Full - we did have one server at 10Mb/Half and it took 4+ hours rather than 20 minutes); and the SQLServerAgent must be running under an account that has permissions to the remote server (we use a domain account). Just specify the target drive using the UNC \\server\share\etc.... 3GB, < 20 minutes.

Make sure you get the server collations right, especially if moving up versions (we were moving 7 to 2000 - hello 21st Century).

chandrika5 SSCrazy Points: 2734 More actions · Answer 5

Yes, these are the backup files: to be precise the backup of the MDF: dbname_datetime.BAK.

I copied the .BAK file across and am trying to restore to a newly created db using EM:

I am not able to see the .BAK file I copied to restore from ???

how do I do this ?

Thanks for all the help

John Rowan SSC Guru Points: 56440 More actions · Answer 6

Well, where did you copy the .bak to? The Windows account running your SQL Server service must have read access to the folder where you've placed the .bak file.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

chandrika5 SSCrazy Points: 2734 More actions · Answer 7

Thanks John,

I restored the db: was looking at the wrong spot.