Backing up MSDE 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

MSDE (2000)

Backing up MSDE database

Author

Topic

sbt1
Yak Posting Veteran

89 Posts

Posted - 2003-04-28 : 06:55:52

Hi, can some helpful folks please verify if I'm making backups correctly?

I am stopping the SQL Server service via the Service Manager, then copying the \data folder and everything in it to a backup device.

Is this the correct way to make a backup?

Also - are there backup software programs with the ability to stop the SQL Server service before copying the data, then restarting it? Or is this best done with a script or batch file?

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-04-28 : 08:35:57

That's a way to do it. You can also use the BACKUP command to do warm backups (with the service still running). You can schedule this using the AT command and the OSQL utility, which runs SQL commands from the commandline.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-28 : 10:26:41

You rarely ever need to stop the SQL Server service to take a backup, I guess you are doing it because you cant copy the .mdf files while they are in use (by SQL Server). If you use the built-in backup functionality, you dont need to stop the service.

From Books online:

USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1',
DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_1.dat'

-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1

You can run this batch of statements from either OSQL or an ADO connection/command object, as long as the user has the appropriate permissions.

OS

Edited by - mohdowais on 04/28/2003 10:28:13

sbt1
Yak Posting Veteran

89 Posts

Posted - 2003-05-02 : 09:45:25

Having a problem, can someone help?

1> use sdxtestsql
2> exec sp_addumpdevice 'disk','sdxtestsql_1',disk="m:\backup\sdxtestsql_1.dat"
3> backup database sdxtestsql to sdxtestsql_1
4> go
Msg 156, Level 15, State 1, Server US545W2076, Procedure , Line 2
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the
keyword 'disk'.
1>

I have tried using this:
disk='m:\backup....' (using ' instead of ") but no difference

any ideas what's wrong?

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-05-02 : 09:58:59

Row 2 should be

exec sp_addumpdevice 'disk','sdxtestsql_1', 'm:\backup\sdxtestsql_1.dat'

Edited by - andraax on 05/02/2003 09:59:26

sbt1
Yak Posting Veteran

89 Posts

Posted - 2003-05-02 : 15:00:14

Ok, but still no luck:

1> use sdxtestsql
2> exec sp_addumpdevice 'disk','sdxtestsql_1','m:\backup\sdxtestsql_1.dat'
3> backup database sdxtestsql to sdxtestsql_1
4> go
Msg 15061, Level 16, State 1, Server US545W2076, Procedure sp_addumpdevice, Line
69
[Microsoft][ODBC SQL Server Driver][SQL Server]Add device request denied. A
physical device named 'm:\backup\sdxtestsql_1.dat' already exists.
Msg 3201, Level 16, State 1, Server US545W2076, Procedure , Line 3
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device
'sdxtestsql_1'. Device error or device off-line. See the SQL Server error log
for more details.
Msg 3013, Level 16, State 1, Server US545W2076, Procedure , Line 3
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation
terminating abnormally.
1>

I checked, no file named m:\backup\sdxtestsql_1.dat exists. I even deleted the folder and re-created it, but I still get this error. How can that be?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources