.NET BRAINEE WINGS: October 2015

SELECT sdb.Name AS DatabaseName,

COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime

FROM sys.sysdatabases sdb

LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name

GROUP BY sdb.Name

SQL Query for Find the Database Restore History in SQL Server

declare @DB sysname = 'DatabaseName';

select * from msdb.dbo.restorehistory where destination_database_name = @DB order by restore_history_id desc;

SET cn = CREATEOBJECT("ADODB.Connection")

SET cmd = CREATEOBJECT("ADODB.Command")

cmd.CommandTimeout = 1800

SET rs = CREATEOBJECT("ADODB.RecordSet")

Set fso = CreateObject("Scripting.FileSystemObject")

fso.CreateTextFile ("C:\Users\pselladurai\Desktop\ListofDatabase.txt")

Set fil = fso.OpenTextFile("C:\Users\pselladurai\Desktop\ListofDatabase.txt", 8, true, 0)

'**** Change the connection string as required

cn.open "Provider=SQLOLEDB.1;Data Source=(local);Integrated Security=SSPI;Initial Catalog=Master"

cmd.activeconnection = cn

cmd.commandtext = "exec sp_helpdb"

SET rs = cmd.EXECUTE

'display = "Size" & vbtab & vbtab & "Name" & vbcrlf

WHILE rs.eof <> TRUE AND rs.bof <> TRUE

'display = display & trim(rs("db_size")) & vbtab & vbtab & rs("name") & vbcrlf

fil.WriteLine rs("name")

rs.movenext

WEND

cn.close

fil.Close

Wscript.Echo "Done."