Restore db from network location
-
We have two sql2005 servers, SQLA & SQLB, on the same windows 2003 domain. One customers db of size 50GB is on SQLA and it is periodically backed up to one network share say \\WINBACK\SQLBACK\. Now we want to create same customers db on SQLB server by restoring the backup from \\WINBACK\SQLBACK\. But SQL does not allow to restore the backup from network share even after mapping it to drive letter in SQLB. So, Does SQL support restoring from network location? do I need to create a blank db customers on SQLB before restoring or will restore automatically create customers db on SQLB? Which is the easiest way to complete this job? please help.
-
Hi
By default SQL do not detect network location. U can use below command to restore DB from the network location:
DBCC TRACEON (1807)
RESTORE DATABASE MyDb
FROM Disk = '\\server\f\SQL Backup\ABCD.bak'
WITH RECOVERY,REPLACE,
MOVE 'Test_Data' TO '\\server\f\MyDb.MDF',
MOVE 'Test_LOG' TO '\\server\f\MyDb.LDF'
u can ignore MOVE clause if u want to continue with the existing logical file.
Hope it'll work for u.
Amit
-
Mapped network shares are not visible to SQL when running on Windows 2003. To restore from the network have to be done using UNC like Amit showed above.
-
Be very carefull !!
DBCC TRACEON (1807) : Allows you to configure SQL Server with network-based database files. Not recommended because it’s difficult to guarantee IO at the network device !
This is not what you meanth !
You just wanted to restrore a db from a bak-file residing on a network drive.
Amit Kumar's example also puts the db-files on a network drive !
What you want is :
Restore database DDBAStatisticsJOBI
from disk='\\yourserver\yourshare\Fullbackup.BAK'
with MOVE N'db_Data' TO N'd:\MSSQL\data\DB_Data.mdf'
, MOVE N'db_Log' TO N'E:\MSSQL\DB_log.ldf'
-- , replace -- if needed !!
, recovery
Your SQLServer service account (or sqlagent service account ) needs access to the unc-file !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply