You can use EM, but the check boxes and options are Not Very Obvious - speaking politely!
There again, I think that the commands for RESTORE are a bit hairy-techie too ...
You need to do three things:
1) Interogate the backup file to find what its logical names are
2) Restore the file into the appropriate database (including indicating where the path is)
3) "RENAME" the logical names to more appropriate ones
Step (1):
RESTORE FILELISTONLY FROM DISK = 'x:\Mypath\MyFullBackupfile.BAK'
This will give something like:
LogicalName PhysicalName Type FileGroupName Size MaxSize
------------------ ---------------------------- ---- ------------- --------- --------------
MyOldDatabase_data D:\MSSQL\DATA\MyDatabase.mdf D PRIMARY 355467264 35184372080640
MyOldDatabase_log D:\MSSQL\DATA\MyDatabase.ldf L NULL 168624128 35184372080640
Step (2a):
RESTORE DATABASE MyNewDatabaseName
FROM DISK = 'x:\Mypath\MyFullBackupfile.BAK'
WITH
REPLACE, -- Overwrite DB - if one exists
-- NORECOVERY, -- Use if more T/Logs to recover
RECOVERY, -- Use if no more T/Logs to recover, database will be set ready to use
STATS = 10, -- Show progress (every 10%)
MOVE 'MyDatabase_data' TO 'x:\MySQLDataPath\MyNewDatabaseName.mdf',
MOVE 'MyDatabase_log' TO 'x:\MySQLDataPath\MyNewDatabaseName.ldf'
GO
Step (2b)
If you have a Differential backup to restore do this:
RESTORE DATABASE MyNewDatabaseName
FROM DISK = 'x:\Mypath\MyDifferentialBackupfile.BAK'
WITH
-- RECOVERY -- Use if NO more files to recover, database will be set ready to use
NORECOVERY -- Use if there are T/Logs to recover
Step (2c)
If you have any Transaction Backup files to restore repeat this for each one in turn:
RESTORE LOG MyNewDatabaseName
FROM DISK = 'x:\Mypath\MyTransactionBackupfile.BAK'
WITH
-- RECOVERY -- Use if NO more T/Logs to recover, database will be set ready to use
NORECOVERY -- Use if more T/Logs to recover
Step (3)
-- Rename logical names:
ALTER DATABASE MyNewDatabaseName MODIFY FILE (NAME = 'MyOldDatabase_data', NEWNAME = 'MyNewDatabase_data')
GO
ALTER DATABASE MyNewDatabaseName MODIFY FILE (NAME = 'MyOldDatabase_log', NEWNAME = 'MyNewDatabase_log')
GO
Kristen
Copy a Database
Forum Locked
Posted - 10/27/2004 : 07:08:05
