January 16, 2015 at 2:06 am
An idea on how to backup half of dbs from a server on C drive and the other half on D drive and vice versa, first half on D drive and other half On C drive using only one job and one stored procedure??
Using scheduling from job add 2 schedules to the job so first schedule backup first half to C and second half to D , the second schedule backup first half to D and second half to D
Any idea is very welcome 🙂
Thanks,
Andrei
January 16, 2015 at 7:56 am
IF exists (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[spBackupAllDatabases]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].spBackupAllDatabases
GO
CREATE PROCEDURE spBackupAllDatabases
@Path varchar(128) ,
@Path1 varchar(128)
AS
BEGIN TRY
IF OBJECT_ID ('DatabaseBackup') IS NOT NULL
DROP TABLE DatabaseBackup
CREATE TABLE DatabaseBackup
(
Name varchar(128) PRIMARY KEY
)
SET NOCOUNT ON
DECLARE @sql varchar(1000)
DECLARE @sql1 varchar(1000)
-- Get all database names
CREATE TABLE #DBName
(
ID int identity (1,1) ,
Name varchar(128) not null ,
RetentionPeriod datetime null
)
INSERT #DBName
(Name)
SELECT name
FROM master..sysdatabases
WHERE dbid < (SELECT COUNT(dbid) - 4 FROM sysdatabases)
--Include any new databases in the backup
INSERT DatabaseBackup
(
Name
)
SELECT #DBName.Name
FROM #DBName
LEFT OUTER JOIN DatabaseBackup
ON DatabaseBackup.Name = #DBName.Name
WHERE DatabaseBackup.Name IS NULL
AND LOWER(#DBName.Name) <> 'tempdb' AND LOWER(#DBName.Name) <> DB_NAME()
-- Remove any non-existant databases
DELETE DatabaseBackup
WHERE NOT EXISTS
(
SELECT *
FROM #DBName
WHERE #DBName.Name = DatabaseBackup.Name
)
DELETE #DBName
-- loop through databases
DECLARE @Name varchar(128) ,
@ID int ,
@MaxID int
INSERT #DBName
(Name)
SELECT Name
FROM DatabaseBackup
WHERE LOWER(DatabaseBackup.Name) <> 'tempdb'
SELECT @MaxID = max(ID) ,
@ID = 0
FROM #DBName
WHERE LOWER(#DBName.Name) <> 'tempdb'
WHILE @ID < @MaxID
BEGIN
-- get next database to backup
SELECT @ID = min(ID) FROM #DBName WHERE ID > @ID
SELECT @Name = Name
FROM #DBName
WHERE ID = @ID
DECLARE @eID int ,
@eMaxID int ,
@eName varchar(128)
-- now do the backup
SELECT @sql = @Path + @Name + '_' + 'FULL' + '_'
+ convert(varchar(8),getdate(),112) + '.bak'
BACKUP DATABASE @Name
TO DISK = @sql WITH STATS = 10, COMPRESSION, FORMAT, INIT
END
-- Get all database names
CREATE TABLE #DBName1
(
ID int identity (1,1) ,
Name varchar(128) not null ,
RetentionPeriod datetime null
)
INSERT #DBName1
(Name)
SELECT name
FROM master..sysdatabases
WHERE dbid >= (SELECT COUNT(dbid) - 4 FROM sysdatabases)
--Include any new databases in the backup
INSERT DatabaseBackup
(
Name
)
SELECT #DBName1.Name
FROM #DBName1
LEFT OUTER JOIN DatabaseBackup
ON DatabaseBackup.Name = #DBName1.Name
WHERE DatabaseBackup.Name IS NULL
AND LOWER(#DBName1.Name) <> 'tempdb' AND LOWER(#DBName1.Name) <> DB_NAME()
-- Remove any non-existant databases
DELETE DatabaseBackup
WHERE NOT EXISTS
(
SELECT *
FROM #DBName1
WHERE #DBName1.Name = DatabaseBackup.Name
)
DELETE #DBName
-- loop through databases
DECLARE @Name1 varchar(128) ,
@ID1 int ,
@MaxID1 int
INSERT #DBName1
(Name)
SELECT Name
FROM DatabaseBackup
SELECT @MaxID1 = max(ID) ,
@ID1 = 0
FROM #DBName1
WHILE @ID1 < @MaxID1
BEGIN
-- get next database to backup
SELECT @ID1 = min(ID) FROM #DBName1 WHERE ID > @ID1
SELECT @Name1 = Name
FROM #DBName1
WHERE ID = @ID1
SELECT @sql1 = @Path1 + @Name1 + '_' + 'FULL' + '_'
+ convert(varchar(8),getdate(),112) + '.bak'
BACKUP DATABASE @Name1
TO DISK = @sql1 WITH STATS = 10, COMPRESSION, FORMAT, INIT
END
END TRY
BEGIN CATCH
THROW;
END CATCH;
I have something like this but as you can see it's kind of hardcoded, any idea to change that??
Thanks,
Andrei
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply