How to restore a database to an existing database in use

How to restore a database to an existing database in use

How to restore a database to an existing database in use

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 = 'database database _ data', NEWNAME = 'mydatabase _ data ')
GO
Alter database MyDatabase
Modify file (NAME = 'database database _ log', NEWNAME = 'mydatabase _ log ')
GO