Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Script change

Author  Topic 

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2013-04-02 : 08:00:32
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

737 Posts

Posted - 2013-04-02 : 08:13:08
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
Go to Top of Page

JaybeeSQL
Posting Yak Master

112 Posts

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

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2013-04-02 : 11:34:26
Anyone?
Go to Top of Page

Wsql
Starting Member

5 Posts

Posted - 2013-04-02 : 11:41:13
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

5072 Posts

Posted - 2013-04-02 : 12:22:34
[code]
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[/code]
Go to Top of Page
   

- Advertisement -