November 30, 2006 at 7:12 pm
In todays wold of auditing, I have been asked to be able to show auditors that a database has been restored. I manage over 250 databases, and I really do not have alot of time to sit down and go through each SQL Server and show then the restore history table. So I have created a database on a reports server that I want to populate with all restorehistory.
I cannot get the trigger on the restorehistory to work to insert on the new server because of an error with an fk on the restorefile table.
Anyone have an idea or another automated way to track restores
Stacey W. A. Gregerson
December 1, 2006 at 1:37 pm
1. Create a server list on Report server and create table RestorHistory_All table as RestorHistory table with one additional column server name.
2. create a procedure get the restorehistory into a permanent temp in tempdb and deploy this procedure to all servers using OSQL.
3. Create a procedure on Report server to BCP out/in from all servers one at a time and insert NOT exists data into RestorHistory_All table.
4. Schedule the procedure created in step3 on Report server.
Initially this may take 1-2 hour of write the script but later it saves lot of time.
I use this technic to collect SQL error log in sql2000 from all servers...
MohammedU
Microsoft SQL Server MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply