Backup Strategy Opinion For Real-Time Database

[Moebius01]

I'm in the process of deploying a realtime data system using a SQL 2k db as the backend. ~80% of the data has a lifespan of less than 60 seconds (most closer to 15-30).

I'm working on a backup strategy for the system, but am unsure of the best approach. For the tables that store the realtime data, I only need the table structure backed up, not the data within (as it would be end of life before the backup even completed), but need full backup of the system tables (user data, session data, etc) and stored procedures.

Could anyone offer some tips on what might be the best way to accomplish this? I was considering creating a 'Backup' database, then using some manner of script to copy the table structure only from the data tables, then full copies of the system tables and procs, but I'm not exactly sure how to handle this. Unfortunately, our company lost most of its DBAs recently, and the remaning ones are more Oracle proficient, so I've had to take this task on myself.

 

[SQLBill]

When you install SQL Server and create your database, immediately do a full backup of all the databases (including the system databases but not the TEMPDB).

Then when you make changes to any database (stored procedures, jobs, schema, logins, etc) backup all the system databases and the user databases.

But if you aren't making changes, a backup isn't needed.

-SQLBill

 

[SQLBill]

One change, once you have a full backup of all the databases, only backup the system databases when there are changes made. As for stored procedures, etc on the user database; there is an option in Enterprise Manager to create a script from the stored procedure (same as jobs). Use that and save your scripts.

If you have to restore, restore the system databases (master has the schema and login info, msdb has the job and alert information). Then restore your user database from the initial backup and finally run all your scripts to recreate your stored procedures.

-SQLBill

 

[Moebius01]

Thanks for the quick reply. One thing I failed to clarify there. The user data and dynamic data are both in the same DB (not my choice unfortunately), and the user data will change fairly frequently.

What I'm thinking I'll need to do is figure out a way to backup the table structure of the dynamic data tables, and backup the full tables of the user tables.

 

[JayKusch]

Interesting ... based on your current situation, you will not be able to "backup" specific tables within a DB. It is an all or nothing process as you have it now. Here are a couple of alternatives though:

1. Backup the the DB even w/ the tables that have data that will be obsolete even before the backup finishes.

2. If the static data tables are relatively small, you could create some scripts that are ran thru a job or DTS package that exports them to a file or another temp/backup DB structure.

3. If possible, create another filegroup and move your static tables into the new filegroup. You could then back up that specific filegroup and then use a DTS package to extract the schemas on the tables that hold the dynamic data.

You can gather from info on filegroups and how to back them up in BOL ... Books OnLine. Hope this helps some.

Thanks

J. Kusch

 

[SQLBill]

Yep, you didn't give me all the information. Jay already beat me to it, but here goes....SQL Server does not allow you to backup individual tables. It's the whole database or nothing.

One possibility, lets say your database is userdb. Create a second database (userdbbackup) and create it with only the user tables. Copy the user table data into that database and just back that up. (A better solution would be to just split the tables into two separate databases and join them in your queries.)

-SQLBill

 

[JayKusch]

As a side note though ... Imceda SQL LiteSpeed, which I have deployed to many of my larger sites w/ GERAT success, now offers a backup solution where you are able to "restore" specific objects from your backup. So you could use this tool to backup your whole database, w/ a copy of the dynamic data you really do not desire, but you would then be able to restore specific tables if you needed to.

This is a great tool!

Thanks

J. Kusch