Backing up the database using dump device

Once you have created the dump device, the next nice thing to have is to create a stored procedure that is able to backing up the database to the dump device based on current week day. You have to execute the following stored procedure in SQL Server Management Studio. Then, create a job in SQL Agent and setup the schedule properly.

create proc mgt_backup_db (
 @db_name sysname
)
as
begin

declare @d int

select @d = datepart( weekday, getdate())

if (@d = 2)
 backup database @db_name
 to db_mon_bak
 with init

else if (@d = 3)
 backup database @db_name
 to db_tue_bak
 with init

else if (@d = 4)
 backup database @db_name
 to db_wed_bak
 with init

else if (@d = 5)
 backup database @db_name
 to db_thu_bak
 with init

else if (@d = 6)
 backup database @db_name
 to db_fri_bak
 with init

else if (@d = 7)
 backup database @db_name
 to db_sat_bak
 with init

end

go

Create 7 days cycle backup devices

This is the stored procedure that creates 7 days cycle backup devices. The idea is to create a maximum of 7 backup files and recycle it every week. This backup concept is handy when you need a simple backup.

create proc mgt_create_backup_device (
 @db nvarchar(255),
 @folder nvarchar(100) 
)
as

begin

declare @device_name sysname,
 @file_name sysname

 select @device_name = @db + '_mon_bak', 
   @file_name = @folder + @db + '_mon_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

 select @device_name = @db + '_tue_bak', 
   @file_name = @folder + @db + '_tue_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

 select @device_name = @db + '_wed_bak', 
   @file_name = @folder + @db + '_wed_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

 select @device_name = @db + '_thu_bak', 
   @file_name = @folder + @db + '_thu_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

 select @device_name = @db + '_fri_bak', 
   @file_name = @folder + @db + '_fri_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

 select @device_name = @db + '_sat_bak', 
   @file_name = @folder + @db + '_sat_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

 select @device_name = @db + '_sun_bak', 
   @file_name = @folder + @db + '_sun_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

end
go

View the database size

To view the current database size, execute the following TSQL:

declare @db sysname
select @db = db_name()
exec sp_helpdb @db

Getting the list of parameter for stored procedure

There are 2 ways to list down the parameters for stored procedure.

(1) In SQL2000 or above, you may do this:

  select *
  from syscolumns
  where id = object_id('pr_get_sales_data')

(2) In SQL2005 or above, you may do this:

  select *
  from information_schema.parameters
  where specific_name = 'pr_get_sales_data'

Installing SQL Express silently

Run the following command to install the SQL Express without letting the user to choose the configuration. Please take note that the "checking pre-requisite" window will still appear on the screen.

   SQLEXPR.EXE ADDLOCAL=SQL_Engine INSTANCENAME=testsql /qb

Encrypting database in SQL 2008

SQL 2008 provides a transparent data encryption. No more complicated calls to the encryption function and SQL statement.


http://msdn.microsoft.com/en-us/library/bb934049.aspx

Maximum Capacity Specifications for SQL Server

Want to know how far MSSQL Server can go? Check this out:

http://msdn.microsoft.com/en-us/library/ms143432.aspx

Query performance

Checklist for Analyzing Slow-Running Queries

http://technet.microsoft.com/en-us/library/ms177500.aspx

Advanced Query Tuning Concepts

http://technet.microsoft.com/en-us/library/ms191426.aspx

SQL analyzer - graphical execution plan icons

There are 2 ways to analyze the query. Either using the "SET SHOWPLAN_TEXT" option or the graphical execution option (available in the SQL Management Studio).

The following URL contains the icons that were used in the graphical execution option.

http://technet.microsoft.com/en-us/library/ms175913.aspx

Indexed view

To speed up the aggregate functions, you may consider using indexed view. The following script shows you how to create an indexed view.

Indexed view basic requirements:

1. COUNT_BIG() must be included in the field list.
2. An unique clustered index must be created.
   

create view vw_test1
with schemabinding
as
select cust_id, total_amt = sum(amt), count = count_big(*)
from invoice
group by cust_id

go

create unique clustered index ix_vw_test1
on vw_test1 (cust_id)

go

select *
from vw_test1 with (noexpand)

Notes:

• "WITH (NOEXPAND)" option must be used when you are querying the data through the indexed view. Otherwise, the query optimizer will take use the view definition to query the data and the indexes created for the view will not be used.
  

References:

http://technet.microsoft.com/en-us/library/ms191432.aspx
http://technet.microsoft.com/en-us/library/cc917715.aspx

Converting comma delimeter string into a table

It would be nice to split the incoming comma delimeter string into a table and returns a table back to the caller.

For example, you would like to turn 'A,B' into a table.

select *
from dbo.fn_to_table ('''A'',''B''')

select count(*)
from dbo.fn_to_table ('''A'',''B''')

