We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

MSSQL 2008 Backup Script Help

Medium Priority
578 Views
Last Modified: 2012-05-11
We are currently running a nightly backup at 11:30pm with this script. (MSSQL 2008 on Windows 2008 Server)

declare @Path varchar(500) , @DBName varchar(128)
select @DBName = 'MyDataBase'
select @Path = 'B:\SQLBackup\MyDataBase\'
declare @FileName varchar(4000)
select @FileName = @Path + @DBName + '_Full_'
+ convert(varchar(8),getdate(),112) + '_'
+ replace(convert(varchar(8),getdate(),108),':','')
+ '.bak'
 backup database @DBName to disk = @FileName

It works fine except we continually have to go in and delete the old backups. We just want to keep a months worth of backups. Is there a script that we could add that would delete anything older then a month?
Thanks
Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
Easiest thing to do is create a maintenance plan and set it to remove backups older than XX days.  The command it executes is not documented by MS.  Here is the line from one of my maintenance plans

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL_DBs',N'bak',N'2011-03-30T12:38:29',1




Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
I had a question on this:

DECLARE @DATEValue DATETIME
SET @DATEValue = GETDATE() -30
print @DATEValue
EXECUTE master.dbo.xp_delete_file 0, N'b:\sqlbackup\aaa\',  N'bak',  @DATEValue, 1

It runs and prints the date but doesn't delete any *.bak files from b:\sqlbackup\aaa\ . Is there something i'm missing?
Thanks
Out of my own curiosity I took a look at this article where people also can't get the files to delete either: http://stackoverflow.com/questions/212603/sql-server-xp-delete-file-not-deleting-files. Contains some other interesting information about the subject of deleting from SQL Server.

Author

Commented:
Thanks for the link brutaldev however it still won't delete the *.bak files. I wonder if there has to be something turned on for rights from sql to the b:\ .
Do you have to delete from within SQL? If not use can use the batch file script I provided above along with create a Windows scheduled task. You won't run into permission errors since you can specify the user that the script runs under. Works every time...

You are probably right in thinking it's permissions because the command will run in the context of the logged in SQL service user. If you are using Windows Auth to login to the server you "should" be OK, something to consider?
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
Please refer link to get the last backup history
http://blog.sqlauthority.com/2010/11/10/sql-server-get-database-backup-history-for-a-single-database/

And delete the past backup as compare with date field of backup startdate or enddate

Author

Commented:
Ok It looks like a rights issue it runs fine if the backup folder is under:
C:\Program Files\Microsoft SQL Server\MSSQL10.myserver\MSSQL\Backup\aaa
but doesn't work under the mapped drive b:\sqlbackup\aaa

Author

Commented:
I got it working i had to add the rights for SQL\SQLServerMSSQLUser$sql$MYSERVER to the b:\ Drive.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.