Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

So we had an encrypted database that puked and killed our whole SQL Server setup. Sucks about our data, but we were smart enough to have our data structures / stored procedures / functions in Git

The problem is they're saved as .sql files.

Is there anyway we can batch restore our schema from directories full of these files?

I've looked around and I can only find tutorials for restoring from .bak files or .mdf's. This isn't the lazy man's way out- I just need to find a solution ASAP. Any advice or resources/ anything at all would be greatly appreciated.

Thanks Interwebs,

Dylan

share|improve this question
1  
You can surely execute a SQL script file from sqlcmd, but the real question is how did you script this out? Sequence does matter here (think foreign key relationships), as well as if the scripts contain all DDL. If you did the "Generate scripts..." from SSMS you should be fine. But the true backup and restore should have been more traditional with native full/diff/tran log backups. – Thomas Stringer Feb 17 at 22:00
We did do the generate. If nothing else this has been a learning experience. Working on solution now. Will post when done. – Dylan Madisetti Feb 17 at 22:12

2 Answers

up vote 3 down vote accepted

So you just need to execute the collection of schema scripts to recreate your database? You could execute them one by one or create a batch file and use SQLCMD to execute all script files in directory

for /r "%ScriptPath%" %%X in (*.sql) do ( 
   sqlcmd -S%ServerName% -d%DBName% -b -i "%%X"
)
share|improve this answer
I'm sure I could have written a bat fie, but I got it done pretty quickly in python: tinyurl.com/a5q32uf – Dylan Madisetti Feb 18 at 0:38
up vote 0 down vote

Considering the large size of the data structure I was trying restore, running each files individually was not a practical solution. I'm sure I could have written a bat fie, but I got it done 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()
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.