DanBrother的部落格

(1) Restore SQL Server Database With Replace, Recovery Option

use master
go
alter database testdb set offline with rollback immediate

restore database testdb
from DISK = 'E:\backup\testdb.BAK'
WITH REPLACE,RECOVERY

alter database testdb set online
GO

select d.name as "DB_Name",
          m.name as "Logical_Name",
          d.state_desc as "Status",
          m.physical_name as "Physical Name"
from sys.databases d, sys.master_files m
where d.name = 'testdb'
and d.database_id = m.database_id;

DB_Name Logical_Name  Status     Physical Name
------------ ----------------    -----------  ---------------------------------
testdb      testdb              ONLINE   C:\ORIG_DB\testdb.MDF
testdb      testdb_log       ONLINE   C:\ORIG_DB\testdb.LDF

(2) Restore SQL Server Database With REPLACE, MOVE Option

use master
go
alter database testdb set offline with rollback immediate

restore database testdb
from DISK = 'E:\backup\testdb.BAK'
WITH REPLACE,
MOVE 'testdb' TO 'E:\testdb\testdb.MDF',
MOVE 'testdb_log' TO 'E:\testdb\testdb.LDF'

alter database testdb set online
GO

select d.name as "DB_Name",
          m.name as "Logical_Name",
          d.state_desc as "Status",
          m.physical_name as "Physical Name"
from sys.databases d, sys.master_files m
where d.name = 'testdb'
and d.database_id = m.database_id;

>>

DB_Name  Logical_Name Status    Physical Name
------------- --------------     ----------  ---------------------------
testdb        testdb           ONLINE   E:\testdb\testdb.MDF
testdb        testdb_log    ONLINE   E:\testdb\testdb.LDF