First post! Woo!
Not as epic as it sounded in my head. So a little bit about me. I’m the Senior SQL DBA for a multi national company based in Dublin, and have been for almost 4 years. Before that, I worked as a Sys Admin/Guerilla DBA for many other companies. Having worked as a Sys admin/DBA, and with Sys Admin/DBA types, I’ve found that the transition is a lot more complex than it first seems. I plan to use this blog to help out beginner/intermediate DBA’s with some of the more random/obscure problems, that crop up more randomly/obscurely than you might first think. I also want to cover off some of the very basics that I’ve found that techies don’t want to sit and learn about, but still need to know about. As one of those impatient techies I figured that clicking “Next” until something happened was the way the world worked, and that has bitten me many times when working with SQL.
This blog is not intended to compete/run alongside the many excellent technical blogs/sites out there such as the geniuses over at SQLSkills or SQLServerCentral. This is purely for the weekend warrior who may need a little dig out from time to time, and also some interesting/odd problems that I come across in the daily grind. The first offering I will bring you is the most important and basic step in the life of a DBA. It is the full backup script. In non DBA speak, that’s Covering Your A$$.
DECLARE @name VARCHAR(50) — database name
DECLARE @path VARCHAR(256) — path for backup files
DECLARE @fileName VARCHAR(256) — filename for backup
DECLARE @fileDate VARCHAR(20) — used for file name
SET @path = ‘C:Backup’
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor