none
Is possible to restore database back up file from external hard drive to same location

    Question

  • hi

    Is possible to restore database back up file from external hard drive to same location(free space is terabyte) using sql server 2008. this is only for test purpose. but database is 175gb. thanks in advanced.

    Saturday, February 15, 2014 2:12 AM

Answers

All replies

  • As per me you can restore the database on external drive, can try below if works

    But make sure SQL services not running on local system. As Localsystem account has no access to shares on the network as it isn't an authenticated network account.  Therefore SQL Server running under this account cannot backup/restore to a normal network share.

    According to http://support.microsoft.com/kb/304261, you may use SAN/NAS storage to store your databases' files using a TraceON flag. Something like:
    DBCC TraceOn(1807);
    GO

     this command flags the 1807 TranceOn to let you use a UNC like "\Server-name\Path-to-Database-File.mdf" as the path to database files. Now you need to Create a folder on your removable USB HDD and use "Sharing and Security" to grant Full Control over that folder to "Network Service" or whatever user your SQL uses to interact with your Windows. Remember to remove everyone and Add Administrators too.

    Now you're done; create the database and have fun!
    DBCC TraceOn (1807);
    Go

    Use master;
    GO

    CREATE DATABASE [test001]
        ON  PRIMARY (
         NAME = N'test001',
         FILENAME = N'\\PC-Name-Where-Share-Is\TempDB\test001.mdf' ,
         SIZE = 2048KB ,
         FILEGROWTH = 1024KB
         )
        LOG ON (
         NAME = N'test001_log',
         FILENAME = N'\\PC-Name-Where-Share-Is\TempDB\test001_log.ldf' ,
         SIZE = 1024KB ,
         FILEGROWTH = 10%
        )
    GO

    For more details, check below threads

    http://serverfault.com/questions/48958/sql-server-database-on-an-external-hard-disk-drive

    http://windowsitpro.com/windows/why-cant-i-backuprestore-my-sql-server-database-share-another-server

    http://blogs.msdn.com/b/varund/archive/2009/06/04/backup-sql-server-database-to-a-share-drive-on-another-server.aspx


    Please click the Mark as answer button and vote as helpful if this reply solves your problem


    Saturday, February 15, 2014 3:07 AM
  • If you are dealing with this kind of errors or problem then your MS SQL database is corrupted, and you can’t access any table or data that stored in the database without repairing database. Repairing is also necessary because if you don’t repair it immediately then the database will be corrupted permanently. You can repair your corrupted MS SQL database by using :- http://www.filesrecoverytool.com/sql-database-repair.html


    Monday, August 18, 2014 6:58 AM