none
DatabaseBackup RRS feed

  • Question

  • On SQL Server 2012 Version

    I was trying to create a package to perform regular backup and archive of databases on my server.  I have created a table to store selected list of databases to be backed up, however I see that the backup database task is providing the drop down list to select the db.  Is there anyway to parameterized the db list?

    Because I want to have control in table to decide which db to backup etc.  I have created object variable to hold the list of dbs using esql task, and defined couple of variables to catch these values in for each loop

    I want to run backup of only those dbs which I list in the table (or flag with yes or no from db table)

     


    Neil



    • Edited by Neilcse Sunday, February 19, 2017 5:19 PM
    Sunday, February 19, 2017 4:42 PM

Answers

  • Hi Neil,

    How about the suggestion provided by RajkumarMSBI5055 in your another thread? You could try to replace your above code with RajkumarMSBI5055’s suggestion and make sure the parameter mappings are right, instead of passing the variable into the SQL Statement directly.

    This article I provided above shows the detailed information and you’d better have a look at again.

    Besides, since you have posted your new issue in a new thread, if your initial issue has been solved in this thread and my above reply is helpful to you, you could mark it as answer to close this thread, so that to make one thread has one issue and help others easily find out the right solution. For further issue about your new issue, please post them in your new thread and we could continue to discuss them there.

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Edited by Albert_ Zhang Wednesday, February 22, 2017 5:04 AM
    • Proposed as answer by Albert_ Zhang Monday, March 6, 2017 12:14 PM
    • Marked as answer by Neilcse Tuesday, October 23, 2018 7:07 PM
    Wednesday, February 22, 2017 4:45 AM

All replies

  • you can use olla script and mention table for backup.

    https://ola.hallengren.com/


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Sunday, February 19, 2017 5:12 PM
  • was not able to locate the script .. can I use the below one.. e.g.

    Need to know how to parameterized the db name and pass it from object variable in for each loop

    BACKUP DATABASE [SouthDB] TO  DISK 
    = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVERPWC\MSSQL\Backup\South\SouthDB20170219_Test.BAK' 
    WITH NOFORMAT, 
    NOINIT,  
    NAME = N'SouthDB-Full Database Backup', 
    SKIP, 
    NOREWIND, 
    NOUNLOAD, 
    COMPRESSION,  
    STATS = 10
    GO


    Neil


    • Edited by Neilcse Sunday, February 19, 2017 5:36 PM
    Sunday, February 19, 2017 5:35 PM
  • Hi Neil,

    You could add an Execute Task in the Loop Container and use the following code as the SQL Statement to backup the database.

    declare @str varchar(max)
    
    
    set @str = 'BACKUP DATABASE '+?+'
    	TO  DISK = N''c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVERPWC\MSSQL\Backup\South'+?+ CONVERT(varchar(8),GETDATE(),112) +'_Test.BAK'' 
     WITH NOFORMAT, 
     NOINIT,  
     NAME = N'''+?+'-Full Database Backup'', 
     SKIP, 
     NOREWIND, 
     NOUNLOAD, 
     COMPRESSION,  
     STATS = 10;'
    
    exec (@str)
    

    The parameter setting


    For more things, you’d better have a look at following article.

    https://msdn.microsoft.com/en-us/library/ms140355.aspx

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Albert_ Zhang Wednesday, February 22, 2017 5:04 AM
    Monday, February 20, 2017 7:01 AM
  • Hi Zhang,

    I did something similar to what you said.  

    I used a object variable to capture the db id, name and path to be used. In foreach loop I used variable mapping from obj var to 3 var.  

    I used ESQL task in foreach to perform the db backup in expression using similar to your code.

    For any failure I want to capture the exception and continue with other database backup, However, when I tried to capture the exception (using onerror) to populate my audit table with errorcode errordescription in the exception I am getting error for these two columns 

    Execute SQL Task] Error: Executing the query "INSERT INTO Audit_Backuplog
    ( DatabaseId,
    Databas..." failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


    Set system variable propagate to true.



    Neil

    Tuesday, February 21, 2017 6:43 AM
  • Below is the code used in expression of esql exception part getting error for the bold column values insert. I have defined them as nvarchar(100) for code and nvarchar(2000) for desc

    INSERT INTO AUdit table...(....)

    VALUES
    (ISNULL(" +(DT_WSTR,3)(@[User::vDatabaseId])+",''),ISNULL('"+
    @[User::DatabaseName]+"',''),ISNULL('"+
    @[User::DbBkUpPath]+"',''),'No','"+(DT_WSTR,50)@[System::CreationDate]+"','"+
    @[System::UserName]+"','"+
    @[System::TaskName]+"','"+
    (DT_WSTR,50)@[System::ErrorCode]+"','"+
    @[System::ErrorDescription]+"')"



    Neil

    Tuesday, February 21, 2017 6:46 AM
  • Hi Neil,

    Based on the above error message, you should map the all three of the variables for the SQL Statement and make sure their data types are right.

    >> Below is the code used in expression of esql exception part getting error for the bold column values insert.

    Could you clarify where do you use the above code? Do you use it in the precedence constraint expression? Or do you use it in an Execute SQL Task?

    If it’s the latter, you should not use the variable into the SQL Statement directly and replace them with “?” and set the “Parameter mapping” referring to the link I provided above.

    If it’s the former, could you clarify show an image to clarify the settings.

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 21, 2017 9:24 AM
  • Hi Albert,

    I have used the code in execute sql tasks expression in the event handler.  I have used this earlier packages it worked fine.

    And if I exclude the system variables  System::ErrorCode&  System::ErrorDescription and replace it with NULLs it works and insert data into Audit table.

    Scope of the variables is at package level.

    Regards,


    Neil


    • Edited by Neilcse Tuesday, February 21, 2017 11:44 AM
    Tuesday, February 21, 2017 11:29 AM
  • Defined one object and 3 normal variables at package level, 

    1. First top ESQL is to get the list of databaseID, Name, and Path from table into the object variable 

    2. Foreach loop to loop through the object variable and map the other 3 variables in parameter mapping to get the dbID, name and path into respective variables

    3. 0 Exe SQL Task in for each loop to execute the db backup script which is working fine for valid db names 

    4. 1 exe sql task in for each loop to update the audit log table on successful execution 

    Till here everything is working fine. 

    I just added the onerror event handler for the Exec SQL components marked as 0 in red 

    The event handler is as shown below with esql marked as 2 in red, when I force the esql -0 in red to fail ( for testing the on failure scenario) I am getting the said error when I try to insert the system and user variable content to Audit table

    In the event exec sql task I have the code in expression handler to push the current values in the variables with result as Failed.



    Neil

    Tuesday, February 21, 2017 12:01 PM
  • Hi Neil,

    How about the suggestion provided by RajkumarMSBI5055 in your another thread? You could try to replace your above code with RajkumarMSBI5055’s suggestion and make sure the parameter mappings are right, instead of passing the variable into the SQL Statement directly.

    This article I provided above shows the detailed information and you’d better have a look at again.

    Besides, since you have posted your new issue in a new thread, if your initial issue has been solved in this thread and my above reply is helpful to you, you could mark it as answer to close this thread, so that to make one thread has one issue and help others easily find out the right solution. For further issue about your new issue, please post them in your new thread and we could continue to discuss them there.

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Edited by Albert_ Zhang Wednesday, February 22, 2017 5:04 AM
    • Proposed as answer by Albert_ Zhang Monday, March 6, 2017 12:14 PM
    • Marked as answer by Neilcse Tuesday, October 23, 2018 7:07 PM
    Wednesday, February 22, 2017 4:45 AM
  • This is working copy which I have used. This is working perfectly for me by scheduling it using sql job
    Might help others!
    
    Create the below table and set the flag to y/n based on if you need to backup or not.  this code will replace the existing backup. 
    
    Create the SP and schedule the Sp using SQL agent as per need. 
    
    
    
    USE [FRAMEWORK]
    GO
    CREATE TABLE [dbo].[fw_backupDbStatus](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[DbServer] [nvarchar](150) NULL,
    	[DbName] [nvarchar](150) NULL,
    	[Flag] [char](1) NOT NULL,
    	[DbDesc] [nvarchar](200) NULL,
    	[Status] [nvarchar](50) NULL,
    	[BackUpPath] [nvarchar](500) NULL,
    	[LastBackupOn] [datetime2](7) NULL,
     CONSTRAINT [UK_UniqueDbName] UNIQUE NONCLUSTERED 
    (
    	 [DbName] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[fw_backupDbStatus] ADD  DEFAULT ('') FOR [DbServer]
    GO
    
    ALTER TABLE [dbo].[fw_backupDbStatus] ADD  DEFAULT ('n') FOR [Flag]
    GO
    
    
    
    
    use FrameWork
    truncate table [fw_backupDbStatus]
    
    
    insert into [fw_backupDbStatus] 
    (DBName,Flag,DbDesc,BackUpPath) 
    values
    ('DataMart'		       ,'y'   ,'Datamart'   			  ,'C:\DailyDbBackup\'),	
    ('DataMart_Med'            ,'y'   ,'Datamart'   			  ,'C:\DailyDbBackup\'),
    ('framework'		       ,'y'   ,'ETLFramework'		  ,'C:\DailyDbBackup\')
    
    
    use FrameWork
    select * from backupDBs
    
    USE [FRAMEWORK]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[usp_backupDbStatus_job]
    AS 
    BEGIN
    
    	DECLARE @v_cur CURSOR,
    	 @v_dbname NVARCHAR(150),
    	 @v_fullpath NVARCHAR(500),
    	 @v_backuppath NVARCHAR(500),
    	 @v_filename NVARCHAR(150), 
    	 @v_str NVARCHAR(500)
    
    BEGIN
    	SET @v_cur = CURSOR FOR	SELECT DbName,BackUpPath FROM dbo.fw_backupDbStatus	WHERE flag = N'y' 
    	OPEN @v_cur FETCH NEXT FROM @v_cur 
    	INTO @v_dbname,@v_backuppath
    		
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	update fw_backupDbStatus
    	set status = N'Executing'
    	where DBName = @v_dbname
    	and flag = N'y'
    		
    	SET @v_filename = @v_dbname+N'.bak'
    	SET  @v_fullpath = 'N'''+@v_backuppath+@v_filename+''''
    
    		BEGIN
    			SET @v_str = N'					
    			BACKUP DATABASE  '+@v_dbname+ N' TO DISK = '+@v_fullpath+N' 
    			WITH NOFORMAT,
    			INIT,   
    			SKIP,
    			NOREWIND,
    			NOUNLOAD,
    			COMPRESSION,  
    			STATS = 20'
    		END
    
    	exec (@v_str)
    	update fw_backupDbStatus
    	set status = N'Complete',
    	LastBackupOn = getdate()
    	where DBName = @v_dbname
    	and flag = N'y'; 
    			
    	FETCH NEXT FROM @v_cur 
    	INTO @v_dbname,@v_fullpath
    END
    END 
    END


    Neil

    Friday, June 14, 2019 2:02 PM