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 |
|
GRAYWOLF
Posting Yak Master
106 Posts |
 Posted - 2008-12-13 : 03:16:21
|
| Anyone know if it is possible to instigate a restore to a remote server using Redgate SQL Backup?Server A & Server B have SQL Backup installed. I would like to either:1) start a restore from Server A SQL Backup to Server B or 2) sent the command from Server A to Server B SQL Backup to restore.or3) Command from Server A instigates a restore on Server while passing pertinent information (backup file name, DB name, etc).---------------------------Working until "the morning sun sets the midnight sky on fire"! |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-15 : 04:48:09
|
| I don't know about RedGate but it is possible directly in SQL Server so I imagine RedGate would also support it.It is NOT available using SQL Server wizard, but it is possible using T-SQL, you will need to use the full path to remote file rather than a mapped drive. Also permissions to file - give the account SQL Server service runs under full permission to file:The easiest way to script backup:use wizard to create a script from a local backup file then to edit it with full path to remote file. |
 |
|
|
GRAYWOLF
Posting Yak Master
106 Posts |
Posted - 2008-12-16 : 07:52:30
|
| This is giving me an "incorrect syntax error near '@newdbname'": set @restorecmd = 'exec \\server\2k5\master..sqlbackup N''-SQL "RESTORE DATABASE '@newdbname'' I would assume it is due to adding the server, but I don't know how to add that part.---------------------------Working until "the morning sun sets the midnight sky on fire"! |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-16 : 09:32:21
|
| Try running the wizard generated script directly in a new query window but pointing to remote file.E.g.RESTORE DATABASE [testDB] FROM DISK = N'\\xxx.xxx.xxx.xxx\c$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DBName\DBName_backup_200812160106.bak' WITH FILE = 2, NOUNLOAD, STATS = 10GORemember that SQL Server service needs to be running under a domain account with permissions to the file. If you change the service account log-on you need to restart the service. |
|
|
|
GRAYWOLF
Posting Yak Master
106 Posts |
Posted - 2008-12-16 : 09:40:57
|
| I can restore from a remote file, I need to restore onto a remote server. I want a command on Server A to initiate a restore onto Server B. Whether the job runs from Server A or Server A sends a command to Server B that causes the job to run on Server B.---------------------------Working until "the morning sun sets the midnight sky on fire"! |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-16 : 10:37:47
|
| Sorry picked you up wrong:Ways you can use1. For a one-off - Make new connection to remote server and directly run query2. As scheduled job on remote server - Save query on remote database as job pointing to backup file which you update e.g. using script / batch file3. As scheduled job on non-remote - use osql either using xp_cmdshell or in batch file: osql -E -S serverName -n -Q "RESTORE DATABASE db FROM DISK = '\\server\public\db.bak' WITH REPLACE" |
|
|
|
|
|
|
Remote Restore With Redgate