This post will shows a step-by-step guide to backup and restore a database

Use of Database Backup

1. Protect you against Database upgrades that go bad.
2. Rollback plan against table corruption during corrective change implementation.
3. Environment Synchronization.
4. Cloning of Database.
5. Disaster Recovery Plan.

Before You begin!

Limitations and Restrictions

1. The BACKUP statement is not allowed in an explicit or implicit transaction.
2. Backups created by more recent version of SQL Server cannot be restored in earlier versions of SQL Server.

Thinks DBA should know before planning backup

1. Ensure to create backups after business hours.
2. During backups users won’t be able to access database as these operations create IO activities.
3. Create a backup on a different drive, to attain maximum IO efficiency from their database.
4. Check the backup status every hour, to ensure that it hasn’t failed.
5. In case your backup process fails, try to fix it immediately to avoid any further damage, it also saves time.
6. Database Recovery model should be based on your business needs.
7. Maintain and set organizational frequencies of your SQL database backup. (Example: Full Backup – Daily, Log Backup – After every 10 minutes, Differential Backup – Every Hour)
8. The speed of backups is more important than speed of queries.

Types of Recovery Model

1. MDF = Master Data File
2. NDF = NTEXT (No Master) Data File
3. LDF = Log Data File
4. BAK = Backup File

Application_Database Prod to UAT cloning activity step by step

Step 1 – Create User

Create user as a member of DBCREATOR Server Role and DB_OWNER Database Role

Sample TSQL Script to Create Login with DBCREATOR Server Role and DB_OWNER Database Role

CREATE LOGIN BackupRestoreAdmin WITH PASSWORD=’$tr0ngP@$$w0rd’

GO

CREATE USER BackupRestoreAdmin FOR LOGIN BackupRestoreAdmin

GO

EXEC sp_addsrvrolemember ‘BackupRestoreAdmin’, ‘dbcreator’

GO

EXEC sp_addrolemember ‘db_owner’,’BackupRestoreAdmin’

GO

Step 2 – Take a Backup of Production Database

BACKUP DATABASE [Application_Database_Instance_Prod] TO  DISK =     N’K:\full\Application_Database_PROD\Application_Database_21_jan_2019_bkp.bak’ 

WITH NOFORMAT, NOINIT,  NAME = N’Application_Database-Full Database Backup’, SKIP, STATS = 10

GO

NOFORMAT  = (The default) you can write multiple backups to the same file.

FORMAT =  Any existing contents get erased.

NOINIT / INIT = Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media.

NOSKIP / SKIP = Controls whether a backup operation checks the expiration date and time of the backup sets on the media before overwriting them.

Step 3 – Take a Backup of UAT Database

BACKUP DATABASE [Application_Database_Instance_UAT] TO  DISK = N’H:\MSSQL11.UAT01\MSSQL\Backup\Application_Database_UAT_21_jan_2019_bkp.bak’ 

WITH NOFORMAT, NOINIT,  NAME = N’Application_Database_UAT_Before cloning’, SKIP, STATS = 10

Step 4 – Now performed the following steps to avoid the locking and blocking on Application_Database_Instance_UAT database only when statistics are on.

SELECT name AS ‘Name’, 

    is_auto_create_stats_on AS “Auto Create Stats”,

    is_auto_update_stats_on AS “Auto Update Stats”,

    is_read_only AS “Read Only” 

FROM sys.databases

WHERE name = ‘Application_Database_Instance_UAT’;

GO

ALTER DATABASE Application_Database_Instance_UAT SET AUTO_CREATE_STATISTICS OFF WITH NO_WAIT

ALTER DATABASE Application_Database_Instance_UAT  SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT

 ALTER DATABASE Application_Database_Instance_UAT  SET AUTO_UPDATE_STATISTICS_ASYNC OFF WITH NO_WAIT

Step 5 – Now take the database in single user and restricted user mode by using following command

ALTER DATABASE Application_Database_Instance_UAT SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE Application_Database_Instance_UAT SET RESTRICTED_USER WITH NO_WAIT (Optional)

SINGLE_USER = In this mode DBA want to do something that can’t be done with others users in (connected).

ROLLBACK IMMEDIATE = This option doesn’t wait for transactions to complete it just take all uncommitted transactions, running queries and roll them back immediately.

RESTRICTED_USER = Database can be accessed by users with sysadmin and dbcreator server roles and db_owner database role

NO_WAIT = it doesn’t rollback any transactions. It waits for a specified period of time and if the transactions are not complete the process will fail.

Step 6 – Disable All DB Owner user which will be using this application.

ALTER LOGIN “sys_admin_user” DISABLE 

Step 7 – Kill the Spid of Application_Database_Instance_UAT database by using following Procedure

 DECLARE @dbname sysname 

 SET @dbname = ‘Application_Database_Instance_UAT’ 

 DECLARE @spid int 

 SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid =        db_id(@dbname) 

 WHILE @spid IS NOT NULL 

 BEGIN 

 EXECUTE (‘KILL ‘ + @spid) 

 SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid 

  END

Spid = Server Process ID. These process ID’s are essentially sessions in SQL Server. Everytime an application connects to SQL Server, a new connection (or SPID) is created.

Step 8 – By using sp_who2 Check if all the processes are killed or not

Step 9 – If you missed the above step no 4 then perform the following steps.

SET DEADLOCK_PRIORITY HIGH

ALTER DATABASE Application_Database_Instance_UAT SET OFFLINE WITH ROLLBACK IMMEDIATE

SET DEADLOCK_PRIORITY HIGH

ALTER DATABASE Application_Database_Instance_UAT SET ONLINE WITH ROLLBACK IMMEDIATE

SET DEADLOCK_PRIORITY HIGH

ALTER DATABASE Application_Database_Instance_UAT SET MULTI_USER WITH ROLLBACK IMMEDIATE

Step 10 – Now restore the database by using following command

RESTORE DATABASE Application_Database_Instance_UAT FROM DISK =      ‘H:\MSSQL11.UAT01\MSSQL\cloning backups\Application_Database_Prod_Instance_21_jan_2019_bkp.bak ‘ WITH REPLACE, RECOVERY, STATS = 10

WITH REPLACE = Overwrites existing database.

RECOVERY = This makes the restored database accessible to the users.

Step 11 – Take the database in multi user mode by using following command

ALTER DATABASE Application_Database_Instance_UAT SET MULTI_USER WITH ROLLBACK IMMEDIATE

Step 12 – Now check for Orphan Users

EXEC sp_change_users_login ‘Report’

Step 13 – Fix the Orphan user

EXEC sp_change_users_login ‘Auto_Fix’, ‘User Name’

Step 14 – Check for the service broker is running or not by using following command.
 

Select is_broker_enabled FROM sys.databases WHERE name = ‘ Application_Database_Instance_UAT ‘;

If it is not enabled then i.e. if the result above showed 0 the execute the following command to enable it.

ALTER DATABASE Application_Database_Instance_UAT SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

Step 15 – Enable the Auto stats sync again by using following method

ALTER DATABASE Application_Database_Instance_UAT SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT

ALTER DATABASE Application_Database_Instance_UAT  SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT

ALTER DATABASE Application_Database_Instance_UAT  SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT

Verify your application.

Thanks for reading

Keep reading, share your thoughts, experiences. Feel free to contact us to discuss more. If you have any suggestion / feedback / doubt, you are most welcome.

Stay tuned on Knowledge-Junction, will come up with more such articles.