July 23, 2012 at 8:20 am
I am using SQL 2008. I have two SQL servers. Server A and Server B. Server A is Prod server and Server B is a test server. Every night we do a refresh of the prod database copy. So copy database from Server A (Prod) and restores it on Server B (Test server). And I am doing it right every day. But now the help I need is after I have restored the database I need to delete that backup copy every day. So does anyone have any idea on how to do that. I am backing it mup on a network drive.
July 23, 2012 at 8:22 am
Why delete it? Does restoring mean that you'll never need to restore that backup again? What happens if the production DB fails and you need to restore full and log backups?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2012 at 8:30 am
GilaMonster (7/23/2012)
Why delete it? Does restoring mean that you'll never need to restore that backup again? What happens if the production DB fails and you need to restore full and log backups?
I think the OP is talking about deleting the copy of the backup file that is created on Server B to do the restore there. I don't think the OP is talking about the deleting the original backup file.
I used to do that when manually restoring a backup file on a test/development server as the copy/restore was faster than attempting to restore over the network.
July 23, 2012 at 8:55 am
actually we don't need that backup copy because we do refresh every day. I just want to delete because i need free space at all times. So any idea how would I accomplish that??
July 23, 2012 at 9:16 am
espanolanthony (7/23/2012)
actually we don't need that backup copy because we do refresh every day. I just want to delete because i need free space at all times. So any idea how would I accomplish that??
Are you deleting the backup file that is copied to server where the restore is done, or are you deleting all copies of the backup?
If you are deleting the copy of the backup from the development server after the copy/restore, just add a job step that uses the os cmd line to delete the backup file.
July 23, 2012 at 9:25 am
let me explain in detail sir.
Step 1 : Server A takes database to a network location. (Let's say Server C //test-dql_1\backups\Prod.bak)
Step 2 : The do a restore from Server B. ( Using the backup copy on Server C).
Step 3: Now here I would like to delete the backup copy on Server C.
I hope I have made myself clear. If you can get me a script I can create a step 3. Thank you for your time..
July 23, 2012 at 9:38 am
espanolanthony (7/23/2012)
let me explain in detail sir.Step 1 : Server A takes database to a network location. (Let's say Server C //test-dql_1\backups\Prod.bak)
Step 2 : The do a restore from Server B. ( Using the backup copy on Server C).
Step 3: Now here I would like to delete the backup copy on Server C.
I hope I have made myself clear. If you can get me a script I can create a step 3. Thank you for your time..
If you delete the backup file from Server C, what happens if the database on the production server crashes and you need to restore it?
From what you have just told me, I agree with Gail. Don't delete the backup file(s).
July 23, 2012 at 10:14 am
We already have the backup plan using DPM. This jobs that I have is just to refresh the test database nightly. So I am not at all worried about the Prod database if there's an issue and need to restore from a backup.
July 23, 2012 at 10:20 am
Then I have already told you what you need to do, create a job step to delete the backup file.
July 23, 2012 at 10:42 am
That's what i am asking. I don't know the script. If you have any please share...
July 23, 2012 at 10:50 am
Have a look at this fine BOL article: http://msdn.microsoft.com/en-us/library/ms190264.aspx
July 23, 2012 at 10:50 am
espanolanthony (7/23/2012)
That's what i am asking. I don't know the script. If you have any please share...
Use Google, you are looking for a delete command.
July 23, 2012 at 10:51 am
Is Server C a SQL Server? If so, use a maintenance plan to delete old backup files. Very simple.
If not, then you can search on this site for "delete old files" and find scripts. Here's one:
http://qa.sqlservercentral.com/scripts/archive/65209/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 23, 2012 at 1:49 pm
Anthony,
If this is done through a Maintenance Plan you should be able to add a Maintenance Cleanup Task just like the one I have attached. Just make sure you enter your appropriate settings.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply