List all database files in a SQL Server instance with Python and pyodbc

For SQL Server management from Linux, I’ve used pymssql on Linux before, but realized lately that it is hard to configure with the heterogeneous environment I am in. So I am giving pyodbc a try. The install on Windows is simple. Listed below is some code that I put together quickly for some testing. It lists all files used by a SQL Server instance. I will test pyodbc on Linux against a SQL Server database and will write my findings in a few days.

How to reproduce:
1. Install Python 2.7 on Windows. I am not bothering with 64-bit Python at the moment;
2. Download and install pyodbc 2.7 for Windows;
3. Add c:\Python27 to your PATH variable

""" The code below lists all files used by a SQL Server instance. Adjust instance and login credentials as necessary. ROWS means data file, LOG means log file """
import pyodbc
cn = pyodbc.connect('DRIVER={SQL Server};SERVER=MyInstance;DATABASE=master;Trusted_Connection=yes')
cursor = cn.cursor()
cursor.execute("select name from sys.databases")
rows = cursor.fetchall()
for row in rows:
	sql = ("select type_desc, physical_name from %s.sys.database_files" % row.name)
	cursor.execute(sql)
	l1rows = cursor.fetchall()
	print "Files for " + row.name + " :"
	for l1row in l1rows:
		print l1row.type_desc + " " + l1row.physical_name

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.