Backup/restore to different servers

Hi,
Is it possible to specify a server in a backup script & likewise specify a
restore to a different server e.g.
BACKUP DATABASE 'LIVESERVER..' + @.DBName
TO DISK = @.Path
Where LIVESERVER is the server you want to backup from?
Is this possible or must you backup & restore from the server you are in?
Many thanks for your help in advance
Ant
Oh well,
Thanks very much none the less Tibor
Ant
"Tibor Karaszi" wrote:

> It can only be a local database.
> If you have configured a linked server, then you can use sp_executesql to execute the backup command
> on the remote server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
>
>
|||But you can always do it remotely from a client utility using osql or sqlcmd
and direct the backup file to a network drive. If you want to manage multiple
server instances, T-SQL may not be the best tool. A scripting language or
even a batch file would be more convenient.
Linchi
"Ant" wrote:
[vbcol=seagreen]
> Oh well,
> Thanks very much none the less Tibor
> Ant
> "Tibor Karaszi" wrote:
|||"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> Hi,
> Is it possible to specify a server in a backup script & likewise specify a
> restore to a different server e.g.
> BACKUP DATABASE 'LIVESERVER..' + @.DBName
> TO DISK = @.Path
> Where LIVESERVER is the server you want to backup from?
> Is this possible or must you backup & restore from the server you are in?
> Many thanks for your help in advance
> Ant
You can't quite do that.
But you can do
BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
Assuming you have share permissions setup correctly.
You can then on otherserver do
RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
Or variations thereof.
And if you're really fancy, you can have a backup job on one server that
when it's done with its backup fires off a job ON the other server to do a
restore.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Hi greg,
That's pretty much what I'm after, I created a share folder & it backusp ok
but when it tries to restore it comes up with a device offline error:
Below is the sql for it: ( I removed the .bak extension off the finlesname
but didn't seem to make a differnce)
ALTER PROC MyBackupLiveData
@.Office NVARCHAR(4),
@.SecmanDB NVARCHAR(20),
@.ERPEDB NVARCHAR(20),
@.ControlDB NVARCHAR(20),
@.CompanyDB NVARCHAR(20)
AS
DECLARE @.FilePath NVARCHAR(60)
DECLARE @.FullPath NVARCHAR(60)
DECLARE @.DatePart CHAR(8)
DECLARE @.DBName NVARCHAR(30)
-- CREATE DATE TO APPEND TO BACKUP NAME
SET@.DatePart = '_' + CONVERT(NVARCHAR, DATEPART(yyyy,GETDATE()))+
CONVERT(NVARCHAR, DATEPART(mm,GETDATE())) +
CONVERT(NVARCHAR, DATEPART(dd,GETDATE()))
-- TEMP TBL TO STORE DB NAMES TO BE BACKED UP
CREATE Table #Databases(DBName NVARCHAR(20))
-- INSERT ALL TABLES PASSED INTO PROC
INSERT #Databases VALUES (@.SecmanDB)
INSERT #Databases VALUES (@.ERPDB)
INSERT #Databases VALUES (@.ControlDB)
INSERT #Databases VALUES (@.CompanyDB)
-- CREATE CURSOR
DECLARE TablesCursor CURSOR
FOR
SELECT DBName FROM #Databases
OPEN TablesCursor
FETCH NEXT FROM TablesCursor INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- SET PATH & FILENAME OF BACKUP FILE
SET @.FilePath = @.Office + '\' + @.DBName + @.DatePart + '.bak'
SET @.FullPath = 'C:\Backups\' + @.FilePath
-- PERFORM BACKUP FROM LIVE SERVER
BACKUP DATABASE @.DBName
TO DISK = @.FullPath
-- PERFORM RESTORE TO TEST SERVER
SET @.FullPath = '\\etestsql\Backups\' + @.FilePath
RESTORE DATABASE @.DBName
FROM DISK = @.FullPath
FETCH NEXT FROM TablesCursor INTO @.DBName
END
-- REMOVE ALL OBJECTS
CLOSE TablesCursor
DEALLOCATE TablesCursor
DROP TABLE #Databases
-- Thanks very much for your time on this
Ant
"Greg D. Moore (Strider)" wrote:

>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> You can't quite do that.
> But you can do
> BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
> Assuming you have share permissions setup correctly.
> You can then on otherserver do
> RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
> Or variations thereof.
> And if you're really fancy, you can have a backup job on one server that
> when it's done with its backup fires off a job ON the other server to do a
> restore.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>
|||"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:06B83767-9E27-4B0A-979F-C9CB6F398EA1@.microsoft.com...
> Hi greg,
> That's pretty much what I'm after, I created a share folder & it backusp
> ok
> but when it tries to restore it comes up with a device offline error:
Make sure the box doing the restore has access to \\etestsql\Backups\.
Remember, this needs to be in the context that the SQL Server engine runs
in.
[vbcol=seagreen]
> Below is the sql for it: ( I removed the .bak extension off the finlesname
> but didn't seem to make a differnce)
> ALTER PROC MyBackupLiveData
> @.Office NVARCHAR(4),
> @.SecmanDB NVARCHAR(20),
> @.ERPEDB NVARCHAR(20),
> @.ControlDB NVARCHAR(20),
> @.CompanyDB NVARCHAR(20)
> AS
> DECLARE @.FilePath NVARCHAR(60)
> DECLARE @.FullPath NVARCHAR(60)
> DECLARE @.DatePart CHAR(8)
> DECLARE @.DBName NVARCHAR(30)
>
> -- CREATE DATE TO APPEND TO BACKUP NAME
> SET@.DatePart = '_' + CONVERT(NVARCHAR, DATEPART(yyyy,GETDATE()))+
> CONVERT(NVARCHAR, DATEPART(mm,GETDATE())) +
> CONVERT(NVARCHAR, DATEPART(dd,GETDATE()))
>
> -- TEMP TBL TO STORE DB NAMES TO BE BACKED UP
> CREATE Table #Databases(DBName NVARCHAR(20))
>
> -- INSERT ALL TABLES PASSED INTO PROC
> INSERT #Databases VALUES (@.SecmanDB)
> INSERT #Databases VALUES (@.ERPDB)
> INSERT #Databases VALUES (@.ControlDB)
> INSERT #Databases VALUES (@.CompanyDB)
>
> -- CREATE CURSOR
> DECLARE TablesCursor CURSOR
> FOR
> SELECT DBName FROM #Databases
>
> OPEN TablesCursor
> FETCH NEXT FROM TablesCursor INTO @.DBName
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- SET PATH & FILENAME OF BACKUP FILE
> SET @.FilePath = @.Office + '\' + @.DBName + @.DatePart + '.bak'
> SET @.FullPath = 'C:\Backups\' + @.FilePath
>
> -- PERFORM BACKUP FROM LIVE SERVER
> BACKUP DATABASE @.DBName
> TO DISK = @.FullPath
>
> -- PERFORM RESTORE TO TEST SERVER
> SET @.FullPath = '\\etestsql\Backups\' + @.FilePath
> RESTORE DATABASE @.DBName
> FROM DISK = @.FullPath
>
> FETCH NEXT FROM TablesCursor INTO @.DBName
> END
>
> -- REMOVE ALL OBJECTS
> CLOSE TablesCursor
> DEALLOCATE TablesCursor
> DROP TABLE #Databases
>
> -- Thanks very much for your time on this
> Ant
>
>
>
> "Greg D. Moore (Strider)" wrote:
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Hi Greg,
Many thanks for your help. Your answer was in fact what helped me out here.
Much Appreciated
"Greg D. Moore (Strider)" wrote:

> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:06B83767-9E27-4B0A-979F-C9CB6F398EA1@.microsoft.com...
> Make sure the box doing the restore has access to \\etestsql\Backups\.
> Remember, this needs to be in the context that the SQL Server engine runs
> in.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>