Lazy DBA: Backup all your databases using a script

I have a compilation of scripts which I use on a daily basis to help me make my work a bit easier. I always mention during my presentations that the laziest people are administrators who happen to have a programming background. Here's one that I frequently use. A message on my instant messenger popped up asking me how to quickly perform backups of databases in SQL Server. I told him to write a BACKUP DATABASE command. Now, he was telling me about how much time it would take since they have like hundreds of databases. The solution: write a script. The script below performs a backup of all the databases on a SQL Server 2000 instance and dumps it in a local folder.

DECLARE @IDENT INT, @sql VARCHAR(1000), @DBNAME VARCHAR(200)
SELECT @IDENT=MIN(DBID) FROM SYSDATABASES WHERE [DBID] > 0 AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
WHILE @IDENT IS NOT NULL
BEGIN
SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT
/*Change disk location here as required*/
SELECT @SQL = 'BACKUP DATABASE ' + @DBNAME + ' TO DISK = ''F\BACKUP\'+@DBNAME+'.BAK'' WITH INIT, STATS=10'
PRINT "==========================================="
EXEC (@SQL)
PRINT "Backup for database " + @DBNAME + " has been created"
SELECT @IDENT=min(DBID) FROM SYSDATABASES WHERE [DBID] > 0 AND DBID>@IDENT AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
END

This excludes the tempdb, Northwind and Pubs databases should you have it in your instance. Restoring is, of course, a different story. You need to start with the system databases (master and msdb, in my case) before you can restore the user databases. In a future blog post, I'll have a script to read the backups generated by this script and restore them all. See how lazy I can be?

Say hello to VMWare

What can I say, I had the opportunity to work on VMWare Workstation because of an not-for-resale (NFR) license provided for Microsoft MVPs. Having a Windows XP laptop with very limited RAM is a limitation I have to contend with everytime I do presentations. Imagine running a virtual image hosting a domain controller, DHCP, DNS, IIS, SQL Server 2005 and System Center Configuration Manager 2007 with at least two or three virtual clients. My laptop goes haywire after firing up all of my virtual images. I think I'll stick to VMWare Workstation while waiting for a more powerful laptop.

One more thing, I managed to configure my virtual network with a server and workstation to access the Internet thru NAT. Since I am not allowed to add workstations on our local network, I cannot use the host machine's network card to access the Internet. I configured the network card of the VMWare image to use NAT, assigned a static IP which is in the same subnet as the VMWare NAT address on my host and pointed its DNS to the same IP as well. This made my VMWare image access the Internet thru NAT using a static IP. Dynamic IP would be a lot easier as the VMWare DHCP will assign an IP to my image but that was not an option for me since I am working with a server, which needs a static IP. After making sure that my server can access the Internet, I configured routing and remote access on my virtual Windows Server 2003 so I route the traffic from my other subnet to the IP I used to access the NAT. This way, my clients can access the Internet but are only accessible thru my virtual network,meaning even my host machine cannot access my virtual clients. Quite cool, huh.

For my first test, I did my SQL Server 2008 Declarative Management Framework session for the Singapore SQL Server User Group all on VMWare Workstation. And the audience didn't even notice I was using a non-Microsoft product. I'll post the details of my session in a separate entry

Goodbye Orcas, Hello RTM

I haven't been up to date with Visual Studio 2008 now that it already has RTMed. Since I have some spare time to download the RTM version, I've decided to uninstall my Beta 1 installation of Visual Studio Orcas. In previous versions like Visual Studio 2005, this was a total pain until Microsoft decided to release those cleanup utility. For Visual Studio 2008, Microsoft came up with a documentation on how to uninstall the previous builds before installing the RTM version (unfortunately, I've installed Beta 1 on my "work" station which I rarely do). The only issue I have encountered is that the uninstall process screwed up my Microsoft Outlook 2003, probably because of the Visual Studio Tools for Office Runtime. I ended up fixing my Microsoft Office 2003 installation. I'll wait until I finish downloading the bits then off I go to install Visual Studio 2008 (I still have to be content with SQL Server 2008 CTP for the mean time)

Change the Local Administrator password on all your domain computers

Imagine this - 200 servers and 1,500 workstations. How would you change the local Administrator password (or any other account which you use to administer the local machine)? Being the lazy guy as I always have been, I don't want to do things which would be repetitive so I wrote a script. This uses a list of servers and workstations stored in a text file named computerList.txt (you can use either hostnames or IP addresses although I prefer IP) and generates a CSV file which I can use as a report.

Dim loopCount, directory, objFSO,objFile,objFSO2,objFile2

'Gets the directory where our script is running from
directory = CreateObject("Scripting.FileSystemObject").GetParentFolderName(Wscript.ScriptFullName)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(directory & "\computerList.txt", 1)

'===LOG of servers with successful PING
strFilePath = directory & "\serversPING.csv"
Set objFSO2 = CreateObject("Scripting.FileSystemObject")
' Open the file for write access.
On Error Resume Next
Set objFile2 = objFSO2.OpenTextFile(strFilePath, 2, True, 0)
If (Err.Number <> 0) Then
On Error GoTo 0
Wscript.Echo "File " & strFilePath & " cannot be opened"
Set objFSO2 = Nothing
End If
On Error GoTo 0

'Write HEADER
objFile2.WriteLine "SERVER,REACHABLE,PASSWORD CHANGED"

Do Until objFile.AtEndOfStream
strComputer = objFile.ReadLine

If Reachable(strComputer) Then
strReachable = "REACHABLE"
strPasswordChanged = "SUCCESSFUL"
Call SetPassword(strComputer)
Else
strReachable = "UNREACHABLE"
strPasswordChanged = "FAILURE"
End If

objFile2.WriteLine strComputer & "," & strReachable & "," & strPasswordChanged

Loop

objFile.Close
Set objFSO =NOTHING
Set objFile = NOTHING

objFile2.Close
Set objFSO2 =NOTHING
Set objFile2 = NOTHING

MSGBOX "Finished"

'===============================
Function Reachable(strComputer)
' On Error Resume Next

Dim wmiQuery, objWMIService, objPing, objStatus

wmiQuery = "Select * From Win32_PingStatus Where Address = '" & strComputer & "'"

Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
Set objPing = objWMIService.ExecQuery(wmiQuery)

For Each objStatus in objPing
If IsNull(objStatus.StatusCode) Or objStatus.Statuscode<>0 Then
Reachable = False 'if computer is unreacable, return false
Else
Reachable = True 'if computer is reachable, return true
End If
Next
End Function

'===================================
Function SetPassword(strComputer)
strComputer = strComputer
Set objUser = GetObject("WinNT://" & strComputer & "/Administrator, user")
objUser.SetPassword "T3$tP@$$w0rd"
objUser.SetInfo

End Function

Imagine how much time can be saved if you needed to do this ever 45 days. I'll work on something which reads Active Directory for the list of all computers and servers joined in the domain so one does not need to create the computerList.txt file