Help with SQL query to backup DB.

[hmdeassis]

Ok.

Here is the scenario:

I have a SQL DB hosted by my ISP. I need to know if there is a SQL query that I can run that it will back up or copy the entire locally into my system. My ISP does not have any tools that I can use to make a DB backup, since it is a shared host.

Any suggestion would be greatly appreciated.

Thx

 

[ptheriault]

What kind of access do you have to the servers?
Can you connect with query analyzer?

 

[hmdeassis]

Once I logon into my website. I have a SQL manager console where it does give me access to the query analyzer.

 

[ptheriault]

If you have access to Enterprise manager you create a job to backup the database. Here is the syntax.

BACKUP DATABASE [DB_NAME] TO 'Path\name' WITH DESCRIPTION = N'description', NOFORMAT, NOINIT, NAME = N'name', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

You can run that command from query analyzer too. I would then use ftp to get my backup off of their server. If you have access to ftp?

Also, In order to run the backup db command you must be a member of the 'sa' role, db owner or db_backupoperator.

 

[hmdeassis]

Thank you. Will this backup the entire DB?

 

[ptheriault]

yup...
If you have access to SQL Server BOL you can search on Backup Database to get the entire syntax and other options. But that command will backup your entire database. You might want to check to see if your database is in full or simple recovery mode too. If it is in FULL recovery mode you will need to backup the log file as well. If you don't it will continue to grow and could (depending on how transactional your db is) get very large. If your db is in simple recovery mode the log will truncate on checkpoint. If the data in your database is very static and you don't need point in time recoverability I would suggest setting your db to simple recovery. If you are not sure of the recovery model you are in.
Right click on the db and select properties.
You will find the Recovery Model under the Options page.
Or
sp_helpdb db_name from query analyzer.

This command will do that for you. just change the db name.
ALTER DATABASE Northwind SET RECOVERY SIMPLE
GO

 

[hmdeassis]

Great thanks.