Dynamically Script Database Backups

  • Comments posted to this topic are about the item Dynamically Script Database Backups

  • Nice idea. But I'm getting error while trying to create the proc.

    Msg 102, Level 15, State 1, Procedure USP_BackupDatabasesOnPr01, Line 32

    Incorrect syntax near '?'.

    Seems that there's something on the previous line SQL doesn't like.

    Ken

  • Short way Backups of All User Databases with sp_msforeachdb undocumented sp.

    MS SQL Server DBA
    www.mehmetguzel.net

  • Hi Ken,

    Sorry about that.

    please try below:

    USE [master]

    GO

    /****** Object: StoredProcedure [dbo].[USP_BackupDatabasesOnPr01] Script Date: 02/14/2011 09:04:17 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    alter Proc [dbo].[USP_BackupDatabasesOnPr01] -- exec USP_BackupDatabasesOnPr01 'V:\Data\January Backups\'

    @path nvarchar(4000)

    as

    /*

    Author:Shanu Hoosen

    Date:Created:31/01/2011

    Execute as:Exec USP_BackupDatabasesOnPr01 'drive:\FolderPath'

    */

    declare @sql nvarchar(4000)

    declare @i nvarchar(3)

    declare @mx int

    declare @DBName nvarchar(500)

    --declare @path nvarchar(4000)

    --set @path = 'V:\Data\January Backups\'

    select ROW_NUMBER() OVER(ORDER BY name)id,name

    into #d

    from sysdatabases

    where sid <> 0x01

    set @i = (select min(id) from #d)

    set @mx = (select max(id)+1 from #d)

    while @i <> @mx

    begin

    set @DBName=(select '['+name+']' from #d where id = @i)

    set @sql = 'BEGIN TRY

    BACKUP DATABASE '+ @DBName +'

    TO DISK = N'''+@path+ replace(replace (@DBName, '[',''),']','') +'-'+ replace(cast(getdate() as nvarchar(500)),':','')+'.bak'''+

    ' WITH NOFORMAT, NOINIT,

    NAME = N'''+@DBName +'-Full Database Backup'''+','

    +'SKIP, NOREWIND, NOUNLOAD, STATS = 10

    END TRY

    BEGIN CATCH

    SELECT

    ERROR_NUMBER() AS ErrorNumber,

    ERROR_SEVERITY() AS ErrorSeverity,

    ERROR_STATE() AS ErrorState,

    ERROR_PROCEDURE() AS ErrorProcedure,

    ERROR_LINE() AS ErrorLine,

    END CATCH'

    --print(@sql)

    exec(@sql)

    set @i =@i+1

    end

    drop table #d

  • Ok, I don't know what you did there but this version compiles and executes (I took out the call to executing the actual backups in favor of printing out the string).

    Thanks,

    Ken

  • This was the original version that I created.

    The only difference is, should it fail eg space limitations, it will not give the effected DB.

    By adding '@DBName' + errormessage() on the last line will overcome the shortfall.

    But as long as it works:)

  • Just ran this on a WIN2K Sql SERVER providing the UNC path to the backup shared network drivespace which I verified I have access to and received this error...

    Server: Msg 195, Level 15, State 10, Procedure USP_BackupDatabasesOnPr01, Line 18

    'ROW_NUMBER' is not a recognized function name.

    Server: Msg 170, Level 15, State 1, Procedure USP_BackupDatabasesOnPr01, Line 32

    Line 32: Incorrect syntax near '?'.

    Any thoughts?

    Thanks..

  • Row_Number() was introduced in SQL 2005. Are you on that or is your SQL Server 2000?

    Ken

  • I'm on a SQL 2000 server....

  • An alternate to the row_number() function would be to create a table with an identity (1,1) column.

    All we are trying to acheive is a sequential increase by 1, which the loop can pick up. During the insert ignore the Identity column.

    Let me know if this helps.

    Shanu

  • Thanks for another good script.

Viewing 11 posts - 1 through 10 (of 10 total)

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