First of all I want to share with you some initial knowledge I gathered after jumping in the making of my first 40+ lines backup-script in this environment (MS,PowerShell,MSSql ..) and having some basic issues in the beginning -  testing my tiny script.
The default config of a powershell environment doesn’t let you run unsigned scripts..
The security settings built into Windows PowerShell include a so called the “execution policy;” – this policy determines how (or if) PowerShell runs scripts. By default, PowerShell’s execution policy is set to Restricted; that means that scripts – including those you write yourself – won’t run. I can tell you that Before I found this information it wasn’t a nice and motivating first step in my PowerShell “scripting career” .
After a short research at MS Technet I learned about this useful commands :

> Get-ExecutionPolicy ( – verify the settings for your execution – the default has a “AllSigned” value )
> Set-ExecutionPolicy  [ AllSigned | RemoteSigned | Unrestricted ]
> Get-Help About_Signing  ( – to learn more about “signing scripts” for PS )

RemoteSigned settings is the best choice if we want to run our scripts only on the local machine.
I choose for my scripting the folder  c:\scripts
If we want to test our script in the development process , the best way is to open a PowerShell command line running PowerShell.exe and run from there the scripts for catching the output, errors..etc :
……………………………………
PS C:\scripts> .\ mssql_backup.ps1
In the command line above we use “.\” notation for executing a PS1 file. ( PS1 being the file extension for Windows PowerShell scripts )

To do a SQL Server backup in SQL Server, we will need to use the SQL Server Management Objects, known as SMO, and specifically we need the SqlBackup method.
SMO is an object model for SQL Server and its configuration settings. SMO-based applications use .NET Framework languages to program against this in-memory object model, rather than sending Transact-SQL (T-SQL) commands to SQL Server to do so.
In SQL Server 2008 + , we will need to load Microsoft.SqlServer.SmoExtended assembly otherwise, we get the following error:
” Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: make sure the assembly containing this type is loaded. “

Here is the a commented version of the < mssql_backup.ps1 >  script :

To schedule the actions – recurring backup – on a daily or weekly basis you may want to run this script from a Windows Server TaskScheduler.

Because  we need to set PowerShell.exe as executable in the Action Pane of TaskScheduler with the Unrestricted or RemoteSigned execution enviroment policy and the script will be only an argument of this,  the best way is to create a batch-file and specify this as the “Program/script” of the TaskScheduler’s “Action Tab”   with the following content:
————————————————-  db_backup.cmd content: ———————————-—-
powershell.exe  -ExecutionPolicy RemoteSigned  -Noninteractive  -Noprofile  -command “& {C:\script\ mssql_backup.ps1 }”
——————————————————————————————————————————

Our tester teams needed a solution for downloading large files (software builds,new releases, iso-images..)  in background from remote servers located in the USA.  Usually the testers starts their work (installing, testing)  after this software packages  are downloaded without errors.  In working hours they had a smaller bandwidth and many interruptions , so the manual download and verification of these large files was  a real wasting of time for us and for the tester guys too.

I wrote a little bash script to do this job for us:  mget.sh (inplemented in three steps).

1. Verifying the content of the remote ftp folder  &  Downloading the files if they exist.
2. Checking in a cycle if downloaded files properties matches the files on the FTP folder:
- if a particular file is corrupt because of a partial download, then it is downloaded again
- if the file size and type matches , then the remote file is deleted from the upload folder
3. Logging all the actions  made by the script (warnings too) in syslog format.

For the logging feature to work the mget.sh script must be invoked by root privileges. After you customize the script with the right variables ( hostname, remote user, password, logfiles, folders etc.)  you will need to make a new crontab entry for the root user to automatize this task In case we don’t want to allow or we don’t have root access to the Linux box, then make sure that the local download folder is accessible for read and write by our User (in this case jaky ) who will launch the mget.sh script.

Let’s say that we put our version of the script in jaky‘s home folder [ /home/jaky/mget.sh ] .

The entry in the  /etc/crontab

# m  h  dom mon dow    user      command  that will run @ 1:30 AM every day
30  1   *      *      *       root       /home/jaky/mget.sh

The logging is made in /var/log/mget.log file with date+time stamps for each operation.

You’ll find my commented version of the script here:

http://sftp.codespring.ro/~jaky/mget.sh

.. and a sample of the log file generated by the script:

http://sftp.codespring.ro/~jaky/mget.log

Have a nice day.. & Good Luck !