While the native backup in SBS backs up everything on the box, sometimes you want to be a bit more… well… paranoid about things.  So one of the things you can do is script extral backups of the Line of business apps that run on SQL.  Now if we had the full SBS 2008 premium this process is trivial as there’s a management piece that you can in the GUI set up the backup…but… since I’m backing up something on the SBS box anyway, we’re going to do this with the native express on SBS 2008 standard.

And we do this by using the GUI to build us a script… yes… a GUI to then build a command line…

So click Start

Find the SQL management and Runas Admin.

Find the SQL instance we want to back up.  Now why SQL calls them “instances” I’ll never know.  they should be called.. SQL databases, or SQL things, but not “instances”..but I digress…

When you launch the SQL management you can see that there’s your database in there

Right mouse click on the database, then click on tasks, then click on backup.

Set up the backup you want (full) and to the location you want it to go to.

Up at the top there’s a little button called “script”

And the resulting script looks something like this —

BACKUP DATABASE [SQL_DATA] TO  DISK = N’E:\\Backup\SQL’ WITH NOFORMAT, NOINIT,  NAME = N’SQL_DATA-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Save it as Backup.sql

Now we’re on to part two.

How do you backup a database using sql server 2005 express???:
http://social.msdn.microsoft.com/forums/en-US/sqlexpress/thread/95750bdf-fcb1-45bf-9247-d7c0e1b9c8d2/

As you read through this you finally understand what Steve Foster was muttering about all those years ago in the versions between SQL 2000 and SQL 2005 where they took out the job scheduler in SQL express that used to be in there.  For the record it is kinda a bummer this is way more complicated than it needs to be in SQL 2005 express but I digress again.

sqlcmd -S LOCALHOST\SQLEXPRESS -i backup.sql -o output.txt

We need to build something like that and save it to a .bat file and place it next to the backup.sql you saved previously.  I called it sql.bat (I know real original)

Now click it to see if it runs correctly.  If it did, it will build an output.txt file.  Open it up and it should be successful

0 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 12272 pages for database ‘SQL_DATA, file ‘Practice’ on file 1.
100 percent processed.
Processed 1 pages for database ‘SQL_Data, file ‘PracticeLog’ on file 1.
BACKUP DATABASE successfully processed 12273 pages in 2.456 seconds (40.936 MB/sec).

Now go to your task scheduler on SBS 2008 and build a task to do this as often as you like.

Start/search box and type in task scheduler.  Right mouse click on the task scheduler local and build a basic task

Name it something descriptive and click next.  In my case I want it to run daily and reoccur every day.

I want it to start a program (the SQL.bat file I wrote earlier), and I’m going to click on the Open properties dialog box to review the task and click finish.

Now that the properties box is open, I’m going to click the box to allow the task to run if I’m logged in or not.  You will need to enter in admin credential here.

And voila…

Task Scheduler successfully finished “{81d64fd5-8e57-4430-ad2c-d50e49a2cf62}” instance of the “\Backup SQL” task

You now have a task that runs an independent backup of SQL for extra paranoia.

 

4 Responses to Being a bit more paranoid about your SQL databases

  1. Mikkie says:

    Look at the easiest way to backup and restore MS SQL Server databases: sqlbackupandftp.com

  2. Phil says:

    Hi Susan,

    This is the sql script I use to backup SQL Express databases.
    ——————————————————————-
    declare @DBName as varchar(50)
    declare @BackupPath as varchar(50)
    — Enter the database name to be backed up and the location to store the backup files
    — Repeat the next three lines for each database if there is more than one.
    set @DBName = ‘AMTRemote’
    set @BackupPath = ‘c:\MaxData\SQLBackup\CRMBackup.bak’
    backup database @dbname to disk = @backuppath with init
    ———————————————–

    The major difference from what you’ve posted is withinit versus noinit. If you use noinit then the subsequent uses of the script append the backup to the backup output file, which just grows and grows. Withinit initialises the file back to zero before appending the backup.

    The use of the term “sql instance” comes from really awful programmer speak. They talk about instantiating an object ie creating an instance of it.

    In the case of SQL Server you are creating an instance of SQL Server and can have a number of different instances of SQL Server running on each Windows Server. My personal opinion is that good practise is to create a separate instance for each major application that uses it. This allow you to have isolated credentials and also to restart the instance independently of the other applications.

    eg. on SBS 2008 you might have separate instances for SBS monitoring, Sharepoint 2010, Quickbooks, Backup Exec, Maximizer CRM….

    Regards,

    Phil
    Sydney SBS User Group

  3. bradley says:

    I think I’ll revisit my script. 🙂 For the record QB doesn’t use SQL but the rest –thank you very much!

  4. Dean says:

    To expand on Phils comment an Instance is like a seperate installation of SQL server. If you have previously installed a version of SQL Server and you run the install again it will ask you if you want to install another Instance. If you do it will ask for a name for the Instance. It will then proceed to set up the Instance. Only the first Instance ( the one created the first time SQL Server setup is run) is the DEFAULT or standard Instance. It’s the Instance that uses the standard SQL Server TCP port. Any other Instances will be assigned a dynamic TCP port. That could be a problem in some circumstances and then you would need to manually assign a TCP port.

    http://www.databasejournal.com/features/mssql/article.php/3689846/Using-Non-Standard-Port-for-SQL-Server.htm

    But most of the time a dynamic port assignment will work.

    Each Instance can have many databases assigned to it.

    There are different reasons why you may want to use more than one instance like the one app one instance reason Phil mentioned. Each Instance can also be on a different service pack level if an app needs that. Each instance will also have it’s own memory space and settings. If one Instance crashes the others should keep running.

    If you have different versions of SQL Server installed on the same server then each one of those versions default Instances or Named Instances will also be a seperate Instance.

    The trade off as always is more complexity. More settings to check. More things to service pack. More things that can go wrong. More memory used.

    To remove an instance run the setup program again.