Post reply

Setup SQL Server Express training database with attach

  • May 27, 2009 at 1:23 am

    #204614

    Hi

    We run a commercial application that uses SSE on the notebook clients.

    Currently, whenever we have a training, I have the users setup the application including the installation of SQL Server express, create the database and all that.

    I was wondering if I could speed up the process by keeping a virgin copy of the initial training database. After training, I would remove the current files and replace them with the original. My idea is to connect to the database with MSQuery, detach the current DB, swap the files and attach again.

    Can anybody tell me if this is a plausible method ? Am I overseeing anything ?

    Wbr

    Jurriaan

  • May 27, 2009 at 2:03 am

    #1000394

    Go ahead no probs in doing that.

    Keep a backup of the current database b4 swapping so that if someone complains you have an answer.

    Tanx 😀

  • May 27, 2009 at 3:21 am

    #1000427

    assumptions :

    --------------

    -> you have 2 folders .One which SQL Server is using currently .Two where your database copy is residing

    Steps :

    ----------

    Step 1 :

    sp_detach_db 'test' or drop database test

    Step2 :

    xp_cmdshell 'E:'

    xp_cmdshell 'copy "E:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Data\test\test.mdf" "E:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Data\test.mdf" /Y'

    xp_cmdshell 'copy "E:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Data\test\test_log.ldf" "E:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Data\test_log.ldf" /Y'

    sp_attach_db 'test' ,'E:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Data\test.mdf' ,'E:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Data\test_log.ldf'

    Make sure xp_cmdshell is enabled .

    Regards

    Abhay

    Abhay Chaudhary
    Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

  • May 27, 2009 at 3:42 am

    #1000436

    Thanks both of you. Good to see that the plan could work.

    @Eswin: thanks, but as it is a training, I can skip backing up the (probably screwed up) database before swapping it with the mint one.

    One other thing that is bugging me is the security.

    Gilamonster wrote in a reply in another topic that user and role info is in the database, but login info is in the master. The system requires that a particular user is the dbo. Let's call this user 'MMX'.

    If I would setup the software on an empty system, the installation procedure will create the database, the system user (MMX), it's login and then all the tables. MMX must be the owner of all those tables or else the software won't work (don't ask....).

    When I switch the newly created DB with the one I prepared previously, how do I connect the MMX user in the training db with the login for MMX in the master ? Which command do I use for that ?

    Wbr

    Jurriaan

  • May 27, 2009 at 4:12 am

    #1000456

    Create the same user in your database in advance that you application creates in the begening ...

    In short make sure that the database you are going to replace has all the users in advance that your application supports ...

    keep that copy for ever ..

    Abhay Chaudhary
    Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

  • May 27, 2009 at 12:15 pm

    #1000902

    Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply