I am configuring the backup of all the databases using Maintenance Plans.

Question

Post reply

I am configuring the backup of all the databases using Maintenance Plans.

forsqlserver

SSCoach

Points: 18902
More actions
July 20, 2011 at 11:51 pm

#244546

I am configuring the backup of all the databases using Maintenance Plans.
1. Backup set will expire in 14 days what does it mean?
2. In maintenance plan Can I not use "With Init" option so that I need not to check continuosly the disk space.I dont want to do daily login and delete the old backup files.
Thanks

Viewing 15 posts - 1 through 15 (of 19 total)

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

Jannie-186227 SSCrazy Points: 2319 More actions · Answer 1

Don't underestimate the power of Bol

Type backup, highlight it and press shift F1

It gives a really nice description on the parameters, those you asked for and some you may want to know about next.

forsqlserver SSCoach Points: 18902 More actions · Answer 2

Actually one more need I want is the name of this automatic genrated backup file by maintenanace plan.

test_backup_2011_07_22_112103_4580638.bak

I want to get a backup file name with as above date time but manually I am not able to do it.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

select 'test_backup_' + year(getdate()) + '_' + month(getdate()) + ....

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

forsqlserver (7/20/2011)
... so that I need not to check continuosly the disk space.I dont want to do daily login and delete the old backup files.

You don't have to manually delete the old backup files.

There is a maintenance plan step for that as well.

forsqlserver SSCoach Points: 18902 More actions · Answer 5

Thanks Suresh what is that plan step?

You don't have to manually delete the old backup files.
There is a maintenance plan step for that as well.

Thanks

Suresh B. SSC-Insane Points: 22986 More actions · Answer 6

Suresh B.

SSC-Insane

Points: 22986

July 21, 2011 at 3:27 am

#1356224

Maintenance Cleanup Task

forsqlserver SSCoach Points: 18902 More actions · Answer 7

Thanks,

There are two categories Maintenance Plan Task and General from which category I should choose it.

Thanks

Suresh B. SSC-Insane Points: 22986 More actions · Answer 8

forsqlserver (7/21/2011)
Thanks,
There are two categories Maintenance Plan Task and General from which category I should choose it.

You can select from any category. Both are same.

Don't hesitate to experiment in a test/dev machine.

forsqlserver SSCoach Points: 18902 More actions · Answer 9

My job is failing,it is a created on Maintenance Plan in which one is backup of all databases and other is Maintenane cleanup task to delete the older files of more than 4 hours.

Date7/25/2011 11:37:00 AM

LogJob History (MaintenancePlan.backup_uyu)

Step ID1

ServerANKIT-ITSM-HO\INS

Job NameMaintenancePlan.backup_uyu

Step Namebackup_uyu

Duration00:01:11

Sql Severity0

Sql Message ID0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted0

Message

Executed as user: ANKIT-ITSM-HO\SYSTEM. ....50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:37:00 AM Progress: 2011-07-25 11:37:00.93 Source: {92523647-9B63-4675-AA78-8FE1484DE9AD} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\ma...".: 6% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\mo...".: 12% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\ms...".: 18% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\Re...".: 25% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\Re...".: 31% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\te...".: 37% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\te...".: 43% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\TK...".: 50% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\te...".: 56% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\te...".: 62% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\En...".: 68% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\uy...".: 75% complete End Progress Progress: 2011-07-25 11:37:01.27 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\uy...".: 81% complete End Progress Progress: 2011-07-25 11:37:01.27 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\di...".: 87% complete End Progress Progress: 2011-07-25 11:37:01.28 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\te...".: 93% complete End Progress Progress: 2011-07-25 11:37:01.28 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\uy...".: 100% complete End Progress Progress: 2011-07-25 11:37:01.78 Source: Back Up Database Task Executing query "BACKUP DATABASE [master] TO DISK = N'D:\Backup\ma...".: 50% complete End Progress Progress: 2011-07-25 11:37:02.03 Source: Back Up Database Task Executing query "declare @backupSetId as int select @backupSetId =...".: 100% complete End Progress Progress: 2011-07-25 11:37:02.37 Source: Back Up Database Task Executing query "BACKUP DATABASE [model] TO DISK = N'D:\Backup\mod...".: 50% complete End Progress Progress: 2011-07-25 11:37:02.48 Source: Back Up Database Task Executing query "declare @backupSetId as int select @backupSetId =...".: 100% complete End Progress Progress: 2011-07-25 11:37:16.31 Source: Back Up Database Task Executing query "BACKUP DATABASE [msdb] TO D... The package execution fa... The step failed.

Thanks

chandan_jha18 SSChampion Points: 11330 More actions · Answer 10

