none
Copy from one database to another on the same server.

    Question

  • Hi,

    I have a sql server 2005 express edition.(Windoes 7 PC)

    I have a database TrManager.
    Now i created a new database named TrManagerTest. i  want to copy the data from the TrManager to TrManagerTest
    What are teh steps to be followed for this?
    Can i do it from a backup file or is there any other way to do it?

    Thanks

    Wednesday, October 27, 2010 6:31 PM

Answers

  • Hi Rowter,

    What you can do is take a backup of existing database TrManager and then restore it with a new name i.e. TrManagerTest. One thing that needs attention is while restoring change the name of Database Files (although SQL Server will alarm you in case you didn't change it).

    HTH


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++
    Wednesday, October 27, 2010 6:46 PM
  • Yes, you can do it from the backup. Make sure to chose override option in the Options dialog of the Restore dialog in SSMS. 
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, October 27, 2010 6:53 PM
  • hi,

    Right click on TRManagerTest
    Select Restore database from the dropdown. Set it to 'TRManagerTest'

    ok,

    Select Source for restore:

    From Device(Radio Button)
    Specify back up source and select the backup file and location.
    ...The location of the backup file is on my memorystick "E:\TrManager.bak"


    ok,

    Then in options i select the overwrite the existing database.


    nope.. in the "Restore the database files as" grid, please modify the "Restore as" files to new files, so that you are not overwriting the original TrManager database's files

    and of course, as already indicated, try first copy the backup file to the "ordinary" file system in a folder accessible (granted NTFS access) to the account running the SQL Server instance..

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/ - http://www.hotelsolericcione.de
    Wednesday, October 27, 2010 10:38 PM

All replies

  • Hi Rowter,

    What you can do is take a backup of existing database TrManager and then restore it with a new name i.e. TrManagerTest. One thing that needs attention is while restoring change the name of Database Files (although SQL Server will alarm you in case you didn't change it).

    HTH


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++
    Wednesday, October 27, 2010 6:46 PM
  • Yes, you can do it from the backup. Make sure to chose override option in the Options dialog of the Restore dialog in SSMS. 
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, October 27, 2010 6:53 PM
  • Hi Gursethi and Naom,

    I do not want to mess the original database. The original remains as it is. But i want to change only the trmanagertest.

     

    These are the steps that i followed and the error that i got.

     

    Right click on TRManagerTest
    Select Restore database from the dropdown. Set it to 'TRManagerTest'

    Select Source for restore:

    From Device(Radio Button)
    Specify back up source and select the backup file and location.
    Then in options i select the overwrite the existing database.
    I have the following error.
    The location of the backup file is on my memorystick "E:\TrManager.bak"

     

    Restore failed for Server 'TESTMACHINE\SQLEXPRESS'.  (Microsoft.SqlServer.Express.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

    System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\TRManager.mdf" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Express.Smo)

    Wednesday, October 27, 2010 8:11 PM
  • You can also use the Copy Database wizard in Management Studio. Right-click a database, point to Tasks, and then select Copy Database. Export data is also a choice.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Wednesday, October 27, 2010 8:17 PM
  • Hi Rick,

     

     I dont see that option here. I am using sql server 2005 Express Edition.

     

    Wednesday, October 27, 2010 8:26 PM
  • Can you do this from the local drive instead of the memory stick? Try to copy the backup locally and re-try. Put it in c:\Backup\TrManager.bak
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, October 27, 2010 9:30 PM
  • Sorry. I rarely see the SSMS Express Edition since I have so many versions running. I forget which things are not included.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Wednesday, October 27, 2010 10:21 PM
  • hi,

    Right click on TRManagerTest
    Select Restore database from the dropdown. Set it to 'TRManagerTest'

    ok,

    Select Source for restore:

    From Device(Radio Button)
    Specify back up source and select the backup file and location.
    ...The location of the backup file is on my memorystick "E:\TrManager.bak"


    ok,

    Then in options i select the overwrite the existing database.


    nope.. in the "Restore the database files as" grid, please modify the "Restore as" files to new files, so that you are not overwriting the original TrManager database's files

    and of course, as already indicated, try first copy the backup file to the "ordinary" file system in a folder accessible (granted NTFS access) to the account running the SQL Server instance..

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/ - http://www.hotelsolericcione.de
    Wednesday, October 27, 2010 10:38 PM