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.
