Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
LeoC
Starting Member
37 Posts |
 Posted - 2004-12-03 : 09:53:49
|
| Does anybody know of a simple way to detect any change to the SQL server (i.e. new DB, DDLs, sprocs., etc..) which turns into a "must" backup of master.dbf?I'll like to set a master backup job that gets triggered by one of these changes.Thanks...lec. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-03 : 10:49:01
|
| Why not just backup master regularly? Once a day is not a bad interval, and even once an hour could work too. You can always overwrite existing backup files if you need to save space and not keep older versions. |
 |
|
|
LeoC
Starting Member
37 Posts |
Posted - 2004-12-03 : 10:56:16
|
| That was my suggestion but unfortunately it didn't fly to well with other DBAs.Also, I would like to know what and when somebody else did something to the server; you see, in our scenario we (dbas) don't have complete control [as we should have, but it's a struggle to get to that point] of some servers. We don't even have the "sa" password for some servers either.This why I'm considering an auditing solution, so we could at least say when and what was done without our involvement.I know the question turned into a larger issue, sorry about! but any input is welcomed.thxs.lec. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-03 : 11:16:20
|
| The real problem is detecting the changes you want, the fact is it's not that easy. Even if it was, it's certainly not nearly as easy or helpful as regular backups are. As to not having complete control, well, I'm not gonna go into that one, but until that situation changes you'll always have problems like this. Frankly, if a DBA does not have control of a server then he/she is simply not responsible for it, period. I'm sure management would understand and agree with this very readily, just point out what happens to them when another department screws up and they take the heat for it. The "other DBAs" you mention need to work somewhere else, there's absolutely nothing wrong with backing up master on a frequent basis. Whatever reservations they have are completely unfounded, and their attitude towards this issue is likely to bite all of you pretty hard in the future. Make sure THEIR managers know that if master goes kablooey and there is no backup handy, your SQL Servers are down, down, down for the count. |
|
|
|
LeoC
Starting Member
37 Posts |
Posted - 2004-12-03 : 11:40:31
|
| Rob, I couldn't agree more w/ya in every aspect of this thread. Yes, the 'up-chain' has been made aware of the potential problems, yet change doesn't come that easy...And about frequent backups of master, the majority [DBAs] come from Oracle alone, so I guess that says a lot already...Myself, have been doing [back then] all 3 major ones (add sybase), so I know the basic conceptual differences by heart, new features and/languages are really not my thing lately.Thanks anyway for the replies!lec. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-03 : 12:15:05
|
The only thing I could suggest at this point is to periodically export the data from master into text files, you could then run a WinDiff or command-line diff/fc utility on them to compare to the last version. If it gives you any differences, then you can kick off the backup job. Can't say I have the actual plumbing for this right off the top of my head, but I'll try to cook something up and post it. I kinda like it actually, might be useful for something.  |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-12-03 : 12:42:13
|
| Set master to full recovery. Do a full backup daily. By Log Explorer to diff changes and run reports. :) Either way, they're spending money.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-03 : 13:49:27
|
OK, paste the following into a file named systables.txt:sysobjectssystypessysfiles1syspermissionssysuserssysfulltextcatalogssysmemberssysprotectssysdatabasessysxloginssysmessagessysconfiguressysserverssysaltfilessysfilessysfilegroupssysallocationsThen paste this into a batch file, in the same directory as the file above:if exist systables.dif del systables.difdel *.sysdatfor /F %%a in (systables.txt) do bcp master..%%a out %%a.sysdat -SmyServer -T -cfor %%a in (*.sysdat) do @diff %%a %%a.old >>systables.diffor %%a in (systables.dif) do @if "%%~za" neq "0" osql -SmyServer -E -h-1 -n -Q"exec msdb..sp_start_job 'backup master'"copy/y *.sysdat *.sysdat.oldYou'd have to change myServer to match your SQL Server name, and add the proper user and password entries if you're not using a trusted connection for bcp and osql. You'd also have to create a job named "backup master" that backs up the master DB if you don't have one already, otherwise change the name above to match it.You need to run the following command before you run the batch file:for /F %a in (systables.txt) do bcp master..%a out %a.sysdat.old -SmyServer -T -cThat will create your reference of the tables in master that you'll compare against. Just run this from the command line, it will not work in a batch file and you don't need to run it more than once.You'd use this by creating a job with a single CmdExec step that runs the batch file. Bascially it bcp's out the system tables that would be affected by a schema, permissions, or server config change, or a database add/drop. It compares them to the .old version using diff. I'm using a Windows version of the Unix diff utility, you can get it here:http://unxutils.sourceforge.net/I'm using it because it does not generate any output if the files are identical. If you have a similar diff utility that runs from the command-line, you can substitute it for diff. The Windows fc utility would not work for this batch file without a major change in the logic.Once the diff is done, if there are any differences they get written to the systables.dif file. It then checks the file size and if it's not zero, it calls osql to run the backup job. It then copies over the newly exported files to the .old files.I'd love to say I've tested the entire thing, but that would be a lie.  I only tested individual pieces of it to make sure they work. If you have problems with it let me know, I do a full test and fix it.It's possible to have this procedure find a difference when there isn't one. bcp can export the same table in a different row order that would cause a difference, even if the data is identical. It will also find a difference if an object or database is simply renamed or has a single attribute changed. I don't know how significant of a change you want to accommodate, but this should pick up everything you'd want to capture. |
|
|
|
|
|
|
|
|
Changes to SQL server that make it into master