Script to backup users in a db along with their db roles in Sql Server 2000 !

if exists(select * from tempdb.dbo.sysobjects where name like '#users%')
drop table #users

create table #users
(
uname nvarchar(150),
gname nvarchar(150),
lname nvarchar(150),
dbname nvarchar(150),
uid tinyint,
sid nvarchar(150)
)

insert into #users
exec sp_helpuser

declare @un sysname
declare @ln sysname
declare @rn sysname
declare @cmd1 nvarchar(500)
declare @cmd2 nvarchar(500)
declare @cu cursor
set @cu=cursor for
select uname,lname,gname from #users where uname!='dbo'

open @cu
fetch next from @cu
into @un,@ln,@rn
while(@@fetch_status=0)
begin
set @cmd1='exec sp_adduser @loginame='''+@ln+''', @name_in_db='''+@un+''''
print @cmd1
set @cmd2='exec sp_addrolemember @rolename='''+@rn+''',@membername='''+@un+''''
print @cmd2
print '--------'
fetch next from @cu
into @un,@ln,@rn
end

close @cu
deallocate @cu

Scripts to add logins !

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'

Capacity Planning - Calculating db growth % of a specific db !

select
bf.file_size/1048576 as "File Size in MB",
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

into kabul

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

where bf.file_type='D'
and
bs.database_name='dumbo'
order by bf.backup_set_id

--with this a new table kabul is populated with statistical data
--The following tweaked query from table kabul will display the growth %
select
a.database_name,
(convert(numeric(5,2),(
(a.[file size in mb]/(select b.[file size in mb] from kabul b where b.backup_set_id=
(select max(x.backup_set_id)
from kabul x
join kabul y
on x.backup_set_id=y.backup_set_id
where x.backup_set_id<
a.backup_set_id))*100)-100))) "Growth %",
a.[Backup Window Time],
a.backup_set_id,
a.backup_start_date,
a.backup_finish_date,
a.[File Size in MB],
a.file_type,
a.Type_Desc,
a.logical_name,
a.physical_drive,
a.state_desc,
a.[backed up by user],
a.compatibility_level,
a.database_version,
a.server_name,
a.machine_name,
a.collation_name,
a.is_password_protected,
a.recovery_model
from
kabul a

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