Backup using TSQL Question

30 Jun 2006 5:20 PM

Mark Moss

Ladies / Gentlemen

                When I issue the command to 'BACKUP' my Database it does so
with no problem.

                However it appends to the last backup. How do I make it
overwrite it.

                I am using the following code

if not exists (select *
                   from master.dbo.sysdevices
                   where name = 'SACS2' )
begin
       USE master
       EXEC sp_addumpdevice 'disk', 'SACS2', 'c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\SACS2.dat'
end

BACKUP DATABASE SACS TO SACS2

Mark Moss

30 Jun 2006 5:34 PM

Tracy McKibben

Mark Moss wrote:
Show quote

> Ladies / Gentlemen
>
>
>                 When I issue the command to 'BACKUP' my Database it does so
> with no problem.
>
>                 However it appends to the last backup. How do I make it
> overwrite it.
>
>
>                 I am using the following code
>
> if not exists (select *
>                    from master.dbo.sysdevices
>                    where name = 'SACS2' )
>   begin
>        USE master
>        EXEC sp_addumpdevice 'disk', 'SACS2', 'c:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\SACS2.dat'
>   end
>
> BACKUP DATABASE SACS TO SACS2
>
>
>
> Mark Moss
>
>

Include WITH INIT in the backup command, or write to a different file...
Found this in Books Online, under the BACKUP command...

30 Jun 2006 5:48 PM

Arnie Rowland

Try:

BACKUP DATABASE SACS TO SACS2 WITH INIT

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

Show quote

"Mark Moss" <markm***@adelphia.net> wrote in message
news:efN9mkGnGHA.4620@TK2MSFTNGP05.phx.gbl...
> Ladies / Gentlemen
>
>
>                When I issue the command to 'BACKUP' my Database it does so
> with no problem.
>
>                However it appends to the last backup. How do I make it
> overwrite it.
>
>
>                I am using the following code
>
> if not exists (select *
>                   from master.dbo.sysdevices
>                   where name = 'SACS2' )
> begin
>       USE master
>       EXEC sp_addumpdevice 'disk', 'SACS2', 'c:\Program Files\Microsoft
> SQL
> Server\MSSQL\BACKUP\SACS2.dat'
> end
>
> BACKUP DATABASE SACS TO SACS2
>
>
>
> Mark Moss
>
>