Backup Script (2.0)

So, as promised, I am posting my new backupscript.  I've completely reworked it.  You know call it with a specific database, backup type (log, full, or differential), and a backup path.

Here's the basic logic:

If we try to backup tempdb, bail (You can't back up tempdb)

If you try to backup master with differential or log, bail

If you try to take a log backup of a non full recovery db, bail
  (I dont have any bulk logged DBs, but I probably should change this)


Get the DB Guid (This was for databases that I restored with the same name, but were different databases altogether.  I using the Database Name to query the history.  the GUID is much more robust.

Force the BackupPathRoot to have a trailing \  - So the path works properly.

Get the last full backup information

If there was never a full backup, switch the backup type to full

If the last full is not in the backup path root, or is missing, switch to full (I'm thinking about changing this to only happen if we're taking a differential)

If we're taking a log backup, Check the log chain (just need to check from the last full or the last differential)

In the backup path root, make a subfolder for the database if it doesn't exist.

Backup the database with the following format:  %BackupPathRoot%\%ServerName%\%DatabaseName%\%DatabaseName% YYYYMMDD HHMMSS %BackupType%.bak

so, there ya go...

here's the link : BackupScript

Here's the source:


USE AdministrationIF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='BackupDatabase')
  DROP PROC BackupDatabase
GOCREATE PROC BackupDatabase
  @database SYSNAME,
  @backuptype VARCHAR(20),
  @backupPathRoot VARCHAR(255)  WITH ENCRYPTION
AS/*
--Test Data
declare
  @database sysname,
  @backuptype varchar(20),
  @backupPathRoot varchar(255)

set @database = 'robtest'
set @backuptype = 'log'
set @backupPathRoot='C:\Backup'
--*/SET NOCOUNT ON
DECLARE
  @databaseGuid UNIQUEIDENTIFIER,
  @debugmessages INT,
  @DifferentialBackupLocation NVARCHAR(260),
  @DifferentialBackupSetId INT,
  @FileExists INT,
  @filename VARCHAR(255),
  @folder VARCHAR(255),
  @FullBackupLocation NVARCHAR(260),
  @FullBackupSetId INT,
  @now DATETIME,
  @LogMessage VARCHAR(MAX),
  @BackupLogID INT
  --select @BackupLogID=MAX(id) from BackupLog -- Just for TestingSET @debugmessages = 0-- Dont Back up TempDBIF (@database = 'TempDB') RETURN-- Only Full backups for MasterIF (@database = 'Master' AND @backuptype <> 'Full') RETURN-- Don't take Log backups when the database isn't in full recoveryIF (@backuptype = 'Log' AND EXISTS (SELECT 1 FROM sys.databases WHERE recovery_model_desc <> 'FULL' AND name=@database))
  RETURN-- Get the Database GuidSELECT @databaseGuid = database_guid FROM sys.database_recovery_status WHERE database_id = DB_ID(@database)-- Make sure the @backupPathRoot has a trailing \IF (@backupPathRoot NOT LIKE '%\') SET @backupPathRoot = @backupPathRoot + '\' -- Get the last full backup location and idSELECT @FullBackupLocation=physical_device_name, @FullBackupSetId=backup_set_idFROM msdb.dbo.backupmediafamily fJOIN msdb.dbo.backupset s ON f.media_set_id=s.media_set_idWHERE backup_set_id=(
  SELECT MAX(msdb.dbo.backupset.backup_set_id)
  FROM msdb.dbo.backupset
  WHERE msdb.dbo.backupset.TYPE = 'D'
    AND database_name=@database
    AND database_guid=@databaseGuid )
IF (@FullBackupLocation IS NULL) BEGIN SET @backuptype='Full'  EXEC BackupLogEntry 'BackupDatabase', @database, 'No Full backup exists' END
ELSE IF ( @FullBackupLocation NOT LIKE @backupPathRoot + '%') BEGIN SET @backuptype='Full' EXEC BackupLogEntry 'BackupDatabase', @database, 'Full backup not in the correct folder' END
ELSE BEGIN
  EXEC MASTER..xp_fileexist @FullBackupLocation, @FileExists output
  IF (@FileExists = 0) BEGIN
    SET @backuptype='Full'
    SET @LogMessage = 'Full Backup Location (' + @FullBackupLocation + ') missing from the folder'
    EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage
  END ELSE BEGIN
    IF (@debugmessages=1) EXEC BackupLogEntry 'BackupDatabase', @database, 'Full backup exists'
  END
END
-- Check to make sure the files are in the correct locationIF (@backuptype = 'Log') BEGIN

  --Get differential backup location
  SELECT @DifferentialBackupLocation=physical_device_name, @DifferentialBackupSetId=backup_set_id
  FROM msdb.dbo.backupmediafamily f
  JOIN msdb.dbo.backupset s ON f.media_set_id=s.media_set_id
  WHERE backup_set_id=(
    SELECT MAX(msdb.dbo.backupset.backup_set_id)
    FROM msdb.dbo.backupset
    WHERE msdb.dbo.backupset.TYPE = 'I'
      AND backup_set_id > @FullBackupSetId
      AND database_name=@database
      AND database_guid=@databaseGuid
  )

  -- Check differential backup location
  IF (@DifferentialBackupLocation IS NOT NULL) BEGIN
    IF (@debugmessages=1) BEGIN SET @LogMessage = 'Checking Differential Backup Location (' + @DifferentialBackupLocation + ')' EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage END
    IF ( @DifferentialBackupLocation NOT LIKE @backupPathRoot + '%' ) BEGIN
      SET @LogMessage = 'Differential Backup (' + @DifferentialBackupLocation + ') in wrong folder'
      EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage
      SET @DifferentialBackupLocation = NULL
    END
    ELSE BEGIN
       EXEC MASTER..xp_fileexist @DifferentialBackupLocation, @FileExists output
      IF (@FileExists = 0) BEGIN
        SET @LogMessage = 'Differential Backup (' + @DifferentialBackupLocation + ') missing'
        EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage
        SET @DifferentialBackupsetId = NULL
      END ELSE BEGIN
        IF (@debugmessages=1) EXEC BackupLogEntry 'BackupDatabase', @database, 'Differential backup found'
      END
    END
  END

  IF (@debugmessages=1) EXEC BackupLogEntry 'BackupDatabase', @database, 'Checking Log Chain'

  DECLARE cur CURSOR FOR
    SELECT physical_device_name
    FROM msdb.dbo.backupmediafamily f
    JOIN msdb.dbo.backupset s ON f.media_set_id=s.media_set_id
    WHERE backup_set_id > COALESCE(@DifferentialBackupsetId, @fullbackupsetid)
      AND database_name=@database
      AND database_guid=@databaseGuid
      AND TYPE = 'L'

  OPEN cur
  FETCH next FROM cur INTO @filename

  WHILE @backuptype='log' AND @@FETCH_STATUS=0 BEGIN
    IF (@debugmessages=1) BEGIN SET @LogMessage = 'Checking Log Backup Location (' + @filename + ')' EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage END
    IF (@filename NOT LIKE @backupPathRoot + '%') BEGIN
      SET @backuptype = ''
      SET @LogMessage = 'Differential Backup (' + @filename + ') in wrong folder'
      EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage
    END ELSE BEGIN
      EXEC MASTER..xp_fileexist @filename, @FileExists output
      IF (@FileExists = 0) BEGIN
        SET @backuptype = ''
        SET @LogMessage = 'Differential Backup (' + @filename + ') missing'
      EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage
      END
    END
    IF @backuptype = '' BEGIN
      IF @fullbackupsetid IS NULL SET @backuptype='Full'
      ELSE SET @backuptype='Differential'
    END
    FETCH next FROM cur INTO @filename
  END
  CLOSE cur
  DEALLOCATE curEND


SET @now = GETDATE()SET @folder = @backupPathRoot + @@SERVERNAME + '\' + @database + '\'SET @folder = REPLACE(@folder,'/','')EXEC MASTER..xp_create_subdir @folderSET @filename = @folder + @database + ' ' + REPLACE(CONVERT(VARCHAR,@now,121),':','-') + ' ' + @backuptype + '.bak'SET @filename = REPLACE(@filename,'/','')
SET @LogMessage = 'Taking a ' + @BackupType + ' Backup,  Filename: ' + @filenameEXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage
IF @backuptype = 'Log'
  BACKUP LOG @database TO DISK=@filename ELSE IF @backuptype = 'Differential'
  BACKUP DATABASE @database TO DISK=@filename WITH  differentialELSE IF @backuptype = 'Full'
  BACKUP DATABASE @database TO DISK=@filename GO--alter database robtest set recovery full

--BackupDatabase 'RobTest','Differential','C:\Backup'

Backup Script - Needs to be updated

Ack, It's been a while since I've updated my SQL backup script.  I've completely reworked it.  I'm in class this week, but will post it early next week.

Logon Failed in SSMS on Windows 2008 (or Windows 7)

We recently moved our SQL Server from Windows Server 2003 to Windows Server 2008. When I tried to connect through SSMS on the server I got a rather odd error:
.

Now, I'm a SQL Administrator and am able to connect to the server from my local PC (Windows XP). I check the SQL Event log and see the following:
. After some Googling I find an article referencing UAC. It suggests right clicking on SSMS and running as administrator. I do this, accept the annoying message, and yowza, the system works.

Now, this is all well and good, but I really don't want to have to right click and run as administrator each time. Plus, as infrequently as I actually log on to the server to use SSMS, I'm sure I'll forget and end up googling this exact same error again. So, I poked around a little bit and found out that you can force the .exe to always run as administrator

• SSMS Properties
  
• Compatability Tab
  
• Show Settings for all users
  
  Notice how this is now for the ssms.exe file, not for the shortcut anymore
• Check "Run this program as administrator"
  

Now, whenever you run SSMS on the server, you will get the annoying message and it will work. I'm a little surprised that Microsoft didn't include that in the install package for SQL Server 2008, but at least there's a pretty easy fix.

Sending Mail in SQL 2000

When our fleet division gets a new vehicle, they spend a few days (or weeks) prepping it and getting it ready. They assign the vehicle number, hourly rate, and a bunch of other stuff. After they're done, our finance department needs to track this in their inventory. The current fleet maintenance software can generate a report, but it doesn't contain everything that finance needs, so, the current practice is:

• Enter the data into the fleet software
• Re-enter it into an old access database (which has a report that has all the info)
• Print off the report from Access
• Make 3 copies
• Interoffice them to Finance.

I've been tasked with coming up with a better solution. After a few minutes of deliberation, a trigger seemed like a logical solution. The trigger will just fire off an email to all the parties when a vehicle is added. No problemo.

Sending mail in SQL Server is nice and easy. Just use sp_send_dbmail and you're good to go. That is, along as you are using SQL 2005 or better. Back in the dark ages (SQL 2000 and 7.0), however, you didn't have sp_send_dbmail. Instead, you had xp_sendmail.

Now, you ask, what is the problem with xp_sendmail. A bunch, but here's the highlights :

1. It's deprecated. I'm surprised it's still around in 2k8, but I bet it won’t be in the next version
2. You need to run it as a db_owner or a sysadmin. Now, you can grant others permissions to it, but that means mucking with permissions in the master database and that's generally frowned upon.
3. You can only send mail as the profile that's set up. This may not seem like a big deal, but I send out emails from the database and I want the recipients to be able to respond to different people based on the message. That means I'll have to set up a separate mail profile for each respondent.

Of course, the SQL Server development team saw these problems (and probably oodles more) and came up with sp_send_dbmail which addresses all of these

Now, since I’m using SQL 2000, I don’t have that option. After much googling, I was able to find xp_smtp_sendmail. The only issue was that most of the links were broken. So, I had to do a little more sleuthing and eventually found the right page in SQLDev.Net. After following their step by step instructions, I was able to send mail as the appropriate person and cut down about 10-15 hours of redundant work from the process.

Yay me.

Of course, we won’t mention that I forgot that the deleted table in a trigger is aptly named deleted and not updated. D’Oh

Insert Triggers always run

Who'da thunk it.
I have an insert trigger that does some data validation. no big deal. The validation that it's performing is this:

if not exists (
select 1
from <My other Database>
where column =(select columndata from inserted)
) raiserror (whatever)


looks simple enough, but there are times when I will have an insert statement that isn't inserting any rows. Apparently triggers get fired whether or not there is any data actually being inserted. They get fired anytime there is an insert statement. So, I had to modify the code to have the following

if (exists (select 1 from inserted) begin
if not exists (
select 1
from <My other Database>
where column =(select columndata from inserted)
) raiserror (whatever)
end


Happy SQLing.. (;

Transaction Log Backup script

So, Like a good boy, I have my SQL Server databases in full recovery model. Because of this, I also employ an aggressive backup strategy. The databases get a full backup weekly to tape, differential backup daily to tape, and transaction log backup to disk every 15 minutes.
We have a few robust SQL Server boxes each of which house lots of databases. I want the same backup strategy applied to each one. For the tape backups, we're employing HP OpenView Storage DataProtector which has a nice interface for doing just such a task.
There isn't a nice little tool (that I've found) in SQL Server to do this however. So, I crafted my own.

I originally had the transaction log files all going to the same file for a particular database. Since transaction logs are based on the last full or differential backup, this made the restores really complicated. I wanted to make sure that there was only 1 transaction log backup file per differential or full backup. So I baked that into the script as well.

Now, since I'm backing up the transaction logs to disk and I want those stored onto tape, there is a job in our tape backup that will also backup all the Transaction Log Backup Files. Finally, I wanted to delete the transaction log backup file has been archived to tape.

Hopefully all this makes sense. And now... the code:



/*
Transaction Log Maintenance Script.sql
Rob Bittner
August 5, 2009

Script to back up all the transaction logs on a database and warn if there
are file backups instead of tape backups

1) For Each Database:
- If the last backup was not a tape backup email the help desk.
- Otherwise, back up the transaction log.
2) Go through all the existing Transaction Log Backup files and delete those
that have been backed up to tape through the nightly file backup.

Modifications:

November 18, 2009 (RB) : Changed Email to go to the help desk instead of me

*/
declare cur cursor for
select name
from sys.databases
where state_desc='online' and recovery_model_desc='full' and name <> 'model'
open cur
declare @backuppath varchar(255)
declare @name varchar(255)
declare @filename varchar(255)
declare @lastbackupdate datetime
set @backuppath='e:\sqldata\Backup\'

fetch next from cur into @name

while @@fetch_status = 0 begin
print 'Backing up ' + @name

select @lastbackupdate=max(backup_start_date) from msdb.dbo.backupset where type in ('i','d') and database_name=@name
set @filename= @backuppath + @name + ' Transaction Log Backup ' + replace(convert(varchar,@lastbackupdate,120),':','') + '.bak'

if not exists ( -- see if there was no tape backup the last time is was backed up
select 1
from msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
where database_name=@name and backup_start_date=@lastbackupdate
and device_type = 7) begin
if not exists (
select 1 -- See if there already was a backup attempt (to prevent multiple emails)
from msdb..sysjobhistory
where job_id = (select job_id from msdb..sysjobs where name='Transaction Log Maintenance')
and step_id=0 -- job finished
and CONVERT -- date started
( DATETIME, RTRIM(run_date) ) +
( run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10 ) / 216e4
> @lastbackupdate
) begin


declare @body varchar(max)
declare @path varchar(max)
declare @subject varchar(255)

select @path=bmf.physical_device_name
from msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
where database_name=@name and backup_start_date=@lastbackupdate
order by backup_start_date

set @subject='Transaction log backup failed on ' + @@servername

set @body=
'<html><head><style type="text/css">body{font-family: calibri;} h1,h2,h3 { margin:0}</style></head>'+
'<body>'+
'<h1>Error when backing up transaction log</h1>' +
'<h2>Most recent backup on the database is a file backup</h2><hr />' +
'<h3>Server: ' + @@servername + '</h3>' +
'<h3>Last Backup Path: ' + @path + '</h3>' +
'<h3>Last Backup Time: ' + convert(varchar,@lastbackupdate,109) + '</h3>' +
'<p style="font-weight:bold">To preserve the integrity of our database backup process, a full backup of ' + @name + ' must be performed utilizing Dataprotector ASAP!</p>'+
'<h2>Failure to back up this database to tape will cause the transaction log to continue to grow</h2>' +
'<p> - SQL Server Database Mailer</p>' +
'</body>' +
'</html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients='email address here',
@subject = @subject,
@body = @body,
@body_format = 'HTML',
@profile_name='HelpDesk' ;
end -- Only email once if statement
end else begin
backup log @name to disk=@filename
end


fetch next from cur into @name
end

close cur

deallocate cur

declare @deletestatement varchar(255)
set @deletestatement = 'del ' + @backuppath + '* /a-a /q'
print @deletestatement
EXEC master..xp_cmdshell @deletestatement



PDF + SQL Reporting Services

Working with SQL Reporting Services has proved to be much easier than Crystal Reports. It's no big surprise that it integrates into M$'s products much easier. Since we're an exclusively M$ shop, that's good news for me.

What I'm currently working on is to create a mailing labels application for a web based GIS application. Basically, we want to provide a pdf that the user can save, or print with information pulled from a database. So, the GIS app will provide me with the Parcel Numbers, I'm going to take those and chuck them into a sproc and return a PDF.

I'm going to be coding a web application that will take a report name, parameters and then will return a PDF with the results.

I'll be posting more details as I code them.