August 9, 2010 at 10:48 pm
How do I use SQL Trace to record ALL actions preformed on the database to a file?
August 10, 2010 at 1:33 am
webmaster 27466 (8/9/2010)
How do I use SQL Trace to record ALL actions preformed on the database to a file?
Have a look at what is available when you choose 'Show All Events' from within SQL Server Profiler. Why do you want to select everything? You'll be swamped with data.
BrainDonor.
August 10, 2010 at 1:42 am
Its a database used by an application called SOAPware.
It uses the whole database with data split up across different tables and databases.
Backing up ALL changes is relevant as the data is important and all relevant to other data elsewhere in the database.
Also I have looked I dont see any option for this.
I have scheduled a daily backup but I dont want to backup the WHOLE database every day, just the changed to it.
August 10, 2010 at 1:43 am
Is there not a simple script I can run on the server or even already programmed tool to do this?
August 10, 2010 at 1:50 am
webmaster 27466 (8/10/2010)
.Backing up ALL changes is relevant as the data is important and all relevant to other data elsewhere in the database.
I have scheduled a daily backup but I dont want to backup the WHOLE database every day, just the changed to it.
Let us back up a step - Backups?
Profiler is a diagnostic tool - it is used to show details of what is running or impacting upon the databases and is used to assist in investigating what is really going on 'behind the scenes', as it were. You're implying that you want to actually find out what has happened so you can backup changes. Surely what you need to look at are the various backup methods you can use. It sounds like Log Backups are what you should be looking at, with the database in Full recovery. Does that sound more sensible, and do you know about the various backup models?
BrainDonor.
August 10, 2010 at 1:58 am
I know nothing about backup models.
Basically what I want to do is
a)Create a full backup
b)apply this full backup to a remote server
Then every day from 00:01 to 20:00 monitor all changes to the database and write all queries from that day into a file.
Then at 20:00 have a program (I can make the program, I simply have no knowledge of advanced SQL servers) read the file and apply all the queries in it(from that day) to the remote server.
This way every day at 8:00PM there will be an identical copy of the database on a remote server.
This idea is to save bandwidth and time, as the whole database is nearly 3GB and backing up the whole database every day would be very cumbersome.
August 10, 2010 at 2:11 am
webmaster 27466 (8/10/2010)
I know nothing about backup models.Basically what I want to do is
a)Create a full backup
This way every day at 8:00PM there will be an identical copy of the database on a remote server.
Then you have some reading ahead of you.
By far the easiest way to do this is a method referred to as 'Log Shipping'. Basically (and my apologies to anyone out there that uses this method and so knows the finer detail) the transactions are backed up on your main database to a Log file at a regular interval. These changes are then sent across to the other server and applied to the copy of the database. It's all part of SQL Server and is the simpler (in my opinion) of several forms of 'replication'.
What you need to understand first is how to use the Full Backup recovery model. After that have a look at Log Shipping.
Books Online (BOL) has an inordinate amount explaining Full Backup, and this site has too. Have a look around and make sure you understand this area first.
Any further questions, don't hesitate to ask.
BrainDonor.
August 10, 2010 at 2:16 am
August 10, 2010 at 2:17 am
I kind of need to be able to do this in 12 hours....
LOL.
But I will look around.
But I am still open to directions on how exactly to do it if anyone has any.
August 10, 2010 at 2:18 am
I am the new NSA, and DBA, I am in training.
Making sure I secure the job requires that I learn at least exactly how to do this.
I can program, I am experienced in NSA but NOT in DBA.
August 10, 2010 at 2:46 am
webmaster 27466 (8/10/2010)
I am the new NSA, and DBA, I am in training.
There's no senior DBA there? What happened to the previous DBA?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2010 at 2:56 am
He just up and left.
Now I am stuck with what I've been left to work with.
I know everything I need to know EXCEPT how to get the database to record all queries preformed on it to a file.
If I can not figure anything else out I am thinking of trying.
CREATE TRIGGER [trigInsTableA]
AFTER INSERT
ON TableA
FOR EACH ROW
BEGIN
insert into TableAChanges select * from TableA where rowid = new.rowid
END
for each table, then making daily dumps of the tables.
I sort of understand the code above my only question is will this code continue to run 24/7 on the server?
August 10, 2010 at 3:04 am
webmaster 27466 (8/10/2010)
He just up and left.Now I am stuck with what I've been left to work with.
Webmaster, I did you a disservice - I assumed you were one of the many people that becomes a DBA under false pretences. My apologies - being dropped in it like that is no fun at all.
BrainDonor.
August 10, 2010 at 3:08 am
Ok, take several steps back. I mean no offence, but you're thinking like a programmer here.
What is the purpose of this? If what you're trying to do is to get a copy of the database up to date on another server, you should be looking at one of transactional replication or log shipping. Log shipping if it's a DR solution (disaster recovery), replication if you're trying to get a second copy of the database for reports or queries.
Writing your own replication is possible (which is what you seem to be trying with SQL Trace/triggers), but a great waste of time. Plus you get to solve all the problems that the people at MS solved a long time ago.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2010 at 3:16 am
Log Shipping for the purpose of DR is exactly what I'm looking at.
No offence taken, I am after all originally a programmer, so henceforth naturally I assume that If I can not find direct instruction (step by step) or do not have time or room for trial and error to get it to work, that naturally I should attempt to solve the problem with code.
I have worked with databases before in situation with websites, but never a live database used by an application and NEVER a database of this magnitude.
The magnitude of the database as previously stated is the reason for the desire to log ship.
Now me currently being on linux the tools which are used at the office wont work properly under wine(im not sure why).
I do however remember a section of the database manager that allowed for backups to be scheduled.
I scheduled a full backup to a network drive daily, only to find no backups in the network drive... so perhaps there is something more in depth that I am not seeing that must be configured.
So you all know I am using the following tools(its a migration kit).
http://soapedia.mysoapware.com/060._Downloads/Legacy_Downloads_(4.x,_etc.)/SOAPware_4.X_Modules/SQL/MSDE_Migration_Kit
I am using the provided data manager with the included MSDE server.
All help is appreciated, I am sorry if I seem daft, I am however ignorant in this subject, but once educated it should be no problem for me.
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply