SQL Backup database - plan using same file name

How to SQL backup database, using scheduled maintenance plans to overwrite the same file name every time the backup is processed?

Solution:

Create a maintenance plan with t-sql in the job step similar to:

exec master.dbo.xp_backup_database @database = '', @filename = 'E:\Backup\_Daily.Bak', @backupname = ' backup', @desc = 'Backup of daily', @logging = 0, @with = 'SKIP', @init= 1, @with = 'STATS = 10'

Note, if the files with same name are overwriting with each backup, there will never be more than one archived backup on that destination.

Example of With Rollup and With Cube in SQL 2005

Cool: Technique with Rollup and with cube in SQL 2005.

Declare @TempTableData table
(
Customer varchar(50),
ItemName varchar(50),
Quantity int,
PricePerItem float
);

Insert into @TempTableData
Select 'Sreedhar', 'Item 1', 2, 60.00 Union all
Select 'Sreedhar', 'Item 2', 2, 40.00 Union all
Select 'Vankayala', 'Item 1', 1, 60.00 Union all
Select 'Vankayala', 'Item 2', 1, 40.00 Union all
Select 'Vankayala', 'Item 3', 1, 10.00

-- Select all from table
Select * from @TempTableData;

-- Select all with calculated price
SELECT Customer, ItemName, SUM(Quantity * PricePerItem) as Price
FROM @TempTableData
GROUP BY Customer, ItemName

-- Select all with calculated price (with ROLLUP)
SELECT
Case when grouping(Customer) = 1 then 'All Customers' else Customer end as Customer,
Case when grouping(ItemName) = 1 then 'All Items' else ItemName end as ItemName,
SUM(Quantity * PricePerItem) as Price
FROM @TempTableData
GROUP BY Customer, ItemName
With ROLLUP

-- Select all with calculated price (with CUBE)
SELECT
Case when grouping(Customer) = 1 then 'All Customers' else Customer end as Customer,
Case when grouping(ItemName) = 1 then 'All Items' else ItemName end as ItemName,
SUM(Quantity * PricePerItem) as Price
FROM @TempTableData
GROUP BY Customer, ItemName
With CUBE


Results for the above SQL:

Customer ItemName Quantity PricePerItem
Sreedhar Item 1 2 60
Sreedhar Item 2 2 40
Vankayala Item 1 1 60
Vankayala Item 2 1 40
Vankayala Item 3 1 10

(5 row(s) affected)

Customer ItemName Price
Sreedhar Item 1 120
Vankayala Item 1 60
Sreedhar Item 2 80
Vankayala Item 2 40
Vankayala Item 3 10

(5 row(s) affected)

Customer ItemName Price
Sreedhar Item 1 120
Sreedhar Item 2 80
Sreedhar All Items 200
Vankayala Item 1 60
Vankayala Item 2 40
Vankayala Item 3 10
Vankayala All Items 110
All Customers All Items 310

(8 row(s) affected)


Customer ItemName Price
Sreedhar Item 1 120
Sreedhar Item 2 80
Sreedhar All Items 200
Vankayala Item 1 60
Vankayala Item 2 40
Vankayala Item 3 10
Vankayala All Items 110
All Customers All Items 310
All Customers Item 1 180
All Customers Item 2 120
All Customers Item 3 10

(11 row(s) affected)