At times, we may need to run SQL files present in a folder against SQL Server Database. Since there are no built in feature available as of now, here is our try with a powershell script.
Save the (.bat) Batch script & (.ps1) PS Script in same folder in which all the SQL Files are stored.
The Machine should have the SQL Server instance and Power shell tool.
Ensure the parameters values are verified in (.bat) Batch file
i) All key values are handled with parameters ii) If database not exist it will throw error and comes out of PS iii) If any script has issues/failed, it will throw error and comes out of PS iv) If we want to run the sql scripts in subfolder as well, then “Includesubfolders” parameter set to 1 v) Batch file automatically retrieve the root path
CD /D "%PowerShellDir%"
Powershell -ExecutionPolicy Bypass -Command "& '%PSScript%' '%path%' '%machine%' '%db%' '%user%' '%pwd%' '%Includesubfolders%'"
If Database Not Exists:
If Database Exists:
If Any Script has Error:
If “Includesubfolders” parameters set to “1”
I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
At times, we need to purge backup files (.bak, *.trn) which are older than some x days from the server on the regular basis, such that Disk Space and SQL Data backups will be maintained consistently within the server.
Power Shell Script:-
The below power shell script will purge the backup files (which are older than 5 days) from server, this script identifies the older files based on the last modified date time.
Based on your requirement, you can change the date range and schedule this script for Server Maintenance.
There may be some situation where you want to find the list of all filenames whose patterns are like “*.txt”, “File*.csv”, “*.html”.., etc. In such scenario you can use the below command shell scripts to get the desired results which you are looking for.
1) To get the list of all filenames and folder names present in a folder “C:\testdata\”
dir /b /s
2) To get all the filenames of pattern “*.txt” or “*.html” from folder “C:\testdata\”
dir /b /s "*.txt"
dir /b /s "*.html"
3) To get all the filenames of pattern “*.txt” from folder “C:\testdata\” and store the corresponding results in the text document
dir /b /s "*.txt" > output.txt
dir /b /s "*.txt" > "c:\testdata\test\outputs.txt"