Backing up databases and tlogs properly?

As I am learning more and more, I wanted to ask what most people think is the proper way to backup SQL DB and Tlogs.

Right now, I have one DB on the server (all the default DB's as well) and I have FULL recovery mode set.

Do most people use a script to backup the DB and TLogs?
Do they use enterprise manager?
Do they use maintenance plans?

I am planning on doing tlog backups every hour as that suits oure need, and full backups nightly.

What are some recommended approaches to doing this properly?

Thank you,
JasonHi Jason

Just so I know where you are coming from - are you unclear about some of the advice you got from SQLTeam or are you looking to get even more opinions on this? :)|||I use maintenance plans. Some pro's and some con's. Although I'm not a big fan of click-clack-done (I prefer scripting, like most DBA's do) this is a reasonable implementation by MS.
Biggest advantage in my humble opinion is that you can create backups with a pretty clear name, because the date/time is included by MS SQL. And still you can cleanup the files older than a certain time without having to script some kind of algorithm that decides what files to delete. It's not the most flexible sollution, but it works for me.

HTH a little.

Gr,
Yveau|||Hi Jason

Just so I know where you are coming from - are you unclear about some of the advice you got from SQLTeam or are you looking to get even more opinions on this? :)

Looking for more opinions to be honest.

I've heard so many things on this topic that it is confusing.

For example:

Don't use Maintenance wizard.
Use the maintenance wizard.
Don't use the Enterprise Manager. Use a script.
Don't use a script. Use EM.

Since I am very new, I am trying to compile all the information I can, experiences from others so I can make the best decision on what to do.

Sometimes I get really vague answers. Sometimes I get no answers. :S

In the end, it is about learning everything that I can. I want to be perfect and don't like to make mistakes.

Thanks.|||You'll get the same answers here too. I know posters that roll their own and others that use the maintenance plan here. Neither is a mistake and neither is perfect - you must make the decision as to what is best for your environment. The most important thing is you have thought about it, sought opinions, evaluated the repsonses, understood the issues and can explain your decision.

HTH|||You'll get the same answers here too. I know posters that roll their own and others that use the maintenance plan here. Neither is a mistake and neither is perfect - you must make the decision as to what is best for your environment. The most important thing is you have thought about it, sought opinions, evaluated the repsonses, understood the issues and can explain your decision.

HTH

Thanks. I appreciate it very much.

I just don't like making mistakes. :D I tend to be as thorough as I can be.

Right now, I am evaluating so many options, but one thing I have noticed immediately is that we need more space on our SQL server. :S

One question though:
After you initially backup a LARGE TLOG, will the following backups be smaller, assuming you back it up more frequently?

Just wanted to make sure.
Thanks,|||Assuming a constant rate of activity on your database ... yes. After a translog has been backed up, it starts from scratch again. So more frequent backups will give you more files, but smaller. Doing the backups less frequent will give you less files, but larger. In the end, the amount of space needed for only the backups is the same ...

If everything (MDF, LDF and backups are on the same discs (not recommended though), you're better of with frequent backups, because the LDF file can be seen as a 'copy' of the yet not made last translog backup. Keeping that small gives you some space ... but I don't think we're talking huge amounts of GB's here ...

When you've go yourself a db that does not have a constant rate of activity, there is a different story, because the LDF file grows along with the activity on the db.

Gr,
Yveau|||Another small issue I've been bumping into ... run the backup jobs as a SQL login, not a domain account ... you do need mixed mode security for this option.

When you have a network failure, the credentials for the domain account can't be checked and the backups will fail !!! Running under a SQL login (and please, do NOT use the 'sa' !!!) will create the backups, even when the domain controllers are not reachable for the SQL server.

Just a small issue to take into consideration ...

Gr,
Yveau

Hmm, reading it I think this might have been better placed in your 'Backing up databases and tlogs properly?' thread. Sorry ...

PatP: Ask for a move, and ye shall receive!|||Another small issue I've been bumping into ... run the backup jobs as a SQL login, not a domain account ... you do need mixed mode security for this option.

When you have a network failure, the credentials for the domain account can't be checked and the backups will fail !!! Running under a SQL login (and please, do NOT use the 'sa' !!!) will create the backups, even when the domain controllers are not reachable for the SQL server.

Just a small issue to take into consideration ...

Gr,
Yveau

Hmm, reading it I think this might have been better placed in your 'Backing up databases and tlogs properly?' thread. Sorry ...

PatP: Ask for a move, and ye shall receive!

One of the things I did was create a specific new account for SQL to run under. Right now, because my SQL boxes are NOT in the Domain, I created the accounts locally, and then mirroed them on the file servers. I made sure to include them in the 'administrators' account. During my test, it seems to work.

Is that ok?

No way would i use the 'sa' account. I come from a pretty strong security background (Security is a huge priority for me), so I stayed away from that route. :beer:|||That's one of many good options.
I usually create a SQL login (not a Windows account) something like 'BackupUser' and give it the 'db_backupoperator' database role. I tend to use one BackupUser for each server, but if you insist, you can use one per database. That login will only be able to do backup related statements. Then create a (set of) job(s) that run the backups at a predefined schedule and have the BackupUser run this job. The backup files will be written to the (file)server using the account that is running the SQL Server service, so set the permissions for that account on the backup disk location. Note that the BackupUser SQL login has nothing to do with the Windows account that is used to run the SQL Server.

In my humble opinion this is a pretty simple (no Windows related issues) an secure way (BackupUser can do only Backup related stuff in your db's) of setting up a backup scenario.

Good job avoiding the tempting 'sa' sollution ! Don't forget to test if you can restore the backups you create, otherwise you might be in for an unpleasant suprise when disaster strikes ...

Happy backup and restoring !

Gr,
Yveau