Friday, May 9, 2014

Restore all SQL Server backups from a directory using Python

Problem:

I had to setup database mirroring for 50 databases on amazon EC2 servers which are in different availability zones. Mirroring needs to be initiated with a full and at least one transaction log restores. Restoring 50 databases manually is a cumbersome task. Some of these databases have multiple data files and the logical names are different. I thought it would be better if I could generate restore scripts for all backups in a backup folder and that is a perfect excuse to try Python at work. I used pyodbc to make connection to database. If you dont have pyodbc module, you might want to install it before trying the script. The script can handle databases with multiple data files. Default target data file/logfile directories can be declared.

import os, sys
import pyodbc

backupdir = 'E:\\SQLData\\backups\\'
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=DBAdmin;UID=sa;PWD=sysadmin')
cursor = conn.cursor()
DataDir = 'D:\SQLData\Data'
LogDir = 'E:\SQLData\Logs'


def iter_islast(row):
    it = iter(row)
    prev = it.next()
    for item in it:
        yield prev, False
        prev = item
    yield prev, True


for root, dirs, files in os.walk(backupdir, topdown = False):
    for name in files:
        TempSQL = ""
        f = os.path.join(root, name)
        print "Restore Script for backupfile " + f + " below: "
        print "==============================================="
        
        cursor.execute("restore headeronly from disk = '%s';" % f)
        
        rows = cursor.fetchall()
        for row in rows:
            TempSQL = "RESTORE DATABASE  '" + row.DatabaseName + "' FROM DISK = '" + f + "' , WITH \n"
        
        cursor.execute("restore filelistonly from disk = '%s';" % f)
        rows2 = cursor.fetchall()
         
        for row, islast in iter_islast(rows2):
            if islast:
                if row.Type == 'L':
                    TempSQL += " MOVE " + "'"+row.LogicalName+"' TO  '" + LogDir + "', NORECOVERY \n"
                else:
                    TempSQL += " MOVE " + "'"+row.LogicalName+"' TO  '" + DataDir + "', NORECOVERY \n"
            else:
                if row.Type == 'L':
                    TempSQL += " MOVE " + "'"+row.LogicalName+"' TO  '" + LogDir + "', \n"
                else:
                    TempSQL += " MOVE " + "'"+row.LogicalName+"' TO  '" + DataDir + "', \n"
                
        print TempSQL

The result will look like this. Just copy the commands and execute!