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 |
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
 Posted - 2010-11-02 : 16:06:04
|
| I need to create a stored procedure that would perform an automatic database restore from 1 server to anotherFirst step in my task is to be able to copy over the .bak file from 1 location to another across different serverHere is what I have createddeclare @bak_source varchar(255), @bak_dest varchar(255), @xcopycmd varchar(1000)set @bak_source='\\qa\d$\Full_2010.bak'set @bak_dest='\\dev\d$\Data'set @xcopycmd=N'xcopy '+ @bak_source +' '+ @bak_dest +'' select @xcopycmdEXEC master.dbo.xp_cmdshell @xcopycmd, NO_OUTPUTNOTHING GETS COPIED OVERAlso,Since the sp will be performing copying of files, does the login associated to execute this sp need to be a windows admin and a sys admin on sql as well ?What kind of access/ account should I create to make it same across all environment to aviod any permissions issue ? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
Posted - 2010-11-03 : 09:57:15
|
| Tara, This is the output of the @xcopycmdxcopy \\qa\d$\Full_2010.bak \\dev\d$\DataAnd this is the execution result Invalid drive specification0 File(s) copiedNULL |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-11-03 : 11:47:37
|
| go to windows explorer or Start/Run and put in \\qa\d$\ and hit enter, what do you get? same with \\dev\d$\Data what do you getIf you don't have the passion to help people, you have no passion |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
Posted - 2010-11-04 : 14:29:12
|
| go to windows explorer or Start/Run and put in \\qa\d$\ and hit enter, what do you get? same with \\dev\d$\Data what do you get'Im able to see the contents of the both folder'And if that (yosaisz's post) works, you'll need to do that same test while logged into the database server (like via RDP) using the SQL Server service accountThis going to be tough, as I dont have the service account password.Im currently need to propose to my boss what kind of access do we require to get this script working.Does it need to be a sql sys admin [server role] and also a windows admin on all of the environments since it involves accessing the windows dir and perfroming copy |
|
|
|
|
|
|
|
|
master.dbo.xp_cmdshell