Hi Guys!
I am trying to create a 'scheduled back up database task'maintenance plan which has an option to 'overwrite backups' but with also the "Create a backup file for every database" option checked. Does anyone know how to do this?
Thanks Guys.
Hello,
I created a backup maintanence plan which run good before. I am not sure why it didn't work now.
The error message from job history is:
05/18/2007 09:04:40,backupdbs.backupdbs,Error,0,PEPESQL2005PROD,backupdbs.backupdbs,(Job outcome),,The job failed. The Job was invoked by User RMWB3cs002291. The last step to run was step 1 (backupdbs).,00:00:00,0,0, 0
05/18/2007 09:04:40,backupdbs.backupdbs,Error,1,PEPESQL2005PROD,backupdbs.backupdbs,backupdbs,,Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed.,00:00:00,0,0,,,,0
How to debug this problem?
Thanks
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 ?
how can i move the database maintanence plans from one server to another server ?
Thanks
Hi everyone,
I am running SQL 2000 SP3. I have one maintenence plan that is backing up one database and that is working fine. I am trying to create another maintenence plan for another database but the job does not execute and it gives me errors. Both SQL server and the agent uses 'local system account' to run the service. I have tried changing this to a domain account to see if it fixes anything. Also on the jobs, the job owner is a domain account. Below are two erros I am getting when running the jobs.
Error1:
BackupDiskFile::CreateMedia: Backup device 'D:SQL Backups....mybackupfile.bak' failed to create. Operating system error = 3(The system cannot find the path specified.).
Error2:
BACKUP failed to complete the command BACKUP DATABASE [Database_Name] TO DISK = N'D:SQL Backups....mybackupfile.bak' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
Any help would be appreciated.
Thanks.
Heya all,
Sorry if this has been posted elsewhere, etc., please point me in the right direction if it has 'cos I couldn't find it!
Right, we have a mirrored database with full safety and a witness for automatic fail over, all works fine, very impressed with it. Now I need to backup the database involved and this is where I could do with some help and answers and/or tips.
As the mirror database is off-line/recovering it seems you can't back that one up, but I'd like to have something that tries to back it up for if/when it fails over and becomes the primary. The solution I've used for now is to write a small .Net application that uses the client side fail over connection string (Data Source=Server1;Failover Partner=Server2) so that it connects to whatever system is currently the primary, and then issues the relevant 'BACKUP xxx' statements to backup the database.
This applications is launched from a windows scheduled task job on the hour (or near to it), every hour. At 06:00 it does a full backup, at 12:00, 18:00 and 00:00 it does a differential backup, and all other times it does a transaction log backup.
This all seems to work fine so far, and generates all the relevant backup files to a share on another server.
So, my main question is; does this look like a good plan? Am I missing some really simple wizard or button that would backup the relevant database from whatever server is up?
Secondly, are the backups from each server interchangeable as they're in a mirrored configuration? That is, for example, the backup application (on the hour) connects to Server1 does a full back up, followed later by a log backup. Server1 then dies, so the next time the backup application runs it connects to Server2 and because of the current time does a log backup from Server2. If we had to restore from backups, could we use the full backup and log from Server1, followed by the log from Server2? Otherwise I'll guess I'll need to modify the logic for the backup application to detect it's failed over, and maybe do a full backup on Server2, or something like that.
Anyway, thanks for any help/advice/tips,
Gareth/OhGod
The database is SQL Server 2000. Recover Plan = 'Simple'. Maintenance Plan always fails when DBCC tries to put database in single-user mode so it can do the backup, if there is a user has a connection to it from EssBase. No queries running - just a connection. How can I get SQL Server to automatically kill the connections so it can do the backup?
View 2 Replies View RelatedI have inherited a server on which is a maintenance plan with two subplans on different schedules. In each subplan there are Execute T-SQL tasks with scripts for index rebuilds. Each task is set up with a Completion arrow to the next task and a Failure arrow to a Notify Operator Task. I was asked to add a task for index rebuilds to a specific subplan for a specific database, which is what the other tasks also do. I discovered that my task was failing but the others were fine. No notification was sent about my task failing even though the job is marked in MSDB as a failed job. I have sent a test email using the "Send Test Email..." option when right clicking Database Mail in SSMSand I receive an email so I know Database Mail works.
I set up a test job to model the index job that I can't get notifications from. I have two T-SQL tasks that just select the top row from a small table. The first task has a syntax error that I did so it would fail. I have a failure arrow to a Notify Operator Task and a Completion arrow to another T-SQL task with no syntax error which has a Success arrow to a Notify Operator task. As expected, when I execute this job I receive one failure email and one success email.
The only other troubleshooting step I know to try is to add a Notify Operator task before my failing task. That Notify Operator task will hopefully fire to tell me that the previous step was successful. I am not having problems with the other steps so I was just thinking I would try to get the subplan to send me a success email about one of the steps that has been working fine.
Created a maintenance plan to backup my sharepoint databases.
When I execute it the following error occurs:
Execution failed. See the maintenance plan and SQL Server Agent job history logs for details:
Additional Information:
Job 'SharePointBackUp.Backup_SharePoint faild. (SqlManagerUI)
- Execute maintenance plan. SharePointBackUP (Error)
Messages
* Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.
------------------------------
ADDITIONAL INFORMATION:
Job 'SharePointBackUP.Backup_SharePoint' failed. (SqlManagerUI)
When checking the Maintenance PlansharePointBackUP log it is empty!
Under Job History I thinks this:
Date 4/18/2008 12:55:35 PM
Log Job History (SharePointBackUP.Backup_SharePoint)
Step ID 1
Server DESD7
Job Name SharePointBackUP.Backup_SharePoint
Step Name Backup_SharePoint
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed.
line 1? Is that line one of the T-SQL statement? Because if that's the case then it's not because I copied and pasted the line in to a query and it executed without error.
Under SQL Agent there are no entries!
So what bloody log am I suppose to be checking?! This is very frustrating.
I then copied and pasted every sql statement in to a query and THEY all ran just fine.
What's going wrong here, and how can I correct it?
Hi,
I am trying in the Back Up Database Task to set the Value of SelectedDatabases via Expression.
Is there any way?
Thanks,
Willfried
SQL Server 2005 SP 1 + Hotfix
Hi,
I am administering several SQL Servers running SQL Server 2005 SP2 Build 3042.
I have a common maintenance plan that runs on each of the servers. The maintenance plan runs
fine on all the servers except for one. On the one server the Database Integrity check fails with the following error:
Check Database integrity on Local server connection
Databases: <list of databases>
Include indexes
Task start: 2008-02-21T00:05:42.
Task end: 2008-02-21T00:05:46.
Failed0) Alter failed for Server €˜XYZ€™
I created a test maintenance plan to just do the integrity check and selected one database only and this also failed with the same error message. I ran this test maintenance plan and configured it for each of the databases in question and it failed each time.
If I run the DBCC manually against the databases they all report fine.
I read some of the post that talked about the €œAllow Updates€? being set incorrectly but that does not apply to my problem since my configured and run values are set to 0.
Does anyone know what the problem could be?
I want to take a sql server 2005 database backup on task schedule every 1 hour is that possible.
View 1 Replies View RelatedHello, everyone:
I just heard that for restore purpose, ths full backup and transaction log backup should be from one maintenance plan. Otherwise transaction log backup files cannot be restored after restoring full backup files.
Is it true? Can anyone offer official documents?
In my system, full and transaction backups are from one maintenance plan. Restores are doing fine. I am not sure that ideal is true or not.
Thanks
ZYT
Hi,
In my SSIS package, I have a backup database task. When I run the package with DestinationAutoFolderPath set to a folder ("Network Service" account has full permission on this folder) and DestinationCreationType set to Auto, the task works just fine creating a backup with its own name. (similar to database_date<count>).
But what I want is in my front-end I am allowing the user to specify the name of the backup file. So I want the task to create the backup file in the name I supply. I set the DestinationCreationType to manual and in the application code added the DestinationManualList with the path from the UI.
Now the pacakge runs fine but does not take any backup. There is no errors as well. If I set the FailPackageOnFailure and FailParentOnFailure to true, then I am getting the DTSExecResult.Failure but I am not getting the actual error from the backup database task.
Am I missing anything here?
Thanks in advance,
Srikanth.
Hi,
I drag the database Backup Task from the toolbox and double-click on it, it shows this error:
Cannot show the editor for this task.
Additional information
Method not found: 'Void Microsoft.SQLServer.Management.DatabaseMaintenance.TaskUIUtils..ctor()'.
(Microsoft.SQLServer.MaintenancePlanTasksUI)
What is the prob with this?
cherriesh
Hi,
I just volunteered to be the database administrator of our new sql server. I am normally the programmer so I have little experience with database administration. There are some basics that I need some assistance with mainly when to perform various maintanence checks. For example, how often do I need to perform some of the dbcc commands that I have read about? How often do I check for data integrity, index fragmentation, rebuilding indexes, or defrag indexes?
I have to come up with a plan by tomorrow and I don't know if I have anything solid enough since I don't exactly know when to perform some of these tasks. :confused:
Thanks so much for your advice.
I am having a hard time setting up a backup plan for my sql server databases. Right now I create a copy of each database on another machine on the network once a day. Once a week I upload a MSAccess version to a remote site. Obviously there are huge amounts of potential data loss if a crash of some sort would occur locally.
My new plan is looking like this:
1) Run a complete database backup once a day
2) Run Transaction Log backups every hour
My questions are as follows:
1) What role does a "Backup Device" play in this process (I currently do everything through the Enterprise Manager interface)?
2) I am having a hard time scheduling mydatabase_complete.bak and mydatabase_trans.TRN at all. I get several errors.
3) I would like to run these backups on another network machine but am not seeing how to access "My Network Places" from the "Backup Database" utility in Enterprise Manager.
Any help in the form of answers or pointing me towards a useful resource for this process would be much appreciated!
Thanks in advance~
Hi all,
I am new to SQL and was given a SQL server to administer. There are lots of databases on the server and no one seems to know which one is used and which one is not. I try to do some cleanning.
1. Can I find out if there is any activity on a database so I can delete them off and how?
2. There are backup jobs that run every night which do a full backup. I am thinking of changing it to do full backup once a week and differential every night. These databases are updated daily. Some have lots of activities, some have less. Does that sound reasonable? and if I do differential backup, should I create separate back up file or should I add on to the full backup file?
Any comments or suggestions are greatly appreciated.
I would like to implement the following backup strategy in SQL Server 2000:
full backup is done once a week
diff backup daily
tran backup every hour
How should I approach this. Should I use a backup device linked to a hard disk file? Should I create a backup device for each type of backup (full, diff, tran) and schedule it. If I use a backup device how do I distinguish each backup for old backups.
thanks
Are there any known issues using Maintenance Plans to backup DBs in SQL Server 7.0
Thanks
I made a backup maintenace plan in sql7.0, but it doesn´t work. The message included on the event logger is:
SQLServerAgent
SQL Server Scheduled Job
'DB Backup Job for DB Maintenance Plan 'Teste''
(0x219C5CB03C11D4119D8F0020188D7311) -
Status: Failed - Invoked on: 4/17/00 3:25:00 PM - Message:
The job failed. The Job was invoked by Schedule 12 (Schedule 1).
The last step to run was step 1 (Step 1).
And before this message there is another one that can be making something goes wrong:
SQLServerProfiler
Executable: C:WINNTsystem32mmc.exe
Description: Unable_To_Set_RegKey_Val
Source: ........srceventlog.c
Line: 315
Someone can help me telling me the reason that is generating this kind of error and doesn´t work. I have do the same think in others two server and there it works ok.
Thanks in regards
Sandra Lino
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
Our database is more than 20,000,000 bytes and increase largely everyday
who can give me some advise about stable backup?
My database is sqlserver2005 and now i use files to backup including full backup and diff backup,whether is better to use device?
any ideas? thanks in advance
Hello all,
I would like to be able to backup our databases to a network share mapped drive. Is this possible? When every I go in to setup a maintenance plan it will only allow me to select local drive as the backup location. If my server crashes its not going to do a whole lot of good if my databases are stored locally (I do also backup up to tape every niight). I backup the databases every hour to local disk and would like to send them to one of my MS Storage Servers on the network.
Any help would be greatly appreciated.
thanks
Jason
Hi:
Set up a Maintenance Plan backup job that is set to delete the backup file after 19 hours but it waits until another SQL backup job is completed before deleting the old backup file 27 hours later. (NT4 server, SQL7 SP1). Therefore the server requires space for two complete SQL backups just to hold one SQL backup. Not an issue when you have enough disk space but on very large databases 45GB+ it results in alot of wasted space.
The current workaround is to manually schedule the backup jobs TO DISK = with a nightlybackup.BAK filename. Then another job deletes that backup file at a specific time. Would like to use the maintenace plan for the detailed time stamp it labels the backup file. Any suggestions?
Thanks,
I have set up two Maintenance Plans to do daily backups overnight for our two DBs that are on our SQL server.
As a separate strategy we want to have a second backup done nightly that would involve
#1 detach the db(s)
#2 copy the mdf(s) and ldf(s) to L:xxx
#3 attach the db(s) again
#4 zip up the db(s)
#5 copy the files to a different server for storage.
I created #1, and #3 in query analyzer and saved the script.
I want the process to run at say 2AM and don't know how to schedule them to run - that is #1 ... #2 ... #3. Anyone have sample scripts for this kind of a backup strategy?
This seems like a very simple process especially for a restore and especially since there would not be any trasaction logs involved. This way if we had a KRASH we could take the backup from the separate server and install it on a warm SQL backup server.
Apart from the new servers name then what other steps would I need to cover to get the apps up and running in the quickest time. The app software runs as a client install - Access 97DB] I'm especially curious if I need to have more than a fresh MS2000 SQL server install.
I'm know I'm asking a lot for a first timer.
It seems we want to handle disaster recovery BEFORE we even have our first system crash.
many thanks
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