Showing posts with label disk. Show all posts
Showing posts with label disk. Show all posts

Thursday, March 29, 2012

backup strategy question

I appologize if this question seems too basic but I need some advice
On one of the SQL servers I manage, I do backups on disk. I need to keep a
historyof backups for at least several weeks.
I currently do full backups twice a week, differential backup on days that I
don't do full backup and transaction log backup every 10 minutes (this is
for a high traffic DB).
If I replace the current backup with every new full backup, then I can't
keep a history of backups but if I keep on appending, my backup files get
huge.
How can I configure my backups to keep only, let say, 3 weeks of backups?
Can that be done all in SQL server or will I have to write scripts outside
of SQL server to archive backup files?
Thanks for your helpHi, you can modify the time expiration of your backups and configure the
files like non overwrite.
Bye
"Benoit Martin" wrote:
> I appologize if this question seems too basic but I need some advice
> On one of the SQL servers I manage, I do backups on disk. I need to keep a
> historyof backups for at least several weeks.
> I currently do full backups twice a week, differential backup on days that I
> don't do full backup and transaction log backup every 10 minutes (this is
> for a high traffic DB).
> If I replace the current backup with every new full backup, then I can't
> keep a history of backups but if I keep on appending, my backup files get
> huge.
> How can I configure my backups to keep only, let say, 3 weeks of backups?
> Can that be done all in SQL server or will I have to write scripts outside
> of SQL server to archive backup files?
> Thanks for your help
>
>|||Thanks John,
when you talk about modifying the time of expiration of the backup, are you
talking about the "Backup set will expire:" section under the "options" tab
when adding a new backup?
If that's the case, this option is available only when I choose to overwrite
the existing media which go against your second advice which was not to
overwrite.
Am I missing something?
PS: in case it matters, I forgot to mention that I am using SQL Server 2000
Thanks
"John Bocachica" <John Bocachica@.discussions.microsoft.com> wrote in message
news:EDF7465B-0A19-4D6A-B1AE-C51358A24F4E@.microsoft.com...
> Hi, you can modify the time expiration of your backups and configure the
> files like non overwrite.
> Bye
>
> "Benoit Martin" wrote:
> > I appologize if this question seems too basic but I need some advice
> >
> > On one of the SQL servers I manage, I do backups on disk. I need to keep
a
> > historyof backups for at least several weeks.
> > I currently do full backups twice a week, differential backup on days
that I
> > don't do full backup and transaction log backup every 10 minutes (this
is
> > for a high traffic DB).
> > If I replace the current backup with every new full backup, then I can't
> > keep a history of backups but if I keep on appending, my backup files
get
> > huge.
> > How can I configure my backups to keep only, let say, 3 weeks of
backups?
> > Can that be done all in SQL server or will I have to write scripts
outside
> > of SQL server to archive backup files?
> >
> > Thanks for your help
> >
> >
> >|||You can not use the expiration dates in the fashion that you want. When you
append backups to the same device (file) you have an all or nothing
situation when it comes to deleting old backups. You can not delete or
overwrite individual backups from a single device. You are best served by
creating a new backup file each time and either deleting them by their
timestamp at the file level or by naming them with a convention that will
allow you to tell when they expire. You should seriously consider using SQL
LiteSpeed for your backups. It will not only speed them up but same a lot
of disk space. http://www.imceda.com/
But here is some code examples to backup to a different file each night and
some code to remove older backups.
-- Do a backup and create a separate file for each day of the
eek --
DECLARE @.DBName NVARCHAR(50), @.Device NVARCHAR(100), @.Name NVARCHAR(100)
IF OBJECT_ID('tempdb..#DBs') IS NOT NULL
DROP TABLE #DBs
CREATE TABLE #DBs ([name] VARCHAR(50),[db_size] VARCHAR(20),
[Owner] VARCHAR(20),[DBID] INT, [Created] VARCHAR(14),
[Status] VARCHAR(1000), [Compatibility_Level] INT)
INSERT INTO #DBs EXEC sp_helpdb
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT [Name]
FROM #DBs
WHERE [DBID] IN (5,6)
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Backups\DD_' + @.DBName + '_Full_' +
CAST(DAY(GETDATE()) AS NVARCHAR(4)) +
CAST(MONTH(GETDATE()) AS NVARCHAR(4)) +
CAST(YEAR(GETDATE()) AS NVARCHAR(8)) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
----
-- Removing Older Backup Files --
DECLARE @.Error INT, @.D DATETIME
SET @.D = CAST('20020801 15:00:00' AS DATETIME)
EXEC @.Error = remove_old_log_files @.D
SELECT @.Error
----
-- *** Procedure to remove old backups **** --
CREATE PROCEDURE remove_old_log_files
@.DelDate DATETIME
AS
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
SET DATEFORMAT MDY
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
CREATE TABLE #Errors (Results VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD C:\Backups\*.trn'
SET @.Error = @.@.ERROR
IF @.Error <> 0
BEGIN
SET @.Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END
--SELECT * FROM #dirList
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
AND SUBSTRING(FName,40,40) LIKE '%.TRN'
OPEN curDir
FETCH NEXT FROM curDir INTO @.Fname
WHILE (@.@.fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @.Delete = 'DEL "C:\Backups\' + @.FName + '"'
INSERT INTO #Errors (Results)
exec master..xp_cmdshell @.Delete
IF @.@.RowCount > 1
BEGIN
SET @.Error = -1
SET @.Msg = 'Error while Deleting file ' + @.FName
GOTO On_Error
END
-- PRINT @.Delete
PRINT 'Deleted ' + @.FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
FETCH NEXT FROM curDir INTO @.Fname
END
CLOSE curDir
DEALLOCATE curDir
DROP TABLE #DirList
DROP TABLE #Errors
RETURN @.Error
On_Error:
BEGIN
IF @.Error <> 0
BEGIN
SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
RAISERROR(@.Msg,12,1)
RETURN @.Error
END
END
GO
Andrew J. Kelly SQL MVP
"Benoit Martin" <benoit@.digitalmediums.com> wrote in message
news:e6sG0$ZgEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Thanks John,
> when you talk about modifying the time of expiration of the backup, are
you
> talking about the "Backup set will expire:" section under the "options"
tab
> when adding a new backup?
> If that's the case, this option is available only when I choose to
overwrite
> the existing media which go against your second advice which was not to
> overwrite.
> Am I missing something?
> PS: in case it matters, I forgot to mention that I am using SQL Server
2000
> Thanks
> "John Bocachica" <John Bocachica@.discussions.microsoft.com> wrote in
message
> news:EDF7465B-0A19-4D6A-B1AE-C51358A24F4E@.microsoft.com...
> > Hi, you can modify the time expiration of your backups and configure the
> > files like non overwrite.
> >
> > Bye
> >
> >
> > "Benoit Martin" wrote:
> >
> > > I appologize if this question seems too basic but I need some advice
> > >
> > > On one of the SQL servers I manage, I do backups on disk. I need to
keep
> a
> > > historyof backups for at least several weeks.
> > > I currently do full backups twice a week, differential backup on days
> that I
> > > don't do full backup and transaction log backup every 10 minutes (this
> is
> > > for a high traffic DB).
> > > If I replace the current backup with every new full backup, then I
can't
> > > keep a history of backups but if I keep on appending, my backup files
> get
> > > huge.
> > > How can I configure my backups to keep only, let say, 3 weeks of
> backups?
> > > Can that be done all in SQL server or will I have to write scripts
> outside
> > > of SQL server to archive backup files?
> > >
> > > Thanks for your help
> > >
> > >
> > >
>

backup strategy question

I appologize if this question seems too basic but I need some advice
On one of the SQL servers I manage, I do backups on disk. I need to keep a
historyof backups for at least several weeks.
I currently do full backups twice a week, differential backup on days that I
don't do full backup and transaction log backup every 10 minutes (this is
for a high traffic DB).
If I replace the current backup with every new full backup, then I can't
keep a history of backups but if I keep on appending, my backup files get
huge.
How can I configure my backups to keep only, let say, 3 weeks of backups?
Can that be done all in SQL server or will I have to write scripts outside
of SQL server to archive backup files?
Thanks for your help
Hi, you can modify the time expiration of your backups and configure the
files like non overwrite.
Bye
"Benoit Martin" wrote:

> I appologize if this question seems too basic but I need some advice
> On one of the SQL servers I manage, I do backups on disk. I need to keep a
> historyof backups for at least several weeks.
> I currently do full backups twice a week, differential backup on days that I
> don't do full backup and transaction log backup every 10 minutes (this is
> for a high traffic DB).
> If I replace the current backup with every new full backup, then I can't
> keep a history of backups but if I keep on appending, my backup files get
> huge.
> How can I configure my backups to keep only, let say, 3 weeks of backups?
> Can that be done all in SQL server or will I have to write scripts outside
> of SQL server to archive backup files?
> Thanks for your help
>
>
|||Thanks John,
when you talk about modifying the time of expiration of the backup, are you
talking about the "Backup set will expire:" section under the "options" tab
when adding a new backup?
If that's the case, this option is available only when I choose to overwrite
the existing media which go against your second advice which was not to
overwrite.
Am I missing something?
PS: in case it matters, I forgot to mention that I am using SQL Server 2000
Thanks
"John Bocachica" <John Bocachica@.discussions.microsoft.com> wrote in message
news:EDF7465B-0A19-4D6A-B1AE-C51358A24F4E@.microsoft.com...[vbcol=seagreen]
> Hi, you can modify the time expiration of your backups and configure the
> files like non overwrite.
> Bye
>
> "Benoit Martin" wrote:
a[vbcol=seagreen]
that I[vbcol=seagreen]
is[vbcol=seagreen]
get[vbcol=seagreen]
backups?[vbcol=seagreen]
outside[vbcol=seagreen]
|||You can not use the expiration dates in the fashion that you want. When you
append backups to the same device (file) you have an all or nothing
situation when it comes to deleting old backups. You can not delete or
overwrite individual backups from a single device. You are best served by
creating a new backup file each time and either deleting them by their
timestamp at the file level or by naming them with a convention that will
allow you to tell when they expire. You should seriously consider using SQL
LiteSpeed for your backups. It will not only speed them up but same a lot
of disk space. http://www.imceda.com/
But here is some code examples to backup to a different file each night and
some code to remove older backups.
-- Do a backup and create a separate file for each day of the
eek --
DECLARE @.DBName NVARCHAR(50), @.Device NVARCHAR(100), @.Name NVARCHAR(100)
IF OBJECT_ID('tempdb..#DBs') IS NOT NULL
DROP TABLE #DBs
CREATE TABLE #DBs ([name] VARCHAR(50),[db_size] VARCHAR(20),
[Owner] VARCHAR(20),[DBID] INT, [Created] VARCHAR(14),
[Status] VARCHAR(1000), [Compatibility_Level] INT)
INSERT INTO #DBs EXEC sp_helpdb
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT [Name]
FROM #DBs
WHERE [DBID] IN (5,6)
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Backups\DD_' + @.DBName + '_Full_' +
CAST(DAY(GETDATE()) AS NVARCHAR(4)) +
CAST(MONTH(GETDATE()) AS NVARCHAR(4)) +
CAST(YEAR(GETDATE()) AS NVARCHAR(8)) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
-- Removing Older Backup Files --
DECLARE @.Error INT, @.D DATETIME
SET @.D = CAST('20020801 15:00:00' AS DATETIME)
EXEC @.Error = remove_old_log_files @.D
SELECT @.Error
-- *** Procedure to remove old backups **** --
CREATE PROCEDURE remove_old_log_files
@.DelDate DATETIME
AS
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
SET DATEFORMAT MDY
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
CREATE TABLE #Errors (Results VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD C:\Backups\*.trn'
SET @.Error = @.@.ERROR
IF @.Error <> 0
BEGIN
SET @.Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END
--SELECT * FROM #dirList
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
AND SUBSTRING(FName,40,40) LIKE '%.TRN'
OPEN curDir
FETCH NEXT FROM curDir INTO @.Fname
WHILE (@.@.fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @.Delete = 'DEL "C:\Backups\' + @.FName + '"'
INSERT INTO #Errors (Results)
exec master..xp_cmdshell @.Delete
IF @.@.RowCount > 1
BEGIN
SET @.Error = -1
SET @.Msg = 'Error while Deleting file ' + @.FName
GOTO On_Error
END
-- PRINT @.Delete
PRINT 'Deleted ' + @.FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
FETCH NEXT FROM curDir INTO @.Fname
END
CLOSE curDir
DEALLOCATE curDir
DROP TABLE #DirList
DROP TABLE #Errors
RETURN @.Error
On_Error:
BEGIN
IF @.Error <> 0
BEGIN
SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
RAISERROR(@.Msg,12,1)
RETURN @.Error
END
END
GO
Andrew J. Kelly SQL MVP
"Benoit Martin" <benoit@.digitalmediums.com> wrote in message
news:e6sG0$ZgEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Thanks John,
> when you talk about modifying the time of expiration of the backup, are
you
> talking about the "Backup set will expire:" section under the "options"
tab
> when adding a new backup?
> If that's the case, this option is available only when I choose to
overwrite
> the existing media which go against your second advice which was not to
> overwrite.
> Am I missing something?
> PS: in case it matters, I forgot to mention that I am using SQL Server
2000
> Thanks
> "John Bocachica" <John Bocachica@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
> news:EDF7465B-0A19-4D6A-B1AE-C51358A24F4E@.microsoft.com...
keep[vbcol=seagreen]
> a
> that I
> is
can't
> get
> backups?
> outside
>

backup strategy question

I appologize if this question seems too basic but I need some advice
On one of the SQL servers I manage, I do backups on disk. I need to keep a
historyof backups for at least several weeks.
I currently do full backups twice a week, differential backup on days that I
don't do full backup and transaction log backup every 10 minutes (this is
for a high traffic DB).
If I replace the current backup with every new full backup, then I can't
keep a history of backups but if I keep on appending, my backup files get
huge.
How can I configure my backups to keep only, let say, 3 weeks of backups?
Can that be done all in SQL server or will I have to write scripts outside
of SQL server to archive backup files?
Thanks for your helpHi, you can modify the time expiration of your backups and configure the
files like non overwrite.
Bye
"Benoit Martin" wrote:

> I appologize if this question seems too basic but I need some advice
> On one of the SQL servers I manage, I do backups on disk. I need to keep a
> historyof backups for at least several weeks.
> I currently do full backups twice a week, differential backup on days that
I
> don't do full backup and transaction log backup every 10 minutes (this is
> for a high traffic DB).
> If I replace the current backup with every new full backup, then I can't
> keep a history of backups but if I keep on appending, my backup files get
> huge.
> How can I configure my backups to keep only, let say, 3 weeks of backups?
> Can that be done all in SQL server or will I have to write scripts outside
> of SQL server to archive backup files?
> Thanks for your help
>
>|||Thanks John,
when you talk about modifying the time of expiration of the backup, are you
talking about the "Backup set will expire:" section under the "options" tab
when adding a new backup?
If that's the case, this option is available only when I choose to overwrite
the existing media which go against your second advice which was not to
overwrite.
Am I missing something?
PS: in case it matters, I forgot to mention that I am using SQL Server 2000
Thanks
"John Bocachica" <John Bocachica@.discussions.microsoft.com> wrote in message
news:EDF7465B-0A19-4D6A-B1AE-C51358A24F4E@.microsoft.com...[vbcol=seagreen]
> Hi, you can modify the time expiration of your backups and configure the
> files like non overwrite.
> Bye
>
> "Benoit Martin" wrote:
>
a[vbcol=seagreen]
that I[vbcol=seagreen]
is[vbcol=seagreen]
get[vbcol=seagreen]
backups?[vbcol=seagreen]
outside[vbcol=seagreen]|||You can not use the expiration dates in the fashion that you want. When you
append backups to the same device (file) you have an all or nothing
situation when it comes to deleting old backups. You can not delete or
overwrite individual backups from a single device. You are best served by
creating a new backup file each time and either deleting them by their
timestamp at the file level or by naming them with a convention that will
allow you to tell when they expire. You should seriously consider using SQL
LiteSpeed for your backups. It will not only speed them up but same a lot
of disk space. http://www.imceda.com/
But here is some code examples to backup to a different file each night and
some code to remove older backups.
-- Do a backup and create a separate file for each day of the
eek --
DECLARE @.DBName NVARCHAR(50), @.Device NVARCHAR(100), @.Name NVARCHAR(100)
IF OBJECT_ID('tempdb..#DBs') IS NOT NULL
DROP TABLE #DBs
CREATE TABLE #DBs ([name] VARCHAR(50),[db_size] VARCHAR(20),
[Owner] VARCHAR(20),[DBID] INT, [Created] VARCHAR(14),
[Status] VARCHAR(1000), [Compatibility_Level] INT)
INSERT INTO #DBs EXEC sp_helpdb
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT [Name]
FROM #DBs
WHERE [DBID] IN (5,6)
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Backups\DD_' + @.DBName + '_Full_' +
CAST(DAY(GETDATE()) AS NVARCHAR(4)) +
CAST(MONTH(GETDATE()) AS NVARCHAR(4)) +
CAST(YEAR(GETDATE()) AS NVARCHAR(8)) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
----
-- Removing Older Backup Files --
DECLARE @.Error INT, @.D DATETIME
SET @.D = CAST('20020801 15:00:00' AS DATETIME)
EXEC @.Error = remove_old_log_files @.D
SELECT @.Error
----
-- *** Procedure to remove old backups **** --
CREATE PROCEDURE remove_old_log_files
@.DelDate DATETIME
AS
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
SET DATEFORMAT MDY
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
CREATE TABLE #Errors (Results VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD C:\Backups\*.trn'
SET @.Error = @.@.ERROR
IF @.Error <> 0
BEGIN
SET @.Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END
--SELECT * FROM #dirList
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
AND SUBSTRING(FName,40,40) LIKE '%.TRN'
OPEN curDir
FETCH NEXT FROM curDir INTO @.Fname
WHILE (@.@.fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @.Delete = 'DEL "C:\Backups' + @.FName + '"'
INSERT INTO #Errors (Results)
exec master..xp_cmdshell @.Delete
IF @.@.RowCount > 1
BEGIN
SET @.Error = -1
SET @.Msg = 'Error while Deleting file ' + @.FName
GOTO On_Error
END
-- PRINT @.Delete
PRINT 'Deleted ' + @.FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
FETCH NEXT FROM curDir INTO @.Fname
END
CLOSE curDir
DEALLOCATE curDir
DROP TABLE #DirList
DROP TABLE #Errors
RETURN @.Error
On_Error:
BEGIN
IF @.Error <> 0
BEGIN
SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
RAISERROR(@.Msg,12,1)
RETURN @.Error
END
END
GO
Andrew J. Kelly SQL MVP
"Benoit Martin" <benoit@.digitalmediums.com> wrote in message
news:e6sG0$ZgEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Thanks John,
> when you talk about modifying the time of expiration of the backup, are
you
> talking about the "Backup set will expire:" section under the "options"
tab
> when adding a new backup?
> If that's the case, this option is available only when I choose to
overwrite
> the existing media which go against your second advice which was not to
> overwrite.
> Am I missing something?
> PS: in case it matters, I forgot to mention that I am using SQL Server
2000
> Thanks
> "John Bocachica" <John Bocachica@.discussions.microsoft.com> wrote in
message
> news:EDF7465B-0A19-4D6A-B1AE-C51358A24F4E@.microsoft.com...
keep[vbcol=seagreen]
> a
> that I
> is
can't[vbcol=seagreen]
> get
> backups?
> outside
>

Backup Standards and Suggestions

Thanks Ekrem.
In your opinion which one is better, using backup to disk? Or directly to
tape; considering I can get tape drive hooked directly to SQL Server.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
It depends on the scenarios. You can see some scenarios from the following
articles.
Some of them:
http://www.smallbusinesscomputing.com/testdrive/article.php/3528326
http://www.storagesearch.com/engenio-art2.html
Ekrem ?nsoy
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:79E1C65C-5732-4957-B18B-14B765A9D558@.microsoft.com...
> Thanks Ekrem.
> In your opinion which one is better, using backup to disk? Or directly to
> tape; considering I can get tape drive hooked directly to SQL Server.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
|||Thanks! Those are good links, now I can backup some of what I say to
management ;-). Thanks again.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
|||I'm glad they helped, good luck! =)
Ekrem ?nsoy
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:59BFA06B-DCAE-4840-8CBF-2C36A946D056@.microsoft.com...
> Thanks! Those are good links, now I can backup some of what I say to
> management ;-). Thanks again.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
sql

