Post reply

Maintenance Plan wont run from package

  • April 15, 2011 at 4:31 am

    #249043

    Hi there, wondered if anyone could help me with a very frustrating issue:

    I have created a maintenance plan in sql server 2008 to backup a number of databases. I have pathed the backup destination to a networked location (which the server i am backing up has permission to write to). When i execute the plan either through a agent job or the package itself i get the following error:

    "Started: 10:35:54 Could not load package "\Maintenance Plans\backup2" because of error 0x800401F3. Description: Invalid class string Source: Started: 10:35:54 Finished: 10:35:54 Elapsed: 0.063 seconds. The package could not be loaded. The step failed."

    Does anyone have any ideas why this is happening? Any help would be much appreciated!

    Thanks

  • April 18, 2011 at 11:57 pm

    #1313179

    Hi ,

    This might be of some help.

    http://qa.sqlservercentral.com/Forums/Topic1064590-391-2.aspx#bm1076255

    Regards

    Jody

  • April 19, 2011 at 2:25 pm

    #1313636

    try this....create SP

    then call it this way....EXEC BackupDbs 'Database_LIVE', 'F:\Backup', 'Database','bak', 'Y', 'N'

    USE [master]

    GO

    /****** Object: StoredProcedure [dbo].[BackupDb] Script Date: 04/19/2011 15:21:50 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROC [dbo].[BackupDb]

    @db_name sysname

    ,@folder nvarchar(255)

    ,@backup_type varchar(13)

    ,@backup_extension varchar(10)

    ,@with_checksum char(1) = 'Y'

    ,@do_verification char(1) = 'Y'

    AS

    DECLARE @sql nvarchar(4000)

    DECLARE @filename nvarchar(255)

    DECLARE @full_path_and_filename nvarchar(1000)

    DECLARE @err_msg nvarchar(2000)

    DECLARE @crlf varchar(2)

    SET @crlf = CHAR(13) + CHAR(10)

    --Verify valid backup type

    IF @backup_type NOT IN('DATABASE', 'LOG', 'DIFFERENTIAL')

    BEGIN

    SET @err_msg = 'Backup type ' + @backup_type + 'is not valid. Allowed values are DATABASE, LOG and DIFFERENTIAL'

    RAISERROR(@err_msg, 16, 1)

    RETURN -101

    END

    --Make sure folder name ends with '\'

    IF RIGHT(@folder, 1) <> '\'

    SET @folder = @folder + '\'

    --Make file extension starts with '.'

    IF LEFT(@backup_extension, 1) <> '.'

    SET @backup_extension = '.' + @backup_extension

    --Construct filename

    SET @filename = @db_name + '_backup_' + REPLACE(REPLACE(REPLACE(CONVERT(char(16), CURRENT_TIMESTAMP, 120), '-', ''), ' ', ''), ':', '')

    --Construct full path and file name

    SET @full_path_and_filename = @folder + @filename + @backup_extension

    --Construct backup command

    SET @sql = 'BACKUP ' + CASE @backup_type WHEN 'LOG' THEN 'LOG' ELSE 'DATABASE' END + ' ' + QUOTENAME(@db_name) + @crlf

    SET @sql = @sql + 'TO DISK = ' + QUOTENAME(@full_path_and_filename,'''') + @crlf

    SET @sql = @sql + 'WITH ' + @crlf

    SET @sql = @sql + 'RETAINDAYS=1,' + @crlf

    SET @sql = @sql + ' INIT,' + @crlf

    SET @sql = @sql + ' NAME = ' + QUOTENAME(@filename,'''') + ',' + @crlf

    SET @sql = @sql + 'COMPRESSION,' + @crlf

    IF @backup_type = 'DIFFERENTIAL'

    SET @sql = @sql + ' DIFFERENTIAL,' + @crlf

    IF @with_checksum <> 'N'

    SET @sql = @sql + ' CHECKSUM,' + @crlf

    --Add backup option below if you want to!!!

    --Remove trailing comma and CRLF

    SET @sql = LEFT(@sql, LEN(@sql) - 3)

    --PRINT @sql

    EXEC(@sql)

    IF @do_verification = 'Y'

    RESTORE VERIFYONLY FROM DISK = @full_path_and_filename

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

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