Solved

SQL Server 2012 backups

Posted on 2015-01-27
5
99 Views
Last Modified: 2015-01-28
I am running a backup nightly but it saves multiple positions for each night. The size of the bak is now 121 GB. Can I  limit it to 5 last  instances?
 
Here is the script:

BACKUP DATABASE databasename
TO DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\databasename.bak'
 
Thank You
0
Comment
Question by:jem1662
5 Comments
 
LVL 25

Assisted Solution

by:Mohammed Khawaja
Mohammed Khawaja earned 167 total points
ID: 40573921
What I recommend for you to do is add -init to your script and it will overwrite.  If you are backing up the backups each night then you just need the last one.  By any chance if you want to keep just the last five then I suggest you change it to keep the last seven by running a batch file once a week to rename the file to something else.
0
 

Author Comment

by:jem1662
ID: 40573933
Thank You.  I will try it.
0
 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 166 total points
ID: 40574678
What I recommend for you to do is add -init to your script and it will overwrite
Be careful with this recommendation. If you do that you'll only have the last backup and you can't recover nothing before that day.

What you should do is a complete backup plan, i.e. backing up the databases and move old backup files to a safe place (another media in another location) so you can free space and keep old backups in the case you need them in the future.

Also, why are you having a single file to backup all databases? Recommendation is one file per database.

Please review your backup plan.
0
 
LVL 30

Accepted Solution

by:
Rich Weissler earned 167 total points
ID: 40575044
Concur with Vitor Montalvão.  While considering changing to your backup implementation, I can say that the Maintenance Plans built by the wizard will follow this recommendation.  If the Maintenance Plan capabilities in SQL is not to your liking, you can also look at Ola Hallengren's solution.

Consider also what would happen if the hard disk volumes were to become unavailable.  Are your backups to disk then backed up to tape, or to alternate storage?
0
 

Author Closing Comment

by:jem1662
ID: 40575060
Thank You all for the input.  We do one file per database.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need return values from a stored procedure 8 21
SQL Availablity Groups List 2 8
SQL Recursion 6 20
SQL Recursion schedule 13 19
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

830 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