SS64 Forum

rupesh

New Member

Registered: 15 May 2014

Posts: 2

batch script for data load from dropbox to sql server

I am new in batch scripting. I wrote code for data upload from dropbox to sql server with help of google which is like:

cd\
del "D:\Database\GoodLuck Kanjurmarg\*.bak"
del "D:\Database\GoodLuck Kanjurmarg\*.xml"
cd "C:\Program Files\7-Zip"
"C:\Program Files\7-Zip"\7z.exe  -o"D:\Database\GoodLuck Kanjurmarg\" e "D:\Dropbox\Shared Folder\GoodLuck Kanjurmarg\*"
cd\
rename "D:\Database\GoodLuck Kanjurmarg\*.bak" MUM_042_Goodluck_General_Stores_Kanjurmarg_23.bak
cd\
sqlcmd -S"ADMIN-PC\SQLEXPRESS" -E -Q "restore database MUM_042_Goodluck_General_Stores_Kanjurmarg_23 from disk='D:\Database\GoodLuck Kanjurmarg\MUM_042_Goodluck_General_Stores_Kanjurmarg_23.bak' with move 'Account180001' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MUM_042_Goodluck_General_Stores_Kanjurmarg_23.mdf',Move 'Account180001_LOG' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MUM_042_Goodluck_General_Stores_Kanjurmarg_23_Log.ldf',replace"

This whole code is hardcoded, if any folder added into D:\Database, I have to again copy above code & pasted below with respective to that folder. example: suppose rupesh named folder added into D:\Database then my whole script become like:

cd\
del "D:\Database\GoodLuck Kanjurmarg\*.bak"
del "D:\Database\GoodLuck Kanjurmarg\*.xml"
cd "C:\Program Files\7-Zip"
"C:\Program Files\7-Zip"\7z.exe  -o"D:\Database\GoodLuck Kanjurmarg\" e "D:\Dropbox\Shared Folder\GoodLuck Kanjurmarg\*"
cd\
rename "D:\Database\GoodLuck Kanjurmarg\*.bak" MUM_042_Goodluck_General_Stores_Kanjurmarg_23.bak
cd\
sqlcmd -S"ADMIN-PC\SQLEXPRESS" -E -Q "restore database MUM_042_Goodluck_General_Stores_Kanjurmarg_23 from disk='D:\Database\GoodLuck Kanjurmarg\MUM_042_Goodluck_General_Stores_Kanjurmarg_23.bak' with move 'Account180001' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MUM_042_Goodluck_General_Stores_Kanjurmarg_23.mdf',Move 'Account180001_LOG' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MUM_042_Goodluck_General_Stores_Kanjurmarg_23_Log.ldf',replace

cd\
del "D:\Database\rupesh\*.bak"
del "D:\Database\rupesh\*.xml"
cd "C:\Program Files\7-Zip"
"C:\Program Files\7-Zip"\7z.exe  -o"D:\Database\rupesh\" e "D:\Dropbox\Shared Folder\rupesh\*"
cd\
rename "D:\Database\rupesh\*.bak" MUM_042_Goodluck_General_Stores_Kanjurmarg_23.bak
cd\
sqlcmd -S"ADMIN-PC\SQLEXPRESS" -E -Q "restore database MUM_042_Goodluck_General_Stores_Kanjurmarg_23 from disk='D:\Database\rupesh\MUM_042_Goodluck_General_Stores_Kanjurmarg_23.bak' with move 'Account180001' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MUM_042_Goodluck_General_Stores_Kanjurmarg_23.mdf',Move 'Account180001_LOG' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MUM_042_Goodluck_General_Stores_Kanjurmarg_23_Log.ldf',replace

so these lines goes on repeating on the basis of folders & all are hardcoded. so I want to do automatic without hardcoded, like one text file containing name of folders & run above few lines code on list of folders coming from text file

foxidrive

Member

Registered: 04 Apr 2013

Posts: 339

Re: batch script for data load from dropbox to sql server

Test this: file.txt should be in the folder with this batch file and contain lines like this:

GoodLuck Kanjurmarg
rupesh

@echo off
for /f "usebackq delims=" %%a in ("file.txt") do (
del "D:\Database\%%a\*.bak" "D:\Database\%%a\*.xml" 2>nul
"C:\Program Files\7-Zip"\7z.exe  -o"D:\Database\%%a\" e "D:\Dropbox\Shared Folder\%%a\*"
rename "D:\Database\%%a\*.bak" "MUM_042_Goodluck_General_Stores_Kanjurmarg_23.bak"
sqlcmd -S"ADMIN-PC\SQLEXPRESS" -E -Q "restore database MUM_042_Goodluck_General_Stores_Kanjurmarg_23 from disk='D:\Database\%%a\MUM_042_Goodluck_General_Stores_Kanjurmarg_23.bak' with move 'Account180001' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MUM_042_Goodluck_General_Stores_Kanjurmarg_23.mdf',Move 'Account180001_LOG' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MUM_042_Goodluck_General_Stores_Kanjurmarg_23_Log.ldf',replace
)
pause

rupesh

New Member

Registered: 15 May 2014

Posts: 2

Re: batch script for data load from dropbox to sql server

Thank you for your code. It is working fine, but I need your little more help Please. renaming *.bak file from second text file i.e file1.txt which cantain rename file name i.e MUM_042_Goodluck_General_Stores_Kanjurmarg_23.bak, rupesh.bak

@echo off
for /f "usebackq delims=" %%a in ("file.txt") do (
del "D:\Database\%%a\*.bak" "D:\Database\%%a\*.xml" 2>nul
"C:\Program Files\7-Zip"\7z.exe  -o"D:\Database\%%a\" e "D:\Dropbox\Shared Folder\%%a\*"
rename "D:\Database\%%a\*.bak" "MUM_042_Goodluck_General_Stores_Kanjurmarg_23.bak"
sqlcmd -S"ADMIN-PC\SQLEXPRESS" -E -Q "restore database MUM_042_Goodluck_General_Stores_Kanjurmarg_23 from disk='D:\Database\%%a\MUM_042_Goodluck_General_Stores_Kanjurmarg_23.bak' with move 'Account180001' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MUM_042_Goodluck_General_Stores_Kanjurmarg_23.mdf',Move 'Account180001_LOG' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MUM_042_Goodluck_General_Stores_Kanjurmarg_23_Log.ldf',replace
)
pause

foxidrive

Member

Registered: 04 Apr 2013

Posts: 339

Re: batch script for data load from dropbox to sql server

What do you need changed?  What part of your example was wrong?