Selva Sharing

SQL Server 2008 Database Administration Part 1
SQL Server 2008 Database Administration Part 2
SQL Server 2008 Database Administration Part 3
SQL Server 2008 Database Administration Part 4
SQL Server 2008 Database Administration Part 5
SQL Server 2008 Database Administration Part 6
SQL Server 2008 Database Administration Part 7
SQL Server 2008 Database Administration Part 8
SQL Server 2008 Database Administration Part 9

DVD2

OK, let me explain a couple of these items.

However, you don’t want to backup your database by hand every time so you want to schedule this to run on its own.  And for this you’re going to put the above code into a job and schedule it.  The easiest way to do this is to create a job in SSMS.  Follow these steps to create the job and put your backup statement inside of it.

Expand the ‘SQL Server Agent’ tree and right-click on ‘Jobs’.  Then choose the top item, ‘New Job…’

Now you’ve got the new job dialog box.  Filling in the info is pretty easy.  You need to give your job a name, and everything else is optional.  Here I’m going to fill in the name of the job as ‘Backup user database’.

Next click on the ‘Steps’ pane on the left and you’ll be presented with this screen.  It’s blank because you haven’t created any steps yet.  So go ahead and click on the ‘New’ button at the bottom.

This is where the real magic happens.  Again, you have to fill in a name so you know what the step is called.  Make it something descriptive.  There are several step types to choose from, but the default is T-SQL and since we’re running a T-SQL command that’s clearly the one we want to go with.  The database defaults to ‘master’ and that’s just fine with us.  Here’s what we have so far.  You see the only thing we’ve had to do is fill in the step name.

The only thing left to do is to copy your backup statement into the query window.  Of course it’s always a good idea to make sure your code will parse before you try to run it.  Just click the ‘Parse’ button I’ve circled.  Here’s what that looks like.

Now click OK and it’ll take you back to your new job window and now you’ll see your job step listed.  And you can stack as many as you like inside there.

To schedule your job, just click on ‘Schedules’ on the left and then choose the schedule that’s right for you.  It works similar to the the way it does in Windows so there’s really not much need for me to rehash it here.

Once you click OK all the way out until the new job box is closed, your job will be added to the job tree.  You may have to right-click on ‘Jobs’ and refresh the display for it to show up.

Now that you’ve created the job to backup your database I’d like to go back to the command again and add one more part.  See, when you backup a database to a file it won’t overwrite the file by default.  By default it will append to the file, so your backup file will just get bigger and bigger.  So what you want to do is add a special flag to the command that tells it to initialize (or overwrite) the file every time.  That makes our backup command look like this

now:
BACKUP Database Model

With INIT
This is a good thing to keep in mind while you’re creating your job.

But what if you wanted to backup more than one database?  Well, again, there are several ways to do this and I’m going to show you the easiest just to get you going.

There are 2 methods we’re going to discuss here:


1.  In the job step you just created, you can just stack backup commands one after another.  So the window that holds your backup code inside the step could easily hold the code for several databases.  That would look something like this:
2.  The other way you could do it is to add each one of your backup commands to their own step by repeating from step 3 above.

Here I’ve shown you how to create a simple backup job.  Most tutorials will show you how to use the backup wizard, but that’s ridiculous because you’ll never do that in production.  Nobody wants to manually backup their databases every day.

Notice how much info Microsoft gives you right away.  Over on the left there are menu choices and the options on the right change as you go through the menus.  You want to click on the Installation menu on the left to be presented with this screen.

2.  You can see there are many options here, but in this case the only one that makes sense is to click the top option for a stand-alone install.  Click that option.

3.  Once you choose the stand-alone install, setup will install the setup support files.  It does this every time you run the installer so don’t be surprised if you run it again and again and it installs them every time.  At this point setup will also run some checks to make sure that your system is ready to run through the install process.  If it’s not and something fails, you’ll see an error similar to the one below.  In my case I have a reboot pending and setup will not continue until I reboot.
I might also add that if you have to reboot, once your server comes back up setup should continue on its own and re-run this check and if all errors have been eliminated, it should present you with the above screen just as if there had never been a problem.

4.  Click OK.  Next you will be presented with the setup support rules page.  This is another set of checks that tells you whether things will complete and work properly once setup completes.  The screen looks like this.
And again, warnings can be ok, but if there are any errors you’ll need to resolve them before moving forward.  I’ll talk more about the kinds of things that can go wrong with setup in another article.

5.   Now it’s time to choose the installation type.  Are you installing a new instance of SQL Server 2008, or adding to an existing instance?