如何恢复数据库备份到一个已存在的正在使用的数据库上
程序员文章站
2022-05-29 13:19:47
use master -- (cant sit in the database...
use master -- (cant 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
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
上一篇: Oracle 系统变量函数用法指南