Solved

Backup Exec 2010 R2 - MS SQL Backups

Posted on 2010-11-11
2
1,054 Views
Last Modified: 2012-06-21
Hi,

I am currently using Backup Exec 2010 R2 to backup all of our servers.

I have created a selection list that includes all servers that have an instance
of Microsoft SQL installed on them and have selected all the LIVE databases to be backed up.

The majority of our databases are set to have recovery model of FULL as do not understand why you would want to use "simple" on production servers. Therefore I have created a Job Policy with two templates:

a). Full - Back up entire database or file group

If template "a" successfully completes run "b"

b). Log - Back up transaction log (need to run this to truncate the logs)

However, the problem I have is that template "b" compeltes with exceptions. The exceptions are that the "master" database only excepts "full" backups and "log" backups cannot be performed.

Am I able to exclude the masterlog.ldf from template "b"?

When I created my SQL Selection list I drilled down to the servers name and then selected "Microsoft SQL Server" and ticked the appropriate databases to be backed up. It did not show the log files here and I MUST backup the master database.

Thanks for your time and hope this can be resolved...
0
Comment
Question by:spen_lang
2 Comments
 
LVL 23

Accepted Solution

by:
Iamthecreator earned 500 total points
ID: 34110057
Final error: 0xe0000333 - Microsoft SQL Server does not support database  snapshots of the master or model system databases. Either remove these resources  from the selection list, or select another backup method
http://www.symantec.com/docs/TECH71095

Resolution:
You cannot take a database snapshot backup of Master,  model or TempDB databases.

For more on the limitations of Database  snapshot, please ref. the following documentation from Microsoft:
 http://msdn.microsoft.com/en-us/business/support/library/ms189940.aspx  

Note:
Applies to SQL 2005 onwards.



 About backing up  SQL databases
http://www.symantec.com/docs/HOWTO24056 
Backup Exec includes three methods for backing up databases: Full, Differential, and for SQL 2005 or later, Full Copy-only. The full method backs up the entire database including all system tables and filegroups. The differential method backs up only the changes made to the database since the last full backup. The copy method works in the same manner as the full method, except that it does not affect future differential or log backups.
A differential backup is smaller and faster than a full backup, so differential backups can be run more often than full backups. Because differential backups allow the restore of a system only to the point that the differential backup was created, you should also create multiple log backups between the differential backups. Using transaction log backups allows you to recover the database to the exact point of failure.
Consider using differential backups when only a relatively small amount of data changes between full backups, or if the same data changes often. Differential backups may also work well in your environment if you are using the simple recovery model and need backups more often, but cannot spare the time to do frequent full backups. If you are using the full or bulk-logged recovery models, you can use differential backups to decrease the time it takes to roll forward log backups when restoring a database.
If you want to run database backups only, instead of a mix of database and log backups, use the simple recovery model for the database so that the transaction log is automatically truncated when a checkpoint occurs in the database. This helps prevent transaction logs from becoming full since with other recovery models the logs are not cleared after a database backup.
With the simple recovery model, copies of the transactions are not stored in the log file, which prevents transaction log backups from being run.
If you do not run transaction log backups, you can recover the database to the point of the last backup, but you cannot restore the database to the point of failure or to a specific point in time.
The master database can only be backed up with the full method; you cannot use the log or differential methods to back up the master database.
Note:
You cannot back up databases to devices that are attached to a computer on which the Remote Media Agent for Linux Servers is installed.


0
 

Author Comment

by:spen_lang
ID: 34111829
Am I able to exclude the msterlog.ldf from the backup as do not want to have create an extra policy to backup the master databases...
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
need help in sql 4 66
SQL Query Syntax Join 4 34
Undelete of overwritten files 27 60
Sql Query with datetime 3 12
Are you looking to recover an email message or a contact you just deleted mistakenly? Or you are searching for a contact that you erased from your MS Outlook ‘Contacts’ folder and now realized that it was important.
In 2017, ransomware will become so virulent and widespread that if you aren’t a victim yourself, you will know someone who is.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now