We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Recommended way to automate daily scheduled backup of remote SQL 2005 database

Medium Priority
1,178 Views
Last Modified: 2012-05-06
Can someone tell me the best way to automate a daily scheduled remote backup of our SQL Server 2005 database? I would like to backup the database to our Windows 2003 Server, or my local PC running Vista Business.

Can anyone recommend any software?

I noticed that sql server Management Studio has a 'copy database' wizard. I have Express edition installed locally on a Windows Vista PC, but it won't work with Express edition. Does anyone know if the copy database wizard works with SQL Server 2005 Developer edition, and whether you can schedule this somehow to run daily?

One limitation I have is that the remote database is in a shared hosting environment. The host provider will not grant sysadmin access to customers for their databases, but have told me that the user I log in as "does have enough privileges to remotely backup sql databases."

I have tried using Red Gate SQL Backup (http://www.red-gate.com/sqlbackup) but this requires sysadmin access.

I have tried using 'Simple SQL Copy' (http://projects.c3o.com/files/3/plugins/entry11.aspx), but I cannot get this to work - it assumes that the tables in the source database are already in the destination database, and doesn't create them when it finds they are missing.

Another frustration is using the import/export wizard in SQL Server Management Studio and not being able to export identity keys on any of the 200 or so database tables. I would like to be able to make an exact duplicate including identity keys, nulls.

The ability to be able to rotate local backups say every 3-5 days would be a great feature.

Any advice would be greatly appreciated.

Thanks,

turpya
Comment
Watch Question

Marten RuneSQL Expert/Infrastructure Architect
CERTIFIED EXPERT

Commented:
Buy this service from the hosting company. If you need the backup locally, then have them put the files on some share, i e ftp, sftp, webdavfolder. Access this and copy the .bak files to your local computer.

While youre at it, consider if your DB should be in SIMPLE or FULL Recovery mode. And have your hosting company set up a backupjob accordingly to your specification. If need be you can tell them to mail you in the event of failure, success or both so you can monitor that the backup gets done properly. And also you should instruct them to do a 'RESTORE VERIFYONLY FROM DISK...' , to check the file integrity.

Automation for ftp, and sftp exists. Cant think of any right now.

/Marten

Author

Commented:
Hi Marten,

Thanks for your response. The hosting company does do nightly backups, however if we need to restore anything they charge $135/hour (min 1 hour). Unfortunately for us they do not offer any other form of database mirroring/backup for shared hosting customers.

turpya

Commented:
Have a look at SQL Schedule at lazycoding.com
Marten RuneSQL Expert/Infrastructure Architect
CERTIFIED EXPERT

Commented:
OR see if you can access the backupfiles that are being taken. No need to take them all over again. Plus you could get in trouble with LOG-Chains

Run the following query, but alter:
set @DBNAME = '<yourdbname>' to yourdbname

It will show backups taken, path to the backups ordered by time.

From here it's easy to create nessesary restore statements.

/Marten
declare @DBNAME sysname
set @DBNAME = '<yourdbname>'
 
select bs.database_name, bmf.physical_device_name, bs.backup_start_date,
      null first_lsn,
      null last_lsn,
      bs.checkpoint_lsn checkpoint_lsn
from MSDB.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf 
	ON bs.media_set_id = bmf.media_set_id
where bs.database_name = @DBNAME
and (right(bmf.physical_device_name,4) like '.bak' or right(bmf.physical_device_name,4) like '.trn')
order by backup_start_date desc

Open in new window

Author

Commented:
Thanks Marten. Tried to run the above script against the database, but it didn't return anything.

I have given SQL Scheduler a go, but have hit a snag...

I have installed SQL Scheduler and setup the smtp settings - all looks okay, except when I run the backup script, I get the following error:

The .Net SqlClient Data Provider has received a severity 16, state 1 error number 3201
 on line 7 of procedure  on server server-sql.com:
Cannot open backup device 'c:\dbbackup\241073_2a20090212.bak'. Operating system error 3(The system cannot find the path specified.).
The .Net SqlClient Data Provider has received a severity 16, state 1 error number 3013
 on line 7 of procedure  on server server-sql.com:
BACKUP DATABASE is terminating abnormally.

I have assigned write and modify permissions to the c:\dbbackup folder for NETWORK SERVICE user. I am trying to backup a remote database to my local PC running Vista Business. I have confirmed that 'c:\dbbackup' exists. Is there anything I have missed?

Thanks.

Commented:
Just for the heck of it turn UAC off in Vista and try running.

Author

Commented:
Hi,

Turned off UAC, rebooted, then rerun SQLSCheduler job. This is what I get now:

10 percent processed. (Severity 0, state 1, error number 3211, line 7)
20 percent processed. (Severity 0, state 1, error number 3211, line 7)
30 percent processed. (Severity 0, state 1, error number 3211, line 7)
40 percent processed. (Severity 0, state 1, error number 3211, line 7)
50 percent processed. (Severity 0, state 1, error number 3211, line 7)
60 percent processed. (Severity 0, state 1, error number 3211, line 7)
71 percent processed. (Severity 0, state 1, error number 3211, line 7)
80 percent processed. (Severity 0, state 1, error number 3211, line 7)
90 percent processed. (Severity 0, state 1, error number 3211, line 7)
Processed 8296 pages for database '241073_2a', file '241073_1_dat' on file 3. (Severity 0, state 1, error number 4035, line 7)
100 percent processed. (Severity 0, state 1, error number 3211, line 7)
Processed 3 pages for database '241073_2a', file '241073_1_log' on file 3. (Severity 0, state 1, error number 4035, line 7)
BACKUP DATABASE successfully processed 8299 pages in 2.385 seconds (28.503 MB/sec). (Severity 0, state 1, error number 3014, line 7)

Here is the job script:

DECLARE @BackupName VARCHAR(255)
DECLARE @BackupFileName VARCHAR(255)

SET @BackupName = '241073_2a' + convert(nvarchar(20), getdate(), 112) + N'.bak'
SET @BackupFileName = N'C:\dbbackup' + @BackupName

BACKUP DATABASE 241073_2 TO  
DISK = @BackupFileName WITH NOFORMAT, NOINIT,  
NAME = @BackupName, SKIP, REWIND, NOUNLOAD,  STATS = 10

After executing, I get a 'backup successful' notification email, but there are no backup files in c:\dbbackup?

Commented:
SET @BackupFileName = N'C:\dbbackup\' + @BackupName

Author

Commented:
Thanks jmoss111. Oops...bad typo.

Now I'm back to this...

The .Net SqlClient Data Provider has received a severity 16, state 1 error number 3201
 on line 7 of procedure  on server wic018q.server-sql.com\SQL3,4658:
Cannot open backup device 'c:\dbbackup\vs241073_2a20090212.bak'. Operating system error 3(The system cannot find the path specified.).
The .Net SqlClient Data Provider has received a severity 16, state 1 error number 3013
 on line 7 of procedure  on server wic018q.server-sql.com\SQL3,4658:
BACKUP DATABASE is terminating abnormally.

Does UAC turn itself back on after a while?

Thanks.

Commented:
It shouldn't. Do you have that device set up in SQL Express? You might want to try that.

Author

Commented:
Still no joy. The database I am trying to backup is a remote SQL Server 2005 database in a shared hosting environment. I cannot setup a backup device on this as I do not have permissions to do so.

Marten RuneSQL Expert/Infrastructure Architect
CERTIFIED EXPERT
Commented:
Quote: 'Cannot open backup device 'c:\dbbackup\241073_2a20090212.bak'. Operating system error 3(The system cannot find the path specified.)'

The server cannot find the path specified. This is because it looks for the c:\dbbackup locally, i e on the sql server machine. It will NEVER put the backup on your drive because the server doesn't have access to it.

If you open up your server through a webdav or something it still wont work, due to the fact that the server sees the connections and disks that the account running the server has access to. Furthermore, even if you could trick the servers running account to hook up to your local machines disk there will be problems with their firewall.

This is not possible, unless you can see the path to the backups already taken, from there you could initiate a restore if you have the right permissions.

It's easy to understand. The backups can only be stored on their network/shares/local drives. Probably all disks are in some mirror/raid cabinet. Possibly SAN disks, wich are expensive. If this is true they will ofcourse prohibit their customers from making backups, since they take up valuable space, and the hosting company can't dispose of them, since they don't know if the backup is for some special reason.

You should investigate what this means, quote:
"...does have enough privileges to remotely backup sql databases."
You say you should have the needed priviliges to do a backup (on their harddisks according to me). Given this my script should work. It will give you the path and backupnames to backups taken. I suggest you try it again. It is your only option as I see it (besides paying for the service).

/Mårten

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Commented:
I investigated "...does have enough privileges to remotely backup sql databases." and they are referring to using the import/export wizard. I created the tables locally first, using script generated on the remote server ('script database as... create to...clipboard'), which kept the identities, then ran the import/export wizard.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.