Top 5 SEO Factors to Consider

In my last SEO post I tried to explain that SEO is not as complex and some would like you to believe. In this video posted April 29, 2013, Google’s head of webspam, Matt Cutts, explains the top five mistakes webmaster make on their pages.

 

1) Crawlability

Crawling is the lifeblood of search engines, it’s what they DO, they crawl! A search engine "bot" will visit your domain and look at the source code, that is, all the plain text markup that defines your page. They tend to begin this search on your home page, or the page of your root domain, such as example.com. In plain text, it crawls the page top to bottom. The bot notices how your page is structured by the use of proper HTML, for example it knows the <head> section, then the <body> section, it recognizes header tags, bolded text, and in HTML5, will see <article>, <aside>, <address>, <blockquote> and so on. Well formed HTML helps the bot know where your primary content is and what it is for.

As the bot crawls top to bottom, it notices any and all of your links. Some of your links may leave your domain, but any links that point to your own domain, it crawls those. What Matt is saying is that, if you have any content which is "hidden" in such a way that the crawler does not get a link, then that page and content will not be indexed.

Let’s say you created a special page all about your favorite documentary, you then linked to this page by "telling people" the address, maybe on Facebook or a chat client, etc. Since your own website does not have a link to this "secret" page, search engines will no find it or index it. They don’t simply know by magic where all your content is, they have to find it by crawling, and finding all your links.

Matt made a small remark about testing your site in a "text browser". This is a popular technique used by hard core webmasters to check exactly what he is talking about. A text browser, such as Lynx, lets you "see" your webpage just as a crawler does. You can verify your markup makes sense, the structure is sound, the links are all there, etc. Search engine bots are not the only critters that see only text, screen readers as well for the blind, because fancy pictures and other multimedia don’t matter.

Bottom Line: Make sure ALL of your website is crawlable by making sure all of your website is linked to and structured correctly. Frameworks and CMSes like WordPress and Joomla do this kind of stuff for you, but custom website designers should take care, and perhaps even use a text browser to verify the site makes sense to crawlers.

2) Include the Right Words

This is a point I stressed in a previous post, make sure you write in such a way as people would search. He uses the example of the height of Mount Everest. Don’t write "Mount Everest Height: ….", but rather use "How high is Mount Everest?". By forming your content in such a way, it is closer to what people actually search for. People tend to search by asking questions, "who, what, when, where, why, how, is, etc". You might sound very scientific to write an article on "The efficacy of consuming pizza for adipose metabolism", but nobody is going to search for that, instead try "Is eating pizza a good way to lose fat?". Or perhaps "can I lose fat and still eat pizza?" There are a dozen different ways to formulate your writing to better serve people who are searching.

Always ask yourself this question, it is the root of all SEO! Ask yourself "what am I writing about? If I were a person looking for this exact information, what would I search for?" It’s like a game where you tell somebody the answer, and they have to give you the question which results in that answer. You have to work backwards in this way, asking yourself what would a person be searching for if they were looking for my info, and then facilitate that search in your writing.

He also mentions not hiding content behind PDFs, images and other non-text forms. If you are a restaurant, list your menu not just in a downloadable PDF, but on the page as well. If you are a business, list your hours and other details.

Always remember that search engine crawlers cannot "assume" what your page is about, where you are, or anything else. Try to never write content in the assumption that the reader simple knows what you’re talking about. Everything should be explicit in terms of the subject matter. Especially if the subject matter is assumed based on pictures or graphs on the page. Pictures make sense to people, but the search engine still needs to know what is going on.

3) Content and Marketing, not Link Building

Matt says specifically if you think your job is to be a link builder, you’re off on the wrong track. Your "job" is to create compelling and useful content and then follow standard marketing principles to spread the word. He mentions news reporters, social media, billboards, advertising, guerilla marketing, community reach out. In other words, don’t make your whole website and life around capturing search engines, when there are many other ways to market and gain popularity. Even if you succeed in "tricking" the search engine to list you higher, a lack of compelling content will make people leave, complain, and in some search engines, mark you to be removed or mark you as a trickster or spammer.

4) Titles and Descriptions

This can be considered meta-tag information. Your page titles and descriptions are often used to describe the page within the search engine results themselves. Are these two small bits of text enough to completely describe what your page content is all about?  Do this at least for your high traffic and most important pages.

Also be mindful of your links, these days we do not want to see links such as "example.com/index.php?p=341&pg=445". If somebody were to bookmark this, there is no way to tell what it is about based on the link itself. Make sure your site uses meaningful URLs such as "example.com/how-to-wax-your-car" or something like that.

5) Do Use Webmaster Tools

Use Google’s own Webmaster Tools as well as many other tools to check your ranks, keyword density, and much more. I will have to post later about some nifty SEO tools, but for now, he suggests Google’s own tools, as well as reading their blog, and watching videos such as this one.

 

Summary:

As I’ve said before, the best way to do what search engines want, and what real people want, is to create useful, engaging, informative, helpful, quality content. Beyond that, there are no "tricks", there are simply helpful techniques to make sure search engine bots can get all the information they need from your pages.

Uninstall a Windows Update from Command Line

A quick tip to keep in your toolbox. If ever there is a “bad update” from Microsoft and you need to quickly uninstall it, use the wusa tool. If you open a command prompt now and type “wusa” and hit <enter>, a screen will pop up with the various command line options:

