Singapore User Groups Certification Campaign

If you are in Singapore and are interested to get certified, then, read on. Microsoft Singapore in cooperation with Microsoft Learning has offered this to members of the local Singapore user groups - Windows, Office, Windows SBS, SQL Server, DotNet. If you achieve your MCP or MCTS certification between December 1, 2007 and March 31, 2008, you can get your exam fees reimbursed, courtesy of Microsoft. Visit this site for more details

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)