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
AntIt 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...
> 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...
> > 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
>
>|||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:
> 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...
> > > 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
> >
> >
> >|||"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...
> > 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
>
>|||"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.
> 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...
>> > 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
>>
--
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...
> > 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.
>
>
> >
> > 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...
> >> > 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
> >>
> >>
> >>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>