Backup Schedule

Hi
I want to take a backup of SQL Server database on weekly
basis. How can I set up things on my server ? any script
will have to be written or any tool to be used ? pl.
help..
JayHi,
You can use database maintenance plans (Enterprise Manager) to create a
backup job which can be scheduled to execute every week once.
Thanks
Hari
MCDBA
"Jay" <neessan_gen@.rediffmail.com> wrote in message
news:048201c39daf$d6959230$a501280a@.phx.gbl...
> Hi
> I want to take a backup of SQL Server database on weekly
> basis. How can I set up things on my server ? any script
> will have to be written or any tool to be used ? pl.
> help..
> Jay|||In addition to Hari's post:
Weekly? That would never go in my installation. You are essentially saying that it is OK to lose up
to one week worth of data. My baseline is database backup every day and log backup every hour.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jay" <neessan_gen@.rediffmail.com> wrote in message news:048201c39daf$d6959230$a501280a@.phx.gbl...
> Hi
> I want to take a backup of SQL Server database on weekly
> basis. How can I set up things on my server ? any script
> will have to be written or any tool to be used ? pl.
> help..
> Jay|||Create a Database Manitance Plan or create job which should start on weekly.
--
Shaju Thomas
"Jay" <neessan_gen@.rediffmail.com> wrote in message
news:048201c39daf$d6959230$a501280a@.phx.gbl...
> Hi
> I want to take a backup of SQL Server database on weekly
> basis. How can I set up things on my server ? any script
> will have to be written or any tool to be used ? pl.
> help..
> Jay|||THANKS HARI and SHAJU, appreciate your help.
Jay
>--Original Message--
>Hi,
>You can use database maintenance plans (Enterprise
Manager) to create a
>backup job which can be scheduled to execute every week
once.
>Thanks
>Hari
>MCDBA
>
>"Jay" <neessan_gen@.rediffmail.com> wrote in message
>news:048201c39daf$d6959230$a501280a@.phx.gbl...
>> Hi
>> I want to take a backup of SQL Server database on weekly
>> basis. How can I set up things on my server ? any script
>> will have to be written or any tool to be used ? pl.
>> help..
>> Jay
>
>.
>|||HI
We hawe 30 database on 3 server with multiple backup strategy. (1-30GB)
Main backup procedure: Backup database to device on network share. Backup the devices to tape.
Customized backup:
Purpose: Reduce nigtly backup time. One backup script for Multi server environment (MSX-TSX).
Easy configuration for new or migrated databases.
Reduce disk usage.
Easy restore. (We can futurely write one script with parameters for restore)
Monday - Saturday
1. - 4 logbackup / day and differential backup 22:00
2. - 4 logbackup / day and differential backup 20:00
3. - 3 logbackup / day and differential backup 20:00
4. - 1 logbackup / day and differential backup 20:00
5. - differential backup 20:00
6. - differential backup 22:00
7. - full backup 20:00 (include master and msdb)
Sunday full backup instead of differential backup
Implementation:
Create registry entry for Bakup folder (eg: HKEY_LOCAL_MACHINE\SOFTWARE\Vodafone\MSSQLbackup)
Create table in msdb:
/********************************************************/
CREATE TABLE Backups (
dbName varchar (255) COLLATE Hungarian_CI_AS NOT NULL , --Database names
MainBKP int NULL --Backup configuration identifier
) ON PRIMARY
GO
/********************************************************/
Populate this table with database name and backup id
Create the following sp in master database:
The Backup script:
/********************************************************/
SET QUOTED_IDENTIFIER OFF GO
SET ANSI_NULLS ON GO
--=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
-- AdmSpBackup: r=F6vid le=EDr=E1s
----
-- Param=E9terek:
--
----
-- T=E1bl=E1k:
-- Haszn=E1lt t=E1bl=E1k neve, hozz=E1f=E9r=E9s m=F3dja
-- ----
-- Hivatkoz=F3 elj=E1r=E1sok:
-- Mely elj=E1r=E1sb=F3l ker=FCl megh=EDv=E1sra ----
-- H=EDvott elj=E1r=E1sok:
-- Mely elj=E1r=E1sokat h=EDv meg
--
----
-- Visszat=E9r=E9si =E9rt=E9kek:
-- =C9rt=E9k: 0, ha sikeres, -101 ha hib=E1s a fut=E1s
-- Eredm=E9nyhalmaz:
-- ----
-- Tranzakci=F3k:
-- Nem haszn=E1l tranzakci=F3t
----
-- R=E9szletes le=EDr=E1s:
-- ----
-- Jegyzet: ----
-- K=E9sz=EDtette: Jakus Andr=E1s, 2003-10-20
----
-- T=F6rt=E9net:
-- -- --=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
ALTER PROCEDURE AdmSpBackup
AS
SET NOCOUNT ON
SET DATEFIRST 1
DECLARE @.set INT
DECLARE @.sql NVARCHAR(4000)
DECLARE @.name VARCHAR(100)
DECLARE @.main INT
DECLARE @.device VARCHAR(255)
DECLARE @.defPath VARCHAR(255)
DECLARE @.dbPath VARCHAR(255)
DECLARE @.tmpStr VARCHAR(200)
DECLARE @.message VARCHAR(8000)
DECLARE @.Err int
DECLARE @.SUBJECT VARCHAR(100)
---
---
SET @.set =3D DATEPART(HH, GETDATE())
SET @.message =3D 'Starting No: ' + CAST(@.set AS VARCHAR(2)) + ' backup on: ' + @.@.SERVERNAME + ' ' + CONVERT(VARCHAR
(100), GETDATE(), 20) + Char(13) + Char(13)
SELECT @.sql =3D CASE WHEN @.set =3D 7 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 2)'
WHEN @.set =3D 12 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 2, 3)'
WHEN @.set =3D 16 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 2, 3)'
WHEN @.set =3D 20 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (2, 3, 4, 5, 7)'
WHEN @.set =3D 22 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 6)'
ELSE 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP =3D -1'
END
EXEC sp_executesql @.sql
---
----
SET @.tmpStr =3D REPLACE(@.@.SERVERNAME, '\', '-')
-- A registry-ben be=E1ll=EDtott ment=E9si k=F6nyvt=E1r
EXEC master..xp_regread @.rootkey=3D'HKEY_LOCAL_MACHINE',
@.key=3D'SOFTWARE\Vodafone\MSSQLbackup',
@.value_name =3D @.tmpStr,
@.value=3D@.defPath OUTPUT
-- Van-=E9 k=F6nyvt=E1ra az adatb=E1zisnak
SET @.tmpStr =3D 'master..xp_subdirs ''' + @.defPath + ''''
CREATE TABLE #Dirs (a VARCHAR(100))
insert INTO #Dirs (a) EXEC(@.tmpStr)
---
----
DECLARE bcks CURSOR FOR SELECT * FROM ##dbs
OPEN bcks
FETCH NEXT FROM bcks
INTO @.name, @.main
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
/**---
---**/
/** Verify, that have subfolder for database **/
/**---
---**/
If @.name not in (select a from #Dirs)
BEGIN
=09
SET @.tmpStr =3D 'Mkdir "' + @.defPath + @.name + '"' --
Create subfolder for database
=09
EXECUTE xp_cmdshell @.tmpStr
=09
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: ' + @.tmpStr + Char(13) + Char
(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup folder: ' + @.defPath + @.name + Char(13) + Char
(13)
END
=09
END
/**---
---**/
/** Verify, that have Backup media for full database backup **/
/**---
---**/
If @.Name + '-Full' not in (SELECT logical_device_name from msdb.dbo.backupmediafamily)
BEGIN
SET @.device =3D @.Name + '-Full'
If @.device not in (select LTRIM(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.bak'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
=09
=09
=09
BEGIN
=09
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Full Backup)' + Char(13) + Char(13)
BACKUP DATABASE @.Name
TO @.device
WITH
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
END
ELSE
BEGIN
/**---
---
If @.main < 5 then backup log for @.Name
---
---**/
If @.Main < 5
BEGIN
SET @.device =3D @.Name + '-Log' + CAST(@.set AS VARCHAR(2))
If @.device not in (select LTRIM(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.trn'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
END
=09
=09
END
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Log ' + @.Name + ' started' + Char(13) + Char(13)
BACKUP LOG @.Name=09
TO @.device
WITH
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: BACKUP LOG ' + @.Name + Char
(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' log successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
END
/**---
---
If @.set > 19 then backup database @.Name
If DATEPART(dw, GETDATE()) =3D 7 Then Full backup
---
---**/
If DATEPART(dw, GETDATE()) =3D 7 AND @.set > 19 --@.Name NOT IN ('master', 'msdb')
BEGIN =09
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Full Backup)' + Char(13) + Char(13)
SET @.device =3D @.Name + '-Full'
=09
BACKUP DATABASE @.Name
TO @.device
WITH
RETAINDAYS =3D 6,
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
=09
END
ELSE
/**---
---
If DATEPART(dw, GETDATE()) < 7 Then Differential backup, @.main < 7 ---
---**/
BEGIN
If @.main < 7 AND @.set > 19
BEGIN
SET @.device =3D @.Name + '-Diff'
If @.device not in (select LTRIM(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.bak'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
=09
END
=09
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Differential Backup)' + Char(13) + Char(13)
BACKUP DATABASE @.Name TO @.device
WITH DIFFERENTIAL,
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS varchar(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
ELSE
BEGIN
SET @.device =3D @.Name + 'Full'
If @.set > 19 BEGIN
If @.device not in (select LTRIM
(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.bak'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS varchar(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
=09
END
=09
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Full Backup)' + Char(10) + Char(13)
BACKUP DATABASE @.Name
TO @.device
WITH
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST
(@.Err AS varchar(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
END
END
/**-- next database --
---**/
END
FETCH NEXT FROM bcks
INTO @.name, @.main
END
CLOSE bcks
DEALLOCATE bcks
/**---
---**/
/** Send message **/
/**---
---**/
SET @.message =3D @.message + 'Completed No: ' + CAST(@.set AS VARCHAR(2)) + ' backup on: ' + @.@.SERVERNAME + ' ' + CONVERT(VARCHAR(100), GETDATE(), 20)
SET @.SUBJECT =3D 'Backup report from ' + @.@.SERVERNAME
EXECUTE xp_sendmail @.Recipients =3D 'andras.jakus@.vodafone.com', --Your address here
@.Message =3D @.message,
@.Subject =3D @.SUBJECT
/**---
---**/
/** Drop alltemporary tables **/
/**---
---**/
DROP TABLE ##dbs
DROP TABLE #Dirs
/*******************************************************/
Change @.Recipients to your address
Create (Multi Server)job with one step: EXECUTE master.dbo.AdmSpBackup
Schedule the job: 07:00; 12:00; 16:00; 20:00; 22:00
>--Original Message--
>In addition to Hari's post:
>Weekly? That would never go in my installation. You are essentially saying that it is OK to lose up
>to one week worth of data. My baseline is database backup every day and log backup every hour.
>-- >Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=3Ddjq&as_ugroup=3Dmicrosoft.public.sqlserver
>
>"Jay" <neessan_gen@.rediffmail.com> wrote in message news:048201c39daf$d6959230$a501280a@.phx.gbl...
>> Hi
>> I want to take a backup of SQL Server database on weekly
>> basis. How can I set up things on my server ? any script
>> will have to be written or any tool to be used ? pl.
>> help..
>> Jay
>
>.
>sql