SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 MSDE (2000)
 Backing up MSDE database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sbt1
Yak Posting Veteran

89 Posts

Posted - 04/28/2003 :  06:55:52  Show Profile  Reply with Quote
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

Sweden
790 Posts

Posted - 04/28/2003 :  08:35:57  Show Profile  Reply with Quote
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.

Go to Top of Page

mohdowais
Sheikh of Yak Knowledge

United Arab Emirates
1456 Posts

Posted - 04/28/2003 :  10:26:41  Show Profile  Visit mohdowais's Homepage  Reply with Quote
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
Go to Top of Page

sbt1
Yak Posting Veteran

89 Posts

Posted - 05/02/2003 :  09:45:25  Show Profile  Reply with Quote
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?

Go to Top of Page

Andraax
Aged Yak Warrior

Sweden
790 Posts

Posted - 05/02/2003 :  09:58:59  Show Profile  Reply with Quote
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
Go to Top of Page

sbt1
Yak Posting Veteran

89 Posts

Posted - 05/02/2003 :  15:00:14  Show Profile  Reply with Quote
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?

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000