sql server new users: Backing up SQL files and NTBackup problem/questions. -- sql server new users

Backing up SQL files and NTBackup problem/questions.

Joe
1/17/2007 12:07:07 AM

sql server new users:

Recently I have had the good fortune to come into contact with several
server all using SQL, and I know >.< much about it so I have a few questions
if you will all forgive my ignorance.

SQL Version 8(2000?) on SBS 2003 Premium was installed onto a SBS 2003
server. NTbackup had been backing up for the past several months no problem
at all. Now it skips a lot of files as VSS does not operate. Some of the
files are files from System State I believe and some are the SQL files. I
have read that if I put SQL into Simple recovery mode that NTBackup will
back these up.
What is the difference between Simple, Full and bulk-logged?
What difference is there in the backups?
Which recovery method is best?
Why did NTBackup stop using VSS to back up the files is this because of SQL?
If it is, how do I now change it so it does back up the system state and the
SQL files?
Oh and last one.....what SQL files do backup, and which do I not?

Do the above answers also cover SQL 2005?

Thanks,

Joe

Re: Backing up SQL files and NTBackup problem/questions.

Dave Markle
1/21/2007 9:32:38 AM

1) First off, the answers apply to SQL 2005 and 2000.
2) *Don't use NTBACKUP* to back up SQL Server unless the SQL Server
service is turned OFF. You should use the BACKUP DATABASE command
instead.
3) Here's a good explanation of recovery models, compliments of Google:
http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true

The short of it: If you can't stand lost data and you are willing to put
a bit of time in to make sure that all of your transaction logs are
backed up, go with FULL. I recommend that all production servers use
FULL.

If you don't care about, say, a day's worth of lost data if the server
goes down, use SIMPLE and back up the database once a day. This is
usually only appropriate for developer workstations and maybe
development/QA servers.

HTH

--
-Dave Markle

http://www.markleconsulting.com/blog

[quoted text, click to view]