Thursday, 11 May 2017

How to refresh a database backup from source (prod server) to target(destination\non prod) server


The below process is How to refresh a database backup from source (prod server) to target(destination\non prod) server

1. Check For Calendar entry is there or Ad-hoc request.
2. Before starting anything gather all the details like Prod and Non Prod environment details(Confirm Source & Target\Destination Databases and Source & Destination\Target server names) used by application.
3. Check for the Current SQL version. (Source and  Target)
4. Login to the Source server and see where are all the DB files(Data&Log) are kept using below query.
 use master
go
sp_helpdb <Your database name>

5. To check the backup path, size etc.. use below script (Note: Need to run on source server) and Back up files Availability in Local\Network shared Servers

use master
go
--T-SQL Script to Check the Native Compressed backup file size and Backup Compression Ratio, backup type, size, time
Declare @FromDate as datetime
-- Specify the from date value
set @FromDate = GETDATE() -1

SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SQLServerName,
   msdb.dbo.backupset.database_name, 
   CASE msdb..backupset.type 
       WHEN 'D' THEN 'Database-Full'
       WHEN 'L' THEN 'Log'
       WHEN 'I' THEN 'Differential'
   END AS backup_type, 
   msdb.dbo.backupset.backup_start_date, 
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupmediafamily.physical_device_name,
   msdb.dbo.backupset.backup_size, 
   msdb.dbo.backupset.expiration_date,
   DATEDIFF (SECOND, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) 'Backup Elapsed Time (sec)',
   msdb.dbo.backupset.compressed_backup_size AS 'Compressed Backup Size in KB',
  (msdb.dbo.backupset.compressed_backup_size/1024/1024) AS 'Compress Backup Size in MB',
   CONVERT (NUMERIC (20,3), (CONVERT (FLOAT, msdb.dbo.backupset.backup_size) /CONVERT (FLOAT, msdb.dbo.backupset.compressed_backup_size))) 'Compression Ratio',
   CASE msdb..backupset.type 
       WHEN 'D' THEN 'Database'
       WHEN 'L' THEN 'Log'
   END AS backup_type, 
    
   msdb.dbo.backupmediafamily.logical_device_name, 
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE
CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= @FromDate
AND msdb.dbo.backupset.backup_size > 0
ORDER BY
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date

Verify the backup are valid or not using below commands.

Check a backup file on disk
The following command will check the backup file and return a message of whether the file is valid or not. If it is not valid, this means the file is not going to be useable for a restore and a new backup should be taken. One thing to note is that if there are multiple backups in a file, this only checks the first file.

RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK
GO

Check a backup file on disk for a particular backup
This command will check the second backup in this backup file. To check the contents in a backup you can use RESTORE HEADERONLY and use the Position column to specify the FILE number.

RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK WITH FILE = 2
GO
6. Do Space Projection[Prep Work involves]

a)      Use below query to get the database data&log files size along with physical name(path of the data&log files).

SELECT        DB_NAME(database_id) AS DatabaseName,
                            Name AS Logical_Name,
                            Physical_Name,
(size*8)/1024 SizeMB,
                            (size*8)/1024/1024 SizeGB
              FROM          sys.master_files
              WHERE         DB_NAME(database_id) = 'DBA_test'

Capture the results from the ablove query and save it excel file for cross verification in non pord(Target) server.

7. Space Requirement in Data file Drives in non prod:-Now you have to login into the Target\Destination server->verify the database data&log files are in which drives(use step 4 query) then compare the source database data&log files  sizes with prod&non prod, if enough space is available in target\Destination server you can proceed with refresh activity or else verify any un used files(if here clean it), try to shrink if any other db data\log files if still no luck please raise a request to respective(storage\Vm\window) team through change request(CR-ITIL process).

Once all the above things are in handy we have to start the activity as per the schedule\whenever activity start.
1.       Inform to application team to bring down the a Application, wait Until the Environment is down(Once got the confirmation from application\respective team).
2.       Once confirmation receives follow the below steps

Check for the active connection .

use master
go
select a.* from master..sysprocesses a,
master..sysdatabases b where a.dbid=b.dbid and b.name='your database name'
go

If there is any, kill it using the session id.
Note: If active connection is coming up again and again, first verify with the application team and then kill based on confirmation.

Kill <session_id>

Remove replication\if any high availability options. Replication is used to sync different databases.
After removing the replication\ all high availability option and no active connections on target server, start the restoration activity.

If only full backup having use below query

--Restore a full backup
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
GO

If you have full backup along with diff and transactional backups please follow the below steps.

Restore a full backup allowing additional restores such as a differential or transaction log backup (NORECOVERY)

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY
GO

Restore a differential backup

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.dif' WITH NORECOVERY
GO

Restore using a backup file that has multiple backups

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
GO

If you have multiple transactional backups please use below quires.

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks_1.TRN' WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks_2.TRN'
GO

Below query is to verify the percentage of the restoration.

SELECT  r.session_id [Session Id],
r.command [Command],
CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],
CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0) AS [ETA Sec],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(
SELECT  SUBSTRING(text,r.statement_start_offset/2,
CASE
WHEN r.statement_end_offset = -1 THEN 1000
ELSE (r.statement_end_offset-r.statement_start_offset)/2
END
) FROM sys.dm_exec_sql_text(sql_handle))) AS [SQL]
FROM    sys.dm_exec_requests r
WHERE   r.session_id > 50
and     r.session_id <> @@SPID
AND     command IN ('RESTORE DATABASE','BACKUP DATABASE','RESTORE LOG','BACKUP LOG')


Once it is completed fix the orphaned logins\user access to the databases

First, make sure that this will lists the orphaned users:


use yourdatabse --(replace with actual database name)
go
exec sp_change_users_login 'report'

If you already have a login id and password for this user, fix it by doing:
go
exec sp_change_users_login 'auto_fix', 'user'
go

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'
go



Inform to application team to bring up the application and validation.


Please feel free to correct me by comments. If you like the article, do Like & Share.

No comments:

Post a Comment

Disable CDC at table level

 How to Disable CDC( Change Data Capture) for tables Change Data Capture (cdc) property is disabled as default.  here I will Query you how t...