Monday, March 26, 2012

Restoring to a different server

I have a back up of one of our database, which i want to restore onto our
Devolopment server. I have tied to do a 'restore' through Enterprise manager,
but it fails with a SQL 42000 error. It also says something about ' use WITH
MOVE'
You have different disk layout on your two servers. The EM GUI allows you
to MOVE a data or log file to a different drive. You also have the ability
to specify WITH MOVE when restoring a database via T-SQL within Query
Analyzer.
Bottom line: you will need to tell SQL Server to move the data and log
file(s) to a drive that exists on the machine that you are restoring to.
Lots of information is available within Books Online (within the SQL Server
program group) or Transact-SQL Help (available from Query Analyzer).
Keith
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:DC547DF1-D385-463E-96FF-16C7C9C1D6CE@.microsoft.com...
> I have a back up of one of our database, which i want to restore onto our
> Devolopment server. I have tied to do a 'restore' through Enterprise
manager,
> but it fails with a SQL 42000 error. It also says something about ' use
WITH
> MOVE'
>
|||Hi,
1. Using Restore filelistonly command identify the logical file names of the
database backup file
RESTORE FILELISTONLY from disk='c:\x.bak'
2. With the output of the above query use RESTORE database
RESTORE DATABASE <newdbname> from disk='c:\backup\x.bak'
WITH move 'logical_mdf_filename' to 'new physical name with path',
move 'logical_ldf_filename' to 'new physical log name with Path'
In the physical path give the available drive letters and folder names
Thanks
Hari
MCDBA
"Peter Newman" wrote:

> I have a back up of one of our database, which i want to restore onto our
> Devolopment server. I have tied to do a 'restore' through Enterprise manager,
> but it fails with a SQL 42000 error. It also says something about ' use WITH
> MOVE'
>

No comments:

Post a Comment