I feel like a complete nitwit. I've been using SQL 2000 for about 5 years now and regularly set up automatic backups using the wizard for many people. I'm in no way an advanced user and have enjoyed some of the more user-friendly features of SQL 2000 such as the backup wizard.
Well now we have SQL 2005 and I can't find the "wizard". I did a lot of googling and found very little on this. The frustration is mounting - let alone the fact that I had to edit the registry just to be able to perform a manual backup in the first place... :-(
Apparently their is a "maintenance plans" folder but the only place I can find this is under "Legacy" and you can't create jobs there.
Has anybody else had this issue? Is there a bug in SQL 2005? I reinstalled the full version with all options selected and still can't find this folder. Am I just being completely "blonde"?
I am doing full backups on 25 databases nightly using the SQL maintenance plan. Recently I have started getting the backup process hung on one database, so the whole plan does not finish. The job shows as executing, but nothing is being done, and nothing has been written to disk for this database. The SQL logs show a DBCC TRACEON 208, but the process it refers to is using another database. The NT logs do not show anything going on either. Additionally the backup process will not die when it is killed. They only way to make it go away is to stop and start the SQL Server. The system is a quad Pentium 450, 2Gig of ram, and plenty of disk space. It is NT SP5, SQL7 SP1. The DB is about 8.2 Gig. There are no other jobs scheduled to run at the time the backups run. Does anyone have any ideas what would be causeing the process to hang? Also what is a trace flag 208, it is not listed in BOL. Thanks for your help.
I have a maintenance plan that I use to backup some of my databases and transaction logs. I have "Check Database Integrity" checked. If the database is being used by anyone when the backups are being performed, the check issues an error because the DB cannot be put into single user mode and then skips the backup. In the Plan, I have unchecked the box for "Perform these checks before backing up the database or transaction file" and apply the change. When I go back into the plan, the option is checked again. How can I get around this problem?
I have a maintenance plan where I backup the system databases and my application databases every night. Once a week I do a Check Database Integrity. For the Master and MSDB databases, it errors out telling me that the database must be in single user mode. I thought that the maintenance plan would switch the DB to single user mode when it needed to perform its checks. Any suggestions on how to get this to work successfully?
I'm running sql server 2000. All the backups created through the Maintenance Plan failed (no specific error code), whereas backups created by itself (through database name => All Tasks => Backup database...) run fine. Does anybody have a clue why the Maintenance backup failed or how to trouble shout the problem?
The reason I want to make the backups from maintenance plan to work is that each backup will have a unique name and I can schedule to remove the old backup files from the system.
We are running SQL Server 2000 with SP3. I have recently created a DB Maintenance Plan to backup all of our databases nightly to a local tape drive, and verify the backup of those databases. The backup plan itself runs fine.
The first time it ran, it ran in a little over an hour. Since then, I have noticed the time increasing nightly to the point that last night's backup took 8 hours. Our databases are not that big and do not have enough transactions to cause such a drastic change in a matter of weeks.
Investigating the problem, I have found that the issue lies in the fact that the tape is not initializing each night, and thus each night's backup is appending to an increasingly more full tape.
My question is this: Is there a way to force a DB Maintenance Plan to initialize the tape each night? I know that I can probably add a "with init" command to the T-SQL statement created, but I am not sure exactly where to fit it in. Does anyone have any suggestions?
I'm about to embark on creating a maintenance plan to back up all databases on one of our SQL 2005 servers. I am looking for some advice on best practices for doing this.
I have it in my mind that i want to be taking a full database backup once a week, with differential backups on a daily basis and transactional backups performed every 2 to 4 hours.
Do i need to create three maintenance plans for this, i.e. 1 for full, 1 for differential, and 1 for transactional?
If i want to only keep the backups from the last week, is this done by setting up a maintenance cleanup task in the full backup plan to clear all bak files that are a week old? If so i'll also probably require one to remove the trn files also.
When using the backup command from the context menu in SSMS there is an option to name the backup set. How does this work when using maintenance plans as i haven't been able to find this option whilst trying out some of the features?
I'm sure to have more questions on this subject, but any help on the above queries would be most appreciated.
What is the method to execute backups from batch (.bat) files on the server running SQL Server. I have tried the sqlmaint command - doesn't seem to execute, looked into the xp_sqlmaint with no luck. I'm sure the problem lies in my lack of DOS batch programming skills. If anyone has an example of a batch file that executes a backup would you mind sharing. thanks
I am getting a failure on the db backup job of one of my maintenanceplans. It is coming back with the generic error message of,"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The stepfailed."I then checked the Database Maintenance Plan History page, but thisshows all the steps having run successfully. If I check the drives forthe actual backup files, they exist and look healthy too!There is plenty of space on the drives, so it is not that.I've checked the NT logs and all they say is,"SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DBMaintenance Plan for All User Databases''(0xC06E15E2A9E1414087BE19541D167861) - Status: Failed - Invoked on:2005-06-29 21:00:04 - Message: The job failed. The Job was invoked bySchedule 35 (Schedule 1). The last step to run was step 1 (Step 1). "Which doesn't give me any clues.Since the backups have actually run to success, I am going to take offthe option on the maintenance plan to "Verfiy the integrity of thebackup upon completion". Maybe it is this that is causing problems,rather than the backup?Anyone had anything similar?
I am using SQL server 7.0. After restoring all the databses (Exceptdistribution) Everything seems to be working fine except the backupmaintenance plan. I put following detail in the maintenance plan"General Tab:Plan Name --- DB Maintenance Plan1() All DatabasesOptimizations tab:Reorganize Data and Index PageChange free space per page percentage to 10%Remove unused space from database filesShrink Database when it grows beyond 50 MBAmount of free space to remain after shrink 10% of the data spaceSchedule: Occurs every 1 week(s) on Thursday, at 1:30:00 PM.Integrity tab:Chack database integrityInclude indexes.Attempt to repair any minor problemSchedule : Occurs every 1 week(s) on Thursday, at 1:00:00 PM.Complete Backup tab:Backup the database as part of the maintenance planVerify the integrity of the backup upon completionDisk use this directory: d:SQL BackupsBackup file extension: BAKSchedule: Occurs every 1 day(s), at 2:10:00 PM.Transaction Log Backup tab:schedule: Occurs every 1 day(s), at 2:00:00 PM.Reporting tab:Write report a text file in Directory: D:SQL BackupsLOG "Problem:My databases(mroduction as well as systems) sre not at all gettingbacked up according to above plan.Error Message in the Log Directory:"Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO(ODBC SQLState: 42000)] Error 4062: [Microsoft][ODBC SQL ServerDriver][SQL Server]Cannot open user default database '<ID>'. Usingmaster database instead."In the SQL server registration properties the login through whichgetting logged into the EM for that, default database is "my productiondatabase".Is this problem because of the distribution db which I am not able torestore.Please helpDeepak Sinha
We're new users of SQL Server 2005. I created two maintenance plans...one to backup the database at 2 AM daily and one to back up transaction logs every 30 minutes. These maintenance plans write to a local disk. What we want to do, within the maintenance plan, is copy the files as soon as they are written to a remote server. Is that possible?
I have used google and searched these forums but haven't found an answer.
Here is my problem:
I have MS SQL 2005 DB I need to backup to tape daily. I want the tape to eject (or unload) when it is complete. I know how to do it with a manual backup (simply check off the option), but there aren't any options I can see with a Maintenance Plan backup. I would rather not do a manual backup EVERY DAY.
I really don't understand why MS makes this so complicated...why would the option be in another area when you use a maintenance plan???
I was once told that I was not to use the GUI to setup a maintenance plan. What the person said was that I needed to setup a different plan to do each tab of the GUI instead of going through the tabs and making sure the times do not overlap. Has anyone ever heard of this or is this an old wise tale told by only one person? I am using version 8 of SQL on XP machines with 8 connections to that database and need to back up the database, clean it up and everything the GUI has, so teach me the correct ways and tell me why, thanks!
Hi, I have created an sql server 2005 maintenance plan for a daily backup. The plan has two 'Back up database task' i.e. one backup on the local drive while the second on a network drive. When the plan is executed, a backup is created on the local drive but not on the network drive. If i check the log, it says "Access Denied" whereas i have full access to the network drive with complete permissions to read, write and delete. Can anyone help me understand how to take a backup on both a local and network drive at the same time using a maintenance plan ? I shall be obliged... Regards...
I have a maintenance plan set to back up my 2 primary databases, one about 1 GB, the other about 2GB. I have about 4 GB available on the back up drive. I have set it to delete old backups more than 1 minute old. Problem is, the first night it works fine, the second night it sometimes it refuses to delete the old and therefore does not have enough space to complete the new back-up. Does anyone run into this before? Does it complete both back-ups THEN erase the old, in which case I need to have about 6GB available? Sometimes it works and sometimes it doesn't. It can be going along fine for weeks then stop working. Any info or links to how this is supposed to work would help.
Has anyone been able to successfully delete old backup files(*.bak) and tran logs (*.trn) TOGETHER using maintenance plan cleanup task in SQL 2005 SP2.
this is transact sql running in the back ground. EXECUTE master.dbo.xp_delete_file 0,N'F:MSSQL.2MSSQLBackupibmdir',N'"bak" & "trn"',N'2007-03-26T22:21:14',1
This DOESNT WORK.
It works if I just try to delete only one of the things ie trn or bak files.
I need to find out on runtime, a list of Maintenance plans and Jobs. i dont know how to acquire the database name on which the plan/job are operating. i also dont know from which table can i get the tasks ( backup,integrity,rebuild index etc.) that belongs to the plan.
I'm trying to create a maintenance plan to backup a database, and when I go to select the database in the dropdown, I don't have any of the user databases any more. They were there several weeks ago when I first created the 'plan'. Does anyone know how to get the user db's to show up in the dropdown?
I have a strange trouble on SQL Server 2005 on XP/local and Win 2003/server. When I connect local database and create a backup plan named by "backup plan", I can see the name under "maintenance plan" and "Jobs" under SQL Server Agent. But if I connect to server database and do the samething, I cannot see the name under "maintenance plan". I can see the name under "Jobs" under SQL Server Agent. But cannot delete this job, and message:
Drop failed for job "Backup Plan".(Microsoft.SqlServer.Smo).
I need to create sql 2005 maintenance plan for system and user databases which includes Database integrity check and Rebuild index tasks but these two things should happen at different times. Is it possible to schedule these two tasks at different times under single database maintenance plan, if so please let me know...
I successfully created a simple Backup Maintenance Plan under SSMS. But now that the plan is created I need to change the number of days the backups are kept. When I pull up the plan udner SSIS and lok at the tasks I can't locate that option. I couldn't find it under the sql agent either. Anyone know where it is? (Or do I need to recreate the plan from scratch?)
I want to create sql 2005 maintenance plan which includes Database integrity check and Rebuild index tasks but these two things should happen at different times. Is it possible to schedule these two tasks at different times under single database maintenance plan, if so please let me know...ASAP
I created a database maintenance plan on sql server 2005 (standard SP2, cluster environment). The plan created successfully on scheduled successfully. But when execute the plan, it fails. Here is the information in the log:
The last step to run was step 1 (TranLog backup).,00:00:01,0,0,,,,0 09/19/2007 12:06:38,Tranlog backups.TranLog backup,Error,1,LAIWWORKSITE1LA,Tranlog backups.TranLog backup,TranLog backup,,Executed as user: STROOCKSQLSRV. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:06:38 PM Warning: 2007-09-19 12:06:39.27 Code: 0x80012017 Source: Tranlog backups Description: The package path referenced an object that cannot be found: "PackageTranLog backup.Disable". This occurs when an attempt is made to resolve a package path to an object that cannot be found. End Warning DTExec: Could not set PackageTranLog backup.Disable value to false. Started: 12:06:38 PM Finished: 12:06:39 PM Elapsed: 0.688 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
Hi,We have a SQL-cluster with over 6300 databases, most of them prettysmall in size and usage.For this reason all these databases are on auto-close ( Otherwise SQLwould need many many gigabytes ).We've recently upgraded to SQL 2005 because of the Management Studio,because the EM is unusable on a SQL-Server with lots of databases.But whenever you choose 'all user databases' or 'all databases' in anyof the DB Maintenance task, MSSQL begins starting up every databasewhich takes about 30 minutes. Until then, you cannot use your Studioanymore.My question : is there a possibility to avoid this behavior, it shouldnot go and check all those db's, just add my task.PS : Detaching these databases is not an option ...Thanks in advance,Sven Peeters
Hello, I use SQL Server Express 2005 SP2.(Microsoft SQL Server 2005 - 9.00.3042.00) I want to make a maintenance plan, but I don't have a task called "maintenance plan" I am user sa with "sysadmin" How can I make a maintenance plan. Thanks for your help Thomas
I had created a maintenance plan and configure and scheduled my maintenace plan to run. I would like to save this package as a file into a source control. SO I use SSIS to export the package under stored packages->MSDB->Maintenace Plan. After that, I wanted to test my import process. So I deleted the packages under SSIS ->stored packages->MSDB->Maintenance plan and I use the import to add the package from my previously exported package stored in a file .dtxs extention.
So the problem is, after I imported my package. I lost the configured schedule and the job runs without doing anything. When I try to go in and make changes to the package, by adding a new schedule under SQL server -> management->maintenance plan. I receive a odd error message and it doesn't allow me to save the package.. The error message I got is...."GUID should contain 32 digits with 4 dashes (xxxxx-xxx-xx...)"
So my questions are: 1. why did the re-import loses the originally configure job run schedule. 2. why doesn't the re-import package works by backing up database as it was first setup. 3. why I cannot re-edit this package and saving the package error out?
I have created a simple maintenance plan in SQL Studio. When I double-click on the task boxes (ie 'Rebuild Index', 'Backup') to just make changes or to look, I get the following message:
Cannot show editor for this task
Additional information:
Exception from HRESULT: 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)
I also cannot save any change that I make in the schedule of the plan.
I HAVE installed Integration Services, which at least enabled me to initially SAVE the plan. I just can't edit the tasks or change the schedule.
