April 30, 2013

SQL SERVER: Backup the database and upload it on FTP and on Network

Recently one of my friend called me and asked me that his MS SQL SERVER Database server is crashed and the problem is, he has the database backups on the same server. So now as the server is crashed, and he cannot have any backup to restore the database.

I suggested him, first get data recovery tool to get the data from crashed server and then restore it to make it work, so application will be up.

Then, to overcome this kind of problem in future, I suggested him to always keep the database backup on another server and it is best to have database back up on another location too. He agreed with me that, and asked me a good question that: "Do you know if there is any kind of software by which I can schedule this and get out of this problem?"

As he needs the solution quickly, I checked it out some Tools and I come with the very cool tool "SQL SERVER Backup And FTP". This is very simple to use. This tool works as:

  1. Connect SQL SERVER
  2. Select Database(s)
  3. Select "Network Path", "FTP connection"
  4. Enter Email Address, so software will notify the user when backup is done

We need to install this software on the server and we can take SQL SERVER Database backup on Network and FTP together.

First please install this software from SQL SERVER Backup And FTP and Let me explain it how it works:

  • Connect SQL SERVER: First connect to SQL SERVER Database, for which we need to take backup. We need to Enter SQL SERVER Name and credentials to connect to SQL SERVER

SQLYoga SQL Server Backup

  • Select Database: Once SQL SERVER is connected, all databases of that SQL server will be listed. We need to select all the databases for which we need to take backup as follows:

SQLYoga SQL Server Backup

Here I have done following:

  • Checked two databases, pubs and SQLYoga.
  • Checked "Network Folder" and select Network path
  • Checked "FTP SERVER" and specify FTP Server, to put backup on FTP Server
  • Checked "Email Notification", so I will be notified about success/failure of moving database backup.
  • Unchecked "Schedule this job", as I just need to run it once. If we need to run it on daily basis, we can specify schedule to run it on that time daily.

That's it and now we are out of stress to move database backup to another server or on FTP

Let's click on "Run now" to check it out.

After process is completed, Two zip files, pubs and SQLYoga is on Network path as well as on FTP.

Another cool feature is that, we can schedule Full Backup, Differential Backup, and Transaction Backup, so we can have very minimal amount of data loss.

SQLYoga SQL Server Backup

Here, I have configured this software as:

  • Take full backup of database at every 24 hours
  • Take differential backup at every 1 hour
  • Take Transaction backup at every 15 minutes

Here, we assume that it is ok that we have 15 minutes data loss. It is depends on your application to decide this time window.

Let's take one case study to get more understanding about to restore these backups:

e.g. Database server is crashed at 8:20 AM, How can I restore my database from the backups?

  1. Restore full backup database which was taken at mid night 3 AM.
  2. As we have configured to have differential back at every hour, we also have Differential backup of 8:00 AM, so restore differential database backup.
  3. We have also configured transaction backup for every 15 minutes, so we have one transaction log backup which is taken on 8:15 AM, so let's restore it. So, now we have data up to 8:15 AM
  4. As we have assume that Organization is good if we loss 15 minutes of data. Here in this case, as we have Transaction backup of 8:15 AM,  we are losing transactions which are made between 8:15 AM - 08:20 AM. So, we should proceed with 08:15 AM transaction backup and make database available

Another, option is also available, to remove old Differential backup and Transaction backup. Once new differential backup is generated, there is no need to keep old differential backup. We only need to restore recent differential backup, so we can remove old differential backups. We can also remove all transaction log backup which are generated before Differential Log. SO to remove old backups, this software is also providing an option for the same, we just need to checked this option and that's it.

This software is also allowed following settings (advanced):

SQLYoga SQL Server Backup

  • You can setup custom "temp" folder, by default its using "C:\Windows\Temp"
  • Compression Settings (Zip)
  • Encryption

I like this tool, I recommend this tool to take back up and move it on FTP. Try to use it and let me know your comments

5 comments:

  1. Hi Tejas Shah,

    Great work mate, I was looking for a way to subscribe to your blog via RSS or Atom but couldn't figure out how. Can you please advise if this feature is active on your blog.

    Thanks,
    Tejas Goradia
    bytebugs@gmail.com

    ReplyDelete
  2. Hi Tejas,

    You can add my blog with feeds: http://feeds2.feedburner.com/SQLYoga

    Thanks,
    Tejas
    SQLYoga.com

    ReplyDelete
  3. Our SQL server crashed. SQL support guy restaged Master database. Now user database shows suspect.How can I attach user databse to new server which is not detached from crashed server? Can you recommned good SQL data recovery software?

    DM

    ReplyDelete
  4. Hi,

    can you ask SQL support to give you mdf file of your database?

    Tejas

    ReplyDelete