November 1, 2012 at 2:01 am
Hello all,
In SQL SERVER 2008, if any DML activity or any import export or any Backup restore etc is been done... then where does this information gets stored?????
How will i able to know where to find & what would be the best approach??
IF i have to set this in oredr to track work.. how should i set this task???
Please help me
************************************
Every Dog has a Tail !!!!! :-D
November 1, 2012 at 3:23 am
Backup restore is stored in MSDB, in the restore history and backupset tables and many more.
Some DML (Insert, Update, Delete) is stored in the transaction log, assuming you have bulk or full recover set on your database and then you need to ensure that you do regular transaction log backups.
Import Export is not logged unless you create some custom logging and build it into the import export routines.
November 1, 2012 at 3:37 am
But what if the databse doesnot start due to some reason.. how to find the reason & where to fin the information :w00t:
************************************
Every Dog has a Tail !!!!! :-D
November 1, 2012 at 3:38 am
look in the sql and windows logs
November 1, 2012 at 3:39 am
That will be in the SQL Server error log.
Just to clarify something Anthony said. Data modifications (insert, update, delete and DDL) are stored in the tran log regardless of what recovery model you have, but the transaction log is not an audit log, it's not there to tell you who did what. It's there to allow rollbacks, database recovery and to guarantee durability of changes.
If you want an audit trail, you have to set one up.
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
November 1, 2012 at 3:43 am
Thanks Gila, but reagrding SQL SERVER Error Log, will this will a file stored on any location???
coz if DB wont start how to check Error logs then??
More over coming to Audit, can u provide links where in i would read, understand & start implementing it?
🙂
************************************
Every Dog has a Tail !!!!! :-D
November 1, 2012 at 3:47 am
runal_jagtap (11/1/2012)
Thanks Gila, but reagrding SQL SERVER Error Log, will this will a file stored on any location???coz if DB wont start how to check Error logs then??
More over coming to Audit, can u provide links where in i would read, understand & start implementing it?
🙂
Typically, the error logs are stored in C:\Program Files\Microsoft SQL Server\MSSQL10_50.instname\MSSQL\Log but obviously this may have been changed
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 1, 2012 at 3:50 am
If SQL doesn't start it will tell you why in the Windows event viewer, if a DB doesn't start it will be in the SQL error log.
As Perry has already detailed the logs are typically stored where the binaries are but can be changed.
As for auditing it all depends on what you want to audit, maybe here will be a good place to start reading up http://msdn.microsoft.com/en-us/library/cc280386%28v=sql.105%29.aspx or do a search on a search engine of your choice on the topic
November 1, 2012 at 3:56 am
i opened Event Viewer.. but exactly where do i find the Logs stating SQL server
************************************
Every Dog has a Tail !!!!! :-D
November 1, 2012 at 4:00 am
Not event viewer. The SQL Server error log. (Those log entries are in the application event log too, often harder to find among all events from all the other apps)
It's a text file on disk (location depends where you installed SQL), latest one is simply called ERRORLOG
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
November 1, 2012 at 4:05 am
Ok fine.. willl see to it 🙂
thank you everyone for quick response n help 🙂
I will like to add one more thing
We have one Rack Space server & other normal local server..
Now Rackspace servers have some databases running...
Whenever i need to copy a file from Rackspace to local i have to FTP it..
It means that i can not apply Database mirroring or Log Shipping :w00t:
that day i copied one file of 1gb.. it almost took an hour to complete :w00t:
that day i wanted to import/export some tables from rack space to local one for that i have to take backup of one DB from rack space & then FTP it to local drive then restore n then import/export :w00t:
Do any one have any document or link or idea who can help me with this type of scenario????
************************************
Every Dog has a Tail !!!!! :-D
November 1, 2012 at 4:18 am
runal_jagtap (11/1/2012)
Ok fine.. willl see to it 🙂thank you everyone for quick response n help 🙂
I will like to add one more thing
We have one Rack Space server & other normal local server..
Now Rackspace servers have some databases running...
Whenever i need to copy a file from Rackspace to local i have to FTP it..
It means that i can not apply Database mirroring or Log Shipping :w00t:
that day i copied one file of 1gb.. it almost took an hour to complete :w00t:
that day i wanted to import/export some tables from rack space to local one for that i have to take backup of one DB from rack space & then FTP it to local drive then restore n then import/export :w00t:
Do any one have any document or link or idea who can help me with this type of scenario????
have you consulted your network\domain admins for help communicating between servers?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 1, 2012 at 4:42 am
I spoke with them,... they said they are using a FTP via which the files are transffered.. but if i want to make a database backup restore or data import/export then this will be a mess to work... i we have to forget abt Database Mirroring.. coz Mirroring would fail or slow down the network :w00t::crazy:
************************************
Every Dog has a Tail !!!!! :-D
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply