Validating Backups & Restore Verification

Validating Backup includes checking/validating if the backup taken can be successfully restored without any issues.(Pre-Restore Operation)

Restore Verification includes verifying if the restore operation conducted is successful or not without any issues.(Post-Restore Operation)

Validating Backups :
-------------------

restore verifyonly
from disk='C:\backups\sample.bak'

Result is :
The backup set on file 1 is valid.

Similarly just remember these as well : restore headeronly,restore labelonly,restore filelistonly.

However the successful validation would include restoring the backuped up db onto a development environment.:-)

Restore Verification :
-----------------------

select
rh.destination_database_name,
rf.restore_history_id,
rf.destination_phys_drive,
rf.destination_phys_name,

rh.restore_date,
rh.destination_database_name,
rh.user_name as "Restore performed by",
rh.backup_set_id,
rh.restore_type,
(case rh.restore_type
when 'D' then 'Database'
when 'F' then 'File'
when 'G' then 'Filegroup'
when 'I' then 'Differential'
when 'L' then 'Log'
when 'V' then 'VerifyOnly'
when 'R' then 'Revert'
else
'NULL' end) as "Restore Type Desc",
rh.replace,
rh.recovery,
rh.restart,
rh.stop_at,
rh.stop_at_mark_name,
rh.stop_before
from restorefile rf
join restorehistory rh
on rf.restore_history_id=rh.restore_history_id

From the resultset watch out for these 2 columns
destination_database_name and restore_date.
With this verification can be completed.

Backup History Analysis Script !

select
bs.database_name,
bf.backup_set_id,
bf.file_type,
(case bf.file_type
when 'D' then 'Database'
when 'I' then 'Differential'
when 'L' then 'Log'
when 'F' then 'File/Filegroup'
when 'G' then 'File Differential'
when 'P' then 'Partial'
else
'Partial Differential'
end) as 'Type_Desc',
bf.logical_name,
bf.physical_drive,
bf.state_desc,

bs.user_name as "backed up by user",
bs.backup_start_date,
bs.backup_finish_date,
(convert(varchar,datediff(hh,bs.backup_start_date,bs.backup_finish_date))+':'+convert(varchar,datediff(mi,bs.backup_start_date,bs.backup_finish_date))+':'+convert(varchar,datediff(ss,bs.backup_start_date,bs.backup_finish_date))) as "Backup Window Time",
bs.compatibility_level,
bs.database_version,
bs.database_name,
bs.server_name,
bs.machine_name,
bs.collation_name,
bs.is_password_protected,
bs.recovery_model

from
msdb.dbo.backupfile bf
join
msdb.dbo.backupset bs
on bf.backup_set_id=bs.backup_set_id
order by bf.backup_set_id

Attaching n Detaching Dbs !

A) In server Instance A execute the following script

use master
sp_helpdb 'test22'

test1 1 C:\backups\test_rest1.mdf PRIMARY 1408 KB Unlimited 10% data only
test1_log 2 C:\backups\test_rest2.ldf NULL 3456 KB Unlimited 10% log only

sp_detach_db 'test22'
Msg 3703, Level 16, State 2, Line 1
Cannot detach the database 'test22' because it is currently in use.

alter database test22 set single_user with rollback immediate
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

sp_detach_db 'test22'

With this db is successfully detached.

B) Execute the following in server instance B

sp_attach_db 'test22','mdf_loc','ldf_loc'

Movement 2000 dbs into 2005 dbs successful
but
2005 dbs to 2000 dbs failure

Please note that while attaching and detaching dbs ,the dbowner will get changed.You will have to now use changedbowner to set the original owner of the db.For this before detaching please make a note of the original dbowner.

How to change a dbowner for a db ?

use db_name_here

--find out the existing dbowner name here :
select name,suser_sname(owner_sid) from sys.databases--sql server 2005
select name,suser_sname(sid) from sys.databases--sql server 2000


sp_changedbowner @loginame='login_name_here'

Please note that the login name here should exists as a server principal and most importantly it should not have any user mapped in the current db context.It is only then that the command will execute successfully.

Now in order to check the new dbowner re-execute the below command :

select name,suser_sname(owner_sid) from sys.databases
select name,suser_sname(sid) from sys.databases--sql server 2000

How to find who the db owner is for a specific db ?

select name,suser_sname(owner_sid) from sys.databases