create function dbo.fn_to_table (@str nvarchar(max))
returns  @tb table (code nvarchar(50)) 
as
begin

declare 
  @pos int,
  @prev_pos int,
  @s nvarchar(50)

select @pos = charindex(',', @str)
select @prev_pos = 0

if ((len(@str) > 0) and (@pos = 0))
 insert into @tb (code)  
 select replace(@str, '''','');

while @pos > 0
begin
 select @s = replace( substring(@str, @prev_pos, 
                 @pos - @prev_pos), '''','');
 -- select @prev_pos, @pos, @s
 insert into @tb (code)
 select @s

 select @prev_pos = @pos + 1;
 select @pos = charindex(',', @str, @pos + 1);
end

if ((@prev_pos > 0) and (@pos = 0))
begin
 insert into @tb (code)
 select  replace( substring(@str, @prev_pos, 
             len(@str) - @prev_pos), '''','');
end

return 

end

Statistics

Query optimizer is using statistics to come out with a better query plan. By default, the SQL Server will automatically create statistics in the database.

In Microsoft SQL Server Management Studio, right click on the database name and choose Properties. Then, goto "Options" page and look for "Auto Create Statistics" and "Auto Update Statistics" options.

In case you would like to create the statistics manually you may execute the following stored procedure:

  sp_createstats

OR manually update the statistics:

  sp_updatestats

OR

  update statistics {table name}

To view the statistic details, you have to call DBCC command:

  dbcc show_statistics ('{table name}', '{index or statistics}')

References:
http://technet.microsoft.com/en-us/library/ms190397.aspx
http://msdn.microsoft.com/en-us/library/ms174384.aspx
http://msdn.microsoft.com/en-us/library/cc966419.aspx

Check database consistency

if exists( select * from sys.objects where name ='mgt_check_db')
 drop proc mgt_check_db
go

create proc mgt_check_db (
 @db_name sysname
)
as

DBCC CHECKDB(@db_name);

go

Create backup stored procedure

This stored procedure helps you to backup the database in 7 day cycle and each cycle has one file.

exec sp_addumpdevice 'disk', 'db_mon_bak', 
        'c:\mssql\backup\db_mon_bak.bak'
go
exec sp_addumpdevice 'disk', 'db_tue_bak', 
        'c:\mssql\backup\db_tue_bak.bak'
go
exec sp_addumpdevice 'disk', 'db_wed_bak', 
        'c:\mssql\backup\db_wed_bak.bak'
go
exec sp_addumpdevice 'disk', 'db_thu_bak', 
        'c:\mssql\backup\db_thu_bak.bak'
go
exec sp_addumpdevice 'disk', 'db_fri_bak', 
        'c:\mssql\backup\db_fri_bak.bak'
go
exec sp_addumpdevice 'disk', 'db_sat_bak', 
        'c:\mssql\backup\db_sat_bak.bak'
go
exec sp_addumpdevice 'disk', 'db_sun_bak', 
        'c:\mssql\backup\db_sun_bak.bak'
go

if exists( select * from sysobjects 
                where name = 'mgt_backup_db')
 drop proc mgt_backup_db
go

CREATE proc mgt_backup_db (
 @db_name sysname
)
as
begin

declare @d int

select @d = datepart( weekday, getdate())

if (@d = 2)
 backup database @db_name
 to db_mon_bak
 with init

else if (@d = 3)
 backup database @db_name
 to db_tue_bak
 with init

else if (@d = 4)
 backup database @db_name
 to db_wed_bak
 with init

else if (@d = 5)
 backup database @db_name
 to db_thu_bak
 with init

else if (@d = 6)
 backup database @db_name
 to db_fri_bak
 with init

else if (@d = 7)
 backup database @db_name
 to db_sat_bak
 with init



end


GO

Offline rebuilding indexes

"DBCC DBREINDEX" or "ALTER INDEX..REBUILD" is an offline reindexing. You may consider using "DBCC INDEXDEFRAG" or "ALTER INDEX..REORGANIZE" to do the online reindexing in a mission critical database.

if exists( select * from sys.objects where name ='mgt_reindex')
   drop proc mgt_reindex
go

create proc mgt_reindex
as
begin

declare @tb_name sysname,
@idx_name sysname,
@sql nvarchar(500)

declare cr cursor
for

   select
       o.name as tb_name, i.name as idx_name
   from sys.indexes i
   inner join sys.objects o on o.object_id = i.object_id
   where o.type='u'
       and i.name is not null
   order by o.name, i.name

open cr

fetch next from cr
into @tb_name, @idx_name

while (@@fetch_status = 0)
begin
   select @sql = 'dbcc dbreindex(' 
      + @tb_name +','
      + @idx_name + ')'
   exec sp_executesql @sql  

   fetch next from cr
   into @tb_name, @idx_name

end

close cr
deallocate cr


end
go