locked
Copy Database Wizard. Where is the package created? RRS feed

  • Question

  • Hi guys, I am using the copy database wizard. It works correctly so far but I need to update something in the package (not in the job). Any idea where the package is stored?

    Besides, if I open the job I don't see the package set in the window (however, if I cancel and run the package works)

    Tuesday, October 2, 2018 12:32 PM

All replies

  • https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/save-ssis-package-sql-server-import-and-export-wizard?view=sql-server-2017

    https://docs.microsoft.com/en-us/sql/integration-services/import-and-export-packages-ssis-service?view=sql-server-2014


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Tuesday, October 2, 2018 12:57 PM
    Answerer
  • Thanks, URI but the wizard doesn't give that kind of possibility (to save the package). 

    Besides, I am trying to move one db from 2008 to 2012 but I am having error (The package execution failed. The step failed). No more information, I am just assuming that is due by the compatibility level. Any suggestion?

    Tuesday, October 2, 2018 1:17 PM
  • Thanks, URI but the wizard doesn't give that kind of possibility (to save the package). 

    Besides, I am trying to move one db from 2008 to 2012 but I am having error (The package execution failed. The step failed). No more information, I am just assuming that is due by the compatibility level. Any suggestion?

    Generally package gets saved in the destination server under MSDB -> Database Copy Wizard Packages

    Not sure what error you got

    was there any other error message?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, October 2, 2018 1:32 PM
  • No Visakh, 

    Just the error I posted. The strange thing is that I see the copy in the data folder, it's been created but at one point it gets dropped. 

    Using the transfer DB in SSIS I noticed the same behavior, it creates the data file and the log file but they get dropped. I don't know if it is something related with the upgrade of the version (from 2008r2 to 2012). 

    Tuesday, October 2, 2018 1:36 PM
  • Please read the link I posted above (saving the package)

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, October 2, 2018 1:40 PM
    Answerer
  • Why not using BACKUP\RESTORE command to move the data ?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, October 2, 2018 1:40 PM
    Answerer
  • RESTORE DATABASE dbname
    FROM DISK='B:\DBANE2008.BAK'
    WITH RECOVERY,
       MOVE 'dbname' TO 'D:\DATA\dbname_DATA.mdf', 
       MOVE 'dbname_Log' TO 'L:\DATA\dbname_log.ldf'

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, October 2, 2018 1:43 PM
    Answerer
  • Hi URI, 

    I am using (trying to use...) the Copy Database wizard and it does look different from the mask above, hence I can't save the file.

    I am trying to do not use the Backup and Restore just because I don't want a lot of backups around at the end of the day. I am using the wizard for some tiny db from 2016 to 2016 and it's working but I don't know what else I can do if I want copy in an upgrading way (if this is the problem). 

    Do we have any way to ask Microsoft if this is a bug or not? I am googling around and I am not finding any clue if a db can be copied and upgraded using this wizard. 


    • Edited by DIEGOCTN Tuesday, October 2, 2018 1:53 PM
    Tuesday, October 2, 2018 1:53 PM
  • If you want to upgrade the database, why do you think about lots of backups, btw you can delete automatically old backups

    I do see the option to save the package


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, October 2, 2018 1:58 PM
    Answerer
  • URI, that is the import/export wizard not the copy wizard. 

    I am already taking backups and with the developers around you are forced to take more and more, so I'd be happy to use the move database if only works between two different versions. It would be a nightmare to recognize the backups to delete.  

    Tuesday, October 2, 2018 2:05 PM
  • Sorry, but cannot you see  the Save Package option at the end of the wizard?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, October 2, 2018 2:21 PM
    Answerer
  • No. At all. 
    Tuesday, October 2, 2018 2:22 PM
    • Select the Save a copy project check box to save the completed database copying as a project.
    • Connect to the SSIS  instance and see the package under msdb database

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, October 2, 2018 2:34 PM
    Answerer
  • Do we have any way to ask Microsoft if this is a bug or not? I am googling around and I am not finding any clue if a db can be copied and upgraded using this wizard. 

    It was a long time since I ran CDW, but I know that there is a log produced which you can inspect. It is quite verbosed, but it typically gives the answer. I don't recall exactly, but I think you can configure where the log is produced in the wizard. But it will always be on the destination, since that is where the log is produced.

    However, I entirely agree with Uri, use BACKUP/RESTORE instead. This is a far better method to move databases. Not the least since it is entirely scriptable. If you have an issue with backups lying around - well delete them once you are done.

    Which model are using in CDW? Detach/attach or the SMO method?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, October 2, 2018 2:58 PM
  • SMO method. It works quite fine between 2016 and 2016 but it is tricky between 20018 and 2012. As I wrote, I can see the mdf file in the folder but suddenly disappear (the same if I use SSIS). The file is dropped without I asked to do it. 
    Tuesday, October 2, 2018 3:10 PM
  • No Visakh, 

    Just the error I posted. The strange thing is that I see the copy in the data folder, it's been created but at one point it gets dropped. 

    Using the transfer DB in SSIS I noticed the same behavior, it creates the data file and the log file but they get dropped. I don't know if it is something related with the upgrade of the version (from 2008r2 to 2012). 

    Hmm..

    There's a chance

    you were trying to copy db from 2008 r2 to 2012 ?

    And both connection were using Windows authentication?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, October 2, 2018 4:13 PM
  • Correct.
    Tuesday, October 2, 2018 4:26 PM
  • I remember that during the beta of SQL 2005, I tested the SMO method quite a lot and found lots of things. The released product had most of the tihngs ironed out, but I think it still can fail if you use too esoteric features.

    Anyway, on one of the last pages you can configure where the packaged is logged. Default is Windows eventlog, but you can select a text file, as seen in the screen shot. The file is verbose, but supposedly you need a .NET stack dump topped by an error message from SQL Server.

    The fact that the database go away are probably due to a cleanup after the failure.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, October 2, 2018 9:17 PM
  • Copy db is a SSIS Package whichc is available by default and is stored in msdb database.

    Ramesh Babu Vavilla MCTS,MSBI

    Thursday, October 4, 2018 9:30 AM
  • Thanks Ramesh, how can I get it?

    However, I am giving up. We need to say that is not working in a robust and reliable way. Unfortunately. 

    Thursday, October 4, 2018 9:46 AM
  • It dropped because the service was turned of, why? Do you have backups ?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, October 4, 2018 10:26 AM
    Answerer