Automate backup file maintenance with Python

I automated my disk backups, but I kept having to delete the extra backup files manually so I wouldn't run out of space.  I decided I needed to automate that task as well.

Add the following Python script to a task so it will run automatically.

Python script:
import os
import datetime
import fnmatch

dir_to_search = "[full path to backup directory]"
for dirpath, dirnames, filenames in os.walk(dir_to_search):
   intCountOfTotalBackups = len(fnmatch.filter(filenames, '[Backup name, same as the Macrium XML file]*-00-00.mrimg'))
   print "Total Backups: " + str(intCountOfTotalBackups)
   intCountOfBackupsToDelete = 0
 
   for file in fnmatch.filter(filenames, '[Backup name, same as the Macrium XML file]*-00-00.mrimg'):
      curpath = os.path.join(dirpath, file)
      file_modified = datetime.datetime.fromtimestamp(os.path.getmtime(curpath))
      #keep a weeks worth of backups
      if datetime.datetime.now() - file_modified > datetime.timedelta(hours=168):
          intCountOfBackupsToDelete += 1

   print intCountOfBackupsToDelete
   #always keep at least a few backups, even if they are old.
   if intCountOfBackupsToDelete < (intCountOfTotalBackups - 4):
      print str(intCountOfBackupsToDelete) + ' < ' + str(intCountOfTotalBackups - 4)
      for file in fnmatch.filter(filenames, 'Atom_Nightly*-00-00.mrimg'):
         curpath = os.path.join(dirpath, file)
         file_modified = datetime.datetime.fromtimestamp(os.path.getmtime(curpath))
         if datetime.datetime.now() - file_modified > datetime.timedelta(hours=168):
             os.remove(curpath)
             print "remove " + curpath
#input("Press Enter to continue...")

Restore Database and "Single User" mode

I recently restored a database and it gave an error after restore about indexes and rowguid columns, but the database was there, so I'm going with it.

But it showed up as "databasename (single user)" and I couldn't use it because "there is already a user logged into this database".

so I had to change it, this link was very helpful:
http://remidian.com/mssql/remove-sql-server-database-from-single-user-mode.html

SELECT spid
FROM sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
WHERE d.name = 'DatabaseName';

KILL <spid returned above>

EXEC sp_dboption 'DatabaseName', 'single user', 'FALSE';

Processing QBXML In SQL Server

I've been working with QuickBooks again, and this time I need to parse some QBXML in the database so I can do some data syncronization on a schedule.

SQL Server 2005+ has some nice XML tools that make things a lot easier.  I was having trouble getting things to show up, but eventually found some pretty basic examples.

I'm parsing an Invoice Query response.

Here is what I came up with:

-- variable to hold document reference
DECLARE @docHandle int

-- variable to hold actual XML
DECLARE @xmlDocument nvarchar(max) -- or xml type

-- Fill XML variable
SELECT TOP 1 @xmlDocument = QBXML FROM QBBridge ORDER BY QBBridgeID DESC

-- Use built in Procedure to process the XML into Data
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;

    DECLARE @ParentID int
    DECLARE qbXMLCursor CURSOR FOR
        -- Get All InvoiceRet Objects.
        SELECT ID FROM OPENXML(@docHandle, N'/QBXML/QBXMLMsgsRs') WHERE NodeType = 1 AND LocalName = 'InvoiceRet'

    -- cycle through InvoiceRets and compile Sub Data.
    OPEN qbXMLCursor
    FETCH NEXT FROM qbXMLCursor INTO @ParentID

   
    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- Use Common Table Expression to compile values to names of parent nodes
        -- I named my table columns <ParentName>.<ParentName>.<Name> so I'm sitting pretty for an insert from this.
        WITH InvoiceValues ([LocalName], [Value], [Level], [ID]) AS
        (
            SELECT    LocalName,
                    [text],
                    1 AS [Level],
                    [ID]
            FROM    OPENXML(@docHandle, N'/QBXML/QBXMLMsgsRs')
            WHERE ParentID = @ParentID
            UNION ALL
            SELECT    CASE WHEN SubValues.LocalName = '#text' THEN
                        InvoiceValues.LocalName
                    ELSE
                     InvoiceValues.LocalName + '.' + SubValues.LocalName
                     END As LocalName,
                    [text],
                    InvoiceValues.[Level] + 1 AS [Level],
                    SubValues.[ID]
            FROM    InvoiceValues
                    INNER JOIN OPENXML(@docHandle, N'/QBXML/QBXMLMsgsRs') As SubValues
                    ON InvoiceValues.ID = SubValues.ParentID
        )
        SELECT    [LocalName], [Value]
        FROM    InvoiceValues
        WHERE Value IS NOT NULL
           
        FETCH NEXT FROM qbXMLCursor INTO @ParentID
    END
    CLOSE qbXMLCursor
    DEALLOCATE qbXMLCursor

EXEC sp_xml_removedocument @docHandle

SQL Server FTS Thesaurus tsENU.xml Names

Here is what I am using for my FTS Thesaurus tsENU.xml.  It is mostly names, but I put in some numbers too (though these are useless if they are in your noise file as is default), and the html encoded ampersand '&amp;' with 'and' but I think that is useless too.

<XML ID="Microsoft Search Thesaurus">

    <!--  Commented out

    <thesaurus xmlns="x-schema:tsSchema.xml">
    <diacritics_sensitive>0</diacritics_sensitive>
        <expansion>
            _{Internet Explorer}
            _IE
            _IE5
        </expansion>
        <replacement>
            <pat>NT5</pat>
            <pat>W2K</pat>
            _{Windows 2000}
        </replacement>
        <expansion>
            _run
            _jog
        </expansion>
    </thesaurus>
-->
<!--Original Name Source http://www.tngenweb.org/franklin/frannick.htm-->
    <thesaurus xmlns="x-schema:tsSchema.xml">
        <diacritics_sensitive>0</diacritics_sensitive>
        <!--Special Text-->
        <expansion>
            _&amp;
            _and
        </expansion>
        <expansion>
            ₀
            _Zero
        </expansion>
        <expansion>
            ₁
            _One
        </expansion>
        <expansion>
            ₂
            _Two
        </expansion>
        <expansion>
            ₃
            _Three
        </expansion>
        <expansion>
            ₄
            _Four
        </expansion>
        <expansion>
            ₅
            _Five
        </expansion>
        <expansion>
            ₆
            _Six
        </expansion>
        <expansion>
            ₇
            _Seven
        </expansion>
        <expansion>
            ₈
            _Eight
        </expansion>
        <expansion>
            ₉
            _Nine
        </expansion>
        <expansion>
            ₁₀
            _Ten
        </expansion>
        <!--End Special Text-->
        <!--Names-->
        <expansion>
            _Aaron
            _Ron
        </expansion>
        <expansion>
            _Abigail
            _Abie
            _Aby
            _Abbie
            _abby
            _Gail
            _Nabby
        </expansion>
        <expansion>
            _Abraham
            _Abe
            _Abram
            _Bram
        </expansion>
        <expansion>
            _Adelaide
            _Ada
            _Addie
            _Adela
            _Adeline
            _Della
            _Heide
            _Addy
        </expansion>
        <expansion>
            _Adolphus
            _Ado
            _Adolph
            _Dolph
        </expansion>
        <expansion>
            _Adrienne
            _Adrian
        </expansion>
        <expansion>
            _Agatha
            _Ag
            _Aggie
            _Agnes
            _Aggy
        </expansion>
        <expansion>
            _Alan
            _Alanson
        </expansion>
        <expansion>
            _Albert
            _Al
            _Bert
            _Bird
            _Alberta
            _Abertina
            _Albertine
            _Bertie
            _Adelbert
            _Del
            _Delbert
            _Bertram
            _Alfred
            _Alf
            _Alfie
            _Alfreda
            _Alfy
            _Frieda           
        </expansion>
        <expansion>
            _Alexander
            _Alec
            _Alex
            _Lex
            _Alexis
            _Alexandra
            _Lexa
            _Xan
            _Alexandria
            _Lexi
            _Alyx
            _Alix
        </expansion>
        <expansion>
            _Alice
            _Alcy
            _Alicia
        </expansion>
        <expansion>
            _Alison
            _Ali
            _Ally
            _Allie
            _Aly
        </expansion>
        <expansion>
            _Amanda
            _Manda
            _Mandy
            _Mandie
            _Mandi
        </expansion>
        <expansion>
            _Andrew
            _Andy
            _Ansey
            _Drew
            _Andrea
            _Andi
        </expansion>
        <expansion>
            _Angela
            _Angel
            _Angelica
            _Angelina
            _Angeline
            _Angie
        </expansion>
        <expansion>
            _Ann
            _Anna
            _Anne
            _Annie
            _Annette
        </expansion>
        <expansion>
            _Anthony
            _Tony
            _Antoinette
            _Antonia
            _Toni
            _Tonie
        </expansion>
        <expansion>
            _Barbara
            _Babs
            _Barb
            _Barbie
            _Barby
        </expansion>
        <expansion>
            _Barnard
            _Bernard
            _Barney
            _Bernie
        </expansion>
        <expansion>
            _Bartholomew
            _Bart
            _Bartel
            _Barth
            _Bat
            _Barticus
        </expansion>
        <expansion>
            _Benjamin
            _Ben
            _Benjie
            _Bennie
            _Benny
        </expansion>
        <expansion>
            _Beverly
            _Bev
        </expansion>
        <expansion>
            _Boe
            _Bo
        </expansion>
        <expansion>
            _Brian
            _Bryan
            _Bryant
        </expansion>
        <expansion>
            _Brittany
            _Britt
            _Brittney
        </expansion>
        <expansion>
            _Cameron
            _Cam
        </expansion>
        <expansion>
            _Candace
            _Candy
        </expansion>
        <expansion>
            _Carl
            _Karl
        </expansion>
        <expansion>
            _Charles
            _Charlie
            _Charly
            _Chaz
            _Chuck
        </expansion>
        <expansion>
            _Catherine
            _Cathy
            _Katharine
            _Katie
            _Kathleen
            _Kittie
            _Cathleen
            _Kat
            _Kate
            _Katy
            _Kitsey
            _Kitty
        </expansion>
        <expansion>
            _Cecilia
            _Celia
            _Cecil
        </expansion>
        <expansion>
            _Celinda
            _Linda
            _Lindy
            _Lynn
        </expansion>
        <expansion>
            _Christine
            _Christiana
            _Crissy
            _Christy
            _Tina
            _Kristine
        </expansion>
        <expansion>
            _Clara
            _Claire
            _Clarice
            _Clarissa
        </expansion>
        <expansion>
            _Cory
            _Corey
            _Cori
        </expansion>
        <expansion>
            _Crystal
            _Chrisetc
            _Christal
            _Chrystal
            _Cristal
        </expansion>
        <expansion>
            _Cynthia
            _Cindy
        </expansion>
        <expansion>
            _Daniel
            _Dan
            _Danny
        </expansion>
        <expansion>
            _Danielle
            _Dani
        </expansion>
        <expansion>
            _David
            _Davy
            _Davey
            _Dave
        </expansion>
        <expansion>
            _Deborah
            _Deb
            _Debi
            _Debbie
            _Debby
        </expansion>
        <expansion>
            _Denise
            _Dennis
        </expansion>
        <expansion>
            _Diane
            _Diana
            _Di
        </expansion>
        <expansion>
            _Dorothy
            _Dee
            _Dolly
            _Dorothea
            _Dot
            _Dotha
            _Dotty
        </expansion>
        <expansion>
            _Douglas
            _Doug
        </expansion>
        <expansion>
            _Edward
            _Ed
            _Eddie
            _Eddy
        </expansion>
        <expansion>
            _Elias
            _Eli
            _Elijah
            _Elisha
            _Ellis
        </expansion>
        <expansion>
            _Elisa
            _Lisa
        </expansion>
        <expansion>
            _Eliza
            _Elizabeth
            _Beth
            _Liz
            _Liza
            _Lizabeth
            _Lizzie
        </expansion>
        <expansion>
            _Emanuel
            _Manny
            _Manuel
        </expansion>
        <expansion>
            _Emeline
            _Emmer
            _Emily
            _Emmy
            _Emilie
        </expansion>
        <expansion>
            _Ernest
            _Earnest
            _Ernie
            _Ernestine
        </expansion>
        <expansion>
            _Estella
            _Stella
        </expansion>
        <expansion>
            _Eugene
            _Gene
            _Eugenia
            _Genie
        </expansion>
        <expansion>
            _Evelyn
            _Ev
            _Eve
            _Evelina
        </expansion>
        <expansion>
            _Ezekiel
            _Ez
        </expansion>
        <expansion>
            _Frances
            _Fan
            _Fanny
            _Fran
            _Franie
            _France
            _Franz
            _Francis
        </expansion>
        <expansion>
            _Franklin
            _Frank
            _Frankie
            _Franky
        </expansion>
        <expansion>
            _Frederick
            _Fred
            _Fredric
            _Freddie
        </expansion>
        <expansion>
            _Gabriel
            _Gabe
            _Gabby
            _Garbrielle
            _Gabbie
        </expansion>
        <expansion>
            _Geoffrey
            _Jeff
            _Geoff
            _Jeffrey
        </expansion>
        <expansion>
            _George
            _Jorge
            _Georgia
            _Georgiana
        </expansion>
        <expansion>
            _Gerald
            _Jerry
            _Geraldine
            _Gerry
            _Gerri
            _Gerrie
        </expansion>
        <expansion>
            _Gertrude
            _Gertie
            _Trudy
        </expansion>
        <expansion>
            _Gilbert
            _Gil
            _Gill
        </expansion>
        <expansion>
            _Gloria
            _Glory
        </expansion>
        <expansion>
            _Gregory
            _Greg
        </expansion>
        <expansion>
            _Gwendolyn
            _Gwenny
            _Gwen
            _Gwennie
        </expansion>
        <expansion>
            _Hannah
            _Hanna
            _Hanne
        </expansion>
        <expansion>
            _Harold
            _Hal
            _Harry
        </expansion>
        <expansion>
            _Henry
            _Hank
        </expansion>
        <expansion>
            _Herbert
            _Herb
        </expansion>
        <expansion>
            _Irvin
            _Irving
        </expansion>
        <expansion>
            _Irwin
            _Erwin
        </expansion>
        <expansion>
            _Isaac
            _Ike
            _Zeke
        </expansion>
        <expansion>
            _Isabelle
            _Isabella
            _Bella
            _Belle
        </expansion>
        <expansion>
            _Jacob
            _Jake
        </expansion>
        <expansion>
            _Jacqueline
            _Jackie
        </expansion>
        <expansion>
            _James
            _Jamie
            _Jim
        </expansion>
        <expansion>
            _Jane
            _Janet
            _Janette
            _Janie
            _Jan
        </expansion>
        <expansion>
            _Jeanette
            _Jeanne
        </expansion>
        <expansion>
            _Jennifer
            _Jen
            _Jenny
        </expansion>
        <expansion>
            _Jeremiah
            _Jeremy
        </expansion>
        <expansion>
            _Jessica
            _Jess
            _Jesse
            _Jessie
        </expansion>
        <expansion>
            _Joanna
            _Joan
            _Jody
            _Johanna
        </expansion>
        <expansion>
            _Jonathan
            _Johnathan
            _John
            _Jack
            _Jackson
            _Jehu
            _Johnny
            _Jon
        </expansion>
        <expansion>
            _Joseph
            _Joe
            _Joey
            _Josephine
            _Josepha
            _Jo
            _Josey
            _Josie
        </expansion>
        <expansion>
            _Joshua
            _Josh
        </expansion>
        <expansion>
            _Joyce
            _Joy
        </expansion>
        <expansion>
            _Judith
            _Juda
            _Jude
            _Judi
            _Judie
            _Judy
        </expansion>
        <expansion>
            _Julia
            _Julie
            _Juliet
            _Julian
            _Julius
            _Jule
        </expansion>
        <expansion>
            _Karen
            _Karon
            _Karin
        </expansion>
        <expansion>
            _Kristopher
            _Christopher
            _Chris
            _Topher
            _Toph
        </expansion>
        <expansion>
            _Lawrence
            _Larry
            _Laurence
            _Lawrie
            _Lon
            _Lorry
            _Laura
        </expansion>
        <expansion>
            _Leonard
            _Leo
            _Leon
            _Leona
        </expansion>
        <expansion>
            _LeRoy
            _Lee
            _Roy
        </expansion>
        <expansion>
            _Leslie
            _Leslea
        </expansion>
        <expansion>
            _Louis
            _Lewis
            _Lou
            _Louie
            _Louise
        </expansion>
        <expansion>
            _Marcus
            _Mark
            _Marc
        </expansion>
        <expansion>
            _Mariah
            _Maria
        </expansion>
        <expansion>
            _Mary
            _Mari
            _Marian
            _Marianna
            _Marion
        </expansion>
        <expansion>
            _Marjorie
            _Madge
            _Marge
            _Margie
        </expansion>
        <expansion>
            _Marcy
            _Marcie
        </expansion>
        <expansion>
            _Marvin
            _Marv
            _Merv
            _Mervyn
        </expansion>
        <expansion>
            _Matthew
            _Matt
            _Matthias
        </expansion>
        <expansion>
            _Maxine
            _Max
            _Maxwell
        </expansion>
        <expansion>
            _May
            _Mae
        </expansion>
        <expansion>
            _Michael
            _Mickey
            _Mike
        </expansion>
        <expansion>
            _Mitchell
            _Mitch
        </expansion>
        <expansion>
            _Montgomery
            _Monty
        </expansion>
        <expansion>
            _Mortimer
            _Mort
        </expansion>
        <expansion>
            _Natalie
            _Nat
        </expansion>
        <expansion>
            _Nicholas
            _Nick
            _Nicky
        </expansion>
        <expansion>
            _Nicole
            _Nikki
        </expansion>
        <expansion>
            _Patricia
            _Pattie
            _Patty
            _Patrick
            _Pat
        </expansion>
        <expansion>
            _Philip
            _Filip
            _Phil
            _Philippa
        </expansion>
        <expansion>
            _Rae
            _Ray
        </expansion>
        <expansion>
            _Randolph
            _Randall
            _Randy
        </expansion>
        <expansion>
            _Rebecca
            _Becca
            _Beckie
            _Becky
            _Reba
        </expansion>
        <expansion>
            _Reginald
            _Reg
            _Reggie
        </expansion>
        <expansion>
            _Richard
            _Dick
            _Rich
            _Richie
            _Rick
            _Ritchie
        </expansion>
        <expansion>
            _Robert
            _Dob
            _Dobbin
            _Bob
            _Bobby
            _Rob
            _Robin
            _Rupert
            _Roberta
            _Robbie
            _Robby
            _Bobbie
        </expansion>
        <expansion>
            _Roxane
            _Roxy
            _Roxie
        </expansion>
        <expansion>
            _Ryan
            _Ry
            _Rian
        </expansion>
        <expansion>
            _Samantha
            _Mantha
            _Sammy
            _Samson
            _Sampson
            _Sam
            _Samuel
        </expansion>
        <expansion>
            _Sandra
            _Sandy
        </expansion>
        <expansion>
            _Sarah
            _Cera
            _Sara
        </expansion>
        <expansion>
            _Scott
            _Sceeter
            _Squat
            _Scottie
            _Scotty
        </expansion>
        <expansion>
            _Simeon
            _Sim
            _Simon
        </expansion>
        <expansion>
            _Stan
            _Stanley
        </expansion>
        <expansion>
            _Stephanie
            _Steph
            _Stephie
        </expansion>
        <expansion>
            _Stephen
            _Steve
            _Steven
            _Stevie
        </expansion>
        <expansion>
            _Teresa
            _Terry
            _Tessie
        </expansion>
        <expansion>
            _Theodore
            _Ted
            _Theodora
            _Theo
            _Theophilus
        </expansion>
        <expansion>
            _Thomas
            _Thom
            _Tom
        </expansion>
        <expansion>
            _Tiffany
            _Tiff
            _Tiffy
        </expansion>
        <expansion>
            _Timothy
            _Tim
            _Timmy
        </expansion>
        <expansion>
            _Ty
            _Tie
            _Tye
        </expansion>
        <expansion>
            _Victor
            _Vic
            _Vick
        </expansion>
        <expansion>
            _Victoria
            _Toria
            _Torrie
            _Tory
            _Vicki
        </expansion>
        <expansion>
            _Vincent
            _Vin
            _Vince
            _Vinnie
            _Vinny
        </expansion>
        <expansion>
            _Wallace
            _Wallie
            _Wally
        </expansion>
        <expansion>
            _Walter
            _Walt
        </expansion>
        <expansion>
            _Wilhelmina
            _Helmie
            _Mina
            _Minnie
            _Willie
            _Wilma
            _Wilhelm
            _William
            _Bill
            _Bud
            _Will
            _Willis
            _Willy
            _Billie
        </expansion>
        <expansion>
            _Wilfred
            _Winifred
            _Winnie
            _Winnet
        </expansion>
        <expansion>
            _Woodrow
            _Woody
        </expansion>
        <expansion>
            _Zachariah
            _Zach
            _Zacharias
            _Zachary
            _Zak
            _Zac
        </expansion>
        <!--End Names-->
    </thesaurus>
