I want to restore a MS SQL database from a .dat file.
I did it sucessfully by calling the following command/statement on Windows PowerShell or by saving it in a .bat file and running the file itself:
sqlcmd -S POLIVEIRA-PC\MSSQLSERVER2008 -Q "RESTORE DATABASE [Odemira_2012-07-01_021501] FROM DISK=N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER2008\MSSQL\Backup\ODEMIRA2013\Odemira_2012-07-01_021501' WITH FILE=1, MOVE N'Odemira_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER2008\MSSQL\DATA\Odemira_2012-07-01_021501.MDF', MOVE N'Odemira_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER2008\MSSQL\DATA\Odemira_2012-07-01_021501_1.LDF', NOUNLOAD, STATS=10"
However, since I'll need to repeat this instruction several times with different databases, I'd like to use Python to do it in a loop. I tried to use the subprocess module to replicate the above instruction. Unsuccessefully.
My Python code is as follows:
import subprocess
host = 'POLIVEIRA-PC\\MSSQLSERVER2008'
mssql_dir = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER2008\\MSSQL\\DATA\\'
db_dir = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER2008\\MSSQL\\Backup\\ODEMIRA2013\\'
db = 'Odemira_2012-07-01_021501'
statement = '"RESTORE DATABASE [' + db + '] FROM DISK=N\'' + db_dir + \
db + '\' WITH FILE=1, MOVE N\'Odemira_Data\' TO N\'' + mssql_dir + \
db + '.MDF\', MOVE N\'Odemira_Log\' TO N\'' + mssql_dir + \
db + '_1.LDF\', NOUNLOAD, STATS=10"'
subprocess.call(["sqlcmd", "-S", host, "-Q", statement])
When I run this python script on Windows PowerShell, I get the following error:
PS C:\Users\POliveira\Dropbox\Academia\IST\Dissertacao_de_Mestrado\SQL> python .\lib\temp.py
Sqlcmd: 'RESTORE DATABASE [Odemira_2012-07-01_021501] FROM DISK=N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER2008\MSSQL\Backup\ODEMIRA2013\Odemira_2012-07-01_021501' WITH FILE=1, MOVE N'Odemira_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER2008\MSSQL\DATA\Odemira_2012-07-01_021501.MDF', MOVE N'Odemira_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER2008\MSSQL\DATA\Odemira_2012-07-01_021501_1.LDF', NOUNLOAD, STATS=10\""': Unexpected argument. Enter '-?' for help.
I don't know what am I missing. I've tried escaping the double quotes ("RESTORE ... 10") with backslahes, but it didn't work either.
EDIT: I did try to add the flag shell=True on the subprocess.call, but I ended with the same error.
Remove the double quotes around the statement, like this:
statement = 'RESTORE DATABASE [' + db + '] FROM DISK=N\'' + db_dir + \
db + '\' WITH FILE=1, MOVE N\'Odemira_Data\' TO N\'' + mssql_dir + \
db + '.MDF\', MOVE N\'Odemira_Log\' TO N\'' + mssql_dir + \
db + '_1.LDF\', NOUNLOAD, STATS=10'