• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

SQL 2005 Backup to network location, with multiple fulls and tlogs

All,

I am backing up my DB and t-logs to backup devices that refer to a network location (UNC.) They work well.

Each new daily full backup overwites the previous file.  This leaves me in an bad spot if the SQL server catches fire during the backup process, among other weaknesses.

What I'd prefer to do is backup the DB to a different file every day of the week, repeating.  So every Monday I'd back up to the Monday.bak.  If sever catches fire during this process, I restore Sunday.bak. Since the Monday.bak didn't complete, I should still have the t-logs running since Sunday.bak, right?

I am able to do this with multiple backup jobs pointing to multiple backup devices.  This is much work when expanded to include all of our DB across all our servers.

Is there a canned plan that can accomodate what we want?

Thanks in advance.

0
AndrewW5
Asked:
AndrewW5
  • 5
  • 4
2 Solutions
 
pbarry1Commented:
Hi,

Yes, just use a Backup Maintenance Plan (use the Maintenance Plan Wizard under "Management / Maintenance Plan) in SQL Server Management Studio.

You can configure the retention period, etc...
0
 
pbarry1Commented:
One thing to consider: make sure you have the latest SP installed.  Previous versions of SQL 2005 were very messy for these kind of maintenance plans.
0
 
AndrewW5Author Commented:
I have SP3 installed.  

I couldn't figure out how to apply retention period for my purposes.  In tests it just overwrote the same file.
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
pbarry1Commented:
The trick is to let SQL Server generate the filename (unless you really need to use specific names).  It will track which files it needs to delete or not.  

Do you really need to use specific names?
0
 
AndrewW5Author Commented:
No, I don't at all.  Let me try that!
0
 
AndrewW5Author Commented:
It requires me to set a file name.  I can't put a path without a file name in either Backup Devices or in Select Backup Destination. I must be going about this in the wrong way?
0
 
AndrewW5Author Commented:
I'm in the plan wizard now.  It may be what I needed.
0
 
pbarry1Commented:
Yes, don't use a backup device.  It won't do what you want to use.  Here's a summary of what to set in the Maintenance Plan:

Task "Backup Database":
- Backup Type: FULL
- Database(s): All databases (or the ones you want)
- Backup to Disk
- Create a backup file for every database
   - (Personnaly, I use "Create a sub-directory for each database)
   - Enter the root folder where you want SQL to backup your databases
Click OK

Task "Maintenance Clean up"
- Backups files
- File location: "Search folder and delete files bases on an extension" enter the root folder specified above and the extension
- Enter the aging to use.
0
 
pbarry1Commented:
And do the same for your LOG backups...  (either in a subplan or in another maintenance plan).
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now