Restoration of SQL server Database Backup using T-SQL

RESTORATION OPTIONS:

• Overwrite the existing database [WITH REPLACE]                                                                                                 The restore operation will overwrite the files of any database that is currently using the database name.
• Preserve the replication settings [WITH KEEP_REPLICATION]                                                         Preserves the replication settings when restoring a published database to a server other than the server where the database was created. This option is relevant only if the database was replicated when the backup was created.                                                                                                                                                           This option is available only with the Leave the database ready for use by rolling back the uncommitted transactions option , which is equivalent to restoring a backup with the RECOVERY option.
• Restrict access to the restored database [WITH RESTRICTED_USER]                                                 Makes the restored database available only to the members of db_owner, dbcreator, or sysadmin.

RECOVERY STATE:

• RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. Select this option if you are restoring all of the necessary backups now.
• RESTORE WITH NORECOVERY which leaves the database non-operational, and does not roll back the uncommitted transactions. Additional transaction logs can be restored. The database cannot be used until it is recovered.
• RESTORE WITH STANDBY which leaves the database in read-only mode. It undoes uncommitted transactions, but saves the undo actions in a standby file so that recovery effects can be reverted.

Before using restoration using T-sql , you need have to find out logical name of source data files . For destination you can choose as per your requirment.

Use [DBA]
Go
sp_helpfile

OUTPUT:

-------------------------------------------------------------------------------------------------------------

T-SQL Commands

RESTORATION WITH RECOVERY using replace option

RESTORE DATABASE [DBA] FROM  
DISK = N'H:\SQL_DATA\DBA.bak' WITH  FILE = 1,  
MOVE N'dba' TO N'F:\SQL_DATA\dba.mdf',
MOVE N'dba02' TO N'F:\SQL_DATA\dba02.ndf',  
MOVE N'dba_log02' TO N'I:\LOG_DATA\dba_log02.ldf',  
MOVE N'dba_log' TO N'I:\LOG_DATA\dba_log.LDF',  
NOUNLOAD,  REPLACE,  STATS = 5
GO
-------------------------------------------------------------------------------------------------------------
RESTORATION WITH STANDBY using replace option

RESTORE DATABASE [DBA] FROM  
DISK = N'H:\SQL_DATA\DBA.bak' WITH  FILE = 1,  
MOVE N'dba' TO N'F:\SQL_DATA\dba.mdf',
MOVE N'dba02' TO N'F:\SQL_DATA\dba02.ndf',  
MOVE N'dba_log02' TO N'I:\LOG_DATA\dba_log02.ldf',  
MOVE N'dba_log' TO N'I:\LOG_DATA\dba_log.LDF', 
STANDBY = N'H:\SQLBackup\DBA_RollbackUndo_2016-09-28_16-05-30.bak', NOUNLOAD,  REPLACE, STATS = 5
GO
-------------------------------------------------------------------------------------------------------------
RESTORATION WITH NORECOVERY using replace option

USE [master]
RESTORE DATABASE [DBA] FROM  
DISK = N'H:\SQL_DATA\DBA.bak' WITH  FILE = 1,  
MOVE N'dba' TO N'F:\SQL_DATA\dba.mdf',
MOVE N'dba02' TO N'F:\SQL_DATA\dba02.ndf',  
MOVE N'dba_log02' TO N'I:\LOG_DATA\dba_log02.ldf',  
MOVE N'dba_log' TO N'I:\LOG_DATA\dba_log.LDF', 
NORECOVERY,  NOUNLOAD, REPLACE,  STATS = 5
GO
-------------------------------------------------------------------------------------------------------------

Note:-
You can change Restoration options as per your requirment


More:-
➤ Restoration of SQL server Database Backup using GUI