Hello,
SS2k5 - 9.00.3050 (x64) - Most current SP
I've created a backup maintenance plan 'All User Databases'. Plan saved and executes.
Modify plan to add new subplan step to remove old backup files, save fails with a Studio Dialog encapsulating the message -> No Description
Cannot save the Maintenance Plan.
Any ideas about how to save the Plan?
Thank You... Covi
Had to replace leased server hardware running Server2003, Sql2005. Had machine Server B, set up sql2005, set up maintenance plan to do full backup on all user dbs every night to Server C network share. Worked great for over a week. Came time to do the swamp, brought down old server, renamed Server B to Server A, ran:
EXEC sp_dropserver '<old_name>'
GO
EXEC sp_addserver '<new_name>', 'local'
GO
All other functions to the database seem to be working fine. Was using a network administrator account - same one that worked before the name change so the SID is the same. Unfortunately, I decided to try and delete and start over in case it was just a gliche. Now, the history seems to be gone, but I can't actually delete the job or the maintenance plan. Get an error "Does not allow remote connections." even when I'm physically sitting at the machine.
Now, maintenance plans won't run - can't delete them, can't modify them. Other dts packages that execute a query or bring in data from other datasources work just fine.
Help!?
SQL server 2012 (11.0.3128)It is not possible to modify an existing maintenance plan with SQL Server Managemant Studio. When you try to modify the maintenance plan nothing happens. The OS is Server 2008 R2 64 bit. It running in a VMWare 5.5.
View 5 Replies View RelatedUnable to save a maintenance plan in sql server 2005, SP3. Error is class not registered.
View 2 Replies View RelatedAre there any known issues using Maintenance Plans to backup DBs in SQL Server 7.0
Thanks
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.
View 1 Replies View RelatedI 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?
Thanks,
Ken
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?
Thanks,
Ken Nicholson
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.
Hi,
I have made a maintenance plan to backup a database.
The file name is like this: name_db_200606211209.BAK
isn 't it possible to get the filename to be always the same
(without 200606211209)
I have unchecked the checkbox to remove files older than x-days
but it still appends 2006... to it
Good morning.
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?
Thank you for your time.
Michael
Hi there
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"?
Hi All,
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.
TIA,
Grant
In SQL 2005...
How do I specifty in the maintenance plan to only keep 3 days worth of backup.
Andre
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
View 6 Replies View RelatedI 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?
View 4 Replies View RelatedI 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
View 1 Replies View Related
Hello,
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?
Thanks in advance.
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???
Thanks for replys!!!
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.
TIA
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.
EXECUTE master.dbo.xp_delete_file 0,N'F:MSSQL.2MSSQLBackupibmdir',N'bak',N'2007-03-26T22:21:14',1
But is there a way to delete both bak and trn files using one task ?
Hello,
I have a question that I hope someone can clear up for me. I have come across a number of different suggestions on DB maintenance, for example reindexing with the following script:
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
My question is, doesn't the maintenance plan have this functionality inherent in it when you create the maintenance jobs to reindex? Is there a benefit to scripting things out vs just using the maintenance plan wizard for this sort of thing and any of the items it covers? I came from an Oracle background where this was a no-brainer but I am a bit confused on the choices with SQL Server.
Thanks.
I'm getting an error, "No description found", when trying to save my package to the SQL Server (2005).
View 4 Replies View RelatedI want transfer data from excel file to sql server.
I created a package in sql server managament studio using export import wizard and i gave the option "sql server" as the package location.
At the final stage of the wizard "Save to sql server failed no description found" error is coming. pls give me a solution.
thank u
Hello, I need some help with installing release copies of SQL05(+SSIS) and VS05 on Win03R2. It is very frustrating when a basic install fails. I€™ve been going round and round with this. M$ support has not been of help yet.
I have a new development box for SQL05/VS05 development, and when I do a clean install of release software from MSDN: Windows Server 2003 R2 ent; SQL Server 2005 dev; Visual Studio 2005 pro I find that the "save to server" in SSIS (and other utilities that save a package) fails with a non-informative message *No Description Found* (see error text below).
To setup this box I install Win03R2 first, promote it to a Active Directory DC (the one and only DC in a test forest), then install VS05 onto the E: drive. Note, I must install VS05 first (before SQL05) so that all components get installed to the RAID5 *E:* drive. If SQL05 is installed first then VS that comes with SQL gets installed on to C: (no way I see to get around this) then the full VS05 pro gets forced to install on the C: drive.
Then I install SQL05 (with everything including SSIS). At this point every thing works fine, packages can be saved ok.
If I then install VS05 again so that all settings of VS05 are as expected of a standard VS05 install (not the SQL version) then the problem with saving packages arises.
If the error message was a little more informative, I might be able to track down the source of the problem. Please help! I have tried a bunch of things - creating credentials, proxies, using the most privileged accounts possible, etc.
Thanks, MikeC
Error from create SSIS package (save to server):
===================================
No description found (Microsoft Visual Studio)
------------------------------
Program Location: at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package package, IDTSEvents events, String packagePath, String serverName, String serverUserName, String serverPassword) at Microsoft.DataTransformationServices.Controls.PackageLocationControl.SavePackage(Package package) at Microsoft.DataTransformationServices.Design.Controls.PackageSaveCopyForm.PackageSaveCopyForm_FormClosing(Object sender, FormClosingEventArgs e)
===================================
Error from copy db wizard:
===================================
No description found (Copy Database Wizard)
------------------------------
Program Location: at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package package, IDTSEvents events, String packagePath, String serverName, String
serverUserName, String serverPassword) at Microsoft.SqlServer.Management.CopyDatabaseWizard.PackageCreator.SavePackage()
===================================
Hi,
Whenever I create a maintenance plan either manualy or via the wizard, I get the following error message when saving it.
Does anyone know what might be wrong?
Best regards
===================================
Fehler beim Speichern des Wartungsplanes.
(Error when saving the maintenance plan.)
===================================
Mindestens ein Argument ist ungĂĽltig.
(At least one argument is invalid.)
------------------------------
Speicherort des Programms:
(Save location of programme:)
bei Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package package, IDTSEvents events, String packagePath, String serverName, String serverUserName, String serverPassword)
bei Microsoft.SqlServer.Management.DatabaseMaintenance.MaintenancePlan.Save()
bei Microsoft.SqlServer.Management.MaintenancePlanWizard.MaintenancePlanWizardForm.PerformActions()
I have a complex query (16 table join) that until five days ago took 30sec to run. It now takes eight hours. I restored a backup of thedatabase from five days ago and the query plans are completelydifferent for the same exact query. Five days ago the final estimatedrow count is 1.6M now it is 1.7E+10 OUCH! The amount of data added inthose five days is insignificant (percentage wise). I rebuilt all theindexes using DBCC DBREINDEX with no change in the plan or results. Isthere anyway to save the query plan (from the backup) and run itagainst the new data?TIA,Tracy
View 6 Replies View RelatedI have deleted a database from SQL Enterprise Manager. Anyone know a way to clear that database from my maintenance plan? I do not wish to just uncheck the deleted database or create a new database plan.
Thanks!
Hello All
I have been given a SQL Server 2000 database to look after which has been set up with a Database maintenance plan. The plan is set to backup the complete database and the transaction log. The backups are written to the local disk correctly but the plan is also set to remove any backup files (both database .BAK and transaction log .TRN) that are over one week old. Complete database .BAK files are written daily and the .TRN are written every hour daily. The .BAK files are removed ok automatically but the .TRN files are not - they are just slowly filling the disk. There does not seen to be anything different between the way the main database and the transaction log is set up in the maintenance plan.
I would be very grateful for any ideas
I have a strange thing in one of our Maintenance plans.
On the first tab where you check which databases you're including in the plan I have (say my database name is CAT) a 'CAT' and 'cat' database listed and the one chosen is 'cat'. However my database in all other views shows up in all caps. (even when I do an sp_helpdb)
The backups look like they're working, etc. but it just seems weird. If I go to create a new plan it only gives me the one option 'CAT' which is really what's there. I'm new and I'm thinking the database at one time was 'cat' and this is when the maintenance plan was created. Then it was renamed to 'CAT' and there's the two db's showing in the old mainenance plan.
What would you do? Create a new plan with "CAT" and just get rid of the old one with the weird 'cat' and 'CAT'?
Any other suggestions or ideas on what happened..
ann
I've created a database maintenance plan to backup a database, but it just
isn't happening, am i missing something. The maintenance plan appears to be
created successfully.
responses appreciated.
thanks
Todd Minifie