Arabic Series for SQL Server Backup Compression

 

Arabic Series for SQL Server Database Schema Part 2 Database Schema

 

Arabic Series for SQL Server Database Schema Part 1 – Database Schema

 

Bring SQL Server Database Offline using Script

To bring any database offline immediately you should first stop all processes on this database then bring it offline otherwise you can’t bring database before stop all processes on this database.

Code bellow give your database offline immediately

declare @dbname nvarchar(10)

set @dbname =‘AdventureWorks’

Select SPID into #temp From master.dbo.sysprocesses where [DBID]=DB_ID(@dbname)

Select SPID From master.dbo.sysprocesses where DB_ID(@dbname)=[DBID]

DECLARE @spid INT

DECLARE @getspid CURSOR

DECLARE @tString varchar(8000)

Set @getspid = Cursor For Select SPID from #temp

OPEN @getspid

FETCH NEXT FROM @getspid INTO @spid WHILE @@FETCH_STATUS = 0

BEGIN

SET @tString = ‘KILL ‘ + CAST(@spid AS VARCHAR(5))

EXEC(@tString)

FETCH NEXT FROM @getspid INTO @spid END

CLOSE @getspid

DEALLOCATE @getspid

exec sp_dboption @dbname, ‘offline’, ‘true’

 

Select SPID From master.dbo.sysprocesses where DB_ID(@dbname)=[DBID]  

Drop table #temp

 

 

 

 

SQLHero Dec Session – Microsoft Egypt

SQLHero delivered June Session in Microsoft SQL Server 2008

Topic	Microsoft SQL Server 2008
Date	Dec 29, 2009
Time	10:00 – 3:00
Location	Microsoft, Smart Village
Presenter	Ahmed Mosa
Presentation	download

 

SQL Server Major Backup Types

we have many types of backups but in this article i just discuses 3 types of backups

1. Full Backup:- Backup all data and database objects
2. Differential Backup:- Backup all data and database objects from last full backup
3. Transaction Log Backup:- Backup all transaction from Transaction log file from last official backup official backup can be(Full backup or differential backup or transaction log backup)

let’s discuss fig bellow

F1 is full backup from database 

T1 is Transaction Log backup with difference from F1

D1 is differential backup with difference from F1

T2 is Transaction Log backup with difference from D2

D2 is differential backup with difference from F1

T3 is Transaction Log backup with difference from D2

D3 is differential backup with difference from F1

T4 is Transaction Log backup with difference from D3

D4 is differential backup with difference from F1

T5 is Transaction Log backup with difference from D4

D5 is differential backup with difference from F1

 

SQL Server Recovery Model Types

SQL Server has 3 recovery model types which control types of backups so let’s discuss in more details all recovery types

when you do these operation sequential

right click any database—> properties –> select Pages –> options

you will see the dialog bellow

as you see in fig above you have 3 types

the relationship between backup types(Rows Headers) and Recovery Models(Columns Header) are

	Full	Bulk-logged	Simple
Full Backup	Yes	Yes	Yes
Differential Backup	Yes	Yes	Yes
Transaction Backup	Yes	Yes	No

it’s very clear that you can’t make trasaction log backup in simple mode.

so what is the Differential between Full mode and Bulk-Logged mode?

Full mode log any transaction in Transaction log file but Bulk-logged can’t log huge transaction to log file like bulk insert or create index in huge tables

you can use Bulk logged for more performance to insert bulk data to database because in this mode you will not log in  transaction log.

you can use Full mode to log any small things to transaction log file to feel safe when to return to end point of disaster

 

SQLHero Oct Session – Microsoft Egypt

SQLHero delivered June Session in SQL Enterprise Reporting (SQL2008)

Topic	SQL Enterprise Reporting (SQL2008)
Date	Oct 29, 2009
Time	10:00 – 3:00
Location	Microsoft, Smart Village
Presenter	Ahmed Mosa
Presentation	download

 

Partition Table in SQL Server 2008

1. What is Partition table?

Data is partitioned horizontally by range

2. How to Create Partition Table?

You can create partition table by 2 ways

1. programmability
2. wizard in SQL Server 2008 New

But you must understand 2 things before creation Partition Tables

Partition Function: Partition functions define partition boundaries and Boundary values can be assigned to LEFT or RIGHT

Partition Scheme: A partition scheme assigns partitions to filegroups

3. How to Create Partition Table by wizard in SQL Server 2008?

Right click on table then go to storage then select Create Partition as in fig bellow

Select a column you want to use it in partition as in fig bellow

Create Partition function as in fig bellow

Create Partition Scheme as in fig bellow

Determine ranges as in fig bellow

Create Partition by select run immediately as in fig bellow

    

Congratulation your table now partitioned

 

4. How to Create Partition Table programmability?

 

 

/***************************************************

1)Create database TestDb

****************************************************/

CREATE DATABASE [TestDb] ON PRIMARY

( NAME = N’TestDb’, FILENAME = N’C:\db\TestDb.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),

FILEGROUP [fg1]

( NAME = N’f1′, FILENAME = N’C:\db\f1.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),

FILEGROUP [fg2]

( NAME = N’f2′, FILENAME = N’C:\db\f2.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),

FILEGROUP [fg3]

( NAME = N’f3′, FILENAME = N’C:\db\f3.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),

FILEGROUP [fg4]

( NAME = N’f4′, FILENAME = N’C:\db\f4.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N’TestDb_log’, FILENAME = N’C:\db\TestDb_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)

GO

USE [TestDb]

GO

/***************************************************

3)Create Partition Function

****************************************************/

Create Partition Function pf_Date(Date)

as range right

For values(’01/01/2006′,’01/01/2007′,’01/01/2008′)

go

/***************************************************

3)Create Partition scheme

****************************************************/

Create Partition Scheme ps_Date

AS Partition pf_Date

to(fg1,fg2,fg3,fg4)

go

/***************************************************

3)Create Partition Contact Table

****************************************************/

CREATE TABLE [dbo].[Contact](

[ContactId] [int] IDENTITY(1,1) NOT NULL,

[FullName] [nvarchar](50) NULL,

[CreationDate] [date] NULL

)on ps_Date(CreationDate)

GO

/***************************************************

4)Insert rows to table

****************************************************/

insert into dbo.Contact values(‘amosa’,’01/01/2006′)

insert into dbo.Contact values(‘mmosa’,’01/01/2007′)

insert into dbo.Contact values(‘zemo’,’01/01/2008′)

insert into dbo.Contact values(‘test1′,’05/01/2005′)

insert into dbo.Contact values(‘test2′,’05/01/2006′)

insert into dbo.Contact values(‘test3′,’05/01/2007′)

insert into dbo.Contact values(‘test4′,’05/01/2008′)

insert into dbo.Contact values(‘test5′,’05/01/2009′)

go

/***************************************************

5)Query Table to know where did rows save

****************************************************/

Select *,$partition.pf_Date(CreationDate)FileGroupNo From dbo.Contact 

 

Be Ready for Microsoft SQL Server 2008 R2

to get more information about Microsoft SQLServer 2008 R2 you can visit SQLServer2008 R2