Backup SQL Server DB Using Script

[beneale]

Hi

I have a SQL Werver based website. My hosts don't allow me to have access to the database via Enterprise Manager, so I'm having to administer the site using ASP and SQL, which is not proving to be too difficult.

However I really need to have a local copy of the database, for testing and development purposes, but my hosts are not willing to backup the database to a file and allow me to download it, and recreate the database locally. They said I should also do this with script.

Now I've found some script in Books Online which goes as follows:

-- Create a logical backup device for the full MyNwind backup.
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

Obviously I've replaced the database names and paths. But it doesn't work. I've placed the above code in the CommandText property of an ADODB.Command Object,and executed. No errors are returned, but it's definitey not creating a backup file in the desired location.

Any ideas? I want the complete database - structure and data - so I can recreate it on my local copy of SQL Server.

Help!

 

[SemperFiDownUnda]

do you have ftp or direct network access to the volume your SQL database resides on?

No need to create a dump device.

You can do something like

BACKUP DATABASE blah
TO DISK='C:\MSQL\BACKUP\MyBackup.DAT'

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[nigelrivett]

As SemperFiDownUnda says backup to a disk file rather than a dump device - it's simpler.

You realise that the path will be on the server don't you?

Does your user have permission to do the backup? Does you have permission on the directory? - I think it's the sql server service that needs it but not sure.

Try a dir command in xp_cmdshell to see the directory.

Put the backup command in an SP and execute that.



======================================
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.

 

[beneale]

Thanks Wayne

I tried:

BACKUP DATABASE blah
TO DISK='C:\MSQL\BACKUP\MyBackup.DAT'

and got the following error (I removed the path name for security reasons):

Microsoft OLE DB Provider for SQL Server error '80040e14'

Cannot open backup device '****Path Name Goes Here****'. Device error or device off-line. See the SQL Server error log for more details.


I found the path using Server.MapPath. I've created a remote directory on the remote server so I can download the backup using ftp. However I very much doubt that the database is on the same volume as the web site. Do you have to backup to the same volume that SQL Server resides on?

Ben