如何恢复数据库备份到一个已存在的正在使用的数据库上_13549

							如何恢复数据库备份到一个已存在的正在使用的数据库上

[标签:作者]

[标签:来源]

[标签:时间]


USE master -- (Can’t sit in the database whilst its being restored!)

GO


ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO


-- Restore Full Backup

RESTORE DATABASE MyDatabase

 FROM DISK = ’x:\MSSQL\BACKUP\MyBackupFilename_Full.BAK’

 WITH

     REPLACE,

     NORECOVERY, -- Use if more T/Logs to recover

--      RECOVERY,   -- Use if NO more T/Logs to recover

     STATS = 10, -- Show progress (every 10%)

 MOVE ’MyDatabase_Data’ TO ’x:\MSSQL\DATA\MyDatabase.mdf’,

 MOVE ’MyDatabase_Log’ TO ’x:\MSSQL\DATA\MyDatabase.ldf’

GO
-- Optional restore Differential Backup

RESTORE DATABASE MyDatabase

 FROM DISK = ’x:\MSSQL\BACKUP\MyDatabase_Diff.BAK’

 WITH

--      RECOVERY   -- Use if NO more file to recover

     NORECOVERY -- Use if there are T/Logs to recover

GO


-- Optional restore Transaction Log Backup

RESTORE DATABASE MyDatabase

 FROM DISK = ’x:\MSSQL\BACKUP\MyDatabase_yyyymmdd_hhmm_Trans.BAK’

 WITH

--      RECOVERY   -- Use if NO more T/Logs to recover

     NORECOVERY -- Use if more T/Logs to recover

GO


-- Set the database ready for use (after all backups have been
restored)

RESTORE DATABASE MyDatabase RECOVERY

GO


-- Rename logical names (only needed if restoring from a backup for a
Different database):

ALTER DATABASE MyDatabase

   MODIFY FILE (NAME = ’OrigDatabase_Data’, NEWNAME = ’
MyDatabase_data’)

GO

ALTER DATABASE MyDatabase

   MODIFY FILE (NAME = ’OrigDatabase_Log’, NEWNAME = ’
MyDatabase_log’)

GO