database backup procedure - SQL Server Forums

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

database backup procedure

Author

Topic

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-02 : 07:56:38

--------------------------------------------------------------------------------

hi
i found the procedure to take database backup as

CREATE Procedure Backup_Database
(
@dbName varchar(100),
@Path varchar(200) = '' OUTPUT
)
As
Begin
Declare @Now varchar(100)

if Isnull(@dbName,'') = ''
Begin
RaisError('No Database Name specified while running the procedure',16,1)
return
End
if isnull(@path,'') = ''
Begin
RaisError('No file Name specified while running the procedure',16,1)
return
End
-- Make the filename
SELECT @Now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')
Select @path = @Path + '\' + @dbName + '_' + @Now + '.Bak'
-- Take the backup of the database at the specified folder
Backup Database @dbName
To Disk = @Path
With Init
if @@Error <> 0
Begin
RaisError('Error Occurred while taking the Backup, Please check Error Log for Details',16,1)
return
End

Print 'Backup Taken Successfully at ' + @path
-- RETURN @path
End

GO

and iam executing procs as follows

exec Backup_Database 'Malathi','C:/New Folder (2)'

above executaion gives error as follows

Cannot open backup device 'C:\New Folder (2)\Malathi_20070502170331.Bak'. Operating system error 3(The system cannot find the path specified.).
BACKUP DATABASE is terminating abnormally.
Error Occurred while taking the Backup, Please check Error Log for Details

Malathi Rao

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-02 : 08:00:43

[code]
exec Backup_Database 'Malathi','C:\New Folder (2)'
[/code]

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-02 : 08:14:47

Make sure the SQL service account has the necessary (i.e. WRITE) permissions to the folder specified.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources