?
Solved

MSSQL 2008 Backup Script Help

Posted on 2011-04-27
10
Medium Priority
?
570 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
0
Comment
Question by:CityInfoSys
10 Comments
 
LVL 2

Accepted Solution

by:
Umesh_Madap earned 1000 total points
ID: 35478494
hi if u have created scheduled job for backup u can add the delete step in to the job and you can customise the dates to have backups
below is the code u can add to backup job in delete step for master databse


DECLARE @DATEValue DATETIME
SET @DATEValue = GETDATE() -1 --Replace this value for number of days old file to be deleted
print @DATEValue
EXECUTE master.dbo.xp_delete_file 0, N'O:\MP1Bak\MSSQL2008\backups\user\', N'bak', @DATEValue, 1
0
 
LVL 18

Expert Comment

by:lludden
ID: 35478503
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




0
 
LVL 11

Assisted Solution

by:brutaldev
brutaldev earned 1000 total points
ID: 35478556
1. Create a batch file with the following code in it (this deletes files older than 30 days in the path, using the pattern...):
 
rem This will just tell you which files will be removed, uncomment the script below to perform the actual deletion.
forfiles /p "B:\SQLBackup\MyDataBase\" /m *_Full_*.bak /d -30 /c "cmd /c echo @file will be deleted..."

rem The real delete script.
rem forfiles /p "B:\SQLBackup\MyDataBase\" /m *_Full_*.bak /d -30 /c "cmd /c del @file"

Open in new window

2. Setup a Windows scheduled task to run this batch file once a day.

NB: Please use the echo command commented out to see which files will be deleted before trying the actual delete command just for peace of mind.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 2

Author Comment

by:CityInfoSys
ID: 35479577
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
0
 
LVL 11

Expert Comment

by:brutaldev
ID: 35479710
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.
0
 
LVL 2

Author Comment

by:CityInfoSys
ID: 35479748
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:\ .
0
 
LVL 11

Expert Comment

by:brutaldev
ID: 35481199
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?
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35481511
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
0
 
LVL 2

Author Comment

by:CityInfoSys
ID: 35485033
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
0
 
LVL 2

Author Comment

by:CityInfoSys
ID: 35485093
I got it working i had to add the rights for SQL\SQLServerMSSQLUser$sql$MYSERVER to the b:\ Drive.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
Blockchain technology enhances society similar to the Internet. Its effects are broad, disruptive, and will boost global productivity.
Viewers will learn how the fundamental information of how to create a table.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

621 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