Prev: Report Services KPI
Next: How to set locks to 0
From: . on 10 Aug 2010 14:20
Hello how would I restore a db named example 'UserDB_restore' from it's main
db name backup file 'UserDB.bak'? I'm trying to simply restore it side by
side when pointing to that device file name and wondering do I need to
adjust the 'Logical file name' & 'Move to physical file name' options?
Thanks in advance.


From: Erland Sommarskog on 10 Aug 2010 15:42
.. (nothing(a)nothing.com) writes:
> Hello how would I restore a db named example 'UserDB_restore' from it's
> main db name backup file 'UserDB.bak'? I'm trying to simply restore it
> side by side when pointing to that device file name and wondering do I
> need to adjust the 'Logical file name' & 'Move to physical file name'
> options?

First run sp_helpdb on the source database to get the logical names
of the files, they are in the first column, and the physical path,
which is the third column.

Then do:

RESTORE DATABSE dbcopy FROM DISK = '<somepath>\db.bak'
WITH MOVE = '<name1>' TO '<path>\dbcopy.mdf',
MOVE = '<name2>' TO '<path>\dbcopy.ldf',
REPLACE

That is, you retain the logical names, but you need file names - obviously.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: . on 10 Aug 2010 16:56
Thanks Erland, much appreciated :-)


"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9DD0DCBEA484CYazorman(a)127.0.0.1...
>. (nothing(a)nothing.com) writes:
>> Hello how would I restore a db named example 'UserDB_restore' from it's
>> main db name backup file 'UserDB.bak'? I'm trying to simply restore it
>> side by side when pointing to that device file name and wondering do I
>> need to adjust the 'Logical file name' & 'Move to physical file name'
>> options?
>
> First run sp_helpdb on the source database to get the logical names
> of the files, they are in the first column, and the physical path,
> which is the third column.
>
> Then do:
>
> RESTORE DATABSE dbcopy FROM DISK = '<somepath>\db.bak'
> WITH MOVE = '<name1>' TO '<path>\dbcopy.mdf',
> MOVE = '<name2>' TO '<path>\dbcopy.ldf',
> REPLACE
>
> That is, you retain the logical names, but you need file names -
> obviously.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>


 | 
Pages: 1
Prev: Report Services KPI
Next: How to set locks to 0