Tuesday, March 27, 2012

backup sql with sripts

I have database in sql 'crmsource.mdf" I like to automate a backup for it.
OSQL -Usa -PmyPasword -n -Q "BACKUP DATABASE crmsource TO DISK =
'c:\backup\crmsource.dat_bak'"
I get an error:
[shared memory] SQL Server does not exist or access denied.
[shared memory]connectionOpen <connect>>.
Am I haveing this problem because the database is open? Is there a way to
backup without closing the database?
No you wont need to close the DB, you want to perform a hot backup. Hot
backups are done during the database is in use.
Make sure that you are heading for the right server with specifying the
server you wan to connect and issue the command via the
switch -SServername[\InstanceName].
If you aalready stuck with this, read the article
http://support.microsoft.com/default...;en-us;Q328306 that will
guide you to identify your problem.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"gemirkhanian@.hotmail.com"
<gemirkhanianhotmailcom@.discussions.microsoft.com> schrieb im Newsbeitrag
news:263E0B7C-D7AB-4474-A074-D3B177DD6CF3@.microsoft.com...
>I have database in sql 'crmsource.mdf" I like to automate a backup for it.
> OSQL -Usa -PmyPasword -n -Q "BACKUP DATABASE crmsource TO DISK =
> 'c:\backup\crmsource.dat_bak'"
> I get an error:
> [shared memory] SQL Server does not exist or access denied.
> [shared memory]connectionOpen <connect>>.
> Am I haveing this problem because the database is open? Is there a way to
> backup without closing the database?
|||Hello Jens
I created a batch file, content of the batch file is
OSQL -Sfs5-win\grouplink -Usa -Ppassword -n "BACKUP DATABASE crmsource TO
DISK" = 'c:\backup\crmsource.dat_bak'
I now get a syntax error.
I am still trying to automate a backup please help.
"Jens Sü?meyer" wrote:

> No you won′t need to close the DB, you want to perform a hot backup. Hot
> backups are done during the database is in use.
> Make sure that you are heading for the right server with specifying the
> server you wan to connect and issue the command via the
> switch -SServername[\InstanceName].
> If you aalready stuck with this, read the article
> http://support.microsoft.com/default...;en-us;Q328306 that will
> guide you to identify your problem.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "gemirkhanian@.hotmail.com"
> <gemirkhanianhotmailcom@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:263E0B7C-D7AB-4474-A074-D3B177DD6CF3@.microsoft.com...
>
>
|||What always helps in newsgroups is the exact error information, becasue
syntax errors are a wide range to search for, i guess in your examples you
didnt put the quotes right around that and that qour switch isnt mentioned
in a right way you, the best way is to use the -Q for the Query you want to
execute:
OSQL -Sfs5-win\grouplink -Usa -Ppassword -n -Q"BACKUP DATABASE crmsource TO
DISK = 'c:\backup\crmsource.dat_bak'"
That should work via Copy & Paste.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"gemirkhanian@.hotmail.com"
<gemirkhanianhotmailcom@.discussions.microsoft.com> schrieb im Newsbeitrag
news:65E020C3-67CB-4EDE-9E88-AF6B0994240B@.microsoft.com...[vbcol=seagreen]
> Hello Jens
> I created a batch file, content of the batch file is
> OSQL -Sfs5-win\grouplink -Usa -Ppassword -n "BACKUP DATABASE crmsource TO
> DISK" = 'c:\backup\crmsource.dat_bak'
> I now get a syntax error.
> I am still trying to automate a backup please help.
>
> "Jens Smeyer" wrote:

Backup SQL to both TAPE AND DISK

Here's my problem. I want to perform occasional full backups on a tape but I
also want to backup to disk. The disk backup does a full backup every night
and trans log backups every half hour between 7 AM and 7 PM. The tape backup
(Veritas) just performs a full backup every day. Now when I restore, I don't
want my disk backup to "depend" on my tape backup. In other words, I don't
want to see the tape backup in the SQL backup log when I'm trying to
performs a restore from the disk backups. Is there a way to do this?
Thanks
jBA database backup doesn't break the chain of log backups so you are fine. Yo
u can apply any of the
database backups as long as you apply all subsequent log backups in sequence
. But why not skip the
Veritas SQL Server agent and let Veritas pick up the database backup files?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"msnews.microsoft.com" <jbonds@.XXX.com> wrote in message
news:O2XVi3BWGHA.4960@.TK2MSFTNGP05.phx.gbl...
> Here's my problem. I want to perform occasional full backups on a tape but
I also want to backup
> to disk. The disk backup does a full backup every night and trans log back
ups every half hour
> between 7 AM and 7 PM. The tape backup (Veritas) just performs a full back
up every day. Now when I
> restore, I don't want my disk backup to "depend" on my tape backup. In oth
er words, I don't want
> to see the tape backup in the SQL backup log when I'm trying to performs a
restore from the disk
> backups. Is there a way to do this?
> Thanks
> jB
>|||msnews.microsoft.com wrote:
> Here's my problem. I want to perform occasional full backups on a tape but
I
> also want to backup to disk. The disk backup does a full backup every nigh
t
> and trans log backups every half hour between 7 AM and 7 PM. The tape back
up
> (Veritas) just performs a full backup every day. Now when I restore, I don
't
> want my disk backup to "depend" on my tape backup. In other words, I don't
> want to see the tape backup in the SQL backup log when I'm trying to
> performs a restore from the disk backups. Is there a way to do this?
> Thanks
> jB
Unless you are also doing Differential backups, nothing will "depend"
on your full backup to tape.
Why do you care what is in the backup log (I assume you mean the
backupfile table)? If you aren't using Maintenance Plans you can safely
delete from that table anyway so maybe you could just delete stuff you
don't want to see.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

Sunday, March 25, 2012

Backup set will expire

If I set this option when backing up to the hard disk as 14 days, will
it delete any backup files older than 14 days?
Hi
No. It just writes into the backup set that this backup may be overwritten
by SQL Server in 14 days. It does not delete the file.
In effect, you can have multiple backups in one backup file, and only after
14 days may it throw away the backup within the file.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<menlo11@.hotmail.com> wrote in message
news:1123707091.519058.272270@.f14g2000cwb.googlegr oups.com...
> If I set this option when backing up to the hard disk as 14 days, will
> it delete any backup files older than 14 days?
>
|||All that being said, I have tons of backup files from 2004 til today
taking up about 50 GB of space on one of our file servers. What is the
best way to handle that situation? Should I just delete most of the
backup files, just keeping the last month or so? Is there a way to
archive the earlier backups? Also, how do I stop this from happening
in the future?
|||Yes, you need to make sure that old backup files are deleted so you don't fill your disks. Easiest
to automate this is to use the Maintenance Wizard do your backups. Or write your own TSQL code to do
this (most name the backup files by code where name include date and time and then delete the files
based on that name).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<menlo11@.hotmail.com> wrote in message news:1123711188.786027.258880@.f14g2000cwb.googlegr oups.com...
> All that being said, I have tons of backup files from 2004 til today
> taking up about 50 GB of space on one of our file servers. What is the
> best way to handle that situation? Should I just delete most of the
> backup files, just keeping the last month or so? Is there a way to
> archive the earlier backups? Also, how do I stop this from happening
> in the future?
>

Backup set will expire

If I set this option when backing up to the hard disk as 14 days, will
it delete any backup files older than 14 days?Hi
No. It just writes into the backup set that this backup may be overwritten
by SQL Server in 14 days. It does not delete the file.
In effect, you can have multiple backups in one backup file, and only after
14 days may it throw away the backup within the file.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<menlo11@.hotmail.com> wrote in message
news:1123707091.519058.272270@.f14g2000cwb.googlegroups.com...
> If I set this option when backing up to the hard disk as 14 days, will
> it delete any backup files older than 14 days?
>|||All that being said, I have tons of backup files from 2004 til today
taking up about 50 GB of space on one of our file servers. What is the
best way to handle that situation? Should I just delete most of the
backup files, just keeping the last month or so? Is there a way to
archive the earlier backups? Also, how do I stop this from happening
in the future?|||Yes, you need to make sure that old backup files are deleted so you don't fill your disks. Easiest
to automate this is to use the Maintenance Wizard do your backups. Or write your own TSQL code to do
this (most name the backup files by code where name include date and time and then delete the files
based on that name).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<menlo11@.hotmail.com> wrote in message news:1123711188.786027.258880@.f14g2000cwb.googlegroups.com...
> All that being said, I have tons of backup files from 2004 til today
> taking up about 50 GB of space on one of our file servers. What is the
> best way to handle that situation? Should I just delete most of the
> backup files, just keeping the last month or so? Is there a way to
> archive the earlier backups? Also, how do I stop this from happening
> in the future?
>

Backup set will expire

If I set this option when backing up to the hard disk as 14 days, will
it delete any backup files older than 14 days?Hi
No. It just writes into the backup set that this backup may be overwritten
by SQL Server in 14 days. It does not delete the file.
In effect, you can have multiple backups in one backup file, and only after
14 days may it throw away the backup within the file.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<menlo11@.hotmail.com> wrote in message
news:1123707091.519058.272270@.f14g2000cwb.googlegroups.com...
> If I set this option when backing up to the hard disk as 14 days, will
> it delete any backup files older than 14 days?
>|||All that being said, I have tons of backup files from 2004 til today
taking up about 50 GB of space on one of our file servers. What is the
best way to handle that situation? Should I just delete most of the
backup files, just keeping the last month or so? Is there a way to
archive the earlier backups? Also, how do I stop this from happening
in the future?|||Yes, you need to make sure that old backup files are deleted so you don't fi
ll your disks. Easiest
to automate this is to use the Maintenance Wizard do your backups. Or write
your own TSQL code to do
this (most name the backup files by code where name include date and time an
d then delete the files
based on that name).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<menlo11@.hotmail.com> wrote in message news:1123711188.786027.258880@.f14g2000cwb.googlegroup
s.com...
> All that being said, I have tons of backup files from 2004 til today
> taking up about 50 GB of space on one of our file servers. What is the
> best way to handle that situation? Should I just delete most of the
> backup files, just keeping the last month or so? Is there a way to
> archive the earlier backups? Also, how do I stop this from happening
> in the future?
>

