로그 백업 Query

DECLARE CurQuery CURSOR
FOR

select name from dbo.sysdatabases
where name not in ('master', 'msdb', 'model', 'pubs', 'tempdb', 'Northwind') and
convert(sysname,DatabasePropertyEx(name,'Status')) ='ONLINE' and
convert(sysname,DatabasePropertyEx(name,'Updateability')) <>'READ_ONLY' and
convert(sysname,DatabasePropertyEx(name,'Recovery')) <>'SIMPLE'
DECLARE @name varchar(100)

OPEN CurQuery

FETCH NEXT FROM CurQuery INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
declare @dir nvarchar(200)
set @dir = N'e:\DBBackUP\' + @name+ '\' + @name + '_' + + convert(nvarchar(20), getDate(), 112) + replace(substring(convert(nvarchar(20), getDate(), 114), 1,5),':','') + '.lg'

backup log @name to DISK = @dir
exec [USP_RestoreQuery] @dir ,@name

PRINT @dir

FETCH NEXT
FROM CurQuery
INTO @name

END --end of while

CLOSE CurQuery

DEALLOCATE CurQuery

증분 백업 Query

CREATE TABLE #Tmp(
Query VARCHAR(4000)
)



DECLARE CurQuery CURSOR
FOR

select name from dbo.sysdatabases
where name not in ('master', 'msdb', 'model', 'pubs', 'tempdb', 'Northwind') and
convert(sysname,DatabasePropertyEx(name,'Status')) ='ONLINE' and
convert(sysname,DatabasePropertyEx(name,'Updateability')) <>'READ_ONLY' and
convert(sysname,DatabasePropertyEx(name,'Recovery')) <>'SIMPLE'
DECLARE @name varchar(100)

DECLARE @ndir1 varchar(1000)
DECLARE @ndir2 varchar(1000)
set @ndir1= convert(nvarchar(20), getDate(), 112)
set @ndir2= replace(substring(convert(nvarchar(20), getDate(), 114), 1,5),':','')



OPEN CurQuery

FETCH NEXT FROM CurQuery INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
declare @dir nvarchar(200)
set @dir = N'D:\DBBackUP\' + @name+ '\' + @name + '_' + + @ndir1 +@ndir2+ '.dif'



backup database @name to DISK = @dir with differential
exec [USP_RestoreQuery] @dir ,@name


INSERT INTO #Tmp
(Query)
VALUES
( ' [USP_RestoreQuery] ' + '''' + @dir + '''' + ',' + '''' + @name + '''' )



PRINT @dir

FETCH NEXT
FROM CurQuery
INTO @name

END --end of while

CLOSE CurQuery

DEALLOCATE CurQuery





DECLARE CurQuery2 CURSOR
FOR
select Query from #Tmp
DECLARE @Q nvarchar(4000)

OPEN CurQuery2

FETCH NEXT FROM CurQuery2 INTO @Q

WHILE @@FETCH_STATUS = 0
BEGIN

--PRINT @Q
EXEC sp_executesql @Q
print ' '
print ' '
print ' '

FETCH NEXT
FROM CurQuery2
INTO @Q

END --end of while

CLOSE CurQuery2

DEALLOCATE CurQuery2


TRUNCATE TABLE #Tmp
drop table #Tmp

풀 백업 Query

CREATE TABLE #Tmp(
Query VARCHAR(4000)
)


DECLARE CurQuery CURSOR
FOR

select name from dbo.sysdatabases
where name not in ('master', 'msdb', 'model', 'pubs', 'tempdb', 'Northwind') and
convert(sysname,DatabasePropertyEx(name,'Status')) ='ONLINE' and
convert(sysname,DatabasePropertyEx(name,'Updateability')) <>'READ_ONLY' and
convert(sysname,DatabasePropertyEx(name,'Recovery')) <>'SIMPLE'

DECLARE @name varchar(100)


DECLARE @ndir1 varchar(1000)
DECLARE @ndir2 varchar(1000)
set @ndir1= convert(nvarchar(20), getDate(), 112)
set @ndir2= replace(substring(convert(nvarchar(20), getDate(), 114), 1,5),':','')

OPEN CurQuery

FETCH NEXT FROM CurQuery INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
declare @dir nvarchar(200)
set @dir = N'D:\DBBackUP\' + @name+ '\' + @name + '_' + + @ndir1 +@ndir2+ '.dmp'



backup database @name to DISK = @dir
--exec [USP_RestoreQuery] @dir ,@name

--PRINT 'exec [USP_RestoreQuery] ' + '''' + @dir + '''' + ',' + '''' + @name + ''''

INSERT INTO #Tmp
(Query)
VALUES
( ' [USP_RestoreQuery] ' + '''' + @dir + '''' + ',' + '''' + @name + '''' )



FETCH NEXT
FROM CurQuery
INTO @name

END --end of while

CLOSE CurQuery

DEALLOCATE CurQuery


DECLARE CurQuery2 CURSOR
FOR
select Query from #Tmp
DECLARE @Q nvarchar(4000)

OPEN CurQuery2

FETCH NEXT FROM CurQuery2 INTO @Q

WHILE @@FETCH_STATUS = 0
BEGIN

--PRINT @Q
EXEC sp_executesql @Q
print ' '
print ' '
print ' '

FETCH NEXT
FROM CurQuery2
INTO @Q

END --end of while

CLOSE CurQuery2

DEALLOCATE CurQuery2


TRUNCATE TABLE #Tmp
drop table #Tmp

백업 파일 자동 복원 Query 생성

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO





--[USP_RestoreQuery] 'E:\db 백업\완료121.254.134.170\DBBACKUP\TvianImnews\TvianImnews_201010142359.dmp' ,'MyPageDb'

ALTER PROCEDURE [USP_RestoreQuery]
@path varchar(1000),
@dbname varchar(1000)
AS
SET NOCOUNT ON
BEGIN

--declare @path varchar(1000)
--declare @dbname varchar(1000)
declare @Query varchar(1000)

--set @path = 'E:\db 백업\211.233.27.90\dbbackup\MyPageDb\MyPageDb_201010142335.dmp'
--set @path = 'E:\db 백업\완료121.254.134.170\DBBACKUP\TvianImnews\TvianImnews_1.dif'
--set @dbname = 'MyPageDb'


CREATE TABLE #dbfiles(
LogicalName NVARCHAR(128)
,PhysicalName NVARCHAR(260)
,Type CHAR(1)
,FileGroupName NVARCHAR(128)
,Size numeric(20,0)
,MaxSize numeric(20,0) /*

,FileId INT
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0)
,UniqueId uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInBytes INT
,SourceBlockSize INT
,FilegroupId INT
,LogGroupGUID uniqueidentifier
,DifferentialBaseLSN numeric(25)
,DifferentialBaseGUID uniqueidentifier
,IsReadOnly INT
,IsPresent INT */
)
set @Query = 'Restore FileListOnly from Disk =''' + @path + ''''
INSERT #dbfiles
EXEC(@Query)



DECLARE CurQuery CURSOR
FOR

SELECT LogicalName, PhysicalName --, Type
FROM #dbfiles
--select ';' as LogiclName,'fdsf' as physicalName

DECLARE @LogiclName varchar(1000)
DECLARE @physicalName varchar(1000)

OPEN CurQuery

FETCH NEXT FROM CurQuery INTO @LogiclName,@physicalName

if right(@path,3) <>'dif'
BEGIN
print 'RESTORE DATABASE '+ @dbname +' FROM '
print 'DISK = ''' + @path +''' WITH '
END
WHILE @@FETCH_STATUS = 0
BEGIN
if right(@path,3) <>'dif'
BEGIN
PRINT 'MOVE ''' + @LogiclName + '''' + ' To '''+ @physicalName +''','

END


FETCH NEXT
FROM CurQuery
INTO @LogiclName,@physicalName

END --end of while

CLOSE CurQuery

DEALLOCATE CurQuery
if right(@path,3) <>'dif'
BEGIN
PRINT 'FILE =1, noRECOVERY , REPLACE'
END

if right(@path,3) ='dif'
BEGIN
PRINT 'RESTORE DATABASE '+ @dbname +' FROM DISK = '''+ @physicalName +''' WITH RECOVERY'
PRINT 'alter database '+ @dbname +' set multi_user with rollback immediate'
End

print ' '
print ' '
print ' '
print ' '


TRUNCATE TABLE #dbfiles
drop table #dbfiles

END





GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SQL 정보 관련 조회

버전을 확인하는 방법

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')


SELECT @@VERSION


http://support.microsoft.com/kb/321185/ko