Copy Database Error in Microsoft SQL Server 2008

Recently I needed to create a copy of a development database. We are porting the application from ASP.NET Web Forms to ASP.NET MVC3 and EF but obviously the existing application needed to stay online.

So I launched the copy database wizard in SQL Management Studio and completed the wizard steps as normal, only to be met with the following error:

 

The SQL Agent job had failed.

So I launched the SQL Server Configuration Manager and found the following settings:

I’ve highlighted the culprit. Because the SQL Server Agent requires file system access, it needs to run under an account that has read and write access to the file system.

So to fix the problem, I simply changed the “Log On As” account for the SQL Server Agent to an account such as Local System:

Upon applying this change, the service will be required to restart for the changes to take effect and voila:

Success!

I just thought I’d share a minor hiccup that occurred during a regular day of coding.

Cheers!