Need help with on demand script for transaction log backup

Last Post 08 Feb 2010 05:55 AM by Markus_SQL. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Markus_SQL
New Member
New Member

--
08 Feb 2010 05:36 AM
I have put together this script to backup transaction logs, however, how do I find dbs in simple mode to exclude this script building the backup log for them.

DECLARE @dateString CHAR(12), @dayStr CHAR(2), @monthStr CHAR(2), @hourStr CHAR(2), @minStr CHAR(2)
--month variable
IF (SELECT LEN(CAST(MONTH(GETDATE()) AS CHAR(2))))=2
SET @monthSTR=CAST(MONTH(GETDATE()) AS CHAR(2))
ELSE
SET @monthSTR= '0' + CAST(MONTH(GETDATE()) AS CHAR(2))
--day variable
IF (SELECT LEN(CAST(DAY(GETDATE()) AS CHAR(2))))=2
SET @daySTR=CAST(DAY(GETDATE()) AS CHAR(2))
ELSE
SET @daySTR='0' + CAST(DAY(GETDATE()) AS CHAR(2))
--hour variable
IF (SELECT LEN(DATEPART(hh, GETDATE())))=2
SET @hourStr=CAST(DATEPART(hh, GETDATE()) AS CHAR(2))
ELSE
SET @hourStr= '0' + CAST(DATEPART(hh, GETDATE()) AS CHAR(2))
--minute variable
IF (SELECT LEN(DATEPART(mi, GETDATE())))=2
SET @minStr=CAST(DATEPART(mi, GETDATE()) AS CHAR(2))
ELSE
SET @minStr= '0' + CAST(DATEPART(mi, GETDATE()) AS CHAR(2))
--name variable based on time stamp
SET @dateString=CAST(YEAR(GETDATE()) AS CHAR(4)) + @monthStr + @dayStr + @hourStr + @minStr
--=================================================================
DECLARE @IDENT INT, @sql VARCHAR(1000), @DBNAME VARCHAR(200)
SELECT @IDENT=MIN(dbid) FROM dbo.sysDATABASES WHERE [dbid] > 0 AND NAME NOT IN ('TEMPDB')
WHILE @IDENT IS NOT NULL
BEGIN
SELECT @DBNAME = NAME FROM dbo.sysDATABASES WHERE dbid = @IDENT
/*Change disk location here as required*/
SELECT @SQL = 'BACKUP LOG '+@DBNAME+' TO DISK = ''E:\SQL_Backup\'+@DBNAME+'_db_' + @dateString +'.TRN'' WITH INIT'
EXEC (@SQL)
SELECT @IDENT=MIN(dbid) FROM dbo.sysDATABASES WHERE [dbid] > 0 AND dbid > @IDENT AND NAME NOT IN ('TEMPDB')
END
Markus_SQL
New Member
New Member

--
08 Feb 2010 05:55 AM
Got the answer....


and databasepropertyex([name], 'recovery') in ('FULL','BULK_LOGGED')




Acceptable Use Policy
---