Post reply

Need help with on demand script for transaction log backup

  • February 8, 2010 at 7:34 am

    #138818

    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

  • February 8, 2010 at 7:36 am

    #1115488

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

    ---------------------------------------------------------------------

  • February 8, 2010 at 7:39 am

    #1115490

    perhaps dbid > 4 as well to omit the system databases?

    ---------------------------------------------------------------------

  • February 8, 2010 at 7:44 am

    #1115494

    Great, Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply