SQL Server Backing up and Maintence Plan....how to delete those old .bak files

One of the task I need to do was have a suitable backup plan on several MS SQL 2008 databases. What seemed a straight forward excerise took for ever to work out a complete workable solution. However in the end I got there following was some of the pitfalls I fell into and some of the solutions I found out along the way. I hope this helps other people just starting to work with SQL Server 2008.

STEP 1: Planning

There was four things I took into account when making a backup plan:

1. Useage
2. Time Taken for Backup
3. How far back would I need a backup.
4. Should I use log files.

1.Useage was the most important factor. A database which is used daily should be backed up daily end of story. A Database used weekly should be backed up weekly....I think you get my point.

2. Time taken for a backup suprisingly is usually alot less than what most people might think I had a 30GB database which took 5 minutes to do a full backup. Unless your looking at a database that is in excess of 100GB your not going to be worried about running this on a schedule at 1am in the morning every day.


3. This was the most important thing for me to consider. Errors unfortunetly creep into databases no matter how many precautions are taken. Big banks having the most robust database make many errors over time. You just need to accept this is part and parcel of the database world unless your in denial ofcourse. Recently some software changes caused one of our databases to have errors since November 2008 hence over 7 months. Although it was eailsy fixed with a simply query this may not have been the case if the software bug was more major. Anyway back to the point, I break up the backup procedure into three parts Daily or Weekly , Monthly, Yearly.

a) Schedule once Daily/Weekly: Files 3 days old will be removed only if the last backup was successful. (hence 2 backups are kept in same directory.) Directory file://dbserver/SQL.2008/Archives/DB/Daily If your database is used weekly then change the directory to a weekly one instead of daily.
b) Schedule once Monthly: Files 35 days old will be removed only if the last backup was successful. (hence 2 backups kept in same directory.) Directory file://dbserver/SQL.2008/Archives/DB/Monthly
c) Schedule once a year: Files 11 months old will be removed only if last backup was successful. (hence 1 backup kept in same directory) Directory file://dbserver/SQL.2008/Archives/DB/Yearly


Make sure you create these directories in windows explorer beforehand.


4. Log files, if the harddrive goes I need the daily backup. If the database is running 24/7 and transactions are being done daily then record the log files so you can update the database with the appropriate changes from the backup and then run the log files afterwards. If the database just get's updated once daily then you won't need log files especially if the data is stored in another file format such as XML to text files which are easy to import.


Monthly backup's are important if integrity is lost and a program needs to be re-run. I would save the last two months. anything after this will just take up disk space. A yearly backup is always handy if you think there may have been some major issue that has caused database data error over the course of the year. If you haven't noticed it by a year then clearly you need to look at your operations firstly.


STEP 2: Email reports.
When you do a backup you will want to know how it went and looking at Maintence reports although good for solving don't beat getting an email saying there is something wrong. To setup Database Mail you need to do two things first:


1. You need to create an operator. To send emails you will need to specify who to send it and SQL uses Operator which you can find in Management Studio. Just add Name and E-mail name. Net Send is for sending messages via the network. I think this would be quite annoying after a while so not worth adding.
2. Configure your Database Mail Client - Go to Database Mail under Management Studio and run the wizard. Unless you can easily setup another email account on your domain I suggest use a pre-existing one with a different email address and Display name. At the end of the day you really don't care who sends the email as long as it's sent and you can distinguish it from others.


STEP 3: Creating Maintence Plans in MSSQL Mang. Studio.

The first place to start is in Microsoft SQL Server Management Studio is "Maintence Plans". Use Maintence Plan Wizard. I would use a different plan for a different database, each database is different from each other so the maintence should be different. Select a name for your plan hence "YourDatabase Backup", I select Single schedule for the entire plan or no schedule for the moment, you can always change this later. Select Next.


In this screen I select just one item "Back Up Database (Full)" the reason is I want to extend this later with Success and Failure Constraints linked between each task. Press Next, Next to come to Define your Backup Database (Full) Task. This backup will be for Daily backup select your database you need again I recommend doing one database at a time.

For Daily backups I make the database expire after 3 days. Really I only want to keep two daily backups, if you have a 30-40GB database you don't want too many of these otherwise it won't be too long before you run out of space. I select "Create a backup file for every database" however I don't use sub directories, I want to keep this simple as possible. In the folder part use file://dbserver/SQL.2008/Archives/DB/Daily

