SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Script change
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 04/02/2013 :  08:00:32  Show Profile  Reply with Quote
Hi all, I've got a script below which performs a restoration and deletion of the backup file. I'd like to split out this script so that the file deletion occurs ONLY if the restoration is successful.

Restore Database GAS_RMP_ARCH
--RESTORE FILELISTONLY
From
Disk = 'D:\GAS_RMP_ARCH.bak'

WITH
MOVE 'GAS_RMP_ARCH' TO 'X:\Archives\GAS_RMP_ARCH.mdf',
MOVE 'GAS_RMP_ARCH_log'TO 'Y:\Archives\GAS_RMP_ARCH.ldf',
STATS = 1
Exec master..xp_cmdshell ' DEL /Q D:\GAS_RMP_ARCH.bak'

ahmeds08
Aged Yak Warrior

India
534 Posts

Posted - 04/02/2013 :  08:13:08  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
as far as i know,this thing can be done through sql agent job in two steps.
1.create the backup job.
2.delete the old backup file.
deletion of backup file can be done through 'fortis' command line utility,this will ask for a parameter 'no of days' i.e when you pass the value as -1 for 'no of days' it will delete the file which is older than one day.

other thing using your script.
split the script into two job steps.
step1:create backup file.
step2.on success of step 1 go to step 2 i.e execute your delete command.

mohammad.javeed.ahmed@gmail.com

Edited by - ahmeds08 on 04/02/2013 08:18:20
Go to Top of Page

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 04/02/2013 :  08:16:37  Show Profile  Reply with Quote
I know that Ahmed, but this must be done scriptually.
Go to Top of Page

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 04/02/2013 :  11:34:26  Show Profile  Reply with Quote
Anyone?
Go to Top of Page

Wsql
Starting Member

5 Posts

Posted - 04/02/2013 :  11:41:13  Show Profile  Reply with Quote
After you did the right setting in the GUI then go to the script page and generate the script.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 04/02/2013 :  12:22:34  Show Profile  Visit russell's Homepage  Reply with Quote

BEGIN TRY
	Restore Database GAS_RMP_ARCH
	--RESTORE FILELISTONLY 
	From
	Disk = 'D:\GAS_RMP_ARCH.bak'

	WITH 
	MOVE 'GAS_RMP_ARCH' TO 'X:\Archives\GAS_RMP_ARCH.mdf',
	MOVE 'GAS_RMP_ARCH_log'TO 'Y:\Archives\GAS_RMP_ARCH.ldf',
	STATS = 1
END TRY

BEGIN CATCH
	print ERROR_MESSAGE() 
END CATCH

IF  ERROR_NUMBER() IS NULL
BEGIN
	EExec master..xp_cmdshell ' DEL /Q D:\GAS_RMP_ARCH.bak'
END
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000