make a backup with t-sql - 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

make a backup with t-sql

Author

Topic

fabianus76
Posting Yak Master

191 Posts

Posted - 2007-05-05 : 06:43:29

Hello!

I would like to backup a database through t-sql into a file.

I do this :

EXEC sp_addumpdevice 'disk', 'mydb','c:\mydb.bak'

it tells "Command(s) completed successfully."
but I can not find the file c:\mydb.bak ...

but if I do it again I get :

"Msg 15026, Level 16, State 1, Procedure sp_addumpdevice, Line 74
Logical device 'mealmanager' already exists."

:-(

Any idea? I simply want to get a .bak file of the db, that's all.

Thanks a lot for any feedback!

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-05 : 06:49:36

use the BACKUP DATABASE command


BACKUP DATABASE mydb TO DISK = 'c:\mydb.bak'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-05 : 08:04:58

Make sure you search 'c:\mydb.bak' in Server and not in client

Madhivanan

Failing to plan is Planning to fail

fabianus76
Posting Yak Master

191 Posts

Posted - 2007-05-05 : 09:12:34

hello khtan,

thanks for your help. Here is the error I get with your sql :

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'c:\mealmanager.bak'. Operating system error 5(error not found).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

any idea?

Hi Madhivanan,
yes, thanks for reminding me of this.

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-05 : 09:24:43

can you post the command you used ?

fabianus76
Posting Yak Master

191 Posts

Posted - 2007-05-05 : 11:02:26

here is the command :

BACKUP DATABASE mealmanager TO DISK = 'c:\mealmanager.bak'

my favorit hoster is ASPnix : www.aspnix.com !

fabianus76
Posting Yak Master

191 Posts

Posted - 2007-05-05 : 11:03:09

I forget to tell that I run on VISTA.

my favorit hoster is ASPnix : www.aspnix.com !

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-05 : 11:10:03

do you have permissions to write to that location on the disk?

www.elsasoft.org

fabianus76
Posting Yak Master

191 Posts

Posted - 2007-05-05 : 13:14:54

I suppose, but how may I check?

my favorit hoster is ASPnix : www.aspnix.com !

fabianus76
Posting Yak Master

191 Posts

Posted - 2007-05-05 : 13:17:19

Everything is fine ! I used D: instead of C: and it work !

Thanks a lot to all of you!

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

fabianus76
Posting Yak Master

191 Posts

Posted - 2007-05-05 : 13:22:59

one more question: am I right to think that it is not possible to backup to a location elsewhere on a LAN ?

my favorit hoster is ASPnix : www.aspnix.com !

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-05 : 18:10:45

You can backup to any location as long as sql service account has permission to access it.

fabianus76
Posting Yak Master

191 Posts

Posted - 2007-05-06 : 12:05:21

thanks to you rmiao !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources