MS SQL Server - Restore to different server

Author

Restore to different server

Robert Richards via SQLMonster.com

2005-07-15, 1:23 pm

If I am restoring a database from a backup file on disk on Server_A to a
database on Server_B, in the restore syntax do I need to specify the "with
move" clause if the data and log files as backed up on Server_A are going to
be restored into differently named directories on Server_B?

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...server/200507/1

michael

2005-07-15, 1:23 pm

Here's an example of moving from one location to another.

restore database bar
from disk = 'c:\Program Files\Microsoft SQL Server\MSSQL\Backup\
foo.BAK'
with move 'fooData' to 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\ba
r.mdf',
move 'fooLog' to 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\ba
r_log.ldf',
UNLOAD ,
replace

"Robert Richards via webservertalk.com" wrote:

>
> If I am restoring a database from a backup file on disk on Server_A to a
> database on Server_B, in the restore syntax do I need to specify the "with
> move" clause if the data and log files as backed up on Server_A are going to
> be restored into differently named directories on Server_B?
>
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...server/200507/1
>

Hari Prasad

2005-07-15, 8:23 pm

Hi,

If you are restoring the database into different directories which is
differing to source directory, then you have to say
with MOVE clause in RESTORE DATABASE command.

Syntax:-

RESTORE DATABASE MyNwind FROM Disk='D:\Backup\MyNw
ind.BAK'
WITH MOVE 'MyNwind' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\Ne
wNwind.mdf',
MOVE 'MyNwindLog1' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\Ne
wNwind.ldf', STATS=10

Thanks
Hari
SQL Server MVP

"Robert Richards via webservertalk.com" <forum@webservertalk.com> wrote in message
news:515E51CF9507A@w
ebservertalk.com...
>
> If I am restoring a database from a backup file on disk on Server_A to a
> database on Server_B, in the restore syntax do I need to specify the "with
> move" clause if the data and log files as backed up on Server_A are going
> to
> be restored into differently named directories on Server_B?
>
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...server/200507/1

Robert Richards via SQLMonster.com

2005-07-15, 8:23 pm

Just curious, do the data files and such on the originating server remain
usable after the restore to the destination server? That is, I am creating a
backup of production for testing, and I of course want my production database
to remain.

Hari Prasad wrote:[color=darkred
]
>Hi,
>
>If you are restoring the database into different directories which is
>differing to source directory, then you have to say
>with MOVE clause in RESTORE DATABASE command.
>
>Syntax:-
>
>RESTORE DATABASE MyNwind FROM Disk='D:\Backup\MyNw
ind.BAK'
>WITH MOVE 'MyNwind' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Ne
wNwind.mdf',
> MOVE 'MyNwindLog1' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Ne
wNwind.ldf', STATS=10
>
>Thanks
>Hari
>SQL Server MVP
>

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...server/200507/1

Andrew J. Kelly

2005-07-15, 8:23 pm

When you do a backup it essentially makes a pseudo copy. Nothing happens to
the original files at all.

--
Andrew J. Kelly SQL MVP

"Robert Richards via webservertalk.com" <forum@webservertalk.com> wrote in message
news:515FA18FA3868@w
ebservertalk.com...
>
> Just curious, do the data files and such on the originating server remain
> usable after the restore to the destination server? That is, I am creating
> a
> backup of production for testing, and I of course want my production
> database
> to remain.
>
> Hari Prasad wrote:
>
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...server/200507/1

Sponsored Links