</XML>

SQL Server 2005 Full Text Search FTS Thesaurus tsENU.xml issues

Been spending time reading up on SQL Server 2005 FTS Thesaurus files here is what I've learned:

1. You have to make your own Thesaurus file, can't seem to find one with common expansions.
2. When you have finished your new file, you need to restart SQL 2005 (if it's 2008 there's a stored procedure to reload it "EXEC sys.sp_fulltext_load_thesaurus_file 1033;" 1033 for the language in this case ENU (US English).
3. Make sure your search terms are not surrounded by double quotes because you thought they were necessary from a previous hacked together project using SQL FTS.
4. Run tests and query comparisons to make sure it's working the way you think it should.
5. Wonder about all the other predicates you can add to your search terms because you can't find a definitive list. 
   

   FREETEXT(<FieldNames>,formsof(thesaurus,<searchterms>))
   FREETEXT(<FieldNames>,'"<SearchTerm_1>" NEAR "<SearchTerm_2>"')
   FREETEXT(<FieldNames>,'isabout("<SearchTerm_1>" weight(<DecimalWeightValue_1>), <SearchTerm_2> weight(DecimalWeightValue_2>))')
   FREETEXT(<FieldNames>, '<SearchTerm>', LANGUAGE <LanguageCode>)    [for a list of language codes "select [name], alias, lcid from master.sys.syslanguages", use the lcid field]

6. If you're having problems getting the Thesaurus to appear to work, try using these to look at language settings
   

   exec sp_configure 'default language'
   SELECT @@language, @@langid
   select [name], alias, lcid from master.sys.syslanguages

7. You may end up with lots of web pages open, maybe some of them are actually helpful:
   

   http://www.mssqltips.com/tip.asp?tip=1491
   http://msdn.microsoft.com/en-us/library/ms345187.aspx
   http://msdn.microsoft.com/en-us/library/ms345186.aspx
   http://www.mssqltips.com/tip.asp?tip=1353
   http://www.mssqltips.com/tip.asp?tip=1342
   http://www.mssqltips.com/tip.asp?tip=1332
   http://www.ureader.com/msg/1147186.aspx
   http://msdn.microsoft.com/en-us/library/cc280598.aspx
   http://msdn.microsoft.com/en-us/library/ms176076.aspx
   http://blogs.geekdojo.net/richard/archive/2006/09/01/13805.aspx  (this one is funny as well, which can be nice after not finding anything that seems useful)
   http://www.eggheadcafe.com/community/aspnet/13/10024815/need-help-on-sql-server-2.aspx  (this one seems useful until you realize that there are no replies with answers)
   http://arcanecode.com/2008/05/28/creating-custom-thesaurus-entries-in-sql-server-2005-and-2008-full-text-search/
   http://arcanecode.com/2008/04/29/sql-server-full-text-search-the-fulltextcatalogproperty-function/
   http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features/

8. You finally get a sample working, then you realize you need to build a full list of all the Expansions and Substitutions you want.