Thursday, March 22, 2012

Backup Schedule Troubles

Some how, I've managed to create a backup (disk backup) with three different
schedules. At 6pm each day, the database backups three times (three
different names).
What I'd like to have is... each Monday the previous Monday's backup would
be overwritten, each Tuesday the previous Tuesday's backup would be
overwritten, etc...
Now, I can't figure out how to remove the schedules, as it appears that I
only see one schedule and destination when I go in to the backup.
How do I get to a "clean slate" and accomplish my ideal backup scenario.
Thanks.Check under management, SQL Server Agent, Jobs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"09evaca" <09evaca@.discussions.microsoft.com> wrote in message
news:D8D3EAF7-90A5-495C-B9D5-37E933FDF24A@.microsoft.com...
> Some how, I've managed to create a backup (disk backup) with three different
> schedules. At 6pm each day, the database backups three times (three
> different names).
> What I'd like to have is... each Monday the previous Monday's backup would
> be overwritten, each Tuesday the previous Tuesday's backup would be
> overwritten, etc...
> Now, I can't figure out how to remove the schedules, as it appears that I
> only see one schedule and destination when I go in to the backup.
> How do I get to a "clean slate" and accomplish my ideal backup scenario.
> Thanks.

Backup Schedule Troubles

Some how, I've managed to create a backup (disk backup) with three different
schedules. At 6pm each day, the database backups three times (three
different names).
What I'd like to have is... each Monday the previous Monday's backup would
be overwritten, each Tuesday the previous Tuesday's backup would be
overwritten, etc...
Now, I can't figure out how to remove the schedules, as it appears that I
only see one schedule and destination when I go in to the backup.
How do I get to a "clean slate" and accomplish my ideal backup scenario.
Thanks.
Check under management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"09evaca" <09evaca@.discussions.microsoft.com> wrote in message
news:D8D3EAF7-90A5-495C-B9D5-37E933FDF24A@.microsoft.com...
> Some how, I've managed to create a backup (disk backup) with three different
> schedules. At 6pm each day, the database backups three times (three
> different names).
> What I'd like to have is... each Monday the previous Monday's backup would
> be overwritten, each Tuesday the previous Tuesday's backup would be
> overwritten, etc...
> Now, I can't figure out how to remove the schedules, as it appears that I
> only see one schedule and destination when I go in to the backup.
> How do I get to a "clean slate" and accomplish my ideal backup scenario.
> Thanks.
sql

Backup Schedule Troubles

Some how, I've managed to create a backup (disk backup) with three different
schedules. At 6pm each day, the database backups three times (three
different names).
What I'd like to have is... each Monday the previous Monday's backup would
be overwritten, each Tuesday the previous Tuesday's backup would be
overwritten, etc...
Now, I can't figure out how to remove the schedules, as it appears that I
only see one schedule and destination when I go in to the backup.
How do I get to a "clean slate" and accomplish my ideal backup scenario.
Thanks.Check under management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"09evaca" <09evaca@.discussions.microsoft.com> wrote in message
news:D8D3EAF7-90A5-495C-B9D5-37E933FDF24A@.microsoft.com...
> Some how, I've managed to create a backup (disk backup) with three differe
nt
> schedules. At 6pm each day, the database backups three times (three
> different names).
> What I'd like to have is... each Monday the previous Monday's backup would
> be overwritten, each Tuesday the previous Tuesday's backup would be
> overwritten, etc...
> Now, I can't figure out how to remove the schedules, as it appears that I
> only see one schedule and destination when I go in to the backup.
> How do I get to a "clean slate" and accomplish my ideal backup scenario.
> Thanks.

Tuesday, March 20, 2012

Backup restore linking

Hi guys,
I need some help with backups and restores.
I want to run a backup to disk on server1 followed by a restore on server2
(Sort of replication)
I know how to do this with separate jobs or procedures on each server.
But, is there any way to run this as a single job on server2?
The goal is to abort restore if backup fails rather than relying on "timing"
I'm having problems specifying connections. Not sure if it's doable.
Thanks,
Jim
PS: DTS is too big a mystery...
(Both servers are SQL 2000)
Server 1 should already have a preset backup routine that includes a FULL
backup. I would recommend you use that backup instead otherwise you run the
risk of interfering with the existing backup and or restore strategy
depending on how it is set up. And why duplicate the effort in the first
place. Then just make sure that the account on SQL Server on Server 2 has
the proper permissions to view the share where the backups are being sent
and the proper permissions to do a restore on Server 2 and you can do it all
from one job on Server 2.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
> Hi guys,
> I need some help with backups and restores.
> I want to run a backup to disk on server1 followed by a restore on server2
> (Sort of replication)
> I know how to do this with separate jobs or procedures on each server.
> But, is there any way to run this as a single job on server2?
> The goal is to abort restore if backup fails rather than relying on
> "timing"
> I'm having problems specifying connections. Not sure if it's doable.
> Thanks,
> Jim
> PS: DTS is too big a mystery...
> (Both servers are SQL 2000)
>
|||Hi Andrew,
Thanks for the help.
The goal is to have an "off-line spare" for disaster recovery.
They want to be able to replace the main server quickly.
For complex reasons it's not possible to use clustering or replication.
So the only solution left is to have a second server that is kept up to date
with regular backups and restores.
I realise the potential for conflicts with backups on both machines.
I can take care of that... I hope... :-)
What I am trying to eliminate is the need to rely on timing between tasks.
I want to be able to run the backup and restore within the same job so as to
handle possible backup failures.
On seperate servers, the restore wouldn't know if the backup failed and
would needlessly restore a previous set.
Not the end of the world but I'm aiming to be as efficient as possible.
The problem is that I can't figure out how to run a baclup on the donor
server from the recipient server.
Is that possible?
Thanks,
Jim
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
> Server 1 should already have a preset backup routine that includes a FULL
> backup. I would recommend you use that backup instead otherwise you run
> the risk of interfering with the existing backup and or restore strategy
> depending on how it is set up. And why duplicate the effort in the first
> place. Then just make sure that the account on SQL Server on Server 2 has
> the proper permissions to view the share where the backups are being sent
> and the proper permissions to do a restore on Server 2 and you can do it
> all from one job on Server 2.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>
|||Try using stored procedures to handle the remote server actions (be it
backup or restore).
I have set up a system like this for a client with almost 6600 databases on
one server:
backups occur via a scheduled job that calls a sproc that loops the
databases and performs the correct backup type depending on various
settings. there is a table that stores backup information (maintained by
the backup sproc). at the end of the backup sproc a 'prepare restores'
sproc is run on the production server. it copies the necessary information
from the backup log table to a driver table and fires off a scheduled job on
the standby server. that job fires a sproc that gets the data from the 'to
restore' table and executes restore statements based on that information.
Works like a charm!
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
> Hi Andrew,
> Thanks for the help.
> The goal is to have an "off-line spare" for disaster recovery.
> They want to be able to replace the main server quickly.
> For complex reasons it's not possible to use clustering or replication.
> So the only solution left is to have a second server that is kept up to
> date with regular backups and restores.
> I realise the potential for conflicts with backups on both machines.
> I can take care of that... I hope... :-)
> What I am trying to eliminate is the need to rely on timing between tasks.
> I want to be able to run the backup and restore within the same job so as
> to handle possible backup failures.
> On seperate servers, the restore wouldn't know if the backup failed and
> would needlessly restore a previous set.
> Not the end of the world but I'm aiming to be as efficient as possible.
> The problem is that I can't figure out how to run a baclup on the donor
> server from the recipient server.
> Is that possible?
> Thanks,
> Jim
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>
|||See answers in-line:
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
> Hi Andrew,
> Thanks for the help.
> The goal is to have an "off-line spare" for disaster recovery.
> They want to be able to replace the main server quickly.
> For complex reasons it's not possible to use clustering or replication.
> So the only solution left is to have a second server that is kept up to
> date with regular backups and restores.
If you are on SQL2005 then this sounds like a perfect case for database
mirroring.

> I realise the potential for conflicts with backups on both machines.
> I can take care of that... I hope... :-)
How? You better figure out all the nuances before you implement.

> What I am trying to eliminate is the need to rely on timing between tasks.
> I want to be able to run the backup and restore within the same job so as
> to handle possible backup failures.
> On seperate servers, the restore wouldn't know if the backup failed and
> would needlessly restore a previous set.
> Not the end of the world but I'm aiming to be as efficient as possible.
I don't see the problem. This technique is used every day by thousands of
sites with no problems. The implementations may vary slightly depending on
requirements but there is no reason you need to redo restores. One way is to
have the backup job on Server A copy the file to another folder or network
share. Then the job on Server B restores the file when it sees it. When done
it removes it so it doesn't try that same file again. Another method is to
name the backup files with a monotomically increasing ID or timestamp and
track which files were restored last so you know which to do next. Neither
of these techniques require duplicating the backup operation although they
may make a copy of the backup file. If the backup fails the files don't get
copied so there is no issues.

> The problem is that I can't figure out how to run a baclup on the donor
> server from the recipient server.
> Is that possible?
If you log in with the correct accounts you can issue any command you have
the rights to using oSql or SqlCmd. This can be startign a job, calling a
stored procedure or a command directly.
> Thanks,
> Jim
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>
|||Further questions inline below:
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:unQTa$jTIHA.6060@.TK2MSFTNGP05.phx.gbl...
> See answers in-line:
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
> If you are on SQL2005 then this sounds like a perfect case for database
> mirroring.
We're working with SQL 2000, I only wish it was 2005.......

