Backup database - 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

SQL Server 2000 Forums

SQL Server Administration (2000)

Backup database

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-04-18 : 09:34:22

selvan writes "Hello
I am new to sql,just learning.when i backup a database to my disk i am getting error message.
First i create a device using sp_addumpdevice,sql creates the device successfully,but when i use the device to backup using
"backup database" the following error message.

Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'exercis_backup3'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

My syntax for the error message

1) exec sp_addumpdevice 'disk','exercis_backup3',
'c:\program files\microsfot sql server\mssql\BACKUP\exerci_backup3.bak'

2) backup database exercise to exercis_backup3

In the first syntax i don't have any problem.
when i execute 'sp_helpdevice'
the device name is there.So what is my mistake

If anybody explain to me,it will help me lot
Thank you"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-18 : 10:12:02

Much easier to just backup to a file

backup database exercise to disk='c:\program files\microsfot sql server\mssql\BACKUP\exerci_backup3.bak'
(think that's the syntax - check in bol).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

suhde76
Starting Member

10 Posts

Posted - 2006-04-18 : 19:22:36

Hi Selvan,

The problem with your syntax is that the file 'c:\program files\microsfot sql server\mssql\BACKUP\exerci_backup3.bak' cannot be created, since the path you have given is invalid, isn't it? When you create a device using sp_addumpdevice that points to an invalid path, it will not give you an error, since it does not try to create the file "exerci_backup3.bak". But when you try to take the backup, it will try to create the file, but the location of the file is invalid. May be the folder "microsfot sql server" does not exist. (Shouldn't it be "Microsoft SQL Server"?) Check the path, and then use the same command. Drop the device using sp_dropdevice, add the device so that it points to a valid location, and then backup the database, it should work.

If it does not, post the error message here, I will check.

Thanks, Suhas, MS SQL Server Engineer, Microsoft.

Kristen
Test

22859 Posts

Posted - 2006-04-19 : 00:48:36

"(think that's the syntax - check in bol)."

You're correct Nigel, he might want to add NOINIT etc:

This is a snippet that I usually use:


BACKUP DATABASE MyDatabaseName 
TO 
      DISK = 'x:\MyPath\MyDatabaseName_yyyymmdd_hhmm.BAK' 
WITH
--    DIFFERENTIAL,
      NOINIT, 
      STATS=10

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources