Error trying to backup... - 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

MSDE (2000)

Error trying to backup...

Author

Topic

dreemweaver
Starting Member

7 Posts

Posted - 2004-05-20 : 12:10:52

I am getting the following error:
1> use master
2> EXEC sp_addumpdevice 'disk','mvdata_1','c:\MV Data Backup\mvdata_1.dat'
3> BACKUP DATABASE mvdata TO mvdata_1
4> go
Msg 15026, Level 16, State 1, Server GNFC, Procedure sp_addumpdevice, Line 87
[Microsoft][ODBC SQL Server Driver][SQL Server]Logical device 'mvdata_1'
already exists.
Msg 3201, Level 16, State 1, Server GNFC, Procedure , Line 3
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device
'mvdata_1'. Device error or device off-line. See the SQL Server error log for
more details.
Msg 3013, Level 16, State 1, Server GNFC, Procedure , Line 3
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation
terminating abnormally.

Can anyone help with this problem?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-20 : 12:32:22

Your backup device already exists. Just backup your database directly to the file:

BACKUP DATABASE mvdata
TO DISK = 'C:\mvdata.BAK'
WITH INIT

Tara

dreemweaver
Starting Member

7 Posts

Posted - 2004-05-20 : 12:40:31

Thank you... that worked perfectly...

I'm just starting learning MSDE SQL out of necessity... who would I restore if needed... also how can I list the database and tables from the osql command prompt?

dreemweaver
Starting Member

7 Posts

Posted - 2004-05-20 : 12:43:43

sorry... meant how not "who"... lol...

also, how can I set it up so I can use the "at" scheduler to do the back up every night?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-20 : 12:46:56

You would use the RESTORE command:

RESTORE DATABASE mvdata
FROM DISK = 'C:\mvdata.BAK'
WITH REPLACE

To list the databases:

SELECT * FROM master.dbo.sysdatabases
GO

To list the tables:

USE mvdata
GO
SELECT * FROM sysobjects WHERE type = 'U'
GO

Other objects are also stored in sysobjects, so change the type to see them.

I can't remember how to use the at scheduler. Run at /? to find out what it requires.

Tara

dreemweaver
Starting Member

7 Posts

Posted - 2004-05-20 : 13:31:26

Aha... I have a lot to learn I see... lol

Thank you for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources