Otomatik Primary Key üretimini TSQL ile durdurmak ve başlatmak

-- Toplu INSERT işlemi
INSERT INTO hedefTablo(PKalanAdi, alanAdi1, alanAdi2...)
SELECT (PKalanAdi, alanAdi1, alanAdi1....) FROM kaynakTablo

-- Yukarıdaki durum Primary Key, auto increament olarak ayarlanmışsa
-- çalışmayacak çünkü, haberlerin id lerinide geçiyoruz.
-- Otomatik üretmemesi için aşağıdaki SQL çalıştırılır
SET IDENTITY_INSERT tabloAdi OFF

-- Tekrar aktif etmek için :
SET IDENTITY_INSERT tabloAdi ON

MSSQL Backup Yöntemleri

ref: http://www.databasejournal.com/features/mssql/article.php/3716561/BACKUP-compression-in-SQL-Server-2008.htm

BACKUP compression in SQL Server 2008
By Muthusamy Anantha Kumar aka The MAK

After a long wait, SQL Server 2008 is going to be released with inbuilt compression for backups. Until the release of SQL Server 2005, compression was only available via third party backup software such as SQL LiteSpeed, SQLZip, etc.

This article demonstrates how to take Full, Differential and Transactional log backups with compression, without compression and how to enable compression as a default.

Note: This article is written based on the SQL Server 2008 – Nov CTP.

Let us create a database “MyDB” as shown below.

USE [master]
GO

/****** Object: Database [MyDB]
Script Date: 12/10/2007 01:08:14 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDB')
DROP DATABASE [MyDB]
GO

USE [master]
GO
/****** Object: Database [MyDB]
Script Date: 12/10/2007 01:05:09 ******/
CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB_Data',
FILENAME =
N'F:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\
MSSQL\DATA\MyDB_Data.mdf' ,
SIZE = 2176KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MyDB_log',
FILENAME =
N'F:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\
MSSQL\DATA\MyDB_log.LDF' ,
SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [MyDB] SET RECOVERY FULL
GO

Now let us create a table “MyTable” in the database “MyDB” as shown below

USE [MyDB]
GO

/****** Object: Table [dbo].[MyTable]
Script Date: 12/10/2007 01:12:00 ******/
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]')
AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO
USE [MyDB]
GO

/****** Object: Table [dbo].[MyTable]
Script Date: 12/10/2007 01:12:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[MyTable](
[id] [int] NULL,
[name] [char](100) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Let’s add 10000 rows of data to the table “MyTable” as shown below.

USE [MyDB]
GO

declare @myid int
set @myid=1
while @myid<=10000
begin
insert into MyTable select @myid, 'A'+convert(varchar(10),@myid)
set @myid =@myid +1
end

Select the data using the following T-SQL command. [Refer Fig 1.0]

use MyDB
go
Select * from MyTable
go


Fig 1.0

Create a folder, D:\Backup, as shown below. [Refer Fig 1.1]


Fig 1.1

Now let us take a full backup as shown below. [Refer Fig 1.2]

Backup Database MyDB to disk ='d:\Backup\MyDB_Full.bak' with init


Fig 1.2

Let’s add some more data [1000 rows] to the table “MyTable” in the database “MyDB” as shown below.

USE [MyDB]
GO

declare @myid int
set @myid=1
while @myid<=1000
begin
insert into MyTable select @myid, 'A'+convert(varchar(10),@myid)
set @myid =@myid +1
end

Now let us a take a transaction log backup, as shown below. [Refer Fig 1.3]

Backup log MyDB to disk ='d:\Backup\MyDB_TLog_1.bak' with init


Fig 1.3

By default, SQL Server does not compress the backups. We can compress the backups in two different ways.

a. Change the default behavior of SQL Server to compress all of the backups.

b. Add an optional keyword “With COMPRESSION” in the backup clause.

The database MyDB and the Full backup, Transactional log backup that we took were without compression. That is the default SQL Server behavior.

Now let’s take a full backup of the database with compression as shown below. [Refer Fig 1.4]

Backup Database MyDB to disk ='d:\Backup\MyDB_Full2.bak' with COMPRESSION



Fig 1.4

From figure 1.4, you can see that the size of MyDB_Full2.bak is much smaller when compared to MyDB_Full.Bak and MyDB_Tlog_1.bak.

Add some more data [1000 rows] to the table “MyTable” in the database “MyDB” as shown below.

USE [MyDB]
GO

declare @myid int
set @myid=1
while @myid<=1000
begin
insert into MyTable select @myid, 'A'+convert(varchar(10),@myid)
set @myid =@myid +1
end

Now let’s take a transaction log backup as shown below. [Refer Fig 1.5]

Backup log MyDB to disk ='d:\Backup\MyDB_TLog_new.bak' with COMPRESSION


Fig 1.5

In figure 1.5, you can see that the size of MyDB_Tlog_new.bak is much smaller when compared to MyDB_Tlog_1.bak.

Let’s take a differential backup without compression and compare it with the differential backup with compression.

Execute the following commands as shown below. [Refer Fig 1.6]

backup database MyDB to disk ='d:\Backup\MyDB_Diff.bak' with differential

backup database MyDB to disk ='d:\Backup\MyDB_Diff2.bak' with differential, COMPRESSION


Fig 1.6

Fig 1.6 shows the compression ratio between MyDB_Diff.bak and MyDB_Diff2.bak.

Let’s change the default behavior of SQL Server from uncompressed backup to compressed. This can be done using the SP_CONGIFURE command.

Execute the command as shown below.

USE master
GO
EXEC sp_configure 'backup compression default', '1'
GO
RECONFIGURE WITH OVERRIDE
GO

Now let’s take a full backup of the MyDB database, without the optional keyword “WITH COMPRESSION”. [Refer Fig 1.7]

Backup Database MyDB to disk ='d:\Backup\MyDB_Full3.bak'


Fig 1.7

From figure 1.7, we can clearly see that the backup by default is compressed.
Conclusion

This article demonstrated how to take a Full backup, Differential backup and transactional log backup with or without compression and how to enable compression as a default.

» See All Articles by Columnist MAK

MS SQL de hatalar

Sistemde kayıtlı mesajları görmek için:

SELECT *
FROM sys.messages


Hata mesajı eklemek:

sp_addmessage @msgnum=50001, @severity=11, @msgtext='Üye zaten sisteme kayıtlı', @with_log='true'

with_log = true: event log'a eklesin.


BEGIN TRY
RAISERROR ('Errors found, please fix these errors and retry', 1, 2) WITH SETERROR

// Complete normal process if no errors encountered above
PRINT 'IMPORT SUCCEEDED'
END TRY
BEGIN CATCH
PRINT 'IMPORT ABORTED. ERRORS ENCOUNTERED'
END CATCH



USE AdventureWorks;
GO

-- Declare and set variable
-- to track number of retries
-- to try before exiting.
DECLARE @retry INT;
SET @retry = 5;

--Keep trying to update
-- table if this task is
-- selected as the deadlock
-- victim.
WHILE (@retry > 0)
BEGIN
BEGIN TRY
BEGIN TRANSACTION;

UPDATE my_sales
SET sales = sales + 1
WHERE itemid = 2;

WAITFOR DELAY '00:00:07';

UPDATE my_sales
SET sales = sales + 1
WHERE itemid = 1;

SET @retry = 0;

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Check error number.
-- If deadlock victim error,
-- then reduce retry count
-- for next update retry.
-- If some other error
-- occurred, then exit
-- retry WHILE loop.
IF (ERROR_NUMBER() = 1205)
SET @retry = @retry - 1;
ELSE
SET @retry = -1;

-- Print error information.
EXECUTE usp_MyErrorLog;

IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
END CATCH;
END; -- End WHILE loop.
GO