SQL Server Backup Script with TimeStamp

Posted by Alvin You
2015.07.02 10:34 SQL Server
크리에이티브 커먼즈 라이선스
Creative Commons License

SQL Server를 운영하다보면 백업 스케쥴링에 대해서 고민하게 됩니다. 아래 스크립트는 SQL Server에 생성된 사용자 Database들에 대해서 TimeStamp를 찍어 일단위로 지정된 폴더 경로에 데이터 베이스를 백업하도록 하는 스크립트입니다.

 

저작자 표시
신고
이 댓글을 비밀 댓글로

Table의 사용량(RowCount, Size) 정보 보기

Posted by Alvin You
2015.04.24 10:04 SQL Server
크리에이티브 커먼즈 라이선스
Creative Commons License

아래 스크립트는 Database내에 있는 모든 Table의 Row Count 정보와 Size 정보를 보여 주는 스크립트입니다.

  • Sp_MSForEachTable : Database내의 모든 테이블들을 돌며, 파라미터로 받은 SQL문을 수행
  • Sp_SpaceUsed : 파라미터로 받은 테이블의 사용량을 보여줌

 

exec sp_spaceused 'TBL_HOSP_Info'

 

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),

reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18),

unused VARCHAR(18))

 

EXEC sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '

 

SELECT TableName,CONVERT(bigint,rows) AS NumberOfRows,

CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB

FROM #RowCountsAndSizes

ORDER BY NumberOfRows DESC,SizeinKB DESC,TableName

 

DROP TABLE #RowCountsAndSizes

신고
Tags
이 댓글을 비밀 댓글로

Failed to initialize MSDB database for tuning (exit code: -1073741819). (DTAClient)

Posted by Alvin You
2015.04.15 21:47 SQL Server
크리에이티브 커먼즈 라이선스
Creative Commons License

Analyze Query in Database Engine Tuning Advisor 실행시 아래와 같은 오류가 발생한다면, MSDB에 DTA 관련 Objects들을 생성해 주면 됩니다.

[오류 메시지]

 

[스크립트 실행]

 

DTA_Objects_SQL.txt

 

신고
이 댓글을 비밀 댓글로

[Powershell]SQL Server 복원 작업

Posted by Alvin You
2015.04.07 10:43 SQL Server
크리에이티브 커먼즈 라이선스
Creative Commons License

프로젝트를 진행하다 보면 운영 DB를 백업 받아, 다시 DEV/TEST 계에 데이터를 다시 올리는 경우가 빈번하게 발생합니다. 아래 Powershell 스크립트 내용은 Backup 받은 파일을 Powershell을 이용해 Restore 하는 내용을 담고 있습니다.

업무에 참고하세요.

 

 

저작자 표시
신고
이 댓글을 비밀 댓글로

Upgrading SQL Server 2012 License Edition

Posted by Alvin You
2015.01.07 01:00 SQL Server
크리에이티브 커먼즈 라이선스
Creative Commons License

Dynamics AX의 Power BI가 SQL Server Enterprise에서 구동되는 관계로 로컬에 설치되어 있는 SQL Server의 License Edition을 변경해야 될 필요성이 생겼습니다. 그래서, 관련 자료를 찾아 보니 아래 방법을 통해서 License Edition을 변경 할 수 있습니다.

아래 절차는 Standard Edition à Enterprise Edition으로 변경하는 과정입니다.

1. SQL Server Enterprise Edition 설치 CD를 넣고 SQL Server Installation Center à Edition Upgrade를 실행합니다.

2. Product Key를 확인합니다.

3. 업그레이드 할 SQL Server Instance를 선택합니다.

4. 업그레이드 규칙을 확인합니다.

5. 업그레이드를 진행합니다.

6.Standard à Enterprise Edition 업그레이드가 완료되었습니다.

신고
이 댓글을 비밀 댓글로

시스템 테이블을 이용한 Table Names, Column Names, Data Types 조회

Posted by Alvin You
2014.03.13 11:26 SQL Server
크리에이티브 커먼즈 라이선스
Creative Commons License
프로젝트 산출물의 하나인 테이블 명세서나 DDL 스크립트를 참고해서 데이터를 분석하는 방법 보다는 아래 Query를 이용하는 방법! 강츄~

select SchemaName=SCHEMA_NAME(t.schema_id), TableName=t.name, ColumnName=c.name, DataType=ty.name, TextLength=c.max_length, c.precision, c.scale
from sys.tables t
join sys.columns c on c.object_id = t.object_id
join sys.types ty on ty.user_type_id =c.user_type_id
order by 1, 2, 3
저작자 표시
신고
Tags
이 댓글을 비밀 댓글로

