Posted by Erland Sommarskog on 08/18/06 16:01

Mark D Powell (Mark.Powell@eds.com) writes:
> I know a restore can recreate a database but since I want to change the
> location of the datafiles will it in this case allow me to change them
> on the screen where it shows the datafile names?

Don't know what the screen permits, and I don't want to engage in guessing
what it does.

The syntax is:

RESTORE DATABASE db FROM FILE = 'yourdump.bak',
WITH MOVE 'logicalfilename1' TO 'newlocation.mdf',
MOVE 'logicalfilename2' TO 'newlocation.ldf',
REPLACE

The logical file names talked about here, are those you see when you dp
sp_helpdb in the first column in the second result set. You can also
retrieve this with RESTORE FILELISTONLY.

The command above with create the database files, and then restore the
backup.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Recommendation for tools converting access databases to SQL
• Prev in forum: Re: Using aba_lockinfo in a loop
• Thread view: Re: Best way to move datafile from C drive to D

[Reply to this message]