Video Screencast Help

BE2010R3SP2 - Make a copy of SQL DB w/o affecting BE's backup job

Created: 10 Apr 2013 • Updated: 11 Apr 2013 | 5 comments
This issue has been solved. See solution.

I have a Backup Exec job that backups our SQL database server every night.  One of my developers likes to make copies of the database and put it on another server to play with. The way he makes the copy is by running a backup job through SQL Server Management Studio; however, this causes my Backup Exec job to fail saying that the SQL agent was not used for the last backup.

Is there a way to make a copy of the database for him to load on another server w/o mucking up my backup jobs?

Operating Systems:

Comments 5 CommentsJump to latest comment

VJware's picture

I assume you are running full + log backups for the SQL dBs. If this is indeed the case, then running a native SQL backup via Mgmt Studio would interrupt the log backups of BE as the LSN would change. One way would be to run a native dump/backup after all the log backups (by BE) and before the full backup. The idea is to ensure the full + log backups sequence do not get interrupted.

pkh's picture

You can edit your SQL backup jobs and ask BE to create copies of the backups.

BE 2012 - copies of SQL DB.png

The .bak files would be placed in the directory specified in the screenshot above.  Your developer can then use these SQL Studio to restore these .bak files to his development database.

VJware's picture

Perfect response, didn't remember this one.

Nuri Inuki's picture

Nice! Just want to clarify, will this still run the backup accordingly and just make a .bak copy on the hard drive?  If so, that would be perfect!!

pkh's picture

The copy is made during the BE SQL backup.  It is done simultaneously.

You can test it by turning the option on during the next backup.  If you don't like it, turn it off.  You don't have to change your backup schedule or any other job properties.