Using SQL Server 7.0, Backup Maintenance Plan Not Working

Aug 21, 2006

I am using SQL server 7.0. After restoring all the databses (Except
distribution) Everything seems to be working fine except the backup
maintenance plan. I put following detail in the maintenance plan

"General Tab:

Plan Name --- DB Maintenance Plan1
() All Databases

Optimizations tab:

Reorganize Data and Index Page
Change free space per page percentage to 10%

Remove unused space from database files

Shrink Database when it grows beyond 50 MB
Amount of free space to remain after shrink 10% of the data space
Schedule: Occurs every 1 week(s) on Thursday, at 1:30:00 PM.

Integrity tab:

Chack database integrity
Include indexes.
Attempt to repair any minor problem
Schedule : Occurs every 1 week(s) on Thursday, at 1:00:00 PM.

Complete Backup tab:

Backup the database as part of the maintenance plan
Verify the integrity of the backup upon completion
Disk use this directory: d:SQL Backups
Backup file extension: BAK
Schedule: 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 getting
backed 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 Server
Driver][SQL Server]Cannot open user default database '<ID>'. Using
master database instead."

In the SQL server registration properties the login through which
getting logged into the EM for that, default database is "my production
database".

Is this problem because of the distribution db which I am not able to
restore.

Please help

Deepak Sinha

View 1 Replies


ADVERTISEMENT

Check Database Integrity In Maintenance Plan Not Working In SQL Server 2005 SP2

Apr 13, 2007

I installed SQL Server 2005 Enterprise, then SP1 and then SP2, Maintenance Plan worked. But if I installed SQL Server 2005 Enterprise and then SP2 directly (skipping SP1), the Check Database Integrity in Maintenance Plan was not working. The error message is as follows:



Executed as user: DomainSqlServiceAccount. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:56:21 AM Could not load package "Maintenance PlansTest Plan" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login failed for user ''. The user is not associated with a trusted SQL Server connection.). The SQL statement that was issued has failed. Source: Started: 10:56:21 AM Finished: 10:56:21 AM Elapsed: 0.047 seconds. The package could not be loaded. The step failed.




Other tasks like Rebuild Index seem to be fine. SP2 is supposed to be inclusive. Does anyone have any ideas on why this is happening.



Thanks



E.G.

View 10 Replies View Related

Maintenance Plan Maybe Not Working

Aug 17, 2005

I have a weird one. I have 3 maintenance plans that were working for months and now suddenly are sort of not working. The jobs start and then take forever. I stop them from QA (EM doesn't appear to stop them). When I look at the Application log in the event viewer there are no errors because the jobs appeared to freeze. When I look at the maintenenace plan history it says the jobs completed in the usual short amount of time. When I look at the application log it says it did the backups of the data and transaction logs, etc. When I look on the server in the d:SQL ServerMSSQLLog directory for that maintenance plan, it says it completed the backups. So, essentially, EM is telling me the jobs haven't completed and everything else tells me the jobs did complete. But I won't feel secure until EM tells me the jobs have actually finished.

I did do a manual backup of one of the databases and then ran that maintenance plan for that database. I canceled the maintenance plan job after a few minutes (how long the job would usually take) and then looked at the size of the .BAK files. They were both exactly the same size.

I'd greatly appreciate any help!

View 14 Replies View Related

Maintenance Plan To Backup DB

Jan 24, 2002

Are there any known issues using Maintenance Plans to backup DBs in SQL Server 7.0

Thanks

View 1 Replies View Related

Maintenance Plan Backup

Feb 23, 2000

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 Related

Backup Maintenance Plan

Feb 7, 2003

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?

Thanks,
Ken

View 1 Replies View Related

Backup Maintenance Plan

Mar 21, 2002

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

View 1 Replies View Related

Backup Vs. Maintenance Plan

Sep 6, 2001

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.

View 5 Replies View Related

Maintenance Plan: Backup

Jun 21, 2006

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

View 3 Replies View Related

Maintenance Plan --&> Backup --&> With Init?

Dec 10, 2003

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

View 1 Replies View Related

SQL 2005 Backup Maintenance Plan

Jul 9, 2007

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"?

View 4 Replies View Related

Backup Maintenance Plan - Best Practices

Apr 11, 2007

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

View 1 Replies View Related

SQL 2005 Backup Maintenance Plan

Feb 16, 2007

In SQL 2005...

How do I specifty in the maintenance plan to only keep 3 days worth of backup.

Andre

View 4 Replies View Related

Run Backup (maintenance Plan) From Batch (.bat) File

Oct 5, 2006

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 Related

Bizarre Maintenance Plan Failure (db Backup)

Jul 23, 2005

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?

View 4 Replies View Related

Copy Files After Backup In Maintenance Plan

Sep 19, 2007



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.

View 7 Replies View Related

Unload Tape After Backup Maintenance Plan

Sep 22, 2006

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!!!

View 1 Replies View Related

Backup Maintenance Plan - Best Practices For SQL 2000

Jun 24, 2007

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!

View 1 Replies View Related

Backup On Local/Network Drive Using Maintenance Plan

May 27, 2007

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...

View 2 Replies View Related

Maintenance Back Up Plan Not Deleting Prior Backup

Apr 25, 2001

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

View 3 Replies View Related

No Description - Modify Backup Maintenance Plan Failes To Save.

Apr 26, 2007

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

View 13 Replies View Related

Remove Old Backup And Tran Logs Thru Maintenance Plan Cleanup Task

Mar 27, 2007

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 ?

View 5 Replies View Related

SQL Server Maintenance Plan

May 3, 2004

Hey Gang, Anyone have a Maintenance Plan Checklist? One that I can use on Multiple applications

View 3 Replies View Related

SQL Server DB Maintenance Plan Question

Sep 12, 2005

I have about 15, 100mb databases on my SQLserver and was wondering if my db maintenace plan was correct. I backup the db once a day, and the Transaction log gets backep up every four hours. I am also using the "Bulk-logging" option for each database. My main concern was if I ever do a manual backup of one of the dbs, will it corrupt my backup history and stop me from restoring a db sucessfully. Would simple logging be a "safer" option? I just want to have the most reliable plan in place. I would appreciate any wisdom.Thanks. 

View 7 Replies View Related

Sql Server 2005 Maintenance Plan

Jan 17, 2006

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?

View 7 Replies View Related

SQL Server Maintenance Plan Did Not Work??

May 15, 2000

Can someone tell what could possibly be the problem with SQL7 not running backups that were created and scheduled using the maintance plan wizar. There is no job history, or any record of the backups running. However, I did notice that the SQL Server Agent was set to manual. Could that perhaps have something to do with the jobs not running. If possible could some give me possible solutions or answers as to why these jobs didn't run.

Thanx

View 2 Replies View Related

Maintenance Plan Bug On SQL Server 2005

Oct 4, 2007

Hello, everyone:

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).

Did someone meet this bug and have suggestions?

Thanks

ZYT

View 4 Replies View Related

Maintenance Plan Wizard Vs Tsql Maintenance

Aug 17, 2007

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.

View 1 Replies View Related

Setting A Maintenance Plan For A SQL Database In The SERVER

Jun 10, 2007

I learned how to make SQL database BACKUPs through Maintenance Plans in SQL server 2005 in my own computer.
So my question is: When I finally would be deploying my ( asp.net 2.0) web site in a distant real server, how could I set a Maintenance Plan there ?

View 2 Replies View Related

SQL Server 2005 Maintenance Plan Failed To Run

Sep 19, 2007

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

Any ideas? Thanks.

View 1 Replies View Related

Maintenance Plan With Sql Server 2005 Express Sp2

Apr 21, 2008

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

View 3 Replies View Related

Maintenance Plan For SQL Server 2005 64-bit Doesn't Work!

Jul 5, 2007

Hi all,
We have a SQL Server 2005 64-bit, and recently I upgrade from build 3042 to 3054 and I try to do a maintenance plan for transaction logs(TL) backup, including cleanup for two days (have full backup every night).

Problem I have is that I want the TL files to dump in a different location(due to disk space), so I put in the UNC path in the "Create a backup file for every database - >Folder:\FileServerTLDBLogs"

NB: if using the local drives, it work

Check List
Security:
- The account that I used to create the plan is an sa account
- The location that I dump the TL files, I have full access to the folder

SQL Statement:
exec xp_cmdshell 'dir FileServerTLDBLogs' (it list all files)

Is this a bug for 64-bit? because I can do this on SQL Server 2005 32-bit and it's work perfectly

View 1 Replies View Related

SQL Server Agent Error 15404 - Maintenance Plan

Aug 23, 2007

When trying to execute a maintenance plan I have setup (which contains a shrink command on all databases and then a backup to file command on all databases) i get the following error message from SQL agent:



Date 23/08/2007 10:02:17
Log Job History (Shrink and Backup v4)

Step ID 0
Server 0127-0323-DB01
Job Name Shrink and Backup v4
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
The job failed. Unable to determine if the owner (0127-0323-DB01IIPAdmin) of job Shrink and Backup v4 has server access (reason: Could not obtain information about Windows NT group/user '0127-0323-DB01IIPAdmin', error code 0x534. [SQLSTATE 42000] (Error 15404) The statement has been terminated. [SQLSTATE 01000] (Error 3621)).

I have searched both online help and the forums and have not been able to find a solution, any help would be appreciated

Thanks

Marek

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved