Backup Database

[scabral79]

Is there a way to use the Backup Database command to backup a database to disk from a stored proc?

for example, if you try to run the following command from a stored proc:

Backup database ABLTEST to disk='e:\mssql\mssql\backup\Daily\ABLTest.bak'

I get the following error:

Incorrect syntax near 'Backup'.

Basically, what i need to do is figure out a way to backup the database either by using stored proc or dts package, but NOT using the sql scheduler.

thanks
scott

 

[ptheriault]

yes you can.
I used a backup device.

I just created this quick test and it worked.

create proc backupdb
as
begin
BACKUP DATABASE DBASaveObjects to [backup]
end

Create a backup device and try it again.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

USE master
GO

EXEC sp_addumpdevice 'disk', 'ABLTEST_1','e:\mssql\mssql\backup\Daily\ABLTest.bak'


BACKUP DATABASE ABLTEST TO ABLTEST_1

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

I also tested this, but with the To Disk syntax, and it worked for me.

Can you post the entire contents of your SP so we can have a better look?

-George

"the screen with the little boxes in the window." - Moron

 

[scabral79]

here is my syntax. I created a backup device call Backup:

CREATE PROCEDURE [dbo].[clo_daily_backup] AS

begin
BACKUP DATABASE ABLTEST to Backup
end

I am still getting the following:
Server: Msg 156, Level 15, State 1, Procedure clo_daily_backup, Line 5
Incorrect syntax near the keyword 'Backup'.

 

[ptheriault]

you need [ ] brackets around backup. Did you create the device backup? Use the example that Denis posted?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[aspvbnetnerd]

BACKUP DATABASE ABLTEST TO DISK='e:\mssql\mssql\backup\Daily\ABLTest.bak'
  WITH DESCRIPTION = 'Manual ABLTEST Backup', RETAINDAYS = 0, NOFORMAT, INIT, NAME = 'ABLTEST', NOSKIP, RESTART

 

[scabral79]

Can this stored procedure be created in the database that i am backing up or does it have to be created in master?