| Author |
Topic |
|
vabraham
Starting Member
13 Posts |
 Posted - 2004-02-27 : 12:35:20
|
| Hi everyone, I have 2 questions and as a trying to be SQL Server Admin, dont have an answer to this one in particular. 1. I am trying to create a backup for a business critical database which has voice and screenshot data in it. The business criticality decision makers may come back in a few days and ask that it is absolutely necessary to have a back up done every 15 minutes. I think that is an absolute disaster. The machine will potentially only be done back ups 24 by 7. Any suggestions.2. When the SQL server wants to do a back up, I understand it must go into single user mode to effectively do a successful back up. If the database is in a multiuser mode most of the time, when can it do the back up and be sucessful. Do I have to force it to go into single user mode to do its back up. Is this possible. Yes or No. what are my choices. Any recomendations?Thanks in advanceAbraham |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-27 : 12:37:56
|
| For every 15 minutes, perform transaction log backups. Perform the full backup only once per day. It does not need to go into single user mode to perform any of the backups.Tara |
 |
|
|
vabraham
Starting Member
13 Posts |
Posted - 2004-02-27 : 12:42:47
|
| Hi Tara, This is error I got when i did try to perform a back up yesterday.Witness Backup2_200402261410.txtMicrosoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'CCAPP02' as 'NT AUTHORITY\SYSTEM' (trusted)Starting maintenance plan 'Witness Backup' on 2/26/2004 2:10:00 PM[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'Witness'[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.[1] Database Witness: Check Data and Index Linkage...[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode. The following errors were found:[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode. ** Execution Time: 0 hrs, 0 mins, 1 secs **Deleting old text reports... 0 file(s) deleted.End of maintenance plan 'Witness Backup' on 2/26/2004 2:10:01 PMSQLMAINT.EXE Process Exit Code: 1 (Failed)Could you explain why I did get this error? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-27 : 12:45:40
|
| What query are you running?Here is an example full backup:BACKUP DATABASE DBNameTO DISK = 'E:\MSSQL\BACKUP\DBName.BAK'WITH INITHere is an example transaction log backup:BACKUP LOG DBNameTO DISK = 'E:\MSSQL\BACKUP\DBName.TRN'WITH INITTara |
|
|
|
vabraham
Starting Member
13 Posts |
Posted - 2004-02-27 : 12:49:00
|
| Hi Tara, I am using the Database Maintenece Plan wizard to do my back up. Do you think it right to go in that direction. Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-27 : 12:50:52
|
| No I do not. Since you are on your way to becoming a DBA, you should not be using the wizards to setup admin jobs. But now that you have, what boxes did you check in the maintenance plan. Please explain the optimization, integrity, and backup screens in the plan.Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-02-27 : 12:52:51
|
| Tara, he is running the integrity check. If he runs DBCC CHECKDB('Witness') it should give an error also.If you can, run that command and send us the text of the error.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
vabraham
Starting Member
13 Posts |
Posted - 2004-02-27 : 12:54:30
|
| Hi Tara, I may be able to get back to this topic in a few hours. I am working with a the network admin and working with him to give me access to the box. I will be back shortly to explain the check boxes to you on the wizard.thank you |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-27 : 12:55:09
|
| I realize what is being run, but he is having the plan do too much. DBCC CHECKDB will not error out unless he is running the REPAIR options as well.Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-02-27 : 12:58:50
|
| Yeah...I hate those stupid maintenance plans. It's so much easier to just script it out and do it that way.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
vabraham
Starting Member
13 Posts |
Posted - 2004-02-27 : 12:59:31
|
| Hi everyone, yes, I am asking it to do the Integrity check and also repair any minor options.thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-27 : 13:00:36
|
| Uncheck the repair option.Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-02-27 : 13:03:23
|
| IF you are going to use maintenance plans, set up one to do backups. Set up another to do integrity checks, etc. (Just the way I would do it). You can then be more limiting in how you have each plan set up. For now, do what Tara said. You really need to do a DBCC CHECKDB on that database and find out what's wrong with it though. You can do this afterhours if you need to.Also, if you are going to be the full-time DBA, you might want to look at scripting things out. There are some great resources here and the links in "Elsewhere".MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
vabraham
Starting Member
13 Posts |
Posted - 2004-02-27 : 13:13:40
|
| Thanks everyone,I will get back to you in a few minutes. i have to run and track down the network admin. thanks Tara and Derrick. You guys and gals ROCK!! |
|
|
|
|
what is best ????