Steps to restart an Interrupted Database Restore in SQL Server
There may scenario when you are restoring VLDB database backup and the restore database command interrupted due network failure, power failure, SQL Server Service restart or cluster failover. In this kind of scenario you can easily start the interrupted restore from the point of failure using WITH RESTART command.
Before you start restoring the database using WITH RESTART option check database is in Restoring State not in any other state.
You can check the database state either from SSMS object explorer or by executing below T-SQL command
select name,state_desc from sys.databases
Once confirmed that database is in restoring state execute the backup command WITH RESTART clause
RESTORE DATABASE [AdventureWorks]
FROM DISK ='D:\Backup\AdventureWorks.bak'
WITH RESTART, STATS = 20
Problem: Take a scenario where vendor wants you to up-grade the vendor databae using the SQL scripts. In case if something goes wrong you can either recover database by restoring all backup or by creating database snapshot.
Database snapshots feature is available from the SQL Server 2005. Snapshot will be used to create a read-only copy of a database at a given point in time. Any transactions which are uncommitted at the time you create a snapshot will not be included in the database snapshot. You can create multiple snapshots of a source database, but those snapshots must reside on the same instance as the source database.
You must have SQL Server enterprise edition to create the snapshot. A database snapshot only contains the data pages that have changed in the source database since the snapshot was created. It contains the original copies of those pages in order to give the effect of a read-only view. The file that is created to hold the changed data pages when the snapshot is created is known as a sparse file.
A source database that contains a snapshot cannot be dropped, detached, or restored until all of the snapshots have been dropped.
Check below queries to restore the database using snapshot
Before you start restoring using database snapshot
— Make sure the source database does not contain read-only or compressed filegroups.
–Make sure all the files are online that were online when the snapshot was created.
–Delete all snapshots of the source database, except the one you are reverting to.
create database db_pool
create table tb1
insert into tb1 values(10,'Jugal')
--select the database and execute the below query to get logical name
SELECT name FROM sys.database_files WHERE type <> 1
--execute below command to create the database snapshot
CREATE DATABASE db_pool_Snapshot
(Name = db_pool,
FileName = 'C:\db_pool_Data.ss')
AS SNAPSHOT OF db_pool
--select the original database and drop the rows
delete from tb1
--use the snapshot and query tb1, we can see the data evenif it is deleted
select * from tb1
--now restore database using below script from snapshot
RESTORE DATABASE db_pool FROM DATABASE_SNAPSHOT = 'db_pool_Snapshot'
--query the database to check changes
select * from tb1