Solved

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

Posted on 2010-08-27
9
330 Views
Last Modified: 2012-05-10
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
Comment
Question by:AndrewW5
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 4

Expert Comment

by:pbarry1
ID: 33547206
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
 
LVL 4

Expert Comment

by:pbarry1
ID: 33547217
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
 
LVL 4

Author Comment

by:AndrewW5
ID: 33547242
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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 4

Accepted Solution

by:
pbarry1 earned 500 total points
ID: 33547273
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
 
LVL 4

Author Comment

by:AndrewW5
ID: 33547286
No, I don't at all.  Let me try that!
0
 
LVL 4

Author Comment

by:AndrewW5
ID: 33547310
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
 
LVL 4

Author Comment

by:AndrewW5
ID: 33547345
I'm in the plan wizard now.  It may be what I needed.
0
 
LVL 4

Assisted Solution

by:pbarry1
pbarry1 earned 500 total points
ID: 33547372
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
 
LVL 4

Expert Comment

by:pbarry1
ID: 33547386
And do the same for your LOG backups...  (either in a subplan or in another maintenance plan).
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Not selecting duplicate data 6 66
Why is the output of this function is like this? 4 44
Sql Server group by 10 51
How can I use this function? 3 34
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

726 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