Having difficulty setting Back Up to back up file wihout datetime stamp SQL 2K

Hello,

I'm trying to create a simple back up in the SQL Maintenance Plan that will
make a single back up copy of all database every night at 10 pm. I'd like
the previous nights file to be overwritten, so there will be only a single
back up file for each database (tape back up runs every night, so each days
back up will be saved on tape).

Every night the maintenance plan makes a back up of all the databases to a
new file with a datetime stamp, meaning the previous nights file still
exists. Even when I check "Remove files older than 22 hours" the previous
nights file still exists. Is there any way to create a back up file without
the date time stamp so it overwrites the previous nights file?

Thanks!
RickOn 24.04.2007 15:29, Rico wrote:

Quote:

Originally Posted by

I'm trying to create a simple back up in the SQL Maintenance Plan that will
make a single back up copy of all database every night at 10 pm. I'd like
the previous nights file to be overwritten, so there will be only a single
back up file for each database (tape back up runs every night, so each days
back up will be saved on tape).
>
Every night the maintenance plan makes a back up of all the databases to a
new file with a datetime stamp, meaning the previous nights file still
exists. Even when I check "Remove files older than 22 hours" the previous
nights file still exists. Is there any way to create a back up file without
the date time stamp so it overwrites the previous nights file?

Yes, I believe option is "WITH INIT". Please see BOL for details.

Regards

robert|||Hi Robert,

Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
using T-SQL, just trying to create the Maintenance plan from the Enterprise
Manager.

Rick

"Robert Klemme" <shortcutter@.googlemail.comwrote in message
news:596ghdF2jtij6U1@.mid.individual.net...

Quote:

Originally Posted by

On 24.04.2007 15:29, Rico wrote:

Quote:

Originally Posted by

>I'm trying to create a simple back up in the SQL Maintenance Plan that
>will
>make a single back up copy of all database every night at 10 pm. I'd
>like
>the previous nights file to be overwritten, so there will be only a
>single
>back up file for each database (tape back up runs every night, so each
>days
>back up will be saved on tape).
>>
>Every night the maintenance plan makes a back up of all the databases to
>a
>new file with a datetime stamp, meaning the previous nights file still
>exists. Even when I check "Remove files older than 22 hours" the
>previous
>nights file still exists. Is there any way to create a back up file
>without
>the date time stamp so it overwrites the previous nights file?

>
Yes, I believe option is "WITH INIT". Please see BOL for details.
>
Regards
>
robert

|||Rico (me@.you.com) writes:

Quote:

Originally Posted by

Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
using T-SQL, just trying to create the Maintenance plan from the
Enterprise Manager.

From what you described, you should not use a maintenance plan. Just set up
a scheduled job to run the BACKUP statement. You can do this from the
GUI where you backup databases, and select Schedule somewhere on a button.
In the end you get a one-step job that has a BACKUP job.

Then again, if you have any interest in acquiring basic DBA skills, you
should certainly learn to write basic BACKUP commands in T-SQL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland,

I don't have a problem creating the T-SQL commands, it's using the
Enterprise Manager to do more than create dbs is where I get lost.

I will give that a try (creating a back up job and scheduling)

THanks!
Rick

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns991D5A7E496DYazorman@.127.0.0.1...

Quote:

Originally Posted by

Rico (me@.you.com) writes:

Quote:

Originally Posted by

>Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
>using T-SQL, just trying to create the Maintenance plan from the
>Enterprise Manager.

>
From what you described, you should not use a maintenance plan. Just set
up
a scheduled job to run the BACKUP statement. You can do this from the
GUI where you backup databases, and select Schedule somewhere on a button.
In the end you get a one-step job that has a BACKUP job.
>
Then again, if you have any interest in acquiring basic DBA skills, you
should certainly learn to write basic BACKUP commands in T-SQL.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||
"Rico" <me@.you.comwrote in message news:8LvXh.9$_G.8@.edtnps89...

Quote:

Originally Posted by

Thanks Erland,
>
I don't have a problem creating the T-SQL commands, it's using the
Enterprise Manager to do more than create dbs is where I get lost.

That's the problem with using EM.

BTW... the reason you're seeing the behavior you're seeing is that the
maintenance job takes the conservative approach and assumes that the most
recent backup HAS to succeed before it'll delete the older one.

So setting it to 22 hours or anything won't force it to delete the older
file until the new one is successfully created.

Quote:

Originally Posted by

>
I will give that a try (creating a back up job and scheduling)
>
THanks!
Rick
>
>
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns991D5A7E496DYazorman@.127.0.0.1...

Quote:

Originally Posted by

>Rico (me@.you.com) writes:

Quote:

Originally Posted by

>>Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
>>using T-SQL, just trying to create the Maintenance plan from the
>>Enterprise Manager.

>>
>From what you described, you should not use a maintenance plan. Just set
>up
>a scheduled job to run the BACKUP statement. You can do this from the
>GUI where you backup databases, and select Schedule somewhere on a
>button.
>In the end you get a one-step job that has a BACKUP job.
>>
>Then again, if you have any interest in acquiring basic DBA skills, you
>should certainly learn to write basic BACKUP commands in T-SQL.
>>
>--
>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>Books Online for SQL Server 2005 at
>http://www.microsoft.com/technet/pr...oads/books.mspx
>Books Online for SQL Server 2000 at
>http://www.microsoft.com/sql/prodin...ions/books.mspx

>
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html