By default, every RESTORE DATABASE comes with RECOVERY set up.
The 'NORECOVERY' options, basically tells the SQL Server that the database is waiting for more restore files (could be a DIFF file and LOG file and, could include tail-log backup file, if possible).
The 'RECOVERY' options, finish all transactions and let the database ready to perform transactions.
So:
- if your database is set up with SIMPLE recovery model, you can only perform a FULL restore with
NORECOVERY option, when you have a DIFF backup. No LOG backup are allowed in SIMPLE recovery model database.
- Otherwise, if your database is set up with FULL or BULK-LOGGED recovery model, you can perform a FULL restore followed by
NORECOVERYoption, then perform a DIFF followed by NORECOVERY, and, at last, perform LOG restore with RECOVERY option.
Remember, THE LAST RESTORE QUERY MUST HAVE RECOVERY OPTION. It could be an explicit way or not. In therms of T-SQL, the situation:
1.
USE [master]
GO
RESTORE DATABASE Database_name
FROM DISK = N'\\path_of_backup_file.bak WITH FILE = 1, [REPLACE],NOUNLOAD,
RECOVERY -- This option could be omitted.
GO
WITH REPLACE option must be used with caution as it can lead to data loss
Or, if you perform a FULL and DIFF backup, you can use this
USE [master]
GO
RESTORE DATABASE Database_name
FROM DISK = N'\\path_of_backup_file.bak' WITH FILE = 1,
NOUNLOAD,NORECOVERY
GO
RESTORE DATABASE Database_name
FROM DISK =N'\\path_of_**diff**backup_file.bak' WITH FILE = 1,
NOUNLOAD, RECOVERY
GO
2. USE [master]
GO
-- Perform a Tail-Log backup, if possible.
BACKUP LOG Database_name
GO
-- Restoring a FULL backup
RESTORE DATABASE Database_name
FROM DISK = N'\\path_of_backup_file.bak' WITH FILE = 1,
NOUNLOAD,NORECOVERY
GO
-- Restore the last DIFF backup
RESTORE DATABASE Database_name
FROM DISK = N'\\path_of_DIFF_backup_file.bak' WITH FILE = 1,
NORECOVERY,NOUNLOAD
GO
-- Restore a Log backup
RESTORE LOG Database_name
FROM DISK = N'path_of_LOG_backup_file.trn' WITH FILE = 2,
RECOVERY, NOUNLOAD
GO
Of course, you can perform a restore with the option STATS = 10 that tells the SQL Server to report every 10% completed.
If you prefer, you can observe the process or restore in real-time based query.
As follow:
USE[master]
GO
SELECT session_id AS SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')
GO
Hope this help.
DROP DATABASE dbcommand through SSMS and it worked (earlier I was using SSMS from another machine to issue the commands). I am guessing the other solutions would have worked as well. – Planoconcave