aiDeeva.com

Tag: DB Restore

  • How to restart an Interrupted Database Restore in SQL Server?

    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
GO

  • Script to Enable/Disable Database for Replication

    You can enable the database for replication using below script.

    use master
exec sp_replicationdboption @dbname = 'sqldbpool',
@optname = 'publish',
@value = 'true'
go

    If you have restore the database on test environment and you are getting the error that “Database is part of Replication”, you can clear/disable it by executing below query.

    use master
exec sp_replicationdboption @dbname = 'sqldbpool',
@optname = 'publish',
@value = 'false'
go

  • Steps to restore Database using Database Snapshot

    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.

    Solution:
    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
use db_pool

create table tb1
(
	id int,
	name varchar(10)
)

insert into tb1 values(10,'Jugal')
go 50;

--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
 ON
(Name = db_pool,
 FileName = 'C:\db_pool_Data.ss')
 AS SNAPSHOT OF db_pool
 
--select the original database and drop the rows
use db_pool
delete from tb1

--use the snapshot and query tb1, we can see the data evenif it is deleted 
 use db_pool_Snapshot
 select * from tb1
 
 --now restore database using below script from snapshot
USE master
GO
RESTORE DATABASE db_pool FROM DATABASE_SNAPSHOT = 'db_pool_Snapshot'
 
 --query the database to check changes
 use db_pool
 select * from tb1