forsqlserver (7/25/2011)
My job is failing,it is a created on Maintenance Plan in which one is backup of all databases and other is Maintenane cleanup task to delete the older files of more than 4 hours.
Date7/25/2011 11:37:00 AM
LogJob History (MaintenancePlan.backup_uyu)
Step ID1
ServerANKIT-ITSM-HO\INS
Job NameMaintenancePlan.backup_uyu
Step Namebackup_uyu
Duration00:01:11
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: ANKIT-ITSM-HO\SYSTEM. ....50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:37:00 AM Progress: 2011-07-25 11:37:00.93 Source: {92523647-9B63-4675-AA78-8FE1484DE9AD} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\ma...".: 6% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\mo...".: 12% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\ms...".: 18% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\Re...".: 25% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\Re...".: 31% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\te...".: 37% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\te...".: 43% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\TK...".: 50% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\te...".: 56% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\te...".: 62% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\En...".: 68% complete End Progress Progress: 2011-07-25 11:37:01.24 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\uy...".: 75% complete End Progress Progress: 2011-07-25 11:37:01.27 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\uy...".: 81% complete End Progress Progress: 2011-07-25 11:37:01.27 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\di...".: 87% complete End Progress Progress: 2011-07-25 11:37:01.28 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\te...".: 93% complete End Progress Progress: 2011-07-25 11:37:01.28 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backup\uy...".: 100% complete End Progress Progress: 2011-07-25 11:37:01.78 Source: Back Up Database Task Executing query "BACKUP DATABASE [master] TO DISK = N'D:\Backup\ma...".: 50% complete End Progress Progress: 2011-07-25 11:37:02.03 Source: Back Up Database Task Executing query "declare @backupSetId as int select @backupSetId =...".: 100% complete End Progress Progress: 2011-07-25 11:37:02.37 Source: Back Up Database Task Executing query "BACKUP DATABASE [model] TO DISK = N'D:\Backup\mod...".: 50% complete End Progress Progress: 2011-07-25 11:37:02.48 Source: Back Up Database Task Executing query "declare @backupSetId as int select @backupSetId =...".: 100% complete End Progress Progress: 2011-07-25 11:37:16.31 Source: Back Up Database Task Executing query "BACKUP DATABASE [msdb] TO D... The package execution fa... The step failed.

This log will not be able to give you much info about the failure. You can try an easier option though. Go to your SQL agent jobs where every maintenance plan you configured will be having a scheduled job.

In this case it should be something like Maintenance plan-Full Backup.

Open the properties--> Got to job step--Edit--> Advanced--> Output file

You provide the location of the output file and then store it somewhere in txt format. when the job fails again, read what it offers you. your solution will most likely be here.

forsqlserver SSCoach Points: 18902 More actions · Answer 11

The output of the file:

Microsoft (R) SQL Server Execute Package Utility

Version 10.50.1600.1 for 32-bit

Started: 12:41:00 PM

Error: 2011-07-25 12:41:18.38

Code: 0xC002F210

Source: Back Up Database Task Execute SQL Task

Description: Executing the query "BACKUP DATABASE [test2] TO DISK = N'D:\Backup\tes..." failed with the following error: "The database "test2" is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire restore sequence is completed.

BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

End Error

Error: 2011-07-25 12:42:17.36

Code: 0xC002F210

Source: Back Up Database Task Execute SQL Task

Description: Executing the query "BACKUP DATABASE [test3] TO DISK = N'D:\Backup\tes..." failed with the following error: "The database "test3" is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire restore sequence is completed.

BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

End Error

DTExec: The package execution returned DTSER_FAILURE (1).

Started: 12:41:00 PM

Finished: 12:42:23 PM

Elapsed: 82.993 seconds

Thanks

chandan_jha18 SSChampion Points: 11330 More actions · Answer 12

forsqlserver (7/25/2011)
The output of the file:
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 32-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 12:41:00 PM
Error: 2011-07-25 12:41:18.38
Code: 0xC002F210
Source: Back Up Database Task Execute SQL Task
Description: Executing the query "BACKUP DATABASE [test2] TO DISK = N'D:\Backup\tes..." failed with the following error: "The database "test2" is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire restore sequence is completed.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
End Error
Error: 2011-07-25 12:42:17.36
Code: 0xC002F210
Source: Back Up Database Task Execute SQL Task
Description: Executing the query "BACKUP DATABASE [test3] TO DISK = N'D:\Backup\tes..." failed with the following error: "The database "test3" is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire restore sequence is completed.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 12:41:00 PM
Finished: 12:42:23 PM
Elapsed: 82.993 seconds

Are you trying to backup a database which is in restoring mode or inaccessible?

From your previous post where you had posted the logs, it showed that backups were indeed taking place. But when the job reaches this particular database, it fails.

you cannot backup a database if it is not accessible. Open your maintenance plan properties, instead of backing up all the database, use the option ' the following databases' and select few databases there. Save the package and then run the job again

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 13

It seems this database was recently restored with no recovery. Looks like your facing 2 different problems now , the first to do with backup plans and the second with a database in restoring state.

What does state_desc column say when you run select * from sys.databases.

Jayanth Kurup[/url]

forsqlserver SSCoach Points: 18902 More actions · Answer 14

Yes I have test database for mirroring and test2,test3 database fro logshipping.Now I am applying ir for single database still MNITENANCE CLEANUP TASK is not deleting bak file.

Thanks