모든 DB 테이블에서 특정값을 찾는 프로시저

Posted by Alvin You
2014.03.13 11:24 SQL Server
크리에이티브 커먼즈 라이선스
Creative Commons License

SQL Server Database내의 모든 테이블의 'char', 'varchar', 'nchar', 'nvarchar' 값에 들어있는 특정값을 찾아서 출력해 주는 Stored Procedure입니다.

향후 데이터 변경 프로젝트에서 사용하면 유용할 듯 합니다.

--


CREATE PROC [dbo].[SearchAllTables]
(
    @SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName =
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END
END

SELECT ColumnName, ColumnValue FROM @Results
END

저작자 표시
신고
Tags
이 댓글을 비밀 댓글로

모든 DB 테이블에서 특정값을 찾고 변경하는 프로시저

Posted by Alvin You
2014.03.13 11:23 SQL Server
크리에이티브 커먼즈 라이선스
Creative Commons License

이번엔 찾은 값을 다른값으로 변경해주는 기능까지 포함된 Stored Procedure입니다.
유용하게 쓰셈!

--
CREATE PROC [dbo].[SearchAndReplace]

(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string and replace it with another string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 2nd November 2002 13:50 GMT

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
      ) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' =  REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END

SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END

저작자 표시
신고
Tags
이 댓글을 비밀 댓글로

sp_MSforeachdb, sp_MSforeachtable 프로시저 활용

Posted by Alvin You
2014.03.13 11:21 SQL Server
크리에이티브 커먼즈 라이선스
Creative Commons License

master 데이터베이스는 sp_MSforeachtdb, sp_MSForeachtable 과 같은 유용한 Stored Procedure가 있습니다.

 

 

 

[sp_MSforeachdb]

sp_MSforeachdb 프로시저는 SQL Server 인스턴스 내에 있는 모든 데이터베이스의 이름을 반환하며 이 이름을 참조하여 지정된 반복문을수행합니다..

아래 스크립트는 인스턴스내의 모든 데이터베이스의 helpfile 정보를 보여줍니다..

use master

go

 

exec sp_MSforeachdb 'use ? exec sp_helpfile'

 

[sp_MSforeachtable]

sp_MSforeachtable 프로시저는 현재 데이터베이스 내의모든 테이블 이름을 반환하며 이 테이블이름을 참조하여 지정된 반복문을 수행 한다.

 아래 스크립트는 해당 데이터베이스에 존재하는 모든 테이블의 row count를테이블에 삽입하여 출력한다.

begin try

create table #rowcount (tablename varchar(128), rowcnt int)

end try begin catch end catch

 

exec sp_MSforeachtable

 'insert into #rowcount select ''?'',

              count(*) from ?'

select top 5 * from #rowcount

    order by tablename

drop table #rowcount

 

 위의 두 시스템 프로시저를 잘 활용하면 사용자 프로시저 생성시 코드를 간결하고 빠르게 개발 할 수 있을 듯 하다.

 

[참고자료]

l  http://www.codeproject.com/Articles/459536/SQL-Server-Applying-Filter-on-sp_MSforeachDB#

l  http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

저작자 표시
신고
Tags
이 댓글을 비밀 댓글로

SQL Server db lock 확인 방법

Posted by Alvin You
2013.09.24 22:47 SQL Server
크리에이티브 커먼즈 라이선스
Creative Commons License

AX Client에서 해당 프로그램을 실행시켰는데, 해당 프로그램이 아무런 응답없이 멈춰있다면 SQL Server DB Lock을 의심해 볼 수도 있습니다.

현재 진행중인 프로젝트가 DB Transaction이 많아, Stored Procedure를 호출해서 구성되는 화면이 많다보니 종종 화면이 멈춘듯한 느낌이 받을때가 많습니다.

이럴 경우 확인 방법은 sp_who2 라는 SQL 명령어를 통해서 blkBy 칼럼을 통해 확인할 수 있습니다.

image

실행중인 SQL문장을 보고 싶다면 아래 내용을 통해 확인이 가능합니다.

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 70

SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

image

당연히 Lock 이 걸린 프로세스를 Kill 명령어를 통해서 종료시키면, 해당 lock은 풀립니다.

신고
Tags
이 댓글을 비밀 댓글로

티스토리 툴바