Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2008
    Posts
    13

    Unanswered: my first ssis package: duplicating a database

    I thought the nice interface of Visual Studio 2005 could help me to create an SSIS package to duplicate my database.

    But I just realised I don't see how to do it.
    For example: I see a "Sql Serve Destination" object by no such source object.
    Also: I don't see how to connect a source to a destination like I did with DTS.

    Would you know how to do that ?
    Is there a good start web page somewhere ?

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I can't confirm this - I have not used it (I wrote\ adapted it for someone else) so TEST TEST TEST. But stuff SSIS.

    EDIT - I've rechecked - I didn't write this - I helped someone else write this.

    Code:
    BACKUP DATABASE MyDBLive
        TO DISK = 'D:\MSSQL\BACKUP\MyDBLive_20080718.bak'
    GO
    --Database to restore
        RESTORE DATABASE MyDBMigrate
    --From file location
        FROM DISK = 'D:\MSSQL\BACKUP\MyDBLive_20080718.bak'
    --This is the only file so rollback any incomplete transactions
        WITH RECOVERY, 
    --Move dat file to...
        MOVE 'MyDBlive_data'
        TO   'D:\MSSQL\Data\MyDBmigrate_data.mdf', 
    --move log file to....
        MOVE 'MyDBlive_log' 
        TO   'D:\MSSQL\Log\MyDBmigrate_log.ldf',
    --Get a message every 10% of the process completed.
        STATS = 10
    GO
    ALTER DATABASE MyDBMigrate
    MODIFY FILE
      (NAME = MyDBlive_data, NEWNAME = 'MyDBmigrate_data')
    GO
    ALTER DATABASE MyDBMigrate
    MODIFY FILE
      (NAME = MyDBlive_log, NEWNAME = 'MyDBmigrate_log')
    GO
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's the one you produced for me about 2 months back, which I tweaked to my needs and have put into practice.

    And you call yourself an Archivist, shame on you Mr Flump.
    George
    Home | Blog

  4. #4
    Join Date
    Jun 2008
    Posts
    13
    Thanks a lot pootle flump,

    I will test that soon.
    It looks simple but it scarces me a little bit.
    I have absolutely no experience of these things.

    Could you comment this code a little bit? Telling exactly what it does.
    For example, what does this statement do exactly:

    MOVE 'MyDBlive_data'

    What does the name 'MyDBlive_data' stand for? Is it related to the original database? When was it created? ...

    After this code is executed, can I be sure that I will have two copies of the same database?
    Will that work in the same was as when duplicating Access files?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I knew it was you - I was keeping you anonymous. If you look, that is an edited copy of your it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2008
    Posts
    13
    Finally I solved the problem I mentioned in the trhead http://www.dbforums.com/showthread.php?t=1632319 that is now locked.
    The Copy Database Wizard (CDW) was not working simply because there is a bug.
    I installed SP2 and it worked then.
    This is a small step forward.

    But I also see more clearly where to go for the question I asked here.
    Actually, there are different ways of doing things.
    This seems to be typical for SqlServer, where old and new methods are available as well as bad and good.

    I learned it is possible to duplicate the database files.
    Surely efficient, but I don't like that too much.

    I learned it is possible to rely on the backup system and scripting in tsql.
    That's interresting, specially as a way to stay 100% stored procedure based.

    I learned the SMO object can not be used in VB6.
    One reason per day to complain about Bill Gates, just normal.

    I learned that SMO can be used any X#.net language, including my prefered one, J#.

    I learned that SMO canot be used in the Visual Studio v7, even if it is ".net".
    I deeply regret this expense I made about five years ago and that I never used.
    Sometimes we have a second reason per day to complain about BG.

    I learned that VS express is for free and that I can use SMO with it.
    With about 10 lines of code the problem can be solved and turned into a reusable method.
    This will probably be my prefered solution.
    After all, I like small chunks that I can reuse.
    Would that be one reason per day to be happy about BG?
    Not so sure.
    Last edited by lalbatros; 07-19-08 at 09:00.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I prefer T-SQL where possible. DMO, SMO, DTS, SSIS - all come and go. While SQL Server exists T-SQL will be around.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by pootle flump
    I prefer T-SQL where possible. DMO, SMO, DTS, SSIS - all come and go. While SQL Server exists T-SQL will be around.
    Right on, man, right on!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •