June 30, 2008 at 3:21 pm
Hi all,
Am trying to do a migration of some servers from 2000 to 2005 but need to do some auditing on each server to retrieve some useful information. I need to write queries to supply the info below
1. Login information at server and and database levels: i.e Server name, Database, sql server login
name/database login name as "login name", type of login (sql user, nt user or nt group), date created, date updated, surname, initials, Nulls as size.
2. Database information at server level: i.e Server name, Database, Database owner login name as 'login name', 'database owner' as 'type of login', date created, last database backup as 'date update', surname, initials, size.
3. These information need to be stored in temporary tables.
4. Results from these temporary table should then be exported to Excel.
Any assistance would be appreciated such as tsql to help query the servers to supply above info, also create the temporary tables from the results and to export it (think of using import /export wizard).
July 1, 2008 at 11:48 am
In SQL 2000, what you're looking for are the system tables. Pop those open, including in master (in system databases), find the columns you're looking for, query them.
For example, master.dbo.sysdatabases has a list of all the databases.
Alternately, you can probably get all of the data you want from one of the documentation products by RedGate or ApexSQL. They'll do it faster, more easily, and very accurately.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply