풀 백업 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