Update SQL Jobs to Databases on Netapp Drive

I had to create job to add non naming conversational database names to my existing NetApp snap manager for SQL job.

If a database was not in the LUN I email the users and alerts them to move it.

In this case the databases sit in the R: Drive for .mdf and S: Drive for .ldf

Create SQL Agent Job

then insert Step 1 – Transact-SQL script

USE msdb

GO

SET NOCOUNT ON
 DECLARE @FileName varchar(255)
 DECLARE @File_Exists int
 DECLARE @DBCount int
 DECLARE @DBCounter int
 DECLARE @DBName varchar(50)
 SELECT @DBCount = count(*) from master.dbo.sysdatabases sd
 INNER JOIN sys.master_files mf
 ON mf.database_id = sd.dbid
 WHERE sd.name NOT IN ('master','msdb','model','tempdb')
 AND physical_name NOT LIKE '%.ldf%'
 AND physical_name LIKE '%R:%'
 AND CONVERT(sysname,databasepropertyex(sd.name,'status')) ='ONLINE'
 AND CONVERT(sysname,databasepropertyex(sd.name,'updateability')) ='READ_WRITE'
 --PRINT @DBCount
 BEGIN
 SET @DBCounter = 1
 DECLARE @BackupCmd varchar(500)
 SET @BackupCmd = '"C:\Program Files\NetApp\SnapManager for SQL Server\SmsqlJobLauncher.exe" new-backup -svr ''' + @@SERVERNAME + ''' -d ''' + @@SERVERNAME + ''', ''' + cast(@DBCount as varchar(3)) + ''','
 DECLARE strategy_cursor CURSOR FOR
 SELECT sd.name from master.dbo.sysdatabases sd
 INNER JOIN sys.master_files mf
 ON mf.database_id = sd.dbid
 WHERE sd.name NOT IN ('master','msdb','model','tempdb')
 AND physical_name NOT LIKE '%.ldf%'
 AND physical_name LIKE '%R:%'
 AND CONVERT(sysname,databasepropertyex(sd.name,'status')) ='ONLINE'
 AND CONVERT(sysname,databasepropertyex(sd.name,'updateability')) ='READ_WRITE'
 ORDER BY name
 OPEN strategy_cursor
 FETCH NEXT FROM strategy_cursor INTO @DBName
 WHILE @@FETCH_STATUS = 0
 BEGIN
 IF @DBCounter < @DBCount SET @BackupCmd = @BackupCmd + '''' + @DBName + ''','
 IF @DBCounter = @DBCount SET @BackupCmd = @BackupCmd + '''' + @DBName + ''' -ver –verInst ''DB110'' -mp –mpdir ''C:\Program Files\NetApp\SnapManager for SQL Server\SnapMgrMountPoint'' -RetainBackups 5 -lb -bksif -RetainSnapofSnapInfo 8 -trlog -noutm -gen –mgmt daily'
 SET @DBCounter = @DBCounter + 1
 --PRINT @BackupCmd
 --PRINT @DBCounter
 FETCH NEXT FROM strategy_cursor INTO @DBName
 END
 CLOSE strategy_cursor
 DEALLOCATE strategy_cursor
 END
 --PRINT @BackupCmd
 --DECLARE @rc int
 --EXEC @rc = master.dbo.xp_cmdshell @BackupCmd

EXEC dbo.sp_update_jobstep
 @job_name = N'BACKUP 22:20 DBs',
 @step_id = 1,
 @command =@BackupCmd
 GO

-- Start T-SQL
 USE msdb
 EXEC sp_send_dbmail
 @profile_name = 'DB.sqlserver',
 @recipients = 'jason@test.com',
 @copy_recipients = 'jason@test.com',
 @subject = 'Databases not in NetApp Nightly Snaps',
 @body = 'The Databases below need to be move in R: & S: Drives; if you want them snapped.',
 @execute_query_database = 'msdb',
 @query = 'SELECT sd.name from master.dbo.sysdatabases sd
 INNER JOIN sys.master_files mf
 ON mf.database_id = sd.dbid
 WHERE sd.name NOT IN (''master'',''msdb'',''model'',''tempdb'')
 AND physical_name NOT LIKE ''%.ldf%''
 AND physical_name NOT LIKE ''%R:%'''
 -- End T-SQL --
 GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s