Automate SQL full backup

[kishore_soma]

Dear Experts,

In our company we have a SAP 4.7 installed on MS SQL server 2000, Daily we need to stay untill all the users complete their work and fire the db13 backup and SQl query disk backup, for this i have scheduled a job in the SQL server enterprise manager and put a script where in i have to change the script everyday.

if you can provide me a script where in it changes the date & time of the file name every day automatically

The script i use is as follows:

use master

exec sp_addumpdevice 'disk','PRDF270807_1909','<path>\PRDF270807_1909'

backup DATABASE PRD to PRDF270807_1909

Kindly help me in this regards,

Thnx in advance

Kishore.

[Former Member · Accepted solution]

Hi Kishore,

why don't you create a Maintenance Plan. Look under 'Management -> Database Maintenance Plans'. Right click on 'Database Maintenance Plan -> New Maintenance Plan'. The following dialogs are easy to understand. You can create all sorts of maintenance plans for TA-Log backup, DB-Backup, Check DB and so.

Regards,

Sven

[kishore_soma · Accepted solution]

Thnx sven for the reply,

But the thing is that we have to fire the disk backup to the netowork path not to the local path, is that possible in the Maintainance plan

Regards,

Kishore

[clas_hortien · Accepted solution]

Hi,

i use this script to make a backup to a different device every week, you can easily modify this to a daily change. You can add a network share as target as well.

I have this script scheduled as a SQL Agent Job and it works like a charm.

Regards

Clas

declare @week varchar(3)

declare @devl varchar(20)

declare @devp varchar(400)

declare @dev varchar(20)

-

---

select @week = datepart(ww,getdate())

set @devl = 'vsdata_week_' + @week + '.db'

set @devp = 'c:\tools\VSWMI\Backup\vsdata_week_' + @week + '.db'

if not exists (select name from sys.backup_devices where name = @devl)

exec sp_addumpdevice 'DISK',@devl,@devp

-

---

declare crs cursor for

select name from sys.backup_devices

where name like 'vsdata%' and

name <> @devl

open crs

fetch next from crs into @dev

while (@@fetch_status = 0) begin

exec sp_dropdevice @dev

fetch next from crs into @dev

end

close crs

deallocate crs

-

---

BACKUP DATABASE [VSData] TO @devl WITH NOFORMAT, NOINIT,

NAME = N'VSData-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,

STATS = 10, CHECKSUM, CONTINUE_AFTER_ERROR

-

---

[Former Member · Accepted solution]

Hi Kishore,

Clas delivered the pro version, here is a simple one:

use master

declare @date varchar(10)

declare @path varchar(255)

set @date = (SELECT LEFT(CONVERT(VARCHAR(19), getdate(), 120),10))

set @path = '
server\share\path\backup_PRD_' + @date + '.bak'

exec sp_addumpdevice 'disk','PRD_Backups',@path

backup DATABASE master to PRD_Backups

exec sp_dropdevice 'PRD_Backups'

Regards,

Sven

[former_member193399 · Accepted solution]

Using maintenance plan solves these kinds of problem and it is more elegant to maintain. I used to backup using DB13 but after seeing that DB13 just creates a job in SQL and schedules them, I end up doing it using maintenance plan.

[kishore_soma · Accepted solution]

Thanks Sven,

ITs was very helpfull, but pls also fine tune it so that i can get the time also, whic would be very much helpful

REgards,

Kishore

[former_member193399 · Accepted solution]

Kishore,

This is fairly easy with Svens code. I added more replace commands to have the underscores.

Replace the line

set @date=.....

with below:

set @date = (SELECT (CONVERT(VARCHAR(19), getdate(), 120)))

SET @date=replace(@date,' ','_')

SET @date=replace(@date,':','_')

SET @date=replace(@date,'-','_')

[Former Member · Accepted solution]

Hi Kishore,

if you need the time as well (see R.Th.s post) make sure you declare:

declare @date varchar(19)

Regards,

Sven

[kishore_soma · Accepted solution]

Thnx Sven, R.Th., & Clas Hortien

Thnx alot for helping me in solving this issue

We have made some changes in the Script and made it give the file name as <SID>F/L<DATE>_<TIME>.bak

(F - full backup, L - Log backup) and the script is as follws:

-

---

declare @date varchar(19)

declare @time varchar(10)

declare @path varchar(255)

set @date = (SELECT LEFT(CONVERT(VARCHAR(19), getdate(), 120),19))

SET @date=replace(@date,' ','_')

SET @date=replace(@date,':','')

SET @date=replace(@date,'-','')

set @time = (select substring(@date,10,4))

set @path = 'J:\backup\PRDL' + substring(@date,7,2) + substring(@date,5,2) + substring(@date,3,2) +'_' + @time + '.bak'

exec sp_addumpdevice 'disk','LOG_Backups',@path

backup Log Sharath to LOG_Backups

exec sp_dropdevice 'LOG_Backups'

-

---

For full backup the Dump device is PRD_Backups and for Log backups it is LOG_BAckups

Thnx alot once again looking forward for your help in future

Regards,

Kishore

[Former Member · Accepted solution]

I like the scripting you use to automate backup jobs. You can easily add this to a job for the SQL Server Agent to run on a reoccurring instance.

I have implemented this and it is working great. I then backup the files to tape each night.

Any ideas on how to automate removing the backup files once they have been written to tape?

My guess is I have to do this with my backup software or at the OS level, but I just wanted to know if anyone had any suggestions.

Thanks.

[Former Member · Accepted solution]

Hi Drew,

since you can define job steps with the SQL Server Agent, why not define

Step 1: delete previous backup

Step 2: make new backup

Step 1 could be as simple as this:

EXEC xp_cmdshell 'del <YourDrive>:\<YourPath>\*.bak';

This way you would keep the old backup until the new one starts. You must make sure though that you have the previous backup on tape. Depending on your backup software, that could be step 3 or any other order that you prefer.

Regards,

Sven

[Former Member · Accepted solution]

Hi Class

Can you tell me how to write a simple script as below and make it executable

and run as *.bat file to schedule in windows 2003

Use WG3

EXEC sp_updatestats

go

[kishore_soma]

The answers were very help full

Thnx