>
> How? You better figure out all the nuances before you implement.
That's why I'm taking it slow and methodical. I plan to dump all backups in
favour of the new structure.
(Eventually.. - I am accutely aware of potential conflicts which is why I
didn't just jump right in)

>
> I don't see the problem. This technique is used every day by thousands of
> sites with no problems. The implementations may vary slightly depending on
> requirements but there is no reason you need to redo restores. One way is
> to have the backup job on Server A copy the file to another folder or
> network share. Then the job on Server B restores the file when it sees it.
Ah now, there's the rub. How does it know the file is there?
Remember I am relatively unskilled at SQL, I'm not well versed in t-sql.
I don't know how to remove the file on completed backup.
Although, I suppose I could find it, that's a good pointer / idea, thanks.
How would you do this in sql script?
if exists(backupfile) = false then exit with error
Baring in mind that the backups run to the same network folder that the
restore pulls from.
If the backup is late or still running or gets interupted then the restore
can't run successfully...
that's what I'm trying to rule out.

>When done it removes it so it doesn't try that same file again. Another
>method is to name the backup files with a monotomically increasing ID or
>timestamp and track which files were restored last so you know which to do
>next.
Drive space is an issue, they only have room for one set of backups...
Sucks, but what can you do when they won't buy new hardware...

>Neither of these techniques require duplicating the backup operation
>although they may make a copy of the backup file. If the backup fails the
>files don't get copied so there is no issues.
>
> If you log in with the correct accounts you can issue any command you have
> the rights to using oSql or SqlCmd. This can be startign a job, calling a
> stored procedure or a command directly.
>
|||Thanks Kevin,
One question though,
How do you call the sproc situated on the remote machine?
The core of my questioning is how to you specify "run this on machine x" in
t-sql?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13nqafj3koojca0@.corp.supernews.com...
> Try using stored procedures to handle the remote server actions (be it
> backup or restore).
> I have set up a system like this for a client with almost 6600 databases
> on one server:
> backups occur via a scheduled job that calls a sproc that loops the
> databases and performs the correct backup type depending on various
> settings. there is a table that stores backup information (maintained by
> the backup sproc). at the end of the backup sproc a 'prepare restores'
> sproc is run on the production server. it copies the necessary
> information from the backup log table to a driver table and fires off a
> scheduled job on the standby server. that job fires a sproc that gets the
> data from the 'to restore' table and executes restore statements based on
> that information. Works like a charm!
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
>
|||see linked server in BOL. This allows you do to this:
exec remoteservername.remotedatabasename.dbo.somesproc
Note that the Distributed Transaction Coordinator can be a PITA to get set
up correctly! :-)
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:eUxji0uTIHA.5516@.TK2MSFTNGP02.phx.gbl...
> Thanks Kevin,
> One question though,
> How do you call the sproc situated on the remote machine?
> The core of my questioning is how to you specify "run this on machine x"
> in t-sql?
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13nqafj3koojca0@.corp.supernews.com...
>
|||Thanks, I'll give it a try.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13nt26t2jvudl81@.corp.supernews.com...
> see linked server in BOL. This allows you do to this:
> exec remoteservername.remotedatabasename.dbo.somesproc
> Note that the Distributed Transaction Coordinator can be a PITA to get set
> up correctly! :-)
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:eUxji0uTIHA.5516@.TK2MSFTNGP02.phx.gbl...
>
|||EXCELLENT!
This is exactly what I was hoping for.
Works great.
Thanks Kevin.
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:eQO43rwTIHA.4476@.TK2MSFTNGP06.phx.gbl...
> Thanks, I'll give it a try.
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13nt26t2jvudl81@.corp.supernews.com...
>

Backup restore linking

Hi guys,
I need some help with backups and restores.
I want to run a backup to disk on server1 followed by a restore on server2
(Sort of replication)
I know how to do this with separate jobs or procedures on each server.
But, is there any way to run this as a single job on server2?
The goal is to abort restore if backup fails rather than relying on "timing"
I'm having problems specifying connections. Not sure if it's doable.
Thanks,
Jim
PS: DTS is too big a mystery...
(Both servers are SQL 2000)Server 1 should already have a preset backup routine that includes a FULL
backup. I would recommend you use that backup instead otherwise you run the
risk of interfering with the existing backup and or restore strategy
depending on how it is set up. And why duplicate the effort in the first
place. Then just make sure that the account on SQL Server on Server 2 has
the proper permissions to view the share where the backups are being sent
and the proper permissions to do a restore on Server 2 and you can do it all
from one job on Server 2.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
> Hi guys,
> I need some help with backups and restores.
> I want to run a backup to disk on server1 followed by a restore on server2
> (Sort of replication)
> I know how to do this with separate jobs or procedures on each server.
> But, is there any way to run this as a single job on server2?
> The goal is to abort restore if backup fails rather than relying on
> "timing"
> I'm having problems specifying connections. Not sure if it's doable.
> Thanks,
> Jim
> PS: DTS is too big a mystery...
> (Both servers are SQL 2000)
>|||Hi Andrew,
Thanks for the help.
The goal is to have an "off-line spare" for disaster recovery.
They want to be able to replace the main server quickly.
For complex reasons it's not possible to use clustering or replication.
So the only solution left is to have a second server that is kept up to date
with regular backups and restores.
I realise the potential for conflicts with backups on both machines.
I can take care of that... I hope... :-)
What I am trying to eliminate is the need to rely on timing between tasks.
I want to be able to run the backup and restore within the same job so as to
handle possible backup failures.
On seperate servers, the restore wouldn't know if the backup failed and
would needlessly restore a previous set.
Not the end of the world but I'm aiming to be as efficient as possible.
The problem is that I can't figure out how to run a baclup on the donor
server from the recipient server.
Is that possible?
Thanks,
Jim
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
> Server 1 should already have a preset backup routine that includes a FULL
> backup. I would recommend you use that backup instead otherwise you run
> the risk of interfering with the existing backup and or restore strategy
> depending on how it is set up. And why duplicate the effort in the first
> place. Then just make sure that the account on SQL Server on Server 2 has
> the proper permissions to view the share where the backups are being sent
> and the proper permissions to do a restore on Server 2 and you can do it
> all from one job on Server 2.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>|||Try using stored procedures to handle the remote server actions (be it
backup or restore).
I have set up a system like this for a client with almost 6600 databases on
one server:
backups occur via a scheduled job that calls a sproc that loops the
databases and performs the correct backup type depending on various
settings. there is a table that stores backup information (maintained by
the backup sproc). at the end of the backup sproc a 'prepare restores'
sproc is run on the production server. it copies the necessary information
from the backup log table to a driver table and fires off a scheduled job on
the standby server. that job fires a sproc that gets the data from the 'to
restore' table and executes restore statements based on that information.
Works like a charm!
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
> Hi Andrew,
> Thanks for the help.
> The goal is to have an "off-line spare" for disaster recovery.
> They want to be able to replace the main server quickly.
> For complex reasons it's not possible to use clustering or replication.
> So the only solution left is to have a second server that is kept up to
> date with regular backups and restores.
> I realise the potential for conflicts with backups on both machines.
> I can take care of that... I hope... :-)
> What I am trying to eliminate is the need to rely on timing between tasks.
> I want to be able to run the backup and restore within the same job so as
> to handle possible backup failures.
> On seperate servers, the restore wouldn't know if the backup failed and
> would needlessly restore a previous set.
> Not the end of the world but I'm aiming to be as efficient as possible.
> The problem is that I can't figure out how to run a baclup on the donor
> server from the recipient server.
> Is that possible?
> Thanks,
> Jim
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>|||See answers in-line:
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
> Hi Andrew,
> Thanks for the help.
> The goal is to have an "off-line spare" for disaster recovery.
> They want to be able to replace the main server quickly.
> For complex reasons it's not possible to use clustering or replication.
> So the only solution left is to have a second server that is kept up to
> date with regular backups and restores.
If you are on SQL2005 then this sounds like a perfect case for database
mirroring.

> I realise the potential for conflicts with backups on both machines.
> I can take care of that... I hope... :-)
How? You better figure out all the nuances before you implement.

> What I am trying to eliminate is the need to rely on timing between tasks.
> I want to be able to run the backup and restore within the same job so as
> to handle possible backup failures.
> On seperate servers, the restore wouldn't know if the backup failed and
> would needlessly restore a previous set.
> Not the end of the world but I'm aiming to be as efficient as possible.
I don't see the problem. This technique is used every day by thousands of
sites with no problems. The implementations may vary slightly depending on
requirements but there is no reason you need to redo restores. One way is to
have the backup job on Server A copy the file to another folder or network
share. Then the job on Server B restores the file when it sees it. When done
it removes it so it doesn't try that same file again. Another method is to
name the backup files with a monotomically increasing ID or timestamp and
track which files were restored last so you know which to do next. Neither
of these techniques require duplicating the backup operation although they
may make a copy of the backup file. If the backup fails the files don't get
copied so there is no issues.

> The problem is that I can't figure out how to run a baclup on the donor
> server from the recipient server.
> Is that possible?
If you log in with the correct accounts you can issue any command you have
the rights to using oSql or SqlCmd. This can be startign a job, calling a
stored procedure or a command directly.
> Thanks,
> Jim
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>|||Further questions inline below:
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:unQTa$jTIHA.6060@.TK2MSFTNGP05.phx.gbl...
> See answers in-line:
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
> If you are on SQL2005 then this sounds like a perfect case for database
> mirroring.
We're working with SQL 2000, I only wish it was 2005.......

