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
Hello, 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
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.
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
In SQL 2005...
How do I specifty in the maintenance plan to only keep 3 days worth of backup.
Andre
I have to created a Maintenance Plan in SQL 2005 to take the backup of 5 different DB at 2AM everyday. Each DB has its own sub-directory and backup file The total DB size is 35GB. I need to overwrite the exsisting backup instead of having new backup everytime.
View 4 Replies View RelatedHello,
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
Hello, everyone:
I have a backup plan that remove the old backup files more than two days. Last Monday, the SQL Server restart. Since that, the backup is fine, but it didn't remove the old backup files. Why and how to handle that? Thanks
ZYT
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 RelatedHi 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.
Hey all-
I am working to establish a backup plan for SQL Server 2000. We are currently doing log shipping between servers.
A full backup once a week
transaction log backup every hour
We run a bulk deletion (delete * from table where date < ninetydays) which deletes 22,000 rows approximately. We are running in Full Recovery mode.
I wonder if there was anyway to improve my backup plan, preferably minimizing logging when the deletion takes place but still backup the data with log shipping.
Let me know if any of you have ideas. Thanks
Nate
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 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 RelatedHello all, I was just awarded the job of maintaing the database serverfor our company. I have basically ZERO experience using MS SQL Server2000. Can anyone point me in the direction of a good resource forcreating backups of our database? I would love something that comeswith a gui that really simplifies the process; seeing as how i havenever even opened the MS SQL program.Our database is fairly small we have 7 users with access to thedatabase. That is it.any advice or good resources would be greatly appreciated.
View 2 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.
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
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!