#12. Script to check detached_attached database

Why?
There are multiple ways on which you could maintain your databases numbers along with the details of newly created databases & those which got deleted from your Sql Server , this can be easily identified from SQL logs. But to my scenario, i don't want to do that often rather designed a script which can be run on a weekly basis that will log the details i need. So let's start.

Scenarios:
My idea is to use XP_cmdshell. to check databases available from sys.master_files & load their names into text files & then compare on a weekly basis. & it will log the results into another text file with info.We are going to handle 3 scenarios within this .
 a. No databases got deleted or created.
 b. Databases got newly created.
 c. Databases got deleted.

Script :

/* script to identify detached or attached databases from a SQL server by VSG

 the detached_added_db.txt will give you difference in how database names were there in last week

& how they are in current week along with the databases got detached or deleted or added.

*/

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

-- the following query should be executed only once before you schedule your job.
--please leave it as commented it only once you had executed.
--EXEC xp_cmdshell 'bcp "SELECT [name]FROM SYS.master_files" queryout "D:\current_week.txt" -T -c -t'

-- changing previous file to last_week.txt

EXEC xp_cmdshell'DEL D:\last_week.txt'

EXEC xp_cmdshell'RENAME D:\current_week.txt last_week.txt'

-- loading currnet data
EXEC xp_cmdshell 'bcp "SELECT [name] FROM SYS.master_files" queryout "D:\current_week.txt" -T -c -t'

-- script to compare
EXEC xp_cmdshell'CD D:'

EXEC xp_cmdshell'echo this job is running on >>D:\Detached_added_db.txt'

EXEC xp_cmdshell'DATE /T >>D:\Detached_added_db.txt'

EXEC xp_cmdshell'TIME /T/N>>D:\Detached_added_db.txt'

EXEC xp_cmdshell'FC /C /N D:\last_week.txt D:\current_week.txt >>D:\Detached_added_db.txt'

EXEC xp_cmdshell'echo if databases are detached it will be shown under last_week changes if databases are created it will show in current_week changes>>D:\Detached_added_db.txt'

EXEC xp_cmdshell'echo============================>>D:\Detached_added_db.txt'

Explanation:

 1. For using this script we need  to enable xp_cmdshell  in sp_configure to 1.
 2. we are using 3 text files here.All will be created by the script itself.
      i. last_week--> holds details of last weeks Db details
     i i.current_week--> holds details of current week Db details.
    i i i.Detached_added_db --> holds details of changes happened.
3. Note that we need to create current_week.txt only once using the script commented there & then you need to schedule this job.Also comment the same again.
4.the idea is to compare current_week with last_week files & report the difference into Detached_added_db

4. Ran the commented line & this will create current_week text file on D:\ location on my system.
5.Now I am running whole script. this will create the required Detached_added_db text file on D:\ location.
6.Since we haven't done any changes in databases, the detached_added text file shows following output. ( scenario 1)

7.Now adding one database to the server (scenario 2) and running the query again.the output looks like  

8. Now deleting newly created database and detaching ADVENTURE database (Scenario 3). Then running the query.New log will get appended to the same file. Now the output will be

9. Thus by scheduling this job we can keep a record of our databases created & deleted on a weekly basis & you could see & predict on which week your application or users created or deleted them.

========================================================

Thanks for coming till this point!!
VSG
SQL DBA