SQL Server Performance Forum – Threads Archive
Db back ups
Our NT administrator says that we shouldnt bother with routine back ups via SQL Server. That he backs up the all the files (Sql mdf adn ldf) on the drive server. I dont think this that is a good solution. Can he still perform point in time recovery? Can some one give me the pros and cons of using his stategy, so I can propose that we use standard back ups liek through Db maintenance plans or the Agent. ThanksLet see if I follow.
1) NT administrator made backups of all files, include SQL files on drive server, same server than SQL Server are?.
2) There is no incremental or differencial Backup, using log?.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
So is that the only difference is that he can not perform defferntial backups? Thanks
Well, only and critical if you want to be online asap with mimimal information lost in case of crash. Plus having backups, that way or via Jobs, on same drive same server, is to risk. Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Can you please be kind enough to expalin why. Thanks
This is a good, basic article on recovery strategies, which is why you take backups and helps you determine how much data you can afford to lose. http://databases.about.com/library/weekly/aa031101a.htm And I found this tiny little tidbit in my SQL 2000 Admin training kit (for the exam) "Note: You must use SQL server 20000 or third party database backup programs to backup online database files. Windows 2000 and NT backups cannot back up files that are in use and for this reason cannot backup online SQL server 2000 database files." As for why you copy your backups or make your backups elsewhere is in the case when the server fails. If all copies of your database are on the server, then they are useless. Ditto for drive failures etc. Does that help?
Chris
Yes thanks. Thanks
One of the most important tasks you will face as a DBA is performing backups. Although backups are certainly not the most interesting part of the job, they are probably the single most important. If something goes wrong it’s the DBA’s job to get the server back up and running as quickly as possible. Loss of productivity or, even worse, loss of data can be very expensive for a company. Don’t care about any other alternatives/application on OS level, but make sure your ground is clear and firm by scheduling regular backups. Its a part of best practice by any DBA…. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Let know if this plan will work. Db back up are every day every 6 hours from 9am – 9pm
9am 3pm 9pm
Transaction log back ups are every day every 3 hours from 10am – 10am
10am 1pm 4pm 7pm and 10pm I back up to a different server and to tapes I have a new tape for each day. Every time I switch tapes in the morning the job rewrites all data on the tapes performs the back ups. Do you think this is a good plan. Db back ups take about 10min and so do transacion logs.
Thanks
Indeed, this is a good plan.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
I´ve been testing everything and it works find. Exept that everytime I run the job it fails in one of the steps (backups) I configured it so it will retry when it retries it succeeds, is this normal with tape backups? Here is the error Executed as user: dbAdministrator. Read on ‘Tapebackups’ failed, status = 1117. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3203) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013) Thanks
I find this in BOL Help with Backup and Restore
The online Backup and Restore Troubleshooter is designed to help you resolve problems you may encounter when backing up or restoring databases and transaction logs in Microsoft® SQL Server™ 2000. Note For international users of SQL Server Books Online, the text of the problems as well as the references to Knowledge Base articles appear in English and can be found on nonlocalized Web sites.
Common starting points for troubleshooting backup and restore include: Problems with backing up or restoring a database or transaction log that is password protected.
Problems appending or initializing a backup to an existing backup device.
Problems when backing up a transaction log or performing a differential file or file group backup while the database is set to the ‘SIMPLE’ recovery mode.
Poor performance when performing a backup or restore.
The database restore completes successfully, but the backup appears to have restored the database to a point in time earlier than expected.
Problems performing a point-in-time restore of a transaction log.
System database backups cannot be restored on a different build of SQL Server.
Error 927 when attempting to use a database after a successful restore.
Error 3201.
Error 3203.
Error 3024.
Error 3038.
Error 3101.
Error 3108.
Error 3168.
Error 3141.
Error 3159.
Error 4330.
Errors 1834 and 3156.
Errors 4326 and 4305.
To access troubleshooters online, see Viewing Online Troubleshooters and other PSS Web-Based Information. Hope it help.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
I had read that today earlier but didnt really look into it. Thanks Thanks
The first step in the job runs this command. It writes over any existing data on the tape which would be last weeks back up because I use a different tape for each day of the week. All the other jobs after this append to the tape. This job is executed 3 times a a day. This is probably a stupid question but can the tape screw up do to exess formating? BACKUP DATABASE master
TO TapeBackups
WITH INIT ,
NOUNLOAD ,
NAME = N’masterBK’,
SKIP , STATS = 10,
DESCRIPTION = N’System DataBase Backup ‘,
FORMAT ,
MEDIANAME = N’DB_SET’ I seam to keep getting these errors. I just tested it and got it agian. Executed as user: DBAdministrator. Cannot open backup device ‘TapeBackups’. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). NOTE:
The step was retried the requested number of times (2) without succeeding. The step failed. When I check the error log i notice that it reports something about dismounting. Tape ‘Database_SET’ (Family ID: 0xa6fa9833, sequence 1) dismounted from tape drive ‘Tapebackups’.
Why does it do this? I am using only one family to back up the Dbs and corresponding Transaction logs. should I create two families? Thanks
Try to backup to a local file and then use OS application write into tape, backup directly to tape is often slow and error represents there is no device(file) associated as mentioned in the statement. Ensure SQL service account and your account has required privileges on tape drive.
Its better to create different families for Database backups and TLogs backup. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
The backups all succeed. When I read the history table i notice that it has to arbitrarly retry on a one the steps. I’m running a job that backs up 9 databases, one step at a time. Sometimes it runs with out retries and sometimes does. It happens more on the transaction log job backups which by the way do not overlap when the Datbase backups are running. Is there a way I can put a WAITFOR clause maybe the tapes need to wait a second or so for the next step to run. Thanks
Yes, you can use WAITFOR fow a few seconds, may be you are writhg and tape are not ready to work.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
I get a syntax error near the waitfor keyword with and without the comma. BACKUP LOG Corporativa
TO [TapeBK_01]
WAITFOR DELAY ’00:00:05′,
WITH NOINIT ,
NOUNLOAD ,
NAME = N’CorpTLBK’,
SKIP ,
STATS = 10,
NOFORMAT
Thanks
I was thinking between BACKUPS. Backup.. end backup. waitfor Backup… Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
I suggest you to bifurcate the backups job, for the bigger database keep it seperate and for smaller ones a seperate plan. This would help to dig out any issues for the troubled ones too. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Will do Thanks a lot for your help. By the way is there a way I can run a job just to format the disk and create a new media set? If not no problem. The tapes are just temparary until we get are Stand by server which is on its way. By the way how often should I back up Master and Msdb? Thanks
While using BACKUP statement you can specify FORMAT/INIT clauses.
Where BOL specifies : FORMAT
Specifies that the media header should be written on all volumes used for this backup operation. Any existing media header is overwritten. The FORMAT option invalidates the entire media contents, ignoring any existing content. Important Use FORMAT carefully. Formatting one backup device or medium renders the entire media set unusable. For example, if a single tape belonging to an existing striped media set is initialized, the entire media set is rendered useless. By specifying FORMAT, the backup operation implies SKIP and INIT; these do not need to be explicitly stated. INIT Specifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set data on that device is overwritten. The backup media is not overwritten if any one of the following conditions is met: All backup sets on the media have not yet expired. For more information, see the EXPIREDATE and RETAINDAYS options.
The backup set name given in the BACKUP statement, if provided, does not match the name on the backup media. For more information, see the NAME clause.
Use the SKIP option to override these checks. For more information about interactions when using SKIP, NOSKIP, INIT, and NOINIT, see the Remarks section. Note If the backup media is password protected, SQL Server does not write to the media unless the media password is supplied. This check is not overridden by the SKIP option. Password-protected media may be overwritten only by reformatting it. For more information, see the FORMAT option. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
It depends if there are frequent changes to jobs, system configuration and its reasonable to backup system databases on daily basis, no issues at my end and it never hog-up system resources. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I added the waitfor statement TO each job step as follows. WAITFOR DELAY ’00:00:10′
BACKUP DATABASE…. One thing I notice in the SQL Log was this Tape ‘SERVER04_SET’ (Family ID: 0xcc8c9057, sequence 1) mounted on tape drive ‘TapeBackup’.
Tape ‘BackupDB_SET’ (Family ID: 0xcc8c9057, sequence 1) dismounted from tape drive ‘TapeBackup’
Database backed up: Database: master, creation date(time): 2003/12/14(14:01:24), pages dumped: 1920, first LSN: 202:145:1, last LSN: 202:147:1, number of dump devices: 1, device information: (FILE=1, TYPE=TAPE, MEDIANAME=’BackupDB_SET’: {‘TapeBackup’}). It follows this same sequence for all 9 databases. Are these Mounts and Dismounts part of the backup process? I am going to monitor the backups trough out the day and see what results I get using the WAITFOR statement.
Thanks
I think mount & dismount is processed from OS when SQL completes its backup procedure.
Anyway you may find more information during you monitor the process. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Since I included the WAITFOR DELAY ’00:00:10′ BACKUP DATABASE… statement all backups have succesfully completed without any retries. I dont know if this had any impact or not but I’m not complaining. Thanks! Thanks
BTW, are directly performing backup to tape?
Its always better on the basis of performance to do a local backup and use OS application backup the file to tape, this way you can gain performance on SQL process and while using backup process. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
True. But in no production hours is ok. Nigth I mean. Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
So your saying to back up to local disk first, then back up from disk to Tape Drive? If so how often do I back up from local disk to tape drive? Why shouldnt I back up directly from server to Tape? Thanks
Here are som pros/cons on disk vs tape backup:
http://www.allisonmitchell.com/Articles/bkpdisktape.htm /Argyle
IN addition to Argyle’s reference you can see lot of time difference in executing backup to local disk and a tape. Backups must be scheduled regularly where you cannot excuse for any reason, continue the existing process and monitor the process. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>