0

I am trying to use Copy Database Wizard to copy from my live server (shared hosting) to my local machine. Both the live and local servers are SQL 2008 R2.

I have used CDW for several years with perfect success when copying from a live SQL 2000 server to my local 2008 R2. But now that I have migrated my live database to SQL 2008 R2 the CDW is giving me this error:

Could not read metadata, possibly due to insufficient access rights.

I've learned that this error can be predicted before you even complete the CDW setup: On the page where the CDW asks you for your desired destination database name, it is SUPPOSED to populate the .mdf and .ldf files with their name-to-be and size (e.g. MB, GB).

But in my case these file names and sizes are not being shown (area is simply blank in the wizard) and then of course when I attempt to execute the package it gives me the error.

After much research I believe that reason for this error is due to the CDW requirement of "You must be a member of the sysadmin fixed server role on both the source and destination servers."

On my local server, my Windows Authentication login is listed as a Role Member for the sysadmin Server Role. However on my live server (keep in mind it is a shared SQL server with 250+ databases) the only Role Member listed is [sa].

Am I right in thinking that the only way to satisfy this requirement would be to add my specific SQL user to the live/source Server > Security > Server Roles > sysadmin role? I'm guessing that would never be done on a shared server right? Or is there some other way to make it work by messing with the specific database properties/users/roles?

I can't explain why CDW is working from the live SQL 2000 server and not the 2008 R2. I HOPE it is simply that something isn't set up right on the live database, but maybe it is due to changes that were made to SQL security over the years.

In case it matters, I must use the SMO method instead of detach/attach because it is a live database that I don't want to take down. Historically the CDW from SQL 2000 only takes 3 minutes with SMO method so speed isn't an issue anyway.

Here's my preference for a solution:

  1. Find a way to get CDW to work, most likely by changing something on the live server. Is this possible? What would it be?

If that fails, then...

  1. What about an idea of using CDW to create the package, but then going into to BIDS and manipulating something in the package to circumvent the sysadmin role requirement. (Does it really need the metadata? I don't need anything beside the actual data tables.) Is this possible?

    UPDATE 6/14/2016: Editing a CDW package in BIDS won't work as it appears to simply use the .mdf and .ldf files, which of course I don't have access to on the shared server. I think an alternative is to use Import/Export Wizard to create a package, then edit in BIDS. The annoying part is that without access to metadata the Import/Export Wizard doesn't seem to be aware of Foreign Keys, and thus doesn't know what order to process the tables in.

If that fails, then...

  1. Is there any other way to easily automate a daily copy from my live server to local machine? The reason I like CDW is because it is super simple to use (when it works), it can be scheduled to run daily as a SQL agent job, and requires no manual work on my part. Is there a "next best thing" if CDW can't be made to work?

You'd think that a very common scenario for all websites out there would be "how do I get a copy of my live database onto my local SQL server, daily, automatically"? But maybe I'm the weird one!

shA.t
  • 16,580
  • 5
  • 54
  • 111
Billy Turtleneck
  • 55
  • 9
  • Instead of a CDW operation, can't you try a backup/restore operation ? – DoubleT28 Jun 14 '16 at 07:34
  • @DoubleT28 Requesting a backup from the host is $25. I'd have to wait for them to do it (could take minutes to hours), then they'd have to place the file on my site folder, I'd have to download it, and do the restore manually. With CDW I set it up one time only (takes about 5 min) and then schedule it to run daily. It's that easy. – Billy Turtleneck Jun 14 '16 at 18:52

1 Answers1

0

Another simple solution would be the Import/Export Wizard.

In SSMS right-click the database you want transferred and select 'Tasks' and then 'Export Data...'. It will open a wizard that is very similar to that of CDW. The difference here is that I could not find a sysadmin requirement to use it.

At the end it will give the option to run immediately and/or save the SSIS package. If you save the SSIS package (I prefer to save it to disk) you can then create a schedule via a SQL Agent job.

DBADon
  • 449
  • 5
  • 9
  • The Import and Export Wizard is a good start, but apparently due to permissions it cannot "see" the Foreign Keys. I think it is related to the same error as "could not read metadata". Would FKs be part of the metadata? – Billy Turtleneck Jun 17 '16 at 16:46
  • And since it cannot see/detect the Foreign Keys, it decides to process the tables in alphabetical order, and it tries to use Truncate (which from what I've gathered won't work if the tables have FKs). – Billy Turtleneck Jun 17 '16 at 16:48
  • When you state that on the live server "the only Role Member listed is [sa]", are you saying SysAdmin? If yes, you should have rights to do whatever you need to. If not, try these steps: 1. Right click the server name and select 'Properties' 2. Click on the 'Permissions' page 3. Find and then select your user name 4. In the 'Permissions for [Your User Name]', in the 'Explicit' tab, Find 'View Any Definition' and then check the 'Grant' check box. I'll update my post above if this is at all helpful... – DBADon Jun 17 '16 at 19:40
  • On another site, with a similar issue, it was suggested that when in CDW, deselect the entire database button and instead select all of the individual database objects. This bypassed the metadata issue. [Could not Read MetaData] (https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c801b78d-c069-4593-9183-fc3e93eca4ee/could-not-read-meta-deta?forum=sqldatabaseengine) – DBADon Jun 17 '16 at 19:50
  • In regards to selecting only the database in CDW, I have already tried that. (The database is all I want anyway.) The problem actually reveals itself before you even get to that section in the CDW, as the .mdf and .ldf files do not populate when you enter destination db name. Basically, I think I have exhausted the possibility of the problem being a setting WITHIN CDW. – Billy Turtleneck Jun 20 '16 at 18:41
  • In regards to permissions on the server, it is important to note the difference in wording between permissions at the Server level vs the Database level: At Server > Permissions > My Login there is the Explicit option of VIEW ANY DEFINITION. I can check it and click update but when I come back it is unchecked. (This makes sense, as it is a shared server.) Under Effective all that is listed is CONNECT and VIEW ANY DATABASE. Under Database > Permissions > My Login there is the option of VIEW DEFINITION. Again, I can check it and click update but when I come back it is unchecked. – Billy Turtleneck Jun 20 '16 at 18:46
  • I did alredy have VIEW DEFINITION listed under Database > Explicit. But this doesn't seem to help. Basically, with this being a shared server I don't think I can assign myself any more permissions than what I already have. – Billy Turtleneck Jun 20 '16 at 18:48