Backup and Compress SQL Server Express Backup

I have multiple MS SQL express servers that I need to manage and backup and I have found that the backups can get quite large, so I looked into methods of compressing the backups. I found that there is a builtin method in SQL server, but this is only for a licensed copy of SQL server, not for the express edition. So being the hacker that I am I decided to do it myself with some scripting and 7zip.

This is the process:

• backup SQL server DB.
• compress backup file with 7zip. (7zip website)
• rename the file and copy it to a backup location.
• send an email notification that the backup completed using blat.

It sounds simple right? Well it can be a bit difficult to tie it all together so here are some scripts that I have used to achieve this.

1. Create backup device
• Open SQL server management studio
• Expand Server Objects
• Right Click Backup Devices
• Click new backup device
  
• Give the device a name (you need this for the batch script so try excluding spaces) and give it a backup path. I prefer using another disk and a simple path, something like D:\backups\db-backup.bak
  
  
2. Backup Database Script
• The backup batch script should look something like below, just substitute the databasename to the name of the database you are backing up and backup-location to the name of the backup device you specified above.
  
  sqlcmd -S localhost\databasename -E -Q "BACKUP DATABASE [databasename] TO [backup-location]"
  
  IF NOT EXIST "D:\backup\db-backup.bak"
      set subject=Backup error : SQL Backup failed
      set body=SQL Backup failed. %date% %time%
      GOTO SENDMSG
3. Compress the backup file using 7zip and rename file
• Again update the script below accordingly. Remember the 7zip directory needs to have no spaces otherwise the script does not work.
  
  set zipdir=C:\progra~1\7-Zip
  %zipdir%\7z.exe a "D:\backup\db-backup.bak" "D:\backup\db-backup-%date:~0,4%%date:~5,2%%date:~8,2%.7z"
  
  IF NOT EXIST "D:\backup\db-backup-%date:~0,4%%date:~5,2%%date:~8,2%.7z"
      set subject=Backup error : 7zip failed
      set body=7zip operation failed. %date% %time% 
      GOTO SENDMSG
4. Copy the file to a remote location
• This is pretty simple again just update the script as per you needs
  
  xcopy "D:\backup\db-backup-%date:~0,4%%date:~5,2%%date:~8,2%.7z" "\\backupserver\folder" /s /d
  
  IF EXIST "\\backupserver\folder\db-backup-%date:~0,4%%date:~5,2%%date:~8,2%.7z"
      set subject=Backup successful
      set body=Backup successful. %date% %time%
      GOTO SENDMSG
5. Send email message
• This function requires BLAT, just download blat and copy it to a directory something like C:\blat. It s a great command line app that I have used before for sending mails from some PHP scripts.
  
  set blatlocation=C:\blat
  set smtpserver=mail.myserver.com
  REM add addresses separated by commas
  set toaddress=address@myserver.com
  set fromaddress=address@myserver.com
  set appname=SQL Backup App
  
  :SENDMSG
  %blatlocation%\blat.exe -to %toaddress% -i "%appname%" -server %smtpserver% -f %fromaddress% -subject "%appname% : %subject%" -body "%body%"
  GOTO END

Remote Administration Using PSTools

PSTools is one of the most useful suites of applications out there for a network administrator. The toolkit consists of several command line applications that allow you to perform remote administrative functions such as interrogate processes and services. Below is a list of my favorite tools and how I use them on a daily basis. Please note that you require administrative privileges on the remote system to be able to use these tools.

pslist 

pslist allows you to get a list of the running processes on a remote system. I use this to see if users are running a particular process that I want to kill on their machine or not. It can be useful to troubleshoot certain issues or just determine if a user is running a specific application on their machine.

pslist examples

get a list of all the process on the machine:

pslist \\computername

get a list of all the processes starting with i

pslist \\computername i

pskill 

pskill allows you to kill a process on a remote machine. This is particularly useful if you have a user who is running an application that they shouldn't and you want to shut it down remotely without them noticing it was you or if there is a non-responsive app on a remote computer for instance I have found that adobe acrobat reader often leaves instances running in the background and consumes user resources.

pskill examples

Kill iTunes on the remote computer

pskill \\computername itunes.exe

psexec 

psexec allows you to remotely execute an application on a machine. This is useful to run remote scripted installs.

psexec examples

Open notepad on the remote computer (may not be visible to logged on user)

psexec \\computername notepad.exe

Copy the batch file to the remote computer and execute it. Batch file will map a network drive and call an installer.

psexec \\computername -C remote_install.bat

psloggedon

psloggedon gives you a list of all the users logged onto a remote computer. This is a great way to tell who is loggedon to a machine so you can phone them up and ask them why they are running a game you just noticed using pslist.

psloggedon exampled

Get a list of users logged on to a remote computer

psloggedon \\computername

Download PSTools Here

SPAM fighting with RBL’s

Our companies email server had been taking a bit of strain from SPAM recently and it seemed as though thousands of spam mails where getting through to my end users.

Scanmail for Exchange was picking up about 57000 SPAM mails a month, but I would still get about 200 spam mails a day in my own inbox!

I did some research and eventually found RBL (Real Time Blacklists). A RBL is a list of known spam email servers IP addresses. There are several RBL providers out there and it is very simple and in most cases free to setup.

Check out the following link to get your Exchange 2003 server setup with a RBL:

• http://www.spamblogging.com/archives/000138.html

I am currently using the the following RBL’s

• spamcop.net
• spamhaus.org

Since implementing the RBL I have seen spam on my own inbox drop from 200 a day to about 2 a day. I also noted that since the beginning of the month the number of SPAM is down from 57000 detected to 6500! And I should see an even greater drop next month as I implemented the RBL on about the 10th of the month!

South African Bank Phishing

It seems as though some of our local South African banks are starting to fall foul of the phishers of late. It is a good time to keep your users informed and keep your proxy servers block list up to date with any new evil domains.

Here ore some examples of phishing emails I got from a user.

ABSA PHISHING EMAIL 1:

Url at the bottom takes user to a spoofed website that looks like an absa logon page:

Dear Customer,

We,upgrades its internet security on a continuing basis to ensure that our customers are protected. Protecting information is a shared responsibility and we request you to exercise caution at all times when using online services or accessing your emails.

Due to the open nature of the internet, Absa cannot guarantee the complete security of your transactions from hacking, unauthorized access, virus attacks, and /or other 3rd party attempts to breach our latest security features that we have used. All Online banking users are required to adhere strictly to this warning and follow up the process of this adjustments. Absa will not be responsible for loss of funds to online phishers as a result of failure to comply with this important new directives

Register your online banking access, click continue below and follow the instructions so you don't stand a chance of lossing your details to a third party.

http://www.absa.co.za/ib.jsp/Administrator
Administrator
Absa Bank Limited

Please do not reply to this e-mail. Mail sent to this address cannot be answered.
For assistance, log in to your absa Online account and choose the "Help" link on any page.
absa bank Email ID # 1009

ABSA PHISHING 2:

The link from this mail seemed to be broken at the time of publishing.

Dear Customer,

The internet has become widely accepted for banking online. While we have taken all the possible measures to ensure security and confidentiality of our online banking systems, you play an important role in protecting your personal information and Passwords. You have to protect your information at all times, be it over the internet or during your normal banking activities.

Due to the open nature of the internet, Absa cannot guarantee the complete security of your transactions from hacking, unauthorized access, virus attacks, and /or other 3rd party attempts to breach our latest security features that we have used.

To Upgrade for this comprehensive security features,click below and follow the instructions

http://www.absa.co.za/security/features
Management
Absa Bank Group

Please do not reply to this e-mail. Mail sent to this address cannot be answered.
For assistance, log in to your absa Online account and choose the "Help" link on any page.
absa bank Email ID # 1009

FNB PHISHING 1

At the time of publishing the domain name that the link in the email takes the user to seems to have been disabled or broken.

Dear FNB Customer,

In the last fews weeks, our Online Banking Security team has observed multiple logon attempts on your internet banking account from different blacklisted IP's.

For your safety we have decided to suspend your access. You will need to verify your identity.

Click Here to continue

Security Management
First National Bank

Please do not reply to this e-mail. Mail sent to this address cannot be answered.
For assistance, log in to your fnb Online account and choose the "Help" link on any page.
fnb bank Email ID # 1009