wusa.exe options

To uninstall a particular KB, such as the recently defunct KB2823324, you might type wusa.exe /uninstall /kb:2823324 /quiet /norestart.

Obviously you can then use this command in a script or batch file. And even better, combined with psexec from pstools, you can send this command to remote workstations to uninstall from other systems in your care.

What’s the big deal about SEO? Is it really so complex?

Being in the industry that I am, I come across headlines like “Google changed the algorithm! Quick, buy our software and updates before your website is kicked off the Internet for good!”

These radical scare tactics should not bother anyone, SEO really is not THAT complicated. It is only complicated to those who think the whole system is some kind of scam or trick. Do you really think Google has built its search engine algorithms to only follow archaic rules that are unnatural and only “professional SEO consultants” can put it all together? Google is not interested in “secret techniques”, they are interested in answering peoples’ search queries by providing relevant results, that’s it! If you search for something and find it in the top 10, they succeed, if you find nothing, they fail.

If you search for “blue polka-dot swim trunks”, Google wants to return stores, videos, images, and relevant writing (blogs, forums, etc) on just that. No matter how many SEO “tricks” you think you understand about Google’s “algorithm”, they are only interested in returning the relevant websites, not websites on sales of peanut butter. My point is this, Google will change their algorithm NOT to bump off the Internet valid information, but to STOP returning invalid information, spam sites, master SEO trick sites, etc etc. Google only changes their algorithm to stop tricksters who know too much about SEO for their own good and are constantly trying to get higher ranks in their crummy spam sites.

When you search for something, Google’s sin is to return a top result which has little to nothing to do with your search. A successful SEO shyster is the guy who is getting his bogus websites listed high up for keywords and search phrases that have nothing to do with your search. THIS is why Google changes the algorithms.

Here are the steps to good SEO.

1) What are you writing about? What is the subject matter?

2) What are the keywords for this subject? You want 4 to 8 primary keywords to explain the subject.

3) Do you specifically USE those keywords and discuss the subject matter in light of them?

Here is how this plays out; the good, and the bad way.

1) I’m writing about how to refinish paint on a car.

2) refinish, restore, paint, car, etc

BAD: “The first step in the process is to gather the necessary tools for the job…”

GOOD: ” Restoring the paint of your car requires a handful of specialty tools…”

Notice the opening sentence of the bad entry is “on topic” but the topic itself is assumed rather than explicit. Notice the good sentence immediately starts using the keywords to explain the subject explicitly. This is the essence of good SEO. It means knowing what you’re writing about, knowing the keywords that define it, and using them appropriately in your content. There are certainly some tricks along the way, but this is the start. In fact, “trick” is a bit of a misnomer, there are no “tricks”, rather, there are subtle ways to help Google (and other search engines) understand what your content is about. You don’t have to “trick” Google into thinking your page is about restoring paint on a car, if in fact your page really IS about restoring paint on a car. There is no trick to that. You would only need to “trick” Google if your page were about something else; spam, showing ads, or otherwise isn’t actually about restoring paint at all. THAT requires tricks! And only THOSE people need fear when Google “changes the algorithm”, so they can adjust their tricks.

If your website is about restoring paint on cars, you simply do everything you can to make sure search engines know this is what your page is about. If you don’t use dirty tricks offered by spam experts, you have no worry about changing algorithms. I will explain some of the more subtle SEO ideas in a later post.

Windows 8 Task Manager

Getting Used to Windows 8 (Initial Impressions)

I have been a long time Windows user, coming from 3.1 through 95, 98, XP, and Win7. I skipped Vista on purpose. I have some but minimum regular use of Mac OS’s and Linux Desktops.

That said, I’ve just purchased a small Sony E Series laptop and want to share my first impressions of Windows 8. I will have future posts of my favorite tricks, but for now, just some initial thoughts to help people get started. I’ve had it about one week now.

First off, don’t confuse yourself into thinking there is a vast and humongous, unreasonably complex, life-altering difference between Win7 and Win8, there is not. There are changes, but it’s not life altering. To summarize the biggest change; the old Start Menu is replaced by the “Start” screen (also called the Start Menu, but not to be confusing I’ll say Start Menu for old version, and Start screen for Win8 version). Windows 8 also introduced Microsoft’s new “Modern UI” concept which is theory I’m not really going to go in to here, but does constitute the second largest difference.

This change in the Start screen should not upset people, as Linux users have enjoyed the concept of “multiple desktops” for years, and Mac users were given “Spaces” in OS X Leopard. It is no real stroke of genius for Microsoft to add a second screen to the Windows experience, it is a common concept though one that Windows users are not used to. In fact, Linux users typically get 4 desktops to switch between, and sometimes as many as they want. The Start screen is not a “desktop” though, I’m merely speaking of the concept of having more than one primary place to launch and deal with apps.

The old Start Menu could be described as “that place to launch apps and search for programs”. You could do stuff with Start Menu like pin apps to the top, and view recently opened documents and applications. That is about it. You had links to user data, to Control Panel, to the Run box etc. Essentially, it was the place to launch apps. The new Start screen can be described as…..”that place to launch apps and search for programs”. You can do stuff like pin your apps to the screen, search for apps, uninstall, unpin, and launch anything you want. Hmmm.

I suppose the big issue is that when you click the old Start Menu it would pop up a menu, whereas the Start screen “takes over” your whole screen. The Start screen is a full screen launcher, not just a menu that slides up over your desktop. This is the primary point people will have to get used to. In order to “open the Start menu”, your screen will be taken over by it. It is a mere swipe or click to get back to the main desktop and not much of an inconvenience. Consider that when you open the normal Start menu, you would have to click escape, or click outside of it, or click the Win key again for it to go away, it is just as many clicks to get the new Start screen to go away. Once it appears, you just click a corner to go back. I don’t believe much in the way of speed, accessibility, or number of clicks has changed here.

The other primary change to get used to is the use of the charms menu, hot spots, and swiping (when applicable). Swiping is not just for touch screens, it’s also for touchpads on laptops, and to a degree, the mouse. Hotspots are not a new concept, I remember years ago installing apps specifically to GET hotspots to give me shortcuts in Windows either to launch apps, or to have some other type of control. In fact even now on Windows 7 the Start Menu itself can be considered a hotspot, I’ll often open it with my mouse not by delicately clicking in the middle of the round circle, but simply throwing my cursor into the bottom-left corner and clicking. I never click “on the menu”, it’s essentially a bottom-left hotspot. This is exactly the same in Windows 8, as the bottom-left corner hotspot takes you to the Start screen just as it takes you to the Start menu in Windows 7. Well then, what might the bottom-left hotspot do once you’re already ON the Start screen? It changes depending on past history. The bottom corner toggles the previous program used. So basically you can use the bottom-left corner to toggle between the “current” app and the Start screen. In other words, when NOT on the Start screen, bottom-left gets you there, when ON the Start screen, bottom-left returns to the previous app. The top-left corner will cycle your open apps backwards, going “back” to a previous app around in a circle. In this way the bottom and top corners have a little overlap in function but you’ll get used to it very quickly.

Windows 8 Start

It might be confusing at first but really, 10 seconds and it makes sense. When you first log in to Windows 8, you will be taken to the Start screen and you will not have any past applications, meaning the left hotspots won’t do anything! You actually have to type “desktop” or click the Desktop tile if you have it pinned. Once you’ve ran the desktop once, it will pretty much always be in your history unless you specifically close it from this menu. The menu doesn’t list all the applications individually from the Desktop, it only lists the Desktop itself. So let’s say I have Firefox and a couple other applications running on the Desktop, they will not all be listed in the app switcher, only Desktop itself. If you close the Desktop, it is removed from app switcher, but none of the programs close. If you open Desktop once again, all your apps are there where you left them.

In terms of swiping, this is important. Not everything is just a “click” in the corner, it’s a swipe as well. It is not complex. There are basically two swipe menus. The so-called “Charms” menu on the right, and the running apps menu on the left. If you throw your mouse cursor into the top-right corner and drag down, you get Charms. This will include a link to Start, and usually Search, and Share, and Settings. Swiping down the left edge, as mentioned, shows running apps you can switch to. Remember, a click in the corer performs a function, but throwing your cursor in the corner and dragging down also has a function. You don’t have to click and drag, just drag. For the right Charms menu, you can drag top down or bottom up. There is also the shortcut Win+C to open it from the keyboard.

Another swipe action is one you use while IN a full screen app (native Windows 8). These are apps that are built to work with Windows 8 specifically. They have the fancy icons on the Start screen, and they will usually only open in full screen mode, they have no “edges” to resize, no minimize and maximize buttons or border around them. Many apps are being converted to native Windows 8 functionality. When running these full screen apps, you can throw the cursor to the top of the screen and the cursor will change to a hand icon. Now click and drag down, you will have a few options at this point. If you drag all the way to the bottom, it closes the app. If you drag down and then to the left or right, it will pin the app vertically and not take up the whole screen. This lets you run two apps side-by-side. Note that you can do this arrangement running a Win8 app as well as the Desktop side-by-side. This is pretty much the only way to see a Modern UI app and a regular desktop app side-by-side. You can, of course, arrange the windows of normal apps just like normal on the desktop.

Windows 8 apps side by side

To properly get used to using Windows 8, it is almost essential to learn the shortcuts. If you are a natural keyboard user and don’t like to touch the mouse as much as possible, all your normal shortcut keys work. You can Win+R to quickly run something, Win+D to get to the desktop, Win+F to find apps, Win+W to search the control panel. Hitting Win key by itself will, of course, open the Start screen. If you simply begin typing from the Start screen, it will automatically search for apps. Win+C opens the right-side Charms menu. A quick search online will find you many shortcuts you can learn , for example Shift-F10 is the “right-click” if you are using arrow keys to browse around the Start screen and want to right-click the current selection. Also note that if you are in some kind of context menu, right-click menu, or even have the swipe menus open, pressing ESC gets you out of those types of things.

A handy feature for “power users” is Win+X which brings up a text menu of a few useful tools such as Control Panel link, Task Manager, Explorer, Command Prompt, Computer Management, Disk Management, Device Manager, Event Viewer, Programs and Features, and more.

When on the Start screen, you can scroll side to side by pushing your mouse cursor against the left and right edges; you can use the scroll wheel on the mouse, and you can use arrow keys on the keyboard.  You can simply begin typing the name of an app such as “calculator” to auto-search without opening the Charms first. At the bottom right you’ll see a small “minus” sign, this zooms the entire Start screen out so you can get a wider view of your icons, and rename “groups” for your icons as well. When you right-click (not on an icon), you can go to “All Apps” which gives you an icon list of all the apps. If, on this screen, you click the bottom right again, it “zooms out” of “All Apps” into an alternate way to find apps, by alphabetical order and category name.

If you drag an icon to the far right of Start, a vertical bar will appear, letting you create a new grouping. If you zoom out, you can right-click the new group to give it a new name. You can also rearrange groups when zoomed out by dragging and dropping the entire group. Icons themselves auto-shuffle when moved around. You also have the option of turning off “live tiles” from cycling through content if that gets annoying. Another handy feature of this Start is you can right-click an icon and completely uninstall the application directly, without having to go to Programs and Features to do it. The same applies to searching. If you go in Start and begin typing the app name, you can right-click the icon and uninstall, definitely saves time!

One of the things power users will have to get used to is where to find old tools. For example I used to always click Start Menu to go to Control Panel, but now it’s not quite the same. I also used the Start Menu properties to setup my shortcuts exactly the way I want. Now Control Panel isn’t found in Start! Even if you add System Tools to the Start screen, Control Panel isn’t one of the icons automatically added. However, there are many ways to get to Control Panel just the same:

  • In Explorer when you view the Desktop folder, Control Panel will be there. Note you can use Win+E to open Explorer.
  • Right-click the Desktop and choose Personalize->Change Desktop Icons, you can add it to the desktop here as an icon.
  • Open Start and type “control”, it will show in the list of apps found.
  • Type Win+X and choose Control Panel on this menu.
  • When you open Explorer (i.e. Computer), and have the ribbon open to the “Computer” tab, there is a button to “Open Control Panel” on the ribbon. The ribbon can be collapsed.
  • If you don’t like the ribbon, open it once, right-click on Control Panel choose to add to quick access toolbar, this will add an icon to the title bar area.
  • When in Start if you search for it, from the search results screen, right-click the icon and choose to Pin to Start, or Pin to Taskbar for quicker access.
  • If you’re on the Desktop, slide down the right edge and choose Settings. Control Panel will be there. This one is confusing because if you do this from Start, Control Panel is not an option, only from Desktop.

Lots of ways to get to old areas. Most programs can be treated this way. You can add to Start, add to Taskbar, create a shortcut, and all the usual methods of getting shortcuts to your apps. Another note for power users, you can still open Task Manager with CTRL-SHIFT-ESC, but this tool has had a major face lift with some fun changes. For example there is “App history” where you can see how much CPU time, Network bandwidth, or upload and download MBs and more, used by any given process that has previously run. The current system performance graphs are easier and more useful, such as the CPU now showing current GHz being used, and how many processes, threads, and handles there are.

Windows 8 Task Manager

Another “missing” feature I was looking for is Startup, as in the Startup folder in the old Start Menu. This is now found in Task Manager itself, which now has a Startup tab for this. It even shows sub-apps that are in startup running under a master app. An example here is that it shows Adobe Reader, but under that you see sub-apps like SpeedLauncher and Acrobat Manager. You can disable apps from startup here but not delete them entirely, this may still have to be done through registry. It doesn’t stop here though, you can add more than the default columns. On the Startup tab you can see how many milliseconds it takes the app to start, whether the app is currently running, the so-called “Startup impact” which gauges medium, low, or high impact on system resources. You can also see the Startup type which tells you if it is being called from Registry or what have you.

Another feature of Windows 8 is the option of logging in with your existing Microsoft account, this may be an @hotmail, @live, or @outlook Email address, or you can register a new address. You don’t have to do this, but it adds a few benefits such as being able to sync various system settings between other Win 8 systems you log in to with the same account. Also it can share necessary details with apps, providing authentication and so on. Lastly, you need to use an account anyway to use the app store, but it’s up to you if you want to use the account for logging in or not. If you don’t want the sync feature, it can actually be turned off entirely, OR you can specify which parts of the system to sync specifically.

I might note that using the touchpad on the laptop is actually NOT handier than just using a mouse. This might seem surprising because Windows 8 is supposed to be all about touch right? I find the mouse actually faster. This particular Sony laptop does translated the swipe-down, swipe-left and swipe-right movements into gestures on the trackpad, but if your system doesn’t have that, I’m not sure if Win8 does this automatically. For example it is very difficult to bring the cursor to the top, click and hold, then drag down using the trackpad, because this trackpad has the click button built-into it, not a separate button, so the cursor might jump around on me. However, Sony software translates a “swipe down” on the trackpad into the Win8 close function. It translates a swipe-right into the “back” click to cycle between apps. Nevertheless, I find the mouse easier and more accurate.

All in all, we’re talking maybe 20 or 30 minutes to get used to moving around. It isn’t “brainy” or difficult or confusing really. In fact, by learning shortcuts it can actually be faster than the old Start menu.

My recommendation is that Windows 8 will shine in the tablet market, but I think people will get used to using a mouse and it will be just fine as a desktop as well. The question is whether power users can still be power users, and whether all your old apps and hardware will work. If you don’t like the new Start screen, you can set up all your pin-to-taskbar and Desktop icons like before, and simply use Win+R to run stuff, Win+X for system stuff, Win+W for Control Panel apps, and as necessary a quick Win press then begin typing to find an app. Press Win+C to open Charms or just swipe the right side to get to settings and shut down and what not. You don’t have to use Start very much if you don’t want to, especially if you mostly use desktop apps and not Modern UI apps.

