newbie backup questions

[IT247]

hello

I will be responsible for backups/recovery for our new DB/app running on sql 2000.
Now, what i need to know is
1)In enterprise mgr (EM) when i right click the db name and do "backup DB", what default options are used. What I mean is that when I look at setting up a maint plan, there are several options one can play around with.

2)We have a small DB - does it pay to set up a maint plan?
why not just do a "backup db" - full backup.

3)how would i use tsl to schedule that?

4)can such a backup be done even when data is being accessed by users?

thx a lot

 

[ptheriault]

IT247,
When trying to set up a backup of a database you have to find out a few things.
1. How transactional is your db? Is the data constantly being changed.
2. Do you need to be able to recover to a point in time?

Once you know the answer to these questions you can set the Recovery Model in the options page of Database properties.
The simple recovery model will truncate your log when the db checkpoints. This option is very good for small databases or databases that have static data. If you need to be able to recovery to a piont in time then you need to set you db to FULL recovery and backup your Transaction log as well as your db.
Backups in SQL server are on-line. Meaning that you can access your data while the backup is running. However, I wouldn't suggest that you schedule your backups to run in the middle of the day because that will be take up IO.
I'm not much of a fan of maint plans for the following reason. If you have 5 db's listed and in your plan and it fails on db 1 then 2 - 5 are not backed up. I prefer to create a backup job for each db. That way I can manage them all seperatly.

Your backup are going to be scheduled using SQL Server Agent.

 

[Catadmin]

IT247,

Okay. In order.

1)In enterprise mgr (EM) when i right click the db name and do "backup DB", what default options are used. What I mean is that when I look at setting up a maint plan, there are several options one can play around with.

When you do a backup DB from EM, it also has several options on it. Use Books Online, the Index tab, and type in BACKUP. There are a number of different backup options and what you do with them entirely depends on your business needs. The two most commonly used types are Transaction Log Backups and Full/Complete Database Backups. With a small database (Q#2), it's doubtful you will need an Incremental or Differential backup.

2)We have a small DB - does it pay to set up a maint plan?
why not just do a "backup db" - full backup.

Do you really want to spend 10-15 minutes every day doing a manual backup of your DB? Personally, I have other issues to deal with that I need that extra time, so I have our backups scheduled. Also, you need to verify with your customers how much data they can stand to lose if the DB goes down. If they need Point In Time restores, you have to add transaction backups to your maint. plan or you are in serious trouble when the server crashes.

3)how would i use tsl to schedule that?

Use BOOKS Online (BOL) and search for BACKUP. It has all the T-SQL you want. Also, check the FAQ section for this forum and for the Microsoft SQL Server: Programming forum.
You can schedule a job to run T-SQL code and put the backup code in there if you don't want to use an official db maintenence plan. My answer to #2 is meant to cover either the official DBMP or the use of a scheduled T-SQL job. The tool is up to you.

4)can such a backup be done even when data is being accessed by users?

It can and if your database really is small, you probably won't notice a performance hit. However, the larger a DB gets, the more likely you'll want to do the backup in the off hours just so things don't get slowed down. Also, there is a locking issue sometimes with SQL that could prevent your backup from completing correctly when users are on the DB. Alter Database commands could cause the backup to fail, for instance.

Hope this helps.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[IT247]

thank you both - great info

our DB takes maybe 2-3 minutes to backup - it is small!
and it is set to "full"
but i'm a lil confused about tran logs. When i do a "backup db" the tran logs get backed up correct?
I do not expect transactions to be high since only a few users will actually perform data entry - most will only need to access data for reports.

thx

 

[ptheriault]

If that is the case you should set your recovery model to simple.
When you backup your database it doesn't backup your transaction log. When your database is set to simple recovery it will purge committed transactions. If your database is set to full recovery and you never back it up committed transactions will remain in your log. This will cause your log to grow very large. In fact it will continue to grow till it uses up all the disk space or reaches it's growth limit if you have set one. Either way once your tlog reaches a point where it can't grow anymore then nothing can happen on that db till the log is truncated.
I would do this.
1 set your db to simple recovery.
2 create a job to backup the db every night. (if you backup files to tape schedule your db copy before the tape backup runs and include your *.bak files in the tape backup.)
3. don't forget to backup your master and msdb databases. You won't be able to restore the server unless you have those system databases as well.
Here is an example of how to backup a database.
BACKUP DATABASE [msdb] TO [msdbDataDump] WITH DESCRIPTION = N'Backup Database - msdb', NOFORMAT, INIT, NAME = N'Backup Database - msdb', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
I use devices instead of files. You can replace the [msdbDataDump] with a path to a file. like this.
'F:\program files\microsoft SQL Server\mssql\backup\msdb.bak'

I like devices because I can store more than one backup in the device.

 

[IT247]

hello
thx for your replies

I'm not understanding what is included in a DB backup
That is when i do a right click on a db and choose "all tasks/backup database" and a file is created upon completion, what is in it? (you say no transaction logs are in that file?)

 

[Catadmin]

Actually, according to SQL Server 2000 BOL under the heading "Backup Types supported", I find the following entry:

Backup Types
Backup types supported by SQL Server include:

Full database backup, which backs up the entire database including the transaction log.

Differential database backup performed between full database backups.

Transaction log backup.
A sequence of log backups provides for a continuous chain of transaction information to support recovery forward from database, differential, or file backups.

File(s) and Filegroup(s) backup.

However, ptheriault is correct in that if you don't do regular TransLog backups, the log will continue to grow, especially if you don't have "Truncate Log on Checkpoint" chosen as a database option.

Truncating the Transaction Log

If log records were never deleted from the transaction log, the logical log would grow until it filled all the available space on the disks holding the physical log files. At some point in time, old log records no longer necessary for recovering or restoring a database must be deleted to make way for new log records. The process of deleting these log records to reduce the size of the logical log is called truncating the log.

Does this help?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[IT247]

yes
except that i'm thinking - when the backup is taking place, what happens to transactions that are occurring at that time?

does the sql backup process use some sort of snapshot on the logs?