Need help restoring a database to temp location

  • I deleted a table in my production database. The only way I know how to recover from this is to restore the database to a temporary location, then copy table into the original database. If there is a better way, please let me know.

    The database is of course a large (for us) production database needed for year end close. I have a backup from last night so should be good enough to use. But I don't have the resources on the SAN to do a restore (the process saturates the controller with the write activity) so we are looking to restore it to another SAN that doesn't allow me to mount the disk as a drive letter.

    This is a RTM version of SQL Server 2008 R2.

    This is the SQL that I'm trying to use:

    /* Run this to retore the backup to a new database clarity_restore in this case */

    RESTORE DATABASE [Clarity_RESTORE]

    FROM DISK = N'\\storserver\mcsql\Backups\MCSQL\Clarity\FULL\MCSQL_Clarity_FULL_20120102_203001.bak'

    WITH FILE = 1,

    MOVE N'Clarity_Data' TO N'\\tmcxx\SQLTMP\Restore\Clarity.mdf',

    MOVE N'Clarity_Log' TO N'\\tmcxx\SQLTMP\Restore\Clarity_1.LDF',

    NOUNLOAD, REPLACE, STATS = 2

    GO

    And when I try to run this, I get this error message:

    Msg 3634, Level 16, State 1, Line 2

    The operating system returned the error '1337(The security ID structure is invalid.)' while attempting 'CreateFileW' on '\\tmcxx\SQLTMP\Restore\Clarity.mdf'.

    Msg 3013, Level 16, State 1, Line 2

    RESTORE DATABASE is terminating abnormally.

    This is new to me, trying to use a UNC path for the database file paths. I think it should work but I an't find a way to make it work.

    Any and all suggestions gratefully accepted!

    Thanks,

    Norman

  • You cannot use UNC paths with SQL Server.

    I would recommend downloading the trial version of Redgate's SQL Virtual Restore. After reviewing the functionality, you can then determine whether or not spending the $500 is worth it to recover objects - or just to have a read only copy available.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Data and log files on a network share is not possible by default. See if this helps.

    http://support.microsoft.com/kb/304261

  • Shared folders can be directly accessed for restore operations as long as the account used should have rights on shared folder but the data and log files on the restored database will be stored on the local machine by default.

    use the trace flag 1807 as suggested in the kb article. You may refer here as well http://strictlysql.blogspot.com/2010/01/database-on-network-drive.html

Viewing 4 posts - 1 through 3 (of 3 total)

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