Let me know in comments what your initial thoughts are, and please be objective, in other words “I used to be able to do X, now I can’t/it’s slow/it’s not there/etc”. Also let us know your favorite Windows 8 shortcuts and tricks for navigating.

33% Wide Column Fix for Alignment Issues

There are many templates out there for creating responsive percent-based columns. Percentages like 10% or 50% or 25% work because they add up to 100% so they will align.
When you need columns split into thirds, for example, they will not line up perfectly by using 33% for width.

Let’s say you have your three columns 31% wide with 1% left and right margins. This only totals 99% of the space. If you have other “rows” using 50% or 25% and they equal 100%, this means your 99% columns will not align correctly.
In this image you see a simple form. In the above row, there are two fields at 50% each. Under it are 3 columns that total 99%. You can see the slight alignment problem.

33% wide misaligned columns

The fix basically involves finding a way to make just one of the columns 1% wider. I’m sure this can be accomplished in many different ways. Perhaps a single “fix” class applied to one of the columns? I found a simple way to add 1% to the width of the third element on the page with the css “nth-child-of-type” selector.

._33:nth-last-of-type(3n+3) {
width:32%;
}

The W3C says this is supported in all major browsers except IE8 and older. W3C Info.

Essentially it finds every 3rd appearance of the “_33” class and adds the 1%. This makes it automated as you don’t have to worry about adding an extra class as a fix.
Here you see the same image with the above CSS applied:

33% wide columns alignment fixed

This only works if you “always” use your 33% columns three times. Naturally if you had a 2/3rds column and a 1/3 column it would break. In that case, you can adapt this fix to your needs, use “fix” classes, or have a different class for a 1/3rd column when used with a 2/3rd column etc.

I hope you find this little trick useful as you create your responsive design!

Manage Podcasts on your MP3 Player with Podcastready

I wanted to share this little application with anybody who has an MP3 player that does not use proprietary software to manage files. If you can plug in your MP3 player and browse it like a hard drive, this is for you.

This program should be copied into whatever your "podcast" folder is. I have a COWON J3 and there is a "PodcastReady" folder right on the root of the drive. In here you would copy the app.

The way this works is, you would plug your MP3 player into your computer, go in the PodcastReady folder, start the program. From the program, you can subscribe to podcast feeds and download the lastest podcasts for your feeds. The downloads go in a subfolder, probably "Podcast".

So now any time you want to update your podcasts just run the app right off your player, on any computer, and it will update your podcasts and download them right onto the device. No need for separate programs to manage feeds, files, or syncing.

You are in luck if your MP3 allows you to delete files, some don’t. If you CAN delete files, simply delete them as you listen to them, and you’ll always keep your list current. If you have to manage files from the file system, you’ll have to figure out which podcasts belong to what show and find out how to delete just the podcasts you’ve listened to.

Visit the Podcastready website to download the app. http://www.podcastready.com/download.php

mypodder

Recover Threads and Posts in vBulletin

How to Selectively Recover Threads and Posts in vBulletin 4

You found this page because you made a foo bar on your vBulletin forum and wish to selectively recover threads and posts from a particular forum that was accidentally removed. I think I can help you do this! I had this project and I’m going to provide the steps and tools and queries I used to fix the situation.

You would follow this information if somehow you deleted or pruned threads from a forum accidentally and need them back but don’t want to restore the entire database and file system. Official vBulletin rules are that you should always restore the entire database if you need to recover information. The following procedures are certainly NOT official or endorsed and guess what, if you screw things up, it will only be your fault! Proceed if you are comfortable giving this a shot.

Things you will need:

  • A complete and working version of your backup forum that contains the missing threads/posts/attachments that you want to recover. It’s okay if the backup is older, you can only recover from your latest backup after all.
  • Moderate knowledge of working with MySQL queries and a means to export data from the queries. (I use Navicat as a DB manager). You will have to adapt this to working in phpMyAdmin or command line as needed.
  • A PHP server where you can upload the files from this page and run the included script. Since you have vBulletin, you already have a PHP server, but please use an isolated folder for testing and don’t try to run any of this on live folders! Note that when I performed this, I was using VB 4.1.3. My current and backup were both the same version. I cannot say how this will work on other versions but I imagine all 4.0 versions have a similar database design with few changes. I also suppose this will work best if your backup and current forum are the same version as well. If not, make sure the table structure is the same and adjust your SQL files accordingly.

 

STEP 1: (Backup Threads)

Things you will need:

  • Access to backup database and ability to run queries and export SQL files from it.
  • If you are a very very careful person, you may even want a third copy of your forum to test with. In other words, one server is running your backup, another server is running a copy of your current forum. This way you can test the imports before going live.

When I did this, I had just one backup server, I first set up my test server with the backup data. Then I pruned the forum to duplicate what happened on the main server, then I tested the re-import. However, I found it necessary to recover the backup data again for further testing, so having two test versions of your forum may be easier, or not. Depends how confident you are! It did waste a lot of time rebuilding my one test server.

FROM NOW ON, NOTHING HAPPENS ON YOUR LIVE SERVER EXCEPT TO LOOK UP AN ID WHEN NEEDED! DO BE CAREFUL!

To get the missing threads, we only need the ID of the forum, you can find this from the admin section of VB in the forum manager area. Click the Go button and read the ID as seen in the next image. Or just hover over the link to the forum and get the ID from the URL. I assume you already know how to do this stuff, just being thorough.

forumid-1

forumid-2

In the image, I see the ID is 202. Once you have the forum ID, we can do the bulk of our work. Our first MySQL query is quite simple, we just have to get all the threads that belong to this forum, no less, no more. The query looks like this:

SELECT thread.* FROM thread WHERE thread.forumid = 73

Where “73” is the ID of the forum you need to recover.

That’s it, you need to export this data into an SQL file and call it ‘thread.sql’. In Navicat, I use the Export Wizard to save the file. In phpMyAdmin you will open your database and click the SQL tab button at the top:

phpmyadmin-query

Check that the query works by noticing how many records it returns, it should return a similar number as the threads you are missing. Look at your backup forum and see how many threads it has, compare this number to your query count. Since the backup is older, don’t expect the numbers to be the same. I will leave it up to you to make sure your query returned what you want. Next you want to click the Export link in phpMyAdmin. Not the link under the results table, but the one in the “Query results operations”.

phpmyadmin-export

Use the correct Export link!

Moving right along, you need to set the settings as shown in the next image when you export. We’re trying to export with as little “extra” stuff as possible, all we want is the INSERT statements. Navicat can automatically make a pretty clean output, but phpMyAdmin will include a bunch of stuff that you should remove from the file. Here is a screenshot of the settings and what was changed is marked:

phpmyadmin-export-settings

Here is a shot of the extra stuff that you can remove from the sql file. Use a text editor like Notepad, if the size is too big (Notepad can’t open it), use something that can handle larger file sizes like Notepad++ or other text editor. Remove everything but the INSERT statements, that’s all we need to import the data.

edit-sql-file

In the image, you can see I’ve removed everything except the clean “INSERT INTO…” lines. Edit and save this SQL file.

Now you have a clean SQL file with the INSERT statement of all your lost threads! If you like, you can test this by importing to your second backup install of your forum if you created one. In other words, if you set up a forum to duplicate the data loss of your primary forum, you can test importing this now, refresh the forum, and see if the empty threads appear. The threads won’t have any posts, of course, but they should be there. Information about importing data is down below. You will also need to run the update forum information utility from the admin section to make the threads/posts count accurate. If you don’t have the second backup server to play with, just hold on and move to the next step.

STEP 2: (Backup Posts)

Things you will need:

  • Access to backup database and ability to run queries and export SQL file. Same as Step 1.

This query will return a crap load more data than the other. You may have only had some hundreds or a few thousand threads in the last query, but the post count will be much higher. When I did this recovery, there were near 40,000 posts. That being said, queries, exports and all that will be a little slower. Like before, we need to query the database to get the posts. This time, we want to grab all the posts that belong to any threads which belong to the single forum. This is a two part query. We do this by joining the post and thread tables. It looks like this:

SELECT post.* FROM post
JOIN thread ON post.threadid = thread.threadid
WHERE thread.forumid = 73

In order not to insult your intelligence, I’ll simply say that you need to export this into an SQL file like we did with the thread table. Name it ‘post.sql’ and save it in the same folder as the thread.sql file created earlier. Also like before, if you happen to have a second backup database set up, you can try to import the posts file, and be sure to edit the post file and remove the extra statements beside the INSERT statements. Now that we have the post and thread files, we have to do a more complicated thing, and that is get all the missing attachments that were purged from the deleted posts.

STEP 3: (Backup Attachments)

Things you will need:

  • Access to backup database and ability to run queries and export SQL file. Same as Step 1 and 2.
  • Access to the file system of the forum in order to download the attachments folder. This procedure applies to forums which are set up to save attachments to the file system and not to the database. If your forum is set to save attachments to the database, none of this will apply to you and I don’t have the procedure for you. I imagine it might even be easier, since it can likely be found with queries as well.

Here, we have to mess with the database as well as the file system. Another thing to note, if your backup is very recent, perhaps just a few days or a week, it may be worth your while to simply replace the entire attachments folder with the backup version. You may create some orphan files this way, or bring some attachments back from the dead, but it’s a small price to pay. My backup was a good 3 months old, and I didn’t want to restore that many orphan files, so I’m selectively restoring the attachment files as well, copying back only those files which belong to the threads in our recovered forum. If you need to selectively restore attachments, continue…

First lets get our data out of the database just like we did with the threads and posts. In this case we need combine our previous query and go a step further, after getting our posts that belong to the threads that belong to the forum, we need the attachments that belong to those posts. The query looks like this:

SELECT attachment.*
FROM attachment
JOIN post ON post.postid = attachment.contentid
WHERE post.attach > 0 AND post.threadid
IN
   (
   SELECT thread.threadid
   FROM thread
   WHERE thread.forumid = 73
   )

Here we join the attachments table to the post table by ID. Then we also only select those posts that have at least one attachment, and then we make sure the posts only belong to threads which are in our recovered forum. Note that your query will not return all posts, only posts which have attachments, so the records returned won’t seem to mesh with anything you can check. For example if you have 2000 threads with 8000 posts, you might have 2000 posts with attachments, but your result will not be 2000, since many posts might have more than one attachment. So it will return 2500, for example. Whatever the case, this number is hard to verify and you might just have to trust it and move on. Most likely, though, the number returned should be higher than our “post” query, unless for some reason none of your posts could possibly have more than one attachment.

Here is where everything changes. Export the data just like you did before, using phpMyAdmin or Navicat or something else. Edit the file and remove the extra commands beside the INSERT statements. Next you need to export the data again, but this time export as an Excel XLS file, or a CSV file. Again, this is simply a second export of the same data but in a different format. We need this file to prepare for use with the script I’ll provide in a minute. The export looks more like this:

phpmyadmin-export-attachment

Export this file as ‘attachment.csv’ and open it up in Excel. If you happen to not own a spreadsheet program, you shouldn’t run a forum! In the file, remove all columns except the yellow columns in the image below:

excel-output

You are keeping the “contentid” column and the “filedataid” column, that’s it. These are what we need to know where files are stored in the file system. Next rename the file from ‘attachment.csv’ to ‘list.txt’. I want you to open the txt file and remove the column names. Your text file should look exactly like the image below, but of course with different IDs and the column names removed.

remove-line

With the list.txt file saved and in the correct format, you will need to move it into the folder where you copy the script downloaded below. The script will use this list in order to search through your backup attachments folder to look for the matching attachments. That is part of step 4. Like before with the thread and post SQL files, those of you who have a secondary copy of your forum installed to test the imports, you can try to import the attachment.sql file into the database. It won’t necessarily “fix” anything, since the physical attachment files are missing, but you can make sure it places the attachment box on the post. Look down in step 4 for a before and after shot of a post with and without attachments data inserted.

Now unlike the post and thread queries, to restore attachments, we actually need TWO more sets of data. We now need to get the data from the attachmentcategoryuser table. To grab this data, we’ll use the following query:

SELECT attachmentcategoryuser.*
FROM attachmentcategoryuser
WHERE attachmentcategoryuser.filedataid
IN
   (
   SELECT attachment.filedataid
   FROM attachment
   JOIN post ON post.postid = attachment.contentid
   WHERE post.attach > 0
   AND post.threadid
   IN
      (
      SELECT thread.threadid
      FROM thread
      WHERE thread.forumid = 73
      )
  )

These queries are getting a little more tricky! This time we grab the data by matching the filedataid to the attachments, and matching attachments that match post which match threads to the forum. Again remember to change the forum ID to the one you are recovering! Same drill applies, export the sql, check the file for extra stuff, and set it aside for later testing/importing. If you are adventurous, you can go ahead and import it to your secondary backup test server. But wait, there’s more! We need a third set of data, this time from the filedata table, and this should be our last set of data to get things working again. Without this last set of data, you will see attachment IDs listed in posts, but the files still won’t display until this is in there too. Here is the query:

SELECT DISTINCT filedata.*
FROM filedata
JOIN attachment ON filedata.filedataid = attachment.filedataid
WHERE attachment.contentid
IN
   (
   SELECT post.postid
   FROM post
   JOIN thread ON post.threadid = thread.threadid
   WHERE thread.forumid = 73
   )

Here we select the filedata by linking it to attachments and only selecting attachments which are in posts which are in threads which are in our forum. It should return a similar number of records as the attachment and attachmentcategoryuser queries. All three queries will probably not return the same number of records, but may be close in proximity though. Export the query data and clean it up. Those with secondary test server can import it and skip the next step, you should now get my script and find any missing attachment files to copy. If you decided to just restore the entire backup attachment folder, likely everything is now working and you should go to the last step. For the rest of you, hold on to all your queries and lets go import them!

STEP 4: (Restore Data)

Things you will need:

  • Your post.sql, thread.sql, attachment.sql, attachmentcategoryuser.sql, and filedata.sql files.
  • Direct access to the database directly on the server (optional, faster import when local to MySQL server).

Now it’s time to go ahead and test importing the data. At this point you should have a copy of your forum running on a test server. This should basically be an exact copy of the live server (missing the same data of course). If you have two test installs, you’re good to go, start working with your live site mirror. If you only have one backup to work with (from which you gathered all the queries) you might want to either install a mirror of your live site to test with, or somehow duplicate the issue it has. For me, to duplicate my lost threads all I did was run a prune threads on the forum. Once the threads were all gone, I could test these imports. However, if you find your queries don’t work or you need to re-export them again, tough luck! Cause now you have no copy of the backup and you have to restore the backup database again to your test server. I actually had to do this twice during testing, restoring the backup, and pruning again. If you a little more adventurous, you could skip the tests and just trying importing all this on your live server, but of course you may want to backup the database first! You would only have to restore the database if something corrupts, since we aren’t changing the filesystem at this time.

Copy your sql files to the server or anywhere they need to be to import, in this case I find it’s easier to import through the MySQL command line interface. My test server was running from WAMP on Windows 7, so I’ll give the example of doing this from Windows, but it’s pretty much the same from a Linux box. Assuming your files on are C: drive, and your MySQL executable is in your %PATH% system variable. On Linux you should be able to run MySQL from anywhere, on Windows you may have to change to the directory where MySQL is installed. In any event, open a command prompt and log in to MySQL with something like  > mysql -u root –p.

Next select your database with the command use vbulletin_forum; If you don’t know the name of the database, for some odd reason, type show databases; OK, so assuming you started up MySQL, logged in, and selected to use your test database, you can now import your files in the following order with these commands:

source c:\thread.sql;  You’ll see some output as it imports the data.

And then

source c:\post.sql;  More output.

Next the attachment.sql file:

source c:\attachment.sql; Output…

Now attachmentcategoryuser.sql file:

source c:attachmentcategoryuser.sql; Output…

And finally the filedata.sql file:

source c:filedata.sql; Output…

Note that in the MySQL output it may occasionally say error because a duplicate ID exists, that’s alright, if it already exists, no problem! But for the most part, it should report that “1 row affected” as it inserts each row. I don’t have the time to deal with every problem that may arise, but there is a way to contact me at the bottom and I may start adding some tech support to this page as problems arise. If you do have issues, it is most likely going to come down to how your sql file is formatted, or perhaps with your MySQL user permissions or something. Luckily you are doing this on a test server first! And I’m serious about the formation of your sql file, you might notice that it puts the wrong table name inside, or the column names didn’t match, or who knows what. It might be worth while to mention at this point a particular quirk with Navicat. When you create the query and then save it, the name you used to save the query will become the table name inside the sql output! Don’t believe, me, check this out:

query-name

Notice I saved my query and named it “filedata from specific forum”.

query-name-block

Now when I export, it uses the same name, and I can’t change it here!

Here is what the sql file looks like after exporting:

query-name-wrong

As you can see, I had to rename my queries in Navicat to match the table name I was importing in to. If you happen to use phpMyAdmin for this, keep in mind about script execution times. If you are importing zillions of lines of data, it’s better to execute directly on server. But in my case I had less than 1300 lines and imports took a number of seconds, no big deal. If all your importing is a success, you should be able to refresh the forum (on test server) and see all the threads and posts now recovered. Attachments won’t be there if the physical files are still missing, we do that next. Just for kicks, here is an image of a post before importing the attachments sql:

before-attachment

And after import:

after-attachment

You can see after importing attachments sql file, the attachments are connected to the post, but clicking on one will cause a 404 because the actual file has not been recovered yet.

Next we will deal with finding the missing attachment files in the file system. And that should be our last bit of work!

STEP 5: (Restore Attachment Files)

Things you will need:

  • Your list.txt file created earlier and properly formatted.
  • This php script and files. 
  • A PHP server from which to run the script. (Optional) Compare utility to select just the orphan files from your backup (to save on disk space).

I know it’s odd to create this thing to run in PHP, but it’s just what I happened to do. If somebody wants to make a desktop app or something, be my guest! The PHP code is fairly simple so it won’t be hard to see how it works. Also, I don’t know how much the script can handle as far as work load, if you pass it a gazillion byte txt file I doubt it will work. Perhaps you would have to split your txt file into like 5k lines each or something and run it multiple times, I don’t know! Mine had 1286 lines and ran over 30 seconds or so. Note that PHP’s default max_execution_time is 30 seconds, meaning you might get about 900 files copied before it dies. However, I wrote the script so that it should stay alive if your server has a normal PHP setup. If you run PHP in Safe Mode, probably not. Unzip the folder to your server someplace where you can access the script. The folder is called “attachfind” and has two subfolders in it, “backup” and “restore”, as well as the index.php file.

The backup folder is for you to copy your attachment files in to. That is, the backup folder which contains the attachments you need to find. vBulletin stores attachments in folders named ‘1’, ‘2’, ‘3’ and so forth with many subfolders. So you should end up with a folder called backup with folders 1,2,3,4 inside. In other words, don’t copy vBulletin’s ‘attachment’ folder into backup, but rather copy the files/folders within their attachment folder. The script needs to look for folders such as “backup/1/1/4/6”. I Hope that makes sense. Here is a screen shot in Windows of what mine looks like:

backup-folder

The empty “restore” folder will contain your missing attachments after the script runs, these will need to be copied back to your server when done. Also, copy your list.txt file to the same folder as index.php. Once you have this folder on your server, your backup attachments files copied into the backup folder, your list.txt file copied in, you can browse to the attachfind folder on your server. The script will run and provide instructions from there including any errors it might have. If the script ran successfully, your restore folder will be populated with all the missing attachments to the recovered posts. These folders should then be merged into the attachments folder on your test server to check if it worked. The script will let you know of any errors. If you have issues running the script, check permissions on the script that it can write the folders etc. It will be up to you to copy the files to your test server and live server, my script only copies from the backup folder to the restore folder, that’s it.

STEP 6: FINISHED!

If you inserted the five sql files and copied the missing attachments, go ahead and run the Update Forum Information utility from VB backend. This will get the thread and post counts accurate. There you go! Test your test server, check if your missing threads/posts/attachments are back in the forum you were restoring. If it looks good and acceptable, you can take the plunge to do it all again on the live server! Naturally it would be a good idea to get a fresh backup of your live server first. Then go ahead and do the imports and copies and test again. If it worked, you’ll want yet another fresh backup of the server so that you can include the recovered threads and posts in your latest backup. The way I did the queries should prevent you from restoring orphan threads OR posts. It only grabbed posts which belonged to threads in the forum, and only grabbed threads that belong in the forum. Nevertheless, an operation this like may lead to unpredictable results. Were there any other tables in the DB that had reference data? Do other counters need updated? Will anything mess up when you try to install the next update to VB? Won’t know until you try! Note also that on my server, we don’t really use the CMS or Blogs, so if there was more to recover in relation to threads/posts with CMS and Blog, I’m not aware. This recovery is for the forum only, not for posts to blogs or articles.

I hope it worked for you! If you liked this document and it helped save your butt, feel free to buy me lunch with the donate button!