December 5, 2006 at 9:10 am
Dear all
I have 22 SQL databases all the same stratuctre. I need to back them to a central server. That is to have all the 22 SQL database on one central server.
Anyone, is there a script or a tool that can backup SQL database, then ftp the backup file to a server then restore it.
any IDEAS
December 5, 2006 at 1:29 pm
You can use the following script for backup and you can write a script for ftp ans similar way restore.....
CREATE PROC Stp_Backup_AllDb
@DbName sysname = null,
@BackupLocation Varchar(1000)
AS
/*
EXEC Stp_Backup_AllDb @BackupLocation= '\\backupserver\x$\mssql
EXEC Stp_Backup_AllDb 'MASTER', '\\backupserver\x$\mssql\
*/
DECLARE @sql VARCHAR(1000), @DeviceName sysname
if @DbName is null
begin
select @DbName = '%'
end
if @DbName is not null
begin
if (select 1 from master..sysdatabases where name = @DbName) =0
begin
select 'database does not exist ...'
return
end
end
IF right(@BackupLocation, 1) <> '\'
SELECT @BackupLocation = @BackupLocation+'\'
begin
DECLARE DBCUR CURSOR FOR
SELECT NAME FROM master..SYSDATABASES WHERE NAME NOT IN ('MODEL','TEMPDB')
and DATABASEPROPERTYEX(name, 'status')= 'online'
and name like @DbName
ORDER BY NAME
OPEN DBCUR
FETCH DBCUR INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @DeviceName = @BackupLocation+'backup_'+@@SERVERNAME +'_'+ @DbName+'.BAK'
SELECT @sql = 'EXEC MASTER..XP_CMDSHELL ''del '+@DeviceName+''', no_output'
SELECT @sql
EXEC ( @sql )
BACKUP DATABASE @DbName TO DISK = @DeviceName WITH INIT, STATS = 10
FETCH DBCUR INTO @DbName
END
CLOSE DBCUR
DEALLOCATE DBCUR
end
GO
For ftp script...
http://www.sqlteam.com/item.asp?ItemID=6002
MohammedU
Microsoft SQL Server MVP
December 11, 2006 at 12:38 pm
You didn't say if the 22 databases on different servers would exist as 22 databases on one server or would they be merged into one database on the one server. If they are being merged, I would look at DTS with the option to append rows to the destination table.
Steve
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply