Automatic backups for production databases without taking SQL server offline

346195 pts.
Tags:
Backup
SQL Server
SQL Server 2008
Sql Server 2008 Backup
I need to automatically create backups of my 42 production databases every night without taking my SQL Server 2008 offline. Is this possible? A full backup and transaction backup so I can step back to the exact time I need?

Answer Wiki

Thanks. We'll let you know when a new response is added.

You can set up nightly backups using the SQL Maintenance plan, then backup that flat file with whatever backup software you use for your other servers. There is backup software available that will back up live databases as well, but that would take research and planning to find the one you would like to use for this purpose. We use Tivoli Storage Manager’s Data Protection for SQL, but we have the entire TSM system up and running with TSM server and robotic libraries, and disk space to back up to. It is a rather large backup operation for many servers which we perform.

———

Yes, live backups can be taken using the BACKUP DATABASE command. This will backup the database to a binary file which you can then move to tape. To backup the transaction log use the BACKUP LOG command. You can schedule this to happen using any job scheduler including the SQL Server Agent which is installed by default when you install Microsoft SQL Server.

If you choose to use the maintenance plan all it does is use the BACKUP DATABASE and BACKUP LOG commands to backup the database and it uses the SQL Agent to do the scheduling.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • ITKE
    [...] 7. Mr. Denny and Technochic helped out with automatic backups for production databases without taking the SQL server offline. [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following