Post reply

gotta be an easier way...

  • February 3, 2005 at 12:29 pm

    #109308

    I'd like to give my developers an easy way of restoring the most recent production backup to their local machines. Trouble is, the production box uses about 5 datafiles spread across 2 drives and the developers' machines only have a C:\.

    I can assume that they'd be restoring over an existing instance of the Database, is there anyway to call RESTORE DATABASE and tell it to funnel the 5 production data/log files into the existing ones?

    thanks

    Greg

  • February 3, 2005 at 12:52 pm

    #540492

    restore database db_name from disk = 'c:\test.bak' with move

    'test_data1' to 'c:\test_data.mdf',

    move 'test_data2' to ''c:\test_data2.ndf',

    move 'test_data3' to ''c:\test_data3.ndf',

    move 'test_data4' to ''c:\test_data4.ndf',

    move 'test_log' to 'c:\test_log.ldf', replace

    you can find the logical names from

    restore filelistonly from disk = 'c:\test.bak'

  • February 3, 2005 at 1:10 pm

    #540496

    Thanks Old Hand, but...

    I was hoping for some command that would cause the restore command to recognize that there's a database of the same name on the new computer already and to combine all 5 production datafiles into the 1 datafile on the new machine, thus avoiding the "move 'xxx' to "c:\xxx.ndf" ... portions of the suggested commands.

  • February 3, 2005 at 1:30 pm

    #540501

    Well, for sure you're going to have to use the with MOVE, since the folders on developer's stations have different structure than prod boxes.

    What I've done is write a sproc to look up the data and log file names and locations on the dev boxes, and then dynamically generate the code to restore the backup to those files.

    Now, what I have not tried is consolidating files, which is what I gather you're trying to do. I have not come across any literature excplicitly stating this cannot be done.

  • February 3, 2005 at 1:34 pm

    #540503

    Won't work.  You can change the locations of the files, you can change the (physical) names of the files, but you can't change the structure of the database in a restore.  If the developers want a newer copy of the db, the additional files is part of the new copy, and you should use the WITH MOVE clause as sa24 suggested.   If you MUST keep the structure the same on the developers' machines, then you'll have to script any ddl, and then bcp the data.

    I'm not sure I understand why it would be a problem to create the dbs on the develpers' machines with the current structure though? 

    Steve

  • February 3, 2005 at 1:36 pm

    #540504

    Thanks for clearing that up, Steve.

    P

  • February 3, 2005 at 1:48 pm

    #540506

    thanks gang. you've confirmed my suspicions about RESTORE DATABASE capabilities:

    (schleep: "... what I have not tried is consolidating files, which is what I gather you're trying to do.")

    I didn't know if I was missing anything obvious in BOL.

    greg

  • February 3, 2005 at 2:05 pm

    #540515

    schleep,

    Sorry.  I kinda stepped on you.  I clicked on Reply, then got called away on a production issue, then came back and didn't check to see if anyone else had posted in the meantime!

    Steve

  • February 4, 2005 at 6:51 am

    #540668

    No worries.

  • February 6, 2005 at 1:40 pm

    #540837

    I use the following code for this situation :

    USE master

    EXEC sp_addumpdevice 'disk', 'BackupDB',

       '\\dbsrv01\f$\Testdatabase\BackupDB'

    BACKUP DATABASE Take2 TO BackupDB

    -- Restore the files for MyNwind2_Test.

    RESTORE DATABASE Test

       FROM BackupDB

       WITH RECOVERY,

       MOVE 'LogicalFilename' TO 'f:\TestDatabase\MSSQL\Data\Test.mdf',

       MOVE 'LogicalFilename_log' TO 'f:\TestDatabase\MSSQL\Data\Test.ldf'

    -- remove the file once restoed

    exec sp_dropdevice 'BackupDB'

    exec master..xp_cmdshell 'del Location of temp file'

    --Remove Log files

    EXEC sp_detach_db Test

    exec master..xp_cmdshell 'del f:\TestDatabase\MSSQL\Data\test.ldf'

    EXEC sp_attach_single_file_db @dbname = test,

       @physname = 'f:\TestDatabase\MSSQL\Data\test.mdf'

    Note: I remove the log file before the developers are let loose to the copy of the database.

    Hope this helps

Viewing 10 posts - 1 through 9 (of 9 total)

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