Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2005-05-11 : 14:34:03
|
| Is the RESTORE command capable to restore a file from my local computer into a remote server? Production's BAK files are FTPed into my local every night. I want to create a job on my local to RESTORE these files on a remote server.Canada DBA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-05-11 : 16:15:55
|
| Yes you can use a UNC path in the FROM DISK part of the command.Tara |
 |
|
|
CanadaDBA
583 Posts |
Posted - 2005-05-12 : 08:25:07
|
You mean I run the following from my remote server:RESTORE DATABASE MyTest FROM DISK = '\\w.x.y.z\E$\MSSQL\Backups\MyTest_db_200503022000.BAK' WITH MOVE 'MyTest_dat' TO 'C:\MSSQL\Data\MyTest_Data.MDF', MOVE 'MyTest_log' TO 'C:\MSSQL\Data\MyTest_Log.LDF' Where the w.x.y.z is my local ip address. Does the Windows account on the remote server have access to my E:\MSSQL\Backups? Assume that SQLAdmin is the Windows account in the remote server, which starts the SQL Server service. Do I need to grant full access rights to my local to the SQLAdmin? How?quote:
Originally posted by tduggan
Yes you can use a UNC path in the FROM DISK part of the command.Tara
Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-05-12 : 12:22:01
|
| It'll use your account, whatever you are logged into Query Analyzer with.Tara |
|
|
|
CanadaDBA
583 Posts |
Posted - 2005-05-12 : 12:28:30
|
But I want to create a job and schedule it. So, there is no Query Analyzer.quote:
Originally posted by tduggan
It'll use your account, whatever you are logged into Query Analyzer with.Tara
Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-05-12 : 12:32:36
|
| Then it runs under the MSSQLSERVER account. So yes that account would need access to the network share. BTW, it's not recommended that you do it this way. You should be copying the file over to the remote server then performing a local restore.Tara |
|
|
|
CanadaDBA
583 Posts |
Posted - 2005-05-13 : 08:55:10
|
| BTW, it's not recommended that you do it this way. You should be copying the file over to the remote server then performing a local restore.Do you say this because of the production server might be busy? But our server is idle after midnight. Would you tell me your experience about this recommendation. Thanks,Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-05-13 : 12:16:17
|
| Your restore might be unsuccessful sometimes due to network problems. A network problem as small as one second will interrupt this process. It is recommended that restores happen locally as well as backups. All you need to do is add an xcopy step to your job and then have your restore command be a local restore to follow the recommendation.Tara |
|
|
|
|
|
|
RESTORE from local to a remote server