Recently I went through requirement of running SQL scripts on multiple databases on different servers. As the list of databases was different from script to script I decided to create universal solution, which will allow to easily prepare for deployment and can be simply reused at any time.

As a platform to execute scripts I went with PowerShell as that provides flexibility in case additional functionality will be required.

Whole solution contains 3 files:

• SqlExecutionInflow.csv – file contains list of databases and servers where particular database is located. It is simple CSV file with 2 columns
• SqlExecQuery.sql – contains SQL script which will be executed against all databases listed in SqlExecutionInflow.csv
• SqlExec.ps1 – main script which load SqlExecInflow.csv and executes query from SqlExecQuery.sql

All files have to be placed in same folder. As a result script will create transcript file with output from all executed commands.

And here are example files and script itself…

(more…)

Some time ago I had to transfer SQL Server databases from remote retail locations to central system. At that time whol einfrastructure was operating on Microsoft SQL server 2000 and link in remote locations was 128kb/s ISDN. At that time I thought that the best idea will be backup database to local hard disk on SQL server, RAR it and FTP compressed backup of SQL database to headquater. It did work quite well. So, below is a script I scheduled to be run as part of scheduled job defined on SQL Server Agent.

Before script will be used few things have to be customized:

• location for database backups – @archive_path – default: F:\Backup
• location of WinRAR application – @archive_cmd – default: C:\Program Files\WinRAR
• IP address of FTP server – default: 192.168.10.120
• Username and password for FTP user – default: dbupload/password
• Folder on FTP server for backup upload – default: Site_Backup

As there might ba many remote sites uploading backups, each site can have different folder for data transfer.

Here is script to backup SQL Server database and trasfer backup to FTP server:

(more…)