The backup file extension should be "bak".


I always click "Verify backup integrity" it doesn't take long to run. Depending on your version of SQL you maybe able to change the compression however untill your sorted and using your first plan I would just leave this as a default.

Always create a report which you should email to your operator you sepcified. There is host of information in these reports so definietly keep them and send them to yourself. This is different to the notfiy reports that you will be sending later.

STEP 4: Adding additional functionality to your Plan

Once done the plan should appear under the Maintence Plans folder. Right click and then select Modify. Here is when you start changing things, firstly change the name "SubPlan_1" to something meaningful like "YourDatabase_Daily_Backup_Plan". Also click the schedule or the calender to specify when to run it. Since it's daily backup once a day during non-peek times is the best. For database that change a few times only in a week you need to think when you want to have copy. Remember if something goes wrong you want the easiest process to get the latest backup.


Once you have done this you want cleanup old backup files. You can easily add this by adding the "Maintence Cleanup Task" on the left and drag it to the right. You will need to modify it so it deletes only files from the daily directory and is 3 days old. *** I had huge problems at this part, in the end I realized that the NTService didn't have premission to delete the file from the directory. So make sure you have premission..I was able to test this by doing the following command to delete the file directly:


EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL.2008\Archives\Db\Daily\Db1_backup_2009_06_16_191120_2202108.bak'
GO


Initially I was getting Access Denied Error 5 message. It was only solved when I allowed the permission for the profile to have full access to that directory in Windows Explorer, then everything fell in place after that.


Now you should link these together, you will notice a green arrow these are called "Constraints" . You should extend this so if Backup was successful it will then delete old backup files. You don't want to be deleting old backup if a new backups are not working. Now you want to notify a person by email if anything has gone wrong so drag "Notify Operator Task" on to the screen. You should then modify this to include an operator to email. Then select the Backup again and a new small green arrow should appear out of the box extend this to point to the new Notify Operator Task you have just done. If you right click the line you will see a few options: Success, Failure and Completion. What you would like to do is change this to Failure and the arrow should turn red. You can extend this to include several other opertaions. Below a sample of what the plan should look like if you have done it correctly:









STEP 5:

You can add some additional items to keep your database in good order, Shrink Database, Rebuild Index Task, Update Statistics Task, History Cleanup Task. All these help the smooth running of your database. I suggest trying them out and seeing what effect it has on your system.

Your pretty much done for the daily backup.

STEP 6: Monthly and Yearly backups

To do this I recommend keeping it in the same plan, everything in the same place makes sense. To do this easily just simply add an another sub_plan by clicking "Add Subplan" at the top left hand corner. Name this "Monthly_Backup_Plan" and it should appear as new row below "Daily_Backup_Plan". You could just drag the "Back Up Database Task" from the left and reconfigure everything but I cheat by copying the Daily one and then just paste it on the Monthly one. You will need to edit some of the options such as when Backup will expire to 35 days and the directory to ..\Monthly.

The rest is pretty much repeated from Step 4. for both Monthly and Yearly.

C# Background Worker net 2.0

Recently I got code from a programmer who had imported a load of webpages and parsed them. It took quite a long time to do and hence he made the program run in the background without a GUI. This was fine as is was out of the way however I wanted to visually see what was going on if need be. Intitially he had written a log file which was just as fine solution but I wanted to see if I could see it on the screen.

In .net 1.0 we all used Application.DOEvents() however this had a host of problems which I won't go into it here. In .net 2.0 they introduced a Background worker component to do the trick. Basically this component starts up a managed thread that does all the work in background without impacting on the rest of the application.

There was a whole range of samples on this however I found the following link the easiest to do:

http://dotnetdud.blogspot.com/search/label/.NET%20BackGround%20Worker%20Example

Firstly if your starting out using Background worker just drag a component on to the desktop, this is the easiest thing to do. The component has 3 events all critical to using it properly.


DoWork, ProgressChanged,RunWorkCompleted.

DoWork fuuction kicks everything off, when you double click the property a new function will be generated on your screen as below.

private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
// Put your entry point for your code here //
}

In this function should be the entry code of where your program and task starts. So for example if you where writing code for the entry point in the usual button1_Click event then you would want to simply copy and past the main functionality into the backgroundWorker1_DoWork() function.


private void button1_Click(object sender, EventArgs e)
{
backgroundWorker1.RunWorkerAsync();
}

RunWorkerAsync() kick's off the thread and starts the backgroundWorker1_DoWork() which should now have the code you need.

You can also run private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e) to keep track of your changes but I just have delegates in my thread code which updates the main form for any changes. Since GUI is free up to listen to changes it will detect these straight away.

C# Ordinal Number for a Month

I was parsing this page and I was trying to convert the following date into Datetime format:

Friday 5th of September, 2008

What I thought would end up being something readily available and easy turned out not the case. The biggest issue was "September" and I had to create a procedure to get the Ordinal number with a winding routine that finally did the job for me, although have to say it was alot bigger than I expected.

To call the function it's simply:

DateTime SelDate = ConvertLongDateStr(DateStr);

Below are the routines:


private static int GetMonthsNumber(string monthname,ref bool valid)
{
for (int month = 1; month < 13; month++)
{
DateTime date = new DateTime(1900, month, 1);
if (date.ToString("MMMM").ToUpper() == (monthname.ToUpper()))
{
valid = true;
return month;
}
}
valid = false;
return 0;
}

public DateTime ConvertLongDateStr(string LongDateStr)
{
// Thursday 4th of September, 2008 //
string DoW = LongDateStr.Substring(0,LongDateStr.IndexOf(" "));
LongDateStr = LongDateStr.Remove(0, LongDateStr.IndexOf(" ")+1);
// for loop for max of 2 integers //
string daystr = "";
for (int i=0;i<=1;i++)
{
try
{
int num = Int32.Parse(LongDateStr.Substring(0,1));
daystr = daystr+LongDateStr.Substring(0,1);
LongDateStr = LongDateStr.Remove(0,1);
}
catch (Exception)
{
// nothing required here //
}

}
LongDateStr = LongDateStr.Remove(0, LongDateStr.IndexOf(" ")+1); // removes "th" "st" "rd"
LongDateStr = LongDateStr.Remove(0, LongDateStr.IndexOf(" ")+1); // removes "of"
string LongMonthStr = LongDateStr.Substring(0,LongDateStr.IndexOf(","));
bool validmonth = false;
int MonthsVal = GetMonthsNumber(LongMonthStr,ref validmonth);
LongDateStr = LongDateStr.Remove(0, LongDateStr.IndexOf(" ")+1); // removes ","
string YearStr = LongDateStr.Trim();
string ShortDateStr = daystr+"-"+MonthsVal.ToString()+"-"+YearStr;
DateTime ShortDateVal = Convert.ToDateTime(ShortDateStr);
return ShortDateVal;
}

Start of Blog - Thread crossing C#

This is my first post for the my bog. I have been developer for over 15 years now and have seen a huge number of changes in the industry. I think most of my posts will be random hence if I can write something up here that I found difficult to solve at first and there didn't seem to be an easy to understand solution on the net.

My first post is about the unpleasant experience of upgrading one of my programs from Visual Studio 2003 to 2005. I was continually getting an exception when I was trying to update a listveiw in the main form from a thread. Due to time constraints most code I write is buggy however software needs to put out there and there is usually very little testing done at my end.

After a bit of searching I came to realise that the inital code I had written wasn't correct and I shouldn't have been calling the main form from the thread the way I did. Hence the use of these mystical items called "Delegates". I plondered for several hours trying to use the sample on the net to no avail, until one of my collegues showed me the way, thankfully!!!

Intially in my thread class I would just call the mainform listview like this:

listview1.Items[row].SubItems[column].Text = "Running ";


This failed miserably in VS2005, however the simply solution is as follows, all should be written in your thread class:

delegate void Updatelistview1Invoker(ListView listview1, int row, int column, string message);

private void MainThread_Updatelistview1(ListView listview1, int row, int column, string message)

{ listview1.Items[row].SubItems[column].Text = message; }

public void Updatelistview1(ListView listview1, int row, int column, string message)
{
listview1.Invoke(new Updatelistview1Invoker(MainThread_Updatelistview1), new object[] { listview1,row,column,message});
}

// In your code Call the following to update a particular point in your listveiw

Updatelistview1(listview1, row, column, "Running"));


Anyway I hope this helps someone out there. back to coding....