0

I'm trying to create a dev database so I don't create triggers and such on the production database. I use the right click -> Taks -> Copy, followed the wizard and the process starts.

After maybe a minute, it stops and says Error.

The Event Viewer says this:

Package "ShellPackage" failed.

Event Name: OnError
 Message: ERROR : errorCode=-1073548784 description=Executing the query "CREATE VIEW [dbo].[VIEWNAME]
AS
SEL..." failed with the following error: "Invalid column name 'C_27'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}
StackTrace:   Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
Operator: DOMAIN\administrator
 Source Name: SERVER_SERVER_Transfer Objects Task
 Source ID: {47D1A40A-A6CF-4A5C-897E-80CB9C1D6151}
 Execution ID: {3E979DF7-C5DC-4583-8613-CB9AC9153D56}

What am I doing wrong?

Thank you!

2
  • Does CREATE VIEW [dbo].[VIEWNAME] has C_27 column ? Why dont you just do a backup / restore and then at the end drop all the triggers using select 'drop trigger ' + name + + char(10) + ' go' from sysobjects where type ='Tr'
    – Kin Shah
    Jun 3 '14 at 21:08
  • 2
    Why don't you just BACKUP/RESTORE? Jun 3 '14 at 21:08
3

Best and most reliable way is to use backup / restore

If your database is big and you are on sql server 2008 R2 and up, standard edition also has compression feature to allow you to compress your backups.

Dont forget to sync up users after the restore :-)

0
2

Possible reason for the error: Was view [dbo].[VIEWNAME] created with SELECT * FROM ...? In this case an underlying table could've had a column removed, but the view definition (if it was not refreshed) would be none the wiser.
See Selecting from a view shows the wrong set of columns.

Concerning the approach to "copy" the DB - I agree with Kin and Aaron - better to use backup/restore.

0

I actually did this first but it didn't work. I just found out what my mistake was though. I created a new database before I did the restore which failed because the backup contained a database other than the one I created

"System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'DB_DEV' database. (Microsoft.SqlServer.Smo)"

But what I didn't know is I needed to create the new database by actually typing the name in the restore window without creating it first. I didn't know I could write in this field but I just gave it a shot and it worked!

Thanks everyone!

1
  • Dont use GUI for doing backup / restores. TSQL Commands are much powerful with lots of options that GUI does not offer.
    – Kin Shah
    Jun 4 '14 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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