Bora.Choi

SQL 2008

 December 19, 2007
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.

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

Create a folder, D:\Backup, as shown below. [Refer 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

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

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

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

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

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 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'

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

이 글은 스프링노트에서 작성되었습니다.

•  Different Types of SQL Server Database Recovery Models

  • http://www.sql-server-performance.com/articles/dba/Database_Recovery_Models_in_SQL_Server_p1.aspx

  • simple mode

    • In Simple Recovery Model SQL Server logs minimal amount of information in the transaction log. SQL Server basically truncates the transaction log whenever the transaction log becomes 70 percent full or the active portion of the transaction log exceeds the size that SQL Server could recover in the amount of time which is specified in the Recovery Interval server level configuration

• Switching Database Recovery Models

  • http://www.sql-server-performance.com/articles/dba/Database_Recovery_Models_in_SQL_Server_p2.aspx

• How to Switch Database Recovery Model

  • http://www.sql-server-performance.com/articles/dba/Database_Recovery_Models_in_SQL_Server_p3.aspx

이 글은 스프링노트에서 작성되었습니다.

백업 DB에서 최근  백업정보

• DBA는 백업은 중요한 일중에 하나이다. 모든 장비를 확인해 가면서 백업 성공/실패를 확인할 수 없으므로 마지막으로 백업된 날짜를 확인하여 메일로 공지되는것이 좋은 방법이다. 
• 아래 쿼리는 그 내용이며 위 쿼리와 조합을 해서 백업보고를 받게 할 것이다.  
• 해당 보고는 DTS 패키지나 SSIS를 사용해서 처리하거나 JOB으로 장비별로 OSQL을 접속해서 처리하도록 해도 된다. (링크드 서버를 사용하지 않기 위햬) 

1. select max(isnull(datediff(dd,b.backup_start_date,getdate()),0)) as 'Number of Days since last backup',
   b.type as  'Backup type (D-database,L-log)', b.backup_size, d.name as database_name
   from  master..sysdatabases d with (nolock)
    left join msdb..backupset b  with (nolock) on d.name = b.database_name
    and b.backup_start_date = (select max(backup_start_date)
                                      from msdb..backupset b2
                                      where b.database_name = b2.database_name
                                     and b2.type = 'D')
   where d.name != 'tempdb'
   group by d.name, b.type, b.backup_size
   union all
   select max(isnull(datediff(dd,b.backup_start_date,getdate()),0))
   as 'Number of Days since last backup',
   b.type as  'Backup type (D-database,L-log)', b.backup_size, d.name
   as database_name
   from  master..sysdatabases d with (nolock)
   join msdb..backupset b  with (nolock)
   on d.name = b.database_name
   and b.backup_start_date = (select max(backup_start_date)
                                      from msdb..backupset b2
                                      where b.database_name = b2.database_name
                                      and b2.type = 'L')
   group by d.name, b.type, b.backup_size
   order by d.name,b.type   -- optional 
2. ================================================
   다른 방법 
3. ================================================
   SELECT
    cast(database_name AS varchar(30)) AS [DBName],
    cast(user_name AS varchar(30)) AS [UserName],
    str(cast(backup_size AS decimal(20,2)) / 1048576 ,10,2) + ' MB' AS [BackupSize],
    cast(datediff(n,backup_start_date,backup_finish_date) AS varchar(5)) + ' min.' AS [Duration],
    cast(datediff(dd,backup_finish_date,Getdate()) AS varchar(10))  AS [BackupAge],
    convert(varchar(20),backup_finish_date) AS [FinishDate],
    physical_device_name AS [Location],
    mf.device_type
   FROM
    master..sysdatabases DB
    JOIN
    msdb..BACKUPSET BS
    ON DB.name = BS.database_name
    JOIN msdb..backupmediaset MS
    ON
    BS.media_set_id = MS.media_set_id
    JOIN msdb..backupmediafamily MF
    ON
    BS.media_set_id = MF.media_set_id
    JOIN
    (
     SELECT
      max(backup_set_id) AS backup_set_id
     FROM
      msdb..BACKUPSET BS
      JOIN msdb..backupmediaset MS
       ON
       BS.media_set_id = MS.media_set_id
       JOIN msdb..backupmediafamily MF
       ON
       BS.media_set_id = MF.media_set_id
     WHERE
      type = 'D'
      AND mf.device_type <> 7
     GROUP BY database_name
    ) MaxBackup
    ON
    BS.backup_set_id = MaxBackup.backup_set_id
   WHERE
    type = 'D' 
    

이 글은 스프링노트에서 작성되었습니다.

백업

전체 DB를 백업하는 간단한 스크립트.

저장 프로시저로 만들어서 Job 으로 이용해도 됨.

Simple script to backup all databases

Problem
Sometimes things that seem complicated are much easier then you think and this is the power of using T-SQL to take care of repetitive tasks.  One of these tasks may be the need to backup all databases on your server.   This is not a big deal if you have a handful of databases, but I have seen several servers where there are 100+ databases on the same instance of SQL Server.  You could use Enterprise Manager to backup the databases or even use Maintenance Plans, but using T-SQL is a much simpler and faster approach.

Solution
With the use of T-SQL you can generate your backup commands and with the use of cursors you can cursor through all of your databases to back them up one by one.  This is a very straight forward process and you only need a handful of commands to do this.

Here is the script that will allow you to backup each database within your instance of SQL Server.  You will need to change the @path to the appropriate backup directory and each backup file will take on the name of "DBnameYYYDDMM.BAK".

1.  DECLARE @name VARCHAR(50) -- database name 
   DECLARE @path VARCHAR(256) -- path for backup files 
   DECLARE @fileName VARCHAR(256) -- filename for backup 
   DECLARE @fileDate VARCHAR(20) -- used for file name
   
   SET @path = 'C:\Backup\' 
   
   SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
   
   DECLARE db_cursor CURSOR FOR 
   SELECT name 
   FROM master.dbo.sysdatabases 
   WHERE name NOT IN ('master','model','msdb','tempdb') 
   
   OPEN db_cursor  
   FETCH NEXT FROM db_cursor INTO @name  
   
   WHILE @@FETCH_STATUS = 0  
   BEGIN  
          SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
          BACKUP DATABASE @name TO DISK = @fileName 
   
          FETCH NEXT FROM db_cursor INTO @name  
   END  
   
   CLOSE db_cursor  
   DEALLOCATE db_cursor

In this script we are bypassing the system databases, but these could easily be included as well.  You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases.  Any way you choose to use it, this script gives you the starting point to simply backup all of your databases.

Automating Transaction Log Backups for All Databases

Problem
Maintenance plans are a great thing, but sometimes the end results are not what you expect.  The whole idea behind maintenance plans is to simplify repetitive maintenance tasks without you having to write any additional code.  For the most part maintenance plans work without a problem, but every once in awhile things do not go as planned. Two of the biggest uses of maintenance plans are issuing full backups and transaction log backups.  What other approaches are there to issue transaction log backups for all databases without using a maintenance plan?

Solution
With the use of T-SQL you can generate your transaction log backups and with the use of cursors you can cursor through all of your databases to back them up one by one. With the use of the DATABASEPROPERTYEX function we can also just address databases that are either in the FULL or BULK_LOGGED recovery model since you can not issue transaction log backups against databases in the SIMPLE recovery mode.

Here is the script that will allow you to backup the transaction log for each database within your instance of SQL Server that is either in the FULL or BULK_LOGGED recovery model.

You will need to change the @path to the appropriate backup directory and each backup file will take on the name of "DBname_YYYDDMM_HHMMSS.TRN".

1. DECLARE @name VARCHAR(50) -- database name  
   DECLARE @path VARCHAR(256) -- path for backup files  
   DECLARE @fileName VARCHAR(256) -- filename for backup  
   DECLARE @fileDate VARCHAR(20) -- used for file name 
   
   SET @path = 'C:\Backup\'  
   
   SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
      + '_' 
      + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
   
   DECLARE db_cursor CURSOR FOR  
   SELECT name 
   FROM master.dbo.sysdatabases 
   WHERE name NOT IN ('master','model','msdb','tempdb') 
      AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED')
   
   OPEN db_cursor   
   FETCH NEXT FROM db_cursor INTO @name   
   
   WHILE @@FETCH_STATUS = 0   
   BEGIN   
          SET @fileName = @path + @name + '_' + @fileDate + '.TRN'  
          BACKUP LOG @name TO DISK = @fileName  
   
          FETCH NEXT FROM db_cursor INTO @name   
   END   
   
   CLOSE db_cursor   
   DEALLOCATE db_cursor  

In this script we are bypassing the system databases, but these could easily be included as well. You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases. Any way you choose to use it, this script gives you the starting point to simply backup all of your databases. 

이 글은 스프링노트에서 작성되었습니다.