SQL 2016 yippie… Now too test the new stuff that we want to use.

One of the issues I had with SQL 2014 was that ColumnStore indexes could not be read on the HA read-only secondary we setup. So when I installed 2016 CTP3.1 for testing our Data Warehouse I set it up as a HA cluster. Well if you didn’t know, for this to work you need the the databases to be in full recovery mode. Well this is test, we don’t have that much space on our backup devices. Again this is test, so who cares about the backups anyway?

Jeff?

Yes?

What about the transaction logs that will fill up and never clear because you are not doing backup?

I have a plan……..

A trick I learned, along time ago for SQL 2000, and shrinking log files in an emergency was to do the backup with truncate_only.  Of course that would destroy the log file chain, and restores you needed the trans logs were now useless. But as growing as a DBA, I had to do things I am ashamed of. Not really, when production is down and you can do this then backup immediately it was not a bad solution.

Well, it was smart of Microsoft to take that away from us. I’m sure many a person didn’t know that would happen to the log chain. I am using something like that for this. Again this is test and I don’t care about point in time restores. I most likely on a monthly basis will restore the DBs from production over them anyway.

So here we go…..

What this script does is look at all the database that are in full recovery mode, skips TempDB and will back them up to a null device.

Yes I use a lot of cursors.

Note Do Not use on a production system!*

 

DECLARE @Statment NVARCHAR(4000)
DECLARE @DBName NVARCHAR(255)

DECLARE cBackup CURSOR FAST_FORWARD READ_ONLY FOR
SELECT name FROM master.sys.databases WHERE name not in ('tempdb') and  recovery_model = 1

OPEN cBackup
FETCH NEXT FROM cBackup INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN
  -- But jeff Why is this backing up to Nul? 
  -- Because we don't care about backups of the logs 
  --   but still need the db to be in full recovery mode and don't want them to grow....

      SET @Statment =  'BACKUP LOG [' + @DBName + '] TO  DISK = ''NUL:'' WITH STATS = 10'
      EXEC sp_executeSQL @Statment

      FETCH NEXT FROM cBackup INTO @DBName
END

CLOSE cBackup
DEALLOCATE cBackup

* If you do use this on a prod system and now can't restore your logs... Don't blame me I warned you.

Leave a Reply

Your email address will not be published. Required fields are marked *