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