SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Script to copy new backups to remote server?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aiken
Aged Yak Warrior

USA
525 Posts

Posted - 04/02/2004 :  16:50:12  Show Profile  Send aiken an ICQ Message  Reply with Quote
Hiya, everyone. I'm in need of a script (probably WSH, but whatever) to copy recent backups to a remote server. It's really a piece of cake, probably using xcopy /M, but I just figured I'd ask around before reinventing the wheel. Anyone doing something like that with a script that they'd care to share?

Cheers
-b

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 04/02/2004 :  16:56:02  Show Profile  Visit tkizer's Homepage  Reply with Quote
I have a stored procedure that will take the most recent backup file from a server and restore it onto another server. With slight modifications, I have this:



SET NOCOUNT ON

DECLARE @SQL VARCHAR(7000)
DECLARE @DBName SYSNAME
DECLARE @BkpFileName NVARCHAR(260)

SELECT 	bs.database_name AS DatabaseName, MAX(bms.physical_device_name) AS FullBackupName
INTO 	#Backups
FROM 	msdb.dbo.backupset bs
INNER JOIN 
	msdb.dbo.backupmediafamily bms 
ON 	bs.media_set_id = bms.media_set_id
INNER JOIN 
	master.dbo.sysdatabases s 
ON 	bs.database_name = s.name
WHERE 	CONVERT(VARCHAR(20), bs.backup_finish_date, 101) = CONVERT(VARCHAR(20), GETDATE(), 101) AND
      	s.name NOT IN ('master', 'msdb', 'model', 'pubs', 'Northwind')
GROUP BY 
	bs.database_name

WHILE (SELECT COUNT(*) FROM #Backups) <> 0
BEGIN

	SELECT  TOP 1 @DBName = DatabaseName, @BkpFileName = FullBackupName
	FROM	#Backups
	ORDER BY DatabaseName
	
	SELECT @SQL = 'xcopy ' + @BkpFileName + ' \\SomeServer\Someshare\'

	EXEC master.dbo.xp_cmdshell @SQL

	DELETE FROM #Backups
	WHERE DatabaseName = @DBName

END

DROP TABLE #Backups



So it actually finds out what the most recent backup files are for the user databases, then copies them over. So in case someone ran a manual backup to a non-standard location, this code will catch those too.

Tara

Edited by - tkizer on 04/02/2004 17:03:04
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 04/02/2004 :  17:45:25  Show Profile  Visit tkizer's Homepage  Reply with Quote
http://weblogs.sqlteam.com/tarad/archive/2004/04/02/1192.aspx

Tara
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000