[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1132
  • Last Modified:

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

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
0
turpya
Asked:
turpya
  • 6
  • 4
  • 3
2 Solutions
 
Marten RuneCommented:
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
0
 
turpyaAuthor 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
0
 
jmoss111Commented:
Have a look at SQL Schedule at lazycoding.com
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Marten RuneCommented:
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

0
 
turpyaAuthor 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.
0
 
jmoss111Commented:
Just for the heck of it turn UAC off in Vista and try running.
0
 
turpyaAuthor 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?
0
 
jmoss111Commented:
SET @BackupFileName = N'C:\dbbackup\' + @BackupName
0
 
turpyaAuthor 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.
0
 
jmoss111Commented:
It shouldn't. Do you have that device set up in SQL Express? You might want to try that.
0
 
turpyaAuthor 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.

0
 
Marten RuneCommented:
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
0
 
turpyaAuthor 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.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now