January 4, 2012 at 11:28 am
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
January 4, 2012 at 3:00 pm
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
January 4, 2012 at 3:06 pm
Data and log files on a network share is not possible by default. See if this helps.
January 8, 2012 at 5:22 am
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
Regards,
Raj
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply