Restore with different name on same server

  • nari.koud

    Ten Centuries

    Points: 1116

    Hello All...

    I have a database XYZ (Full Recovery Model,300 users in it size aroung 134800 MB,) on Server 1(dev)

    I need to back it up to some location(z drive/disk)

    Then

    I need to restore to same Server1(dev) with different name XYZ_ABC

    All I know is to do in GUI and writing Basic Scripts ,but my lead needs Scripts .

    Steps: 1.Doing an backup to disk z

    2.Restoring it

    but I am concerend about renaming the DB and restoring it on same server1(dev) ....

    Can u guys help me on giving the Step -Step -Procedure to do ....What will be the steps and Scripts ??

    and after restoring do i need to assign permissions to alla existing 300 users ?

    (may be this one depends on my lead)

    Thanks

  • SQLRNNR

    SSC Guru

    Points: 281210

    Here's a shortcut to learn how to script those backups.

    Through the gui, make the selections you need. Reassign the name, file names, and file locations as necessary. Then click on the script button at the top of the window. This will allow you to script it out to a new query window and see what is going on with the script.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • John.Liu

    SSCommitted

    Points: 1606

    Given that you are restoring onto the same server/instance with just a differenct db name, all the logins (and permissions) will still work on the new db without any change. If you restore onto a different server/instance, you may need to script out the logins and restore onto the new server/instance as well.

  • homebrew01

    SSC Guru

    Points: 55137

    CirquedeSQLeil (9/11/2010)


    Here's a shortcut to learn how to script those backups.

    Through the gui, make the selections you need. Reassign the name, file names, and file locations as necessary. Then click on the script button at the top of the window. This will allow you to script it out to a new query window and see what is going on with the script.

    I was going to suggest this also. You can use the script function in other areas as well. Most functions you do through the GUI (table changes, logins ...) can be scripted so you can see the script, save it for later use and as a log of what was done.

  • nari.koud

    Ten Centuries

    Points: 1116

    I got the script ....Thanks all ....

    But do i need to restore .ndf files also???

    Because i saw 6 ndf files when i try to restore ,,

    MOVE N'XYZ_Data1' TO N'A:\MSSQL\User\XYZDBData1\XYZ_Mock_2.ndf',

    MOVE N'XYZ_Data2' TO N'A:\MSSQL\User\XYZDBData2\XYZ_Mock_3.ndf',

    MOVE N'XYZ_Data3' TO N'A:\MSSQL\User\XYZDBData2\XYZ_Mock_4.ndf',

    MOVE N'XYZ_Index1' TO N'A:\MSSQL\User\XYZDBOthers\XYZ_Mock_5.ndf',

    MOVE N'XYZ_Text1' TO N'A:\MSSQL\User\XYZDBOthers\XYZ_Mock_6.ndf',

    MOVE N'XYz_Index2' TO N'A:\MSSQL\User\XYZDBOthers\XYZ_Mock_7.ndf'

    Thanks

  • John.Liu

    SSCommitted

    Points: 1606

    You need to restore the .mdf, all .ndf and .ldf files. .ndf are secondary data files.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715900

    John is correct. you need to restore/move, all the files that are returned from the RESTORE FILELISTONLY command.

  • SQLRNNR

    SSC Guru

    Points: 281210

    Yes - all files.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • bdkdavid

    Ten Centuries

    Points: 1220

    I know this is an old Post. It is pretty much what I am trying to do. I used the backup via the GUI interface. 
    I backed up database A to a separate location. Once I have done as a file ABC.mdf 
    I used the GUI interface to do a Restore operation changing the device to ABC.mdf
    changed name to database B
    on the files tab Restore AS pointed to directory on same server bothe the data and log file
    Database A files are in the original location and database B are in the new location

    My Problem is that it appears that both database A and database B are insinc with each other.
    One is the production envirnment and the other is the Development area.
    Any possible ways to determine that they are not insinc
    I have never seen this before.

    Thank You
    I would appreciate any feedback

  • Sue_H

    SSC Guru

    Points: 90260

    bdkdavid - Wednesday, June 20, 2018 11:27 AM

    I know this is an old Post. It is pretty much what I am trying to do. I used the backup via the GUI interface. 
    I backed up database A to a separate location. Once I have done as a file ABC.mdf 
    I used the GUI interface to do a Restore operation changing the device to ABC.mdf
    changed name to database B
    on the files tab Restore AS pointed to directory on same server bothe the data and log file
    Database A files are in the original location and database B are in the new location

    My Problem is that it appears that both database A and database B are insinc with each other.
    One is the production envirnment and the other is the Development area.
    Any possible ways to determine that they are not insinc
    I have never seen this before.

    Thank You
    I would appreciate any feedback

    As a test, you can create an object like a test table or stored procedure in one of the databases and see if it shows up in the other database.
    Development and production on the same server isn't really a good idea. I realize a lot of times that's a company decision and they won't buy more hardware. I'd try to get anything for a different development server - even if it was an older PC. Anything.

    Sue

  • bdkdavid

    Ten Centuries

    Points: 1220

    Hello Sue,

    Thank You for the reply.
    I created a table in the Development environment. It appears to not be replicating.
    The record count in the main table is increasing. Since I am the only one using it. 
    It should not be the case. 
    It is one to one with the the production environment.
    Thank You.
    David

  • Sue_H

    SSC Guru

    Points: 90260

    bdkdavid - Wednesday, June 20, 2018 12:14 PM

    Hello Sue,

    Thank You for the reply.
    I created a table in the Development environment. It appears to not be replicating.
    The record count in the main table is increasing. Since I am the only one using it. 
    It should not be the case. 
    It is one to one with the the production environment.
    Thank You.
    David

    So they aren't synchronizing which is correct.
    But for your record count on that table, if you have permissions, run a trace to see what else or who else is inserting records into that main table.

    Sue

  • bdkdavid

    Ten Centuries

    Points: 1220

    HI Sue

    It does not appear to be insync that is good. I do not have the rights to do much of anything here.
    I created the DEV environment for me to some development and testing.
    There shouldn't be anyone using it. I am worried that I in some matter sync the database.
    The records are not in sync so I would have to attempt to figure it out somehow.

  • Sue_H

    SSC Guru

    Points: 90260

    bdkdavid - Wednesday, June 20, 2018 3:28 PM

    HI Sue

    It does not appear to be insync that is good. I do not have the rights to do much of anything here.
    I created the DEV environment for me to some development and testing.
    There shouldn't be anyone using it. I am worried that I in some matter sync the database.
    The records are not in sync so I would have to attempt to figure it out somehow.

    If the database is restored to a new name and the files for the database are different then it nothing else should be using it even accidentally. You probably want to involve whoever is the administrator and get some help from them to make sure no applications are hitting your own database.
    Does the company have an MSDN subscription? They may be able to get you a copy of SQL Server 2005 developer edition that you could install on your own laptop or PC.
    2005 is old so it's not available for the free download and more current versions don't support putting databases in SQL Server 2005 compatibility mode. If you could have your own separate environment then you wouldn't have to worry about this issue. And you'd have somewhere to safely work on the development.

    Sue

Viewing 14 posts - 1 through 14 (of 14 total)

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