Recover SQL Server database from .sql files
So we had an encrypted database that was killing and killing our entire SQL Server installation. Sucks our data, but we were smart enough to have data structure / stored procedures / functions in Git
The problem is that they are saved as files .sql
.
Is there any way to restore our schema from directories full of these files?
I have looked around and I can find tutorials for recovering from .bak
files or .mdf
. This is far from the lazy path - I just need to find a solution as soon as possible. Any advice or resources / anything at all would be appreciated.
Thanks, Interwebs,
Dylan
source to share
Given the large size of the data structure I was trying to recover, running each file separately was not a practical solution. I'm sure I could write bat fie, but I did it pretty quickly in python:
import os, subprocess
processDir = 'C:\\Database-master\\'
files = os.listdir(processDir)
for f in files:
db = processDir + f
#potentially drop corrupt db and create new ones with f
scripts = os.listdir(db)
for script in scripts:
path = db + '\\' + script
proc = subprocess.Popen('sqlcmd -S 127.0.0.1 -i "' + path +'"', shell=True)
proc.wait()
source to share
If your database was large / complex, the real problem you will run into is not batch execution, but the order in which the scripts are executed.
If you don't have a backup file, this will be a real problem.
If you only have your scripts I would suggest something like this.
- tables
- views
- all the rest...
Just run one request after another until you get an error. When you run into an error, it is most likely due to the fact that you are trying to reference an object that does not already exist. Just save this script for future reference and continue running scripts. Then start over from the beginning and review the scripts that caused the error. Now the objects are probably there. Repeat this as many times as necessary until you have created all your objects.
source to share