Tuesday, August 30, 2011

I was on a SQL Server 2008 R2 database I wanted to backup and put on my machine.

First I tried backing it up and got this error:

Backup failed for Server. 'MyDatabaseServerName' (Microsoft.SqlServer.SmoExtended)

System.Data.SqlClient.SqlError: Cannot open backup device 'C:\MyBackupFileName.bak'. Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&LinkId=20476

Then I tried generating scripts and got this error:

Microsoft.SqlServer.Management.Smo.SmoException: Could not read metadata, possibly due to insufficient access rights. at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

I figured I must not have rights to some metadata or something. In the wizard I try selecting Tables, Views, and Schemas. My script generates fine. I notice there is no create database in the resulting script. I go back to the Choose Objects screen, unselect "Script entire database and all database objects" and "Select All" objects. My script generates fine.