Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2004
    Posts
    1

    Unanswered: Copy Database - Same SQL Server

    Hello.
    I am fairly new to SQL Server. I have a hosted sql server with unlimited licensing and database has been created on it from outsourced vendor.
    I need to create a duplicate database, on same server, different directory, with a different name for development and testing.
    I have been told to simply to a backup and restore with MOVE, however in my research I have come across things to be aware of and even errors. I am a newbie dba for small company and I do not want to take chances ruining their current database.
    Does anyone have any directions-specific advice?
    Your help will be greatly appreciated!

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Issues you were warned about are real, but harmless in your case. Backup/restore will work for you just fine. Just make sure to change the name of the database prior to clicking on Restore. As a precaution, also make sure that "Force restore over existing database" is NOT selected.

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    You can also:

    1. detach the database
    2. copy the database to the new location
    3. attach both databases

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The time you really need to be carefull with the Backup/Restore method is when you are restoring to a different server, where user logins may not match those in your database. This should not be an issue in your case.

    By the way, why do you need to have the database in a different directory?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...wait a minute, there's (at least) one more thing you need to be aware of:

    If any of the code in your database reference objects using a fully qualified name that includes that name of the database along with the name of the object, this code will execute against the objects in your original database rather than the differently-named copy. For instance, lets say your databases are called DBORIGINAL and DBCOPY, this code:

    select * from YOURTABLE

    will run fine in your copied database. But this code:

    select * from DBORIGINAL..YOURTABLE
    -or-
    select * from DBORIGINAL.OWNER.YOURTABLE

    will continue to select from the original database, not your copy.

    You should check your stored procedures, views, and functions to look for cases like this.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The application being tested will have to be looked over before you get too far. If the application uses ODBC, and you can not change the DSN in the application's connection strings, then you can not test (except on production). Also, if the connection strings specify a database, then you are back in the above situation. Lastly (and here is my favorite), does the user that is being used have a default database assigned? Likely you will not be able to change that on production.
    Ideally, a second DB server (or a named instance) would be better for testing purposes.

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    Good point blindman - that goes for any object that uses fully qualified names. Even something as simple as copying/scripting an object from one database to another.

  8. #8
    Join Date
    Dec 2003
    Posts
    454
    I used the Backup/Restore method to duplicate a database with different database name for our clients' testing. There are following things you need to do:

    1) Like blindman said, you must make sure to change the original database name into the copied database name in your scripts if there is the original database name in your scripts.

    2) Create new user login for the copied database.

    3) Create the ODBC for your connection using a new DSN

    4) Change you connection string in your code to the copied database.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Good practice that I acquired was to preceed your application with logon screen that captures the essentials of the environment that the application will execute in. This way you avoid the pain of having hard-coded connection strings to be changed every time you move your database to a different server or having to rename your database all together.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The more experience you get, the more parameters you use!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Dec 2003
    Posts
    454
    In ASP, you can use Application variable to store the connection string. So you only need to change one file which is global.asa.

  12. #12
    Join Date
    Feb 2004
    Posts
    3

    Smile Application Variable for connection string NOT ME

    I use conn.inc.asp with a

    Function oc()
    End Function

    Place that in top of ya asp pages and only have to change 1 file.

  13. #13
    Join Date
    Dec 2003
    Posts
    454
    Different ways can get the same target, but the important thing is to use a better way.

  14. #14
    Join Date
    Mar 2004
    Location
    London, UK
    Posts
    71
    ok, i've had to do this today for development work.

    tried thru Trans-SQL didn't get very far, used Enterprise Manager...

    In enterprise manager, create new database on desired server, "Tel_Copy", original "Tel".

    Now right-click on "Tel-Copy", select options, set to restricted/single-user access, click ok to close dialog.

    Now right-click on "Tel-Copy", select Restore Database.

    Make sure "Tel-Copy" is what it says in Restore Database As...

    Now Choose backup device, from file, add old backup file. now goto next tab, select "force restore over existing database".

    Now in the list below, you'll see Tel_Data -> C:\tel_data.mdf, and Tel_Log -> C:\tel_log.ldf

    change c:\tel_data.mdf to c:\tel_copy_data.mdf
    (remember to not change the logical dataname here)

    change c:\tel_log.mdf to c:\tel_copy_log.mdf
    (remember to not change the logical dataname here)

    hit restore and the Tel backup will be restored as Tel-Copy


    worked here.

Posting Permissions

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