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

hello

Simulation of Orphan Users and the fix !

A) EXECUTE THESE ON SERVER A

create database test1

use test1

create table table1
(
id bigint
)

declare @i bigint
set @i=1
while(@i<=100)
begin
insert into table1
values(@i)
set @i=@i+1
end

select * from table1

sp_help sp_addlogin
sp_addlogin @loginame='sqlcat1',@passwd='sqlcat1',@defdb='master',@deflanguage='us_english'
sp_addlogin @loginame='sqlcat2',@passwd='sqlcat2',@defdb='master',@deflanguage='us_english'
sp_addlogin @loginame='sqlcat3',@passwd='sqlcat3',@defdb='master',@deflanguage='us_english'
sp_help sp_grantlogin
sp_grantlogin @loginame='asiapac\smadhavan7'


sp_help sp_adduser
sp_help sp_grantdbaccess
sp_helprole
sp_help sp_addrolemember

sp_adduser @loginame='sqlcat1',@name_in_db='sqlcat1',@grpname='public'
sp_adduser @loginame='sqlcat3',@name_in_db='sqlcat3',@grpname='db_owner'

sp_grantdbaccess @loginame='sqlcat2',@name_in_db='sqlcat2'
sp_addrolemember @rolename='db_owner',@membername='sqlcat2'

--using adduser or grantdbaccess+addrolemember are same

select * from sysusers

sp_helpuser

backup database test1
to disk='C:\backups\full_test1.bak'

sp_helpdb 'test1'

-------------------------------------------------
-------------------------------------------------


B) EXECUTE THESE ON SERVER B

restore database test22
from disk='C:\backups\full_test1.bak'
with file=1,
move 'test1' to 'C:\backups\test_rest1.mdf',
move 'test1_log' to 'C:\backups\test_rest2.ldf'

sp_change_users_login 'report'
The output would be as follows :
sqlcat1 0x1770DFE18F9CD64A812C27DD61CC03C8
sqlcat2 0x1724891474E45A4599453E8F0A3C523F
sqlcat3 0xB884905510F3C0438BDFDE1D888DD612

Now execute : sp_change_users_login 'auto_fix','sqlcat1',NULL,'SQLcat123!'

When u specify null then it means user doesnt have a login to be linked to.
after the script is executed,for the user 'sqlcat1' a new login would be created with the same name as 'sqlcat1' and then the password given would be set to that login.

dbo db_owner NULL NULL dbo 1 0x0105000000000005150000008ACE31E646417D21BAEBAA568B860200
guest public NULL NULL guest 2 0x00
INFORMATION_SCHEMA public NULL NULL NULL 3 NULL
sqlcat1 public sqlcat1 test22 sqlcat1 5 0xF1218C686757F34CA65683218B31060F
sqlcat2 db_owner NULL NULL sqlcat2 6 0x1724891474E45A4599453E8F0A3C523F
sqlcat3 db_owner NULL NULL sqlcat3 7 0xB884905510F3C0438BDFDE1D888DD612
sys public NULL NULL NULL 4 NULL


watch the sqlcat1 it has now a login associated with it.

now create a new login for the server instance B as follows :
create login catsql1
with password='CATsql123!'

sp_change_users_login 'update_one','sqlcat2','catsql1'

dbo db_owner NULL NULL dbo 1 0x0105000000000005150000008ACE31E646417D21BAEBAA568B860200
guest public NULL NULL guest 2 0x00
INFORMATION_SCHEMA public NULL NULL NULL 3 NULL
sqlcat1 public sqlcat1 test22 sqlcat1 5 0xF1218C686757F34CA65683218B31060F
sqlcat2 db_owner catsql1 master sqlcat2 6 0xCE4CEA864162EA469F5FBB55FEEBC0E4
sqlcat3 db_owner NULL NULL sqlcat3 7 0xB884905510F3C0438BDFDE1D888DD612
sys public NULL NULL NULL 4 NULL

Have a look at the above query result.Watch for sqlcat2 it has now got the sql login associated with it and its nothing but catsql1.