July 12, 2010 at 12:28 pm
Hi,
We have SQL Server 2005 databases on Windows 2003 Server and I'm planning to take the backups to a drive on another Server (Windows Server 2008) for all Development and QA databases. And from there, copying to a Tape drive.
The Dev and QA SQL Servers and the Server where we want to keep all the Dev & QA database's backups are in same domain.
Please advice on this setup
Thanks
July 12, 2010 at 12:41 pm
Backing up to a network drive can be problematic. Better to backup locally and then copy the file to a network location. You can do this in the same job.
July 12, 2010 at 12:43 pm
I agree with Emily. Any hiccup will cause the backup to fail. Typically you back up locally and then copy to another server.
July 12, 2010 at 12:56 pm
I use xp_cmdshell, others may use robocopy, etc. This script may help. Understand the security implications of xp_cmdshell if you need to activate this.
Also see http://www.sqlservercentral.com/scripts/Administration/62729/ for a way to maintain your network folder.
DECLARE @Command varchar(1000)
DECLARE @BackupFile nvarchar(1000)
SET @BackupFile= (
SELECT TOP 1 (physical_device_name)
FROM dbo.backupmediafamily
WHERE physical_device_name like '%YourDB%' and physical_device_name like '%.bak%'
ORDER BY media_set_id DESC
)
--print @BackupFile
SELECT @BackupFile
SELECT @Command = ' COPY ' + @BackupFile+ ' \\YOURSERVER\YOURFOLDER
PRINT @Command
EXEC master..xp_cmdshell @Command
July 12, 2010 at 2:23 pm
Thank you,
I'm using the below stored procedure to backup the databases:
I have created the below Stored Procedure in MASTER database:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[BackupDB] Script Date: 07/12/2010 13:11:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[BackupDB]
@DBName VARCHAR(100),
@BackupPath VARCHAR(1000),
@BackupType VARCHAR(4) = 'FULL'
AS
BEGIN
/*
Aim: To be able to take full, Differential and log backup of given database on given file path.
Date : 07/07/2010
Author:Mamata
*/
DECLARE @BackupName VARCHAR(255)
DECLARE @Prefix VARCHAR(50)
DECLARE @backupSetId AS INT
-- Make sure database exists on server
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysdatabases WHERE name = @DBName)
BEGIN
SELECT 'Database name does not exist'
RETURN(1)
END
-- Make sure one one is trying to take tempdb backup
IF @DBName = 'tempdb'
BEGIN
SELECT 'tempdb cannot be backedup'
RETURN(1)
END
-- full, Differential and log backups are allowed, other wise do not do any thing.
IF @BackupType NOT IN ('FULL', 'LOG', 'DIFF')
BEGIN
PRINT 'Invalid type of Backup selected, only FULL and LOG backup is allowed'
RETURN(1)
END
-- If user did not give the back slash, add one.
IF RIGHT(@BackupPath,1) <> '\'
SET @BackupPath = @BackupPath + '\'
IF @BackupType = 'FULL'
BEGIN
SET @BackupName = @DBName + ' Full Backup'
SET @BackupPath = @BackupPath + @DBName +'_'+ CONVERT(VARCHAR(10),GETDATE(),110)+'.bak'
SELECT @BackupPath AS Full_BackupPath_of_The_Database
BACKUP DATABASE @DBName
TO DISK = @BackupPath
WITH NOFORMAT, NOINIT, NAME = @BackupName,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
SELECT @backupSetId = position FROM msdb..backupset WHERE database_name= @DBName
AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset
WHERE database_name = @DBName )
IF @backupSetId IS NULL
BEGIN
RAISERROR (N'Verify failed for database', 16, 1)
END
RESTORE VERIFYONLY
FROM DISK = @BackupPath
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
END
IF @BackupType = 'DIFF'
BEGIN
SET @BackupName = @DBName + ' Differential Backup'
SELECT @Prefix = CONVERT(VARCHAR(50), GETDATE(),120)
SELECT @Prefix = REPLACE(@Prefix, '-', '')
SELECT @Prefix = REPLACE(@Prefix, ':', '')
SELECT @Prefix = REPLACE(@Prefix, ' ', '')
SET @BackupPath = @BackupPath + @DBName + '_' + @Prefix +'.bak'
SELECT @BackupPath AS Diff_Backup_Path
BACKUP DATABASE @DBName
TO DISK = @BackupPath
WITH DIFFERENTIAL ,NOFORMAT, INIT, NAME = @BackupName,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
SELECT @backupSetId = position FROM msdb..backupset WHERE database_name= @DBName
AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset
WHERE database_name = @DBName )
IF @backupSetId IS NULL
BEGIN
RAISERROR (N'Verify failed for database', 16, 1)
END
RESTORE VERIFYONLY
FROM DISK = @BackupPath
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
END
IF @BackupType = 'LOG'
BEGIN
SELECT @Prefix = CONVERT(VARCHAR(50), GETDATE(),120)
SELECT @Prefix = REPLACE(@Prefix, '-', '')
SELECT @Prefix = REPLACE(@Prefix, ':', '')
SELECT @Prefix = REPLACE(@Prefix, ' ', '')
SET @BackupName = @DBName + ' Log Backup'
SET @BackupPath = @BackupPath + @DBName + '_Log' + @Prefix + '.trn'
SELECT @BackupPath AS Log_Backup_Path
BACKUP log @DBName
TO DISK = @BackupPath
WITH NOFORMAT, NOINIT, NAME = @BackupName,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
SELECT @backupSetId = position FROM msdb..backupset WHERE database_name= @DBName
AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset
WHERE database_name = @DBName )
IF @backupSetId IS NULL
BEGIN
RAISERROR (N'Verify failed for database', 16, 1)
END
RESTORE VERIFYONLY
FROM DISK = @BackupPath
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
END
END
Backup the databases via a SQL Agent backup job
SQL Agent Job Step:
EXEC [dbo].[BackupDatabase] 'SharedServices1_DB','Z:\Backups\FULL\','FULL'
GO
EXEC [dbo].[BackupDatabase] 'SharedServices1_Search_DB','Z:\Backups\FULL\','FULL'
GO
EXEC [dbo].[BackupDatabase] 'SharePoint_AdminContent','Z:\Backups\FULL\','FULL'
GO
EXEC [dbo].[BackupDatabase] 'SharePoint_Config','Z:\Backups\FULL\','FULL'
GO
EXEC [dbo].[BackupDatabase] 'WSS_Content','Z:\Backups\FULL\','FULL'
GO
EXEC [dbo].[BackupDatabase] 'WSS_Content_QA','Z:\Backups\FULL\','FULL'
GO
EXEC [dbo].[BackupDatabase] 'WSS_Content_Mysite','Z:\Backups\FULL\','FULL'
GO
EXEC [dbo].[BackupDatabase] 'WSS_Search_MOSS1,'Z:\Backups\FULL\','FULL'
GO
EXEC [dbo].[BackupDatabase] 'WSS_Search_MOSS22','Z:\Backups\FULL\','FULL'
GO
EXEC [dbo].[BackupDatabase] 'Master','Z:\Backups\FULL\','FULL'
GO
EXEC [dbo].[BackupDatabase] 'Model','Z:\Backups\FULL\','FULL'
GO
EXEC [dbo].[BackupDatabase] 'Msdb','Z:\Backups\FULL\','FULL'
GO
Now, once the above Backup job completes, I want to copy the .BAK files to a Network drive on Server A. The Share name on Server A is SQL_Copy_Backups
Please help me in creating the Copy job to copy to the above Network Share..Like a sample copy job step by using robocopy method. Because we do not want to use xp_cmdshell as a security concern.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply