Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Since the progression of this was apparently somewhat difficult to follow the first time around:

I attempted a completely boring, been-done-a-thousand-times-before copy of a database using the copy database wizard with the detach/reattach method.

The copy failed. The log indicates that it was unable to execute a CREATE VIEW action for a particular view, because the datasource for the view did not exist. This is interesting in its own right, as the source most certainly exists, and the view(s) in question are fully functional in the source database. I'm not really clear, just yet, on how significant this is, as I've yet to figure out precisely why this generated an error.

This resulted in the deletion of all non-system user associations from the source database, leaving me with users dbo, information_schema, sys, and guest. Non-system roles were also deleted. Schemas were unaffected.

I have since restored the damaged database from backup. Academically, however, I would like to know the following:

  1. Why would a failed copy operation strip the user associations from the source database?
  2. Is there any sort of maintenance/rebuild/repair operation that could be performed on the source database to repair it?
  3. The loss of the users was immediately obvious, but given the rather mysterious nature of a failed copy job apparently damaging the source database, how concerned should I be about less obvious effects? In theory, I would expect restoring from backup would alleviate this concern, but do I have any cause to worry about, e.g., the master database?

This is entirely repeatable. I've made a handful of copies (manually) for the sake of experimenting with this particular issue, and in each case, the failed copy job obliterates the users and roles from the source database.

Removing the views that generated errors allows the copy to complete, and, as one would expect, produces a copy with identical data, users, etc., in addition to leaving the source database unaltered.

If it's important, I've tried rebuilding the indexes of the system databases, as well as the damaged database, to no appreciable effect.

The error generated:

1:00:25 PM,5/28/2013 1:00:25 PM,0,0x,ERROR : errorCode=-1073548784 description=Executing the query "CREATE VIEW [Sourcing].[PermittedArrProducts]
AS
SELECT     dbo.tblArrProducts.ArrProductID, dbo.tblArrProducts.ArrangementID, dbo.tblArrProducts.ContainerTypeID, dbo.tblArrProducts.Quantity
FROM         Sourcing.PermittedArrangements INNER JOIN
                      dbo.tblArrProducts ON Sourcing.PermittedArrangements.ArrangementID = dbo.tblArrProducts.ArrangementID

" failed with the following error: "Invalid object name 'Sourcing.PermittedArrangements'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
share|improve this question
    
What is the build number of the instance? –  Shawn Melton May 29 '13 at 0:30
    
Are the views that caused the problem the only views in the database? Can you post the complete error you are seeing in the log? –  Shawn Melton May 29 '13 at 0:31
2  
If you're using the detach/attach copy process (which detaches, copies the files, then attaches the copy) why are there create scripts being executed? –  Mark Storey-Smith May 29 '13 at 9:19
    
What is that "create query" action? Doesn't sound like valid TSQL syntax. Is it inside your SMO copy operation or something external? –  Marian May 29 '13 at 11:10
    
@MarkStorey-Smith - Good question! I would have thought detach/reattach would be a glorified file system copy, essentially equivalent to detaching it manually, copy/pasting the file itself, and then manually reattaching it. I've tried the Sql. Management Object copy method with similar results. –  user2418972 May 29 '13 at 16:13

1 Answer 1

So, after Mark's question above, I did some more searching around with much more generic terms (essentially ignoring the loss of the users and just focusing on the fact that the copy job was failing), and came across this thread, this thread, and this thread all of which seem to pretty closely match what I'm experiencing. This lead me to the observation that, while the views functioned in use, their dependencies were no longer marked, which was what caused the copy job to fail.

While I'm still a bit curious as to why a failed copy job would wipe out the users in the source database (and why it seems "stuck" in SMO mode), I think this should more or less answer my question. Thanks everyone.

share|improve this answer
1  
Did you try to refresh your views (sp_refreshview)? Did it solve the problem? –  Justicator May 30 '13 at 9:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.