SQL server 2005 Express Backup

Question

Post reply

SQL server 2005 Express Backup

sanya.ibrahim

SSC Veteran

Points: 286
More actions
December 11, 2009 at 10:58 am

#228535

Hello all,
I have an mdf file in the vb.net project I am involved in. I wanted to write an sp that will create a back up of the db in a selected location.
the sp uses following statements:-
USE master
GO
EXEC sp_addumpdevice 'disk', 'AdvWorksData',
'Z:\SQLServerBackups\AdvWorksData.bak';
GO
BACKUP DATABASE AdventureWorks TO AdvWorksData;
GO
when I execute it from SSMO after attaching the file, it works.
But this does not work when I call it from the application.
What can I do?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Atul DBA Hall of Fame Points: 3610 More actions · Answer 1

You need to make one Stored Procedure in your database and then call the stored procedure from your application code.

Hope this will help you.

paul.knibbs SSCoach Points: 15320 More actions · Answer 2

You could do this with a single SQL statement, rather than having to add a dump device:

BACKUP DATABASE AdventureWorks TO DISK='Z:\SQLServerBackups\AdvWorksData.bak'

Does that work?

sanya.ibrahim SSC Veteran Points: 286 More actions · Answer 3

I tried your suggestion. I am getting a error message that no entry could be found in the sysobjects with the name of the database. The mdf file that I am using is not attached to the SQL Server Express. Is that the reason? If so, is there any walk around?

paul.knibbs SSCoach Points: 15320 More actions · Answer 4

If the MDF isn't attached as a database you could back it up by just copying the file somewhere else--it won't be locked if SQL server isn't using it.

sanya.ibrahim SSC Veteran Points: 286 More actions · Answer 5

I am currently doing that only. Just wanted to check with experts if there is a possibility to back up using the standard methods...... Guess I have to resign with this method.....