>
> How? You better figure out all the nuances before you implement.
That's why I'm taking it slow and methodical. I plan to dump all backups in
favour of the new structure.
(Eventually.. - I am accutely aware of potential conflicts which is why I
didn't just jump right in)

>
> I don't see the problem. This technique is used every day by thousands of
> sites with no problems. The implementations may vary slightly depending on
> requirements but there is no reason you need to redo restores. One way is
> to have the backup job on Server A copy the file to another folder or
> network share. Then the job on Server B restores the file when it sees it.
Ah now, there's the rub. How does it know the file is there?
Remember I am relatively unskilled at SQL, I'm not well versed in t-sql.
I don't know how to remove the file on completed backup.
Although, I suppose I could find it, that's a good pointer / idea, thanks.
How would you do this in sql script?
if exists(backupfile) = false then exit with error
Baring in mind that the backups run to the same network folder that the
restore pulls from.
If the backup is late or still running or gets interupted then the restore
can't run successfully...
that's what I'm trying to rule out.

>When done it removes it so it doesn't try that same file again. Another
>method is to name the backup files with a monotomically increasing ID or
>timestamp and track which files were restored last so you know which to do
>next.
Drive space is an issue, they only have room for one set of backups...
Sucks, but what can you do when they won't buy new hardware...

>Neither of these techniques require duplicating the backup operation
>although they may make a copy of the backup file. If the backup fails the
>files don't get copied so there is no issues.
>
> If you log in with the correct accounts you can issue any command you have
> the rights to using oSql or SqlCmd. This can be startign a job, calling a
> stored procedure or a command directly.
>|||Thanks Kevin,
One question though,
How do you call the sproc situated on the remote machine?
The core of my questioning is how to you specify "run this on machine x" in
t-sql?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13nqafj3koojca0@.corp.supernews.com...
> Try using stored procedures to handle the remote server actions (be it
> backup or restore).
> I have set up a system like this for a client with almost 6600 databases
> on one server:
> backups occur via a scheduled job that calls a sproc that loops the
> databases and performs the correct backup type depending on various
> settings. there is a table that stores backup information (maintained by
> the backup sproc). at the end of the backup sproc a 'prepare restores'
> sproc is run on the production server. it copies the necessary
> information from the backup log table to a driver table and fires off a
> scheduled job on the standby server. that job fires a sproc that gets the
> data from the 'to restore' table and executes restore statements based on
> that information. Works like a charm!
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
>|||see linked server in BOL. This allows you do to this:
exec remoteservername.remotedatabasename.dbo.somesproc
Note that the Distributed Transaction Coordinator can be a PITA to get set
up correctly! :-)
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:eUxji0uTIHA.5516@.TK2MSFTNGP02.phx.gbl...
> Thanks Kevin,
> One question though,
> How do you call the sproc situated on the remote machine?
> The core of my questioning is how to you specify "run this on machine x"
> in t-sql?
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13nqafj3koojca0@.corp.supernews.com...
>|||Thanks, I'll give it a try.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13nt26t2jvudl81@.corp.supernews.com...
> see linked server in BOL. This allows you do to this:
> exec remoteservername.remotedatabasename.dbo.somesproc
> Note that the Distributed Transaction Coordinator can be a PITA to get set
> up correctly! :-)
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:eUxji0uTIHA.5516@.TK2MSFTNGP02.phx.gbl...
>

Backup restore linking

Hi guys,
I need some help with backups and restores.
I want to run a backup to disk on server1 followed by a restore on server2
(Sort of replication)
I know how to do this with separate jobs or procedures on each server.
But, is there any way to run this as a single job on server2?
The goal is to abort restore if backup fails rather than relying on "timing"
I'm having problems specifying connections. Not sure if it's doable.
Thanks,
Jim
PS: DTS is too big a mystery...
(Both servers are SQL 2000)Server 1 should already have a preset backup routine that includes a FULL
backup. I would recommend you use that backup instead otherwise you run the
risk of interfering with the existing backup and or restore strategy
depending on how it is set up. And why duplicate the effort in the first
place. Then just make sure that the account on SQL Server on Server 2 has
the proper permissions to view the share where the backups are being sent
and the proper permissions to do a restore on Server 2 and you can do it all
from one job on Server 2.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
> Hi guys,
> I need some help with backups and restores.
> I want to run a backup to disk on server1 followed by a restore on server2
> (Sort of replication)
> I know how to do this with separate jobs or procedures on each server.
> But, is there any way to run this as a single job on server2?
> The goal is to abort restore if backup fails rather than relying on
> "timing"
> I'm having problems specifying connections. Not sure if it's doable.
> Thanks,
> Jim
> PS: DTS is too big a mystery...
> (Both servers are SQL 2000)
>|||Hi Andrew,
Thanks for the help.
The goal is to have an "off-line spare" for disaster recovery.
They want to be able to replace the main server quickly.
For complex reasons it's not possible to use clustering or replication.
So the only solution left is to have a second server that is kept up to date
with regular backups and restores.
I realise the potential for conflicts with backups on both machines.
I can take care of that... I hope... :-)
What I am trying to eliminate is the need to rely on timing between tasks.
I want to be able to run the backup and restore within the same job so as to
handle possible backup failures.
On seperate servers, the restore wouldn't know if the backup failed and
would needlessly restore a previous set.
Not the end of the world but I'm aiming to be as efficient as possible.
The problem is that I can't figure out how to run a baclup on the donor
server from the recipient server.
Is that possible?
Thanks,
Jim
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
> Server 1 should already have a preset backup routine that includes a FULL
> backup. I would recommend you use that backup instead otherwise you run
> the risk of interfering with the existing backup and or restore strategy
> depending on how it is set up. And why duplicate the effort in the first
> place. Then just make sure that the account on SQL Server on Server 2 has
> the proper permissions to view the share where the backups are being sent
> and the proper permissions to do a restore on Server 2 and you can do it
> all from one job on Server 2.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi guys,
>> I need some help with backups and restores.
>> I want to run a backup to disk on server1 followed by a restore on
>> server2
>> (Sort of replication)
>> I know how to do this with separate jobs or procedures on each server.
>> But, is there any way to run this as a single job on server2?
>> The goal is to abort restore if backup fails rather than relying on
>> "timing"
>> I'm having problems specifying connections. Not sure if it's doable.
>> Thanks,
>> Jim
>> PS: DTS is too big a mystery...
>> (Both servers are SQL 2000)
>|||Try using stored procedures to handle the remote server actions (be it
backup or restore).
I have set up a system like this for a client with almost 6600 databases on
one server:
backups occur via a scheduled job that calls a sproc that loops the
databases and performs the correct backup type depending on various
settings. there is a table that stores backup information (maintained by
the backup sproc). at the end of the backup sproc a 'prepare restores'
sproc is run on the production server. it copies the necessary information
from the backup log table to a driver table and fires off a scheduled job on
the standby server. that job fires a sproc that gets the data from the 'to
restore' table and executes restore statements based on that information.
Works like a charm!
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
> Hi Andrew,
> Thanks for the help.
> The goal is to have an "off-line spare" for disaster recovery.
> They want to be able to replace the main server quickly.
> For complex reasons it's not possible to use clustering or replication.
> So the only solution left is to have a second server that is kept up to
> date with regular backups and restores.
> I realise the potential for conflicts with backups on both machines.
> I can take care of that... I hope... :-)
> What I am trying to eliminate is the need to rely on timing between tasks.
> I want to be able to run the backup and restore within the same job so as
> to handle possible backup failures.
> On seperate servers, the restore wouldn't know if the backup failed and
> would needlessly restore a previous set.
> Not the end of the world but I'm aiming to be as efficient as possible.
> The problem is that I can't figure out how to run a baclup on the donor
> server from the recipient server.
> Is that possible?
> Thanks,
> Jim
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>> Server 1 should already have a preset backup routine that includes a FULL
>> backup. I would recommend you use that backup instead otherwise you run
>> the risk of interfering with the existing backup and or restore strategy
>> depending on how it is set up. And why duplicate the effort in the first
>> place. Then just make sure that the account on SQL Server on Server 2 has
>> the proper permissions to view the share where the backups are being sent
>> and the proper permissions to do a restore on Server 2 and you can do it
>> all from one job on Server 2.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi guys,
>> I need some help with backups and restores.
>> I want to run a backup to disk on server1 followed by a restore on
>> server2
>> (Sort of replication)
>> I know how to do this with separate jobs or procedures on each server.
>> But, is there any way to run this as a single job on server2?
>> The goal is to abort restore if backup fails rather than relying on
>> "timing"
>> I'm having problems specifying connections. Not sure if it's doable.
>> Thanks,
>> Jim
>> PS: DTS is too big a mystery...
>> (Both servers are SQL 2000)
>>
>|||See answers in-line:
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
> Hi Andrew,
> Thanks for the help.
> The goal is to have an "off-line spare" for disaster recovery.
> They want to be able to replace the main server quickly.
> For complex reasons it's not possible to use clustering or replication.
> So the only solution left is to have a second server that is kept up to
> date with regular backups and restores.
If you are on SQL2005 then this sounds like a perfect case for database
mirroring.
> I realise the potential for conflicts with backups on both machines.
> I can take care of that... I hope... :-)
How? You better figure out all the nuances before you implement.
> What I am trying to eliminate is the need to rely on timing between tasks.
> I want to be able to run the backup and restore within the same job so as
> to handle possible backup failures.
> On seperate servers, the restore wouldn't know if the backup failed and
> would needlessly restore a previous set.
> Not the end of the world but I'm aiming to be as efficient as possible.
I don't see the problem. This technique is used every day by thousands of
sites with no problems. The implementations may vary slightly depending on
requirements but there is no reason you need to redo restores. One way is to
have the backup job on Server A copy the file to another folder or network
share. Then the job on Server B restores the file when it sees it. When done
it removes it so it doesn't try that same file again. Another method is to
name the backup files with a monotomically increasing ID or timestamp and
track which files were restored last so you know which to do next. Neither
of these techniques require duplicating the backup operation although they
may make a copy of the backup file. If the backup fails the files don't get
copied so there is no issues.
> The problem is that I can't figure out how to run a baclup on the donor
> server from the recipient server.
> Is that possible?
If you log in with the correct accounts you can issue any command you have
the rights to using oSql or SqlCmd. This can be startign a job, calling a
stored procedure or a command directly.
> Thanks,
> Jim
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>> Server 1 should already have a preset backup routine that includes a FULL
>> backup. I would recommend you use that backup instead otherwise you run
>> the risk of interfering with the existing backup and or restore strategy
>> depending on how it is set up. And why duplicate the effort in the first
>> place. Then just make sure that the account on SQL Server on Server 2 has
>> the proper permissions to view the share where the backups are being sent
>> and the proper permissions to do a restore on Server 2 and you can do it
>> all from one job on Server 2.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi guys,
>> I need some help with backups and restores.
>> I want to run a backup to disk on server1 followed by a restore on
>> server2
>> (Sort of replication)
>> I know how to do this with separate jobs or procedures on each server.
>> But, is there any way to run this as a single job on server2?
>> The goal is to abort restore if backup fails rather than relying on
>> "timing"
>> I'm having problems specifying connections. Not sure if it's doable.
>> Thanks,
>> Jim
>> PS: DTS is too big a mystery...
>> (Both servers are SQL 2000)
>>
>|||Further questions inline below:
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:unQTa$jTIHA.6060@.TK2MSFTNGP05.phx.gbl...
> See answers in-line:
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Hi Andrew,
>> Thanks for the help.
>> The goal is to have an "off-line spare" for disaster recovery.
>> They want to be able to replace the main server quickly.
>> For complex reasons it's not possible to use clustering or replication.
>> So the only solution left is to have a second server that is kept up to
>> date with regular backups and restores.
> If you are on SQL2005 then this sounds like a perfect case for database
> mirroring.
We're working with SQL 2000, I only wish it was 2005.......
>> I realise the potential for conflicts with backups on both machines.
>> I can take care of that... I hope... :-)
> How? You better figure out all the nuances before you implement.
That's why I'm taking it slow and methodical. I plan to dump all backups in
favour of the new structure.
(Eventually.. - I am accutely aware of potential conflicts which is why I
didn't just jump right in)
>> What I am trying to eliminate is the need to rely on timing between
>> tasks.
>> I want to be able to run the backup and restore within the same job so as
>> to handle possible backup failures.
>> On seperate servers, the restore wouldn't know if the backup failed and
>> would needlessly restore a previous set.
>> Not the end of the world but I'm aiming to be as efficient as possible.
> I don't see the problem. This technique is used every day by thousands of
> sites with no problems. The implementations may vary slightly depending on
> requirements but there is no reason you need to redo restores. One way is
> to have the backup job on Server A copy the file to another folder or
> network share. Then the job on Server B restores the file when it sees it.
Ah now, there's the rub. How does it know the file is there?
Remember I am relatively unskilled at SQL, I'm not well versed in t-sql.
I don't know how to remove the file on completed backup.
Although, I suppose I could find it, that's a good pointer / idea, thanks.
How would you do this in sql script?
if exists(backupfile) = false then exit with error
Baring in mind that the backups run to the same network folder that the
restore pulls from.
If the backup is late or still running or gets interupted then the restore
can't run successfully...
that's what I'm trying to rule out.
>When done it removes it so it doesn't try that same file again. Another
>method is to name the backup files with a monotomically increasing ID or
>timestamp and track which files were restored last so you know which to do
>next.
Drive space is an issue, they only have room for one set of backups...
Sucks, but what can you do when they won't buy new hardware...
>Neither of these techniques require duplicating the backup operation
>although they may make a copy of the backup file. If the backup fails the
>files don't get copied so there is no issues.
>
>> The problem is that I can't figure out how to run a baclup on the donor
>> server from the recipient server.
>> Is that possible?
> If you log in with the correct accounts you can issue any command you have
> the rights to using oSql or SqlCmd. This can be startign a job, calling a
> stored procedure or a command directly.
>> Thanks,
>> Jim
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>> Server 1 should already have a preset backup routine that includes a
>> FULL backup. I would recommend you use that backup instead otherwise you
>> run the risk of interfering with the existing backup and or restore
>> strategy depending on how it is set up. And why duplicate the effort in
>> the first place. Then just make sure that the account on SQL Server on
>> Server 2 has the proper permissions to view the share where the backups
>> are being sent and the proper permissions to do a restore on Server 2
>> and you can do it all from one job on Server 2.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi guys,
>> I need some help with backups and restores.
>> I want to run a backup to disk on server1 followed by a restore on
>> server2
>> (Sort of replication)
>> I know how to do this with separate jobs or procedures on each server.
>> But, is there any way to run this as a single job on server2?
>> The goal is to abort restore if backup fails rather than relying on
>> "timing"
>> I'm having problems specifying connections. Not sure if it's doable.
>> Thanks,
>> Jim
>> PS: DTS is too big a mystery...
>> (Both servers are SQL 2000)
>>
>>
>|||Thanks Kevin,
One question though,
How do you call the sproc situated on the remote machine?
The core of my questioning is how to you specify "run this on machine x" in
t-sql?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13nqafj3koojca0@.corp.supernews.com...
> Try using stored procedures to handle the remote server actions (be it
> backup or restore).
> I have set up a system like this for a client with almost 6600 databases
> on one server:
> backups occur via a scheduled job that calls a sproc that loops the
> databases and performs the correct backup type depending on various
> settings. there is a table that stores backup information (maintained by
> the backup sproc). at the end of the backup sproc a 'prepare restores'
> sproc is run on the production server. it copies the necessary
> information from the backup log table to a driver table and fires off a
> scheduled job on the standby server. that job fires a sproc that gets the
> data from the 'to restore' table and executes restore statements based on
> that information. Works like a charm!
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Hi Andrew,
>> Thanks for the help.
>> The goal is to have an "off-line spare" for disaster recovery.
>> They want to be able to replace the main server quickly.
>> For complex reasons it's not possible to use clustering or replication.
>> So the only solution left is to have a second server that is kept up to
>> date with regular backups and restores.
>> I realise the potential for conflicts with backups on both machines.
>> I can take care of that... I hope... :-)
>> What I am trying to eliminate is the need to rely on timing between
>> tasks.
>> I want to be able to run the backup and restore within the same job so as
>> to handle possible backup failures.
>> On seperate servers, the restore wouldn't know if the backup failed and
>> would needlessly restore a previous set.
>> Not the end of the world but I'm aiming to be as efficient as possible.
>> The problem is that I can't figure out how to run a baclup on the donor
>> server from the recipient server.
>> Is that possible?
>> Thanks,
>> Jim
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>> Server 1 should already have a preset backup routine that includes a
>> FULL backup. I would recommend you use that backup instead otherwise you
>> run the risk of interfering with the existing backup and or restore
>> strategy depending on how it is set up. And why duplicate the effort in
>> the first place. Then just make sure that the account on SQL Server on
>> Server 2 has the proper permissions to view the share where the backups
>> are being sent and the proper permissions to do a restore on Server 2
>> and you can do it all from one job on Server 2.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi guys,
>> I need some help with backups and restores.
>> I want to run a backup to disk on server1 followed by a restore on
>> server2
>> (Sort of replication)
>> I know how to do this with separate jobs or procedures on each server.
>> But, is there any way to run this as a single job on server2?
>> The goal is to abort restore if backup fails rather than relying on
>> "timing"
>> I'm having problems specifying connections. Not sure if it's doable.
>> Thanks,
>> Jim
>> PS: DTS is too big a mystery...
>> (Both servers are SQL 2000)
>>
>>
>|||see linked server in BOL. This allows you do to this:
exec remoteservername.remotedatabasename.dbo.somesproc
Note that the Distributed Transaction Coordinator can be a PITA to get set
up correctly! :-)
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:eUxji0uTIHA.5516@.TK2MSFTNGP02.phx.gbl...
> Thanks Kevin,
> One question though,
> How do you call the sproc situated on the remote machine?
> The core of my questioning is how to you specify "run this on machine x"
> in t-sql?
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13nqafj3koojca0@.corp.supernews.com...
>> Try using stored procedures to handle the remote server actions (be it
>> backup or restore).
>> I have set up a system like this for a client with almost 6600 databases
>> on one server:
>> backups occur via a scheduled job that calls a sproc that loops the
>> databases and performs the correct backup type depending on various
>> settings. there is a table that stores backup information (maintained by
>> the backup sproc). at the end of the backup sproc a 'prepare restores'
>> sproc is run on the production server. it copies the necessary
>> information from the backup log table to a driver table and fires off a
>> scheduled job on the standby server. that job fires a sproc that gets
>> the data from the 'to restore' table and executes restore statements
>> based on that information. Works like a charm!
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Hi Andrew,
>> Thanks for the help.
>> The goal is to have an "off-line spare" for disaster recovery.
>> They want to be able to replace the main server quickly.
>> For complex reasons it's not possible to use clustering or replication.
>> So the only solution left is to have a second server that is kept up to
>> date with regular backups and restores.
>> I realise the potential for conflicts with backups on both machines.
>> I can take care of that... I hope... :-)
>> What I am trying to eliminate is the need to rely on timing between
>> tasks.
>> I want to be able to run the backup and restore within the same job so
>> as to handle possible backup failures.
>> On seperate servers, the restore wouldn't know if the backup failed and
>> would needlessly restore a previous set.
>> Not the end of the world but I'm aiming to be as efficient as possible.
>> The problem is that I can't figure out how to run a baclup on the donor
>> server from the recipient server.
>> Is that possible?
>> Thanks,
>> Jim
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>> Server 1 should already have a preset backup routine that includes a
>> FULL backup. I would recommend you use that backup instead otherwise
>> you run the risk of interfering with the existing backup and or restore
>> strategy depending on how it is set up. And why duplicate the effort
>> in the first place. Then just make sure that the account on SQL Server
>> on Server 2 has the proper permissions to view the share where the
>> backups are being sent and the proper permissions to do a restore on
>> Server 2 and you can do it all from one job on Server 2.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi guys,
>> I need some help with backups and restores.
>> I want to run a backup to disk on server1 followed by a restore on
>> server2
>> (Sort of replication)
>> I know how to do this with separate jobs or procedures on each server.
>> But, is there any way to run this as a single job on server2?
>> The goal is to abort restore if backup fails rather than relying on
>> "timing"
>> I'm having problems specifying connections. Not sure if it's doable.
>> Thanks,
>> Jim
>> PS: DTS is too big a mystery...
>> (Both servers are SQL 2000)
>>
>>
>>
>|||Thanks, I'll give it a try.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13nt26t2jvudl81@.corp.supernews.com...
> see linked server in BOL. This allows you do to this:
> exec remoteservername.remotedatabasename.dbo.somesproc
> Note that the Distributed Transaction Coordinator can be a PITA to get set
> up correctly! :-)
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:eUxji0uTIHA.5516@.TK2MSFTNGP02.phx.gbl...
>> Thanks Kevin,
>> One question though,
>> How do you call the sproc situated on the remote machine?
>> The core of my questioning is how to you specify "run this on machine x"
>> in t-sql?
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13nqafj3koojca0@.corp.supernews.com...
>> Try using stored procedures to handle the remote server actions (be it
>> backup or restore).
>> I have set up a system like this for a client with almost 6600 databases
>> on one server:
>> backups occur via a scheduled job that calls a sproc that loops the
>> databases and performs the correct backup type depending on various
>> settings. there is a table that stores backup information (maintained
>> by the backup sproc). at the end of the backup sproc a 'prepare
>> restores' sproc is run on the production server. it copies the
>> necessary information from the backup log table to a driver table and
>> fires off a scheduled job on the standby server. that job fires a sproc
>> that gets the data from the 'to restore' table and executes restore
>> statements based on that information. Works like a charm!
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Hi Andrew,
>> Thanks for the help.
>> The goal is to have an "off-line spare" for disaster recovery.
>> They want to be able to replace the main server quickly.
>> For complex reasons it's not possible to use clustering or replication.
>> So the only solution left is to have a second server that is kept up to
>> date with regular backups and restores.
>> I realise the potential for conflicts with backups on both machines.
>> I can take care of that... I hope... :-)
>> What I am trying to eliminate is the need to rely on timing between
>> tasks.
>> I want to be able to run the backup and restore within the same job so
>> as to handle possible backup failures.
>> On seperate servers, the restore wouldn't know if the backup failed and
>> would needlessly restore a previous set.
>> Not the end of the world but I'm aiming to be as efficient as possible.
>> The problem is that I can't figure out how to run a baclup on the donor
>> server from the recipient server.
>> Is that possible?
>> Thanks,
>> Jim
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>> Server 1 should already have a preset backup routine that includes a
>> FULL backup. I would recommend you use that backup instead otherwise
>> you run the risk of interfering with the existing backup and or
>> restore strategy depending on how it is set up. And why duplicate the
>> effort in the first place. Then just make sure that the account on SQL
>> Server on Server 2 has the proper permissions to view the share where
>> the backups are being sent and the proper permissions to do a restore
>> on Server 2 and you can do it all from one job on Server 2.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi guys,
>> I need some help with backups and restores.
>> I want to run a backup to disk on server1 followed by a restore on
>> server2
>> (Sort of replication)
>> I know how to do this with separate jobs or procedures on each
>> server.
>> But, is there any way to run this as a single job on server2?
>> The goal is to abort restore if backup fails rather than relying on
>> "timing"
>> I'm having problems specifying connections. Not sure if it's doable.
>> Thanks,
>> Jim
>> PS: DTS is too big a mystery...
>> (Both servers are SQL 2000)
>>
>>
>>
>>
>|||EXCELLENT!
This is exactly what I was hoping for.
Works great.
Thanks Kevin.
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:eQO43rwTIHA.4476@.TK2MSFTNGP06.phx.gbl...
> Thanks, I'll give it a try.
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13nt26t2jvudl81@.corp.supernews.com...
>> see linked server in BOL. This allows you do to this:
>> exec remoteservername.remotedatabasename.dbo.somesproc
>> Note that the Distributed Transaction Coordinator can be a PITA to get
>> set up correctly! :-)
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:eUxji0uTIHA.5516@.TK2MSFTNGP02.phx.gbl...
>> Thanks Kevin,
>> One question though,
>> How do you call the sproc situated on the remote machine?
>> The core of my questioning is how to you specify "run this on machine x"
>> in t-sql?
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13nqafj3koojca0@.corp.supernews.com...
>> Try using stored procedures to handle the remote server actions (be it
>> backup or restore).
>> I have set up a system like this for a client with almost 6600
>> databases on one server:
>> backups occur via a scheduled job that calls a sproc that loops the
>> databases and performs the correct backup type depending on various
>> settings. there is a table that stores backup information (maintained
>> by the backup sproc). at the end of the backup sproc a 'prepare
>> restores' sproc is run on the production server. it copies the
>> necessary information from the backup log table to a driver table and
>> fires off a scheduled job on the standby server. that job fires a
>> sproc that gets the data from the 'to restore' table and executes
>> restore statements based on that information. Works like a charm!
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Hi Andrew,
>> Thanks for the help.
>> The goal is to have an "off-line spare" for disaster recovery.
>> They want to be able to replace the main server quickly.
>> For complex reasons it's not possible to use clustering or
>> replication.
>> So the only solution left is to have a second server that is kept up
>> to date with regular backups and restores.
>> I realise the potential for conflicts with backups on both machines.
>> I can take care of that... I hope... :-)
>> What I am trying to eliminate is the need to rely on timing between
>> tasks.
>> I want to be able to run the backup and restore within the same job so
>> as to handle possible backup failures.
>> On seperate servers, the restore wouldn't know if the backup failed
>> and would needlessly restore a previous set.
>> Not the end of the world but I'm aiming to be as efficient as
>> possible.
>> The problem is that I can't figure out how to run a baclup on the
>> donor server from the recipient server.
>> Is that possible?
>> Thanks,
>> Jim
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>> Server 1 should already have a preset backup routine that includes a
>> FULL backup. I would recommend you use that backup instead otherwise
>> you run the risk of interfering with the existing backup and or
>> restore strategy depending on how it is set up. And why duplicate
>> the effort in the first place. Then just make sure that the account
>> on SQL Server on Server 2 has the proper permissions to view the
>> share where the backups are being sent and the proper permissions to
>> do a restore on Server 2 and you can do it all from one job on Server
>> 2.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>>> Hi guys,
>>>
>>> I need some help with backups and restores.
>>>
>>> I want to run a backup to disk on server1 followed by a restore on
>>> server2
>>> (Sort of replication)
>>>
>>> I know how to do this with separate jobs or procedures on each
>>> server.
>>> But, is there any way to run this as a single job on server2?
>>> The goal is to abort restore if backup fails rather than relying on
>>> "timing"
>>>
>>> I'm having problems specifying connections. Not sure if it's doable.
>>>
>>> Thanks,
>>> Jim
>>>
>>> PS: DTS is too big a mystery...
>>> (Both servers are SQL 2000)
>>>
>>
>>
>>
>>
>|||Glad to be able to help you!
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:ODIwsCxTIHA.5264@.TK2MSFTNGP02.phx.gbl...
> EXCELLENT!
> This is exactly what I was hoping for.
> Works great.
> Thanks Kevin.
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:eQO43rwTIHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Thanks, I'll give it a try.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13nt26t2jvudl81@.corp.supernews.com...
>> see linked server in BOL. This allows you do to this:
>> exec remoteservername.remotedatabasename.dbo.somesproc
>> Note that the Distributed Transaction Coordinator can be a PITA to get
>> set up correctly! :-)
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:eUxji0uTIHA.5516@.TK2MSFTNGP02.phx.gbl...
>> Thanks Kevin,
>> One question though,
>> How do you call the sproc situated on the remote machine?
>> The core of my questioning is how to you specify "run this on machine
>> x" in t-sql?
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13nqafj3koojca0@.corp.supernews.com...
>> Try using stored procedures to handle the remote server actions (be it
>> backup or restore).
>> I have set up a system like this for a client with almost 6600
>> databases on one server:
>> backups occur via a scheduled job that calls a sproc that loops the
>> databases and performs the correct backup type depending on various
>> settings. there is a table that stores backup information (maintained
>> by the backup sproc). at the end of the backup sproc a 'prepare
>> restores' sproc is run on the production server. it copies the
>> necessary information from the backup log table to a driver table and
>> fires off a scheduled job on the standby server. that job fires a
>> sproc that gets the data from the 'to restore' table and executes
>> restore statements based on that information. Works like a charm!
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Hi Andrew,
>> Thanks for the help.
>> The goal is to have an "off-line spare" for disaster recovery.
>> They want to be able to replace the main server quickly.
>> For complex reasons it's not possible to use clustering or
>> replication.
>> So the only solution left is to have a second server that is kept up
>> to date with regular backups and restores.
>> I realise the potential for conflicts with backups on both machines.
>> I can take care of that... I hope... :-)
>> What I am trying to eliminate is the need to rely on timing between
>> tasks.
>> I want to be able to run the backup and restore within the same job
>> so as to handle possible backup failures.
>> On seperate servers, the restore wouldn't know if the backup failed
>> and would needlessly restore a previous set.
>> Not the end of the world but I'm aiming to be as efficient as
>> possible.
>> The problem is that I can't figure out how to run a baclup on the
>> donor server from the recipient server.
>> Is that possible?
>> Thanks,
>> Jim
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>>> Server 1 should already have a preset backup routine that includes a
>>> FULL backup. I would recommend you use that backup instead otherwise
>>> you run the risk of interfering with the existing backup and or
>>> restore strategy depending on how it is set up. And why duplicate
>>> the effort in the first place. Then just make sure that the account
>>> on SQL Server on Server 2 has the proper permissions to view the
>>> share where the backups are being sent and the proper permissions to
>>> do a restore on Server 2 and you can do it all from one job on
>>> Server 2.
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>>
>>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>>> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>>> Hi guys,
>>>
>>> I need some help with backups and restores.
>>>
>>> I want to run a backup to disk on server1 followed by a restore on
>>> server2
>>> (Sort of replication)
>>>
>>> I know how to do this with separate jobs or procedures on each
>>> server.
>>> But, is there any way to run this as a single job on server2?
>>> The goal is to abort restore if backup fails rather than relying on
>>> "timing"
>>>
>>> I'm having problems specifying connections. Not sure if it's
>>> doable.
>>>
>>> Thanks,
>>> Jim
>>>
>>> PS: DTS is too big a mystery...
>>> (Both servers are SQL 2000)
>>>
>>>
>>
>>
>>
>>
>>
>