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

having difficulty inserting into the database table

I created a web form where the user fills in some data and when he submits the form, I do an insert into he database table. The problem is, how can I get the data from the form into the insert statement?. here is the code:
Dim Message As String
Dim connStr As String
Dim myConnection As SqlConnection
Dim mySqlCommand As SqlCommand
connStr = "server=SIMI\VSdotNET;Trusted_Connection=yes;database=AeroSea"
myConnection = New SqlConnection(connStr)
mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,name.text)", myConnection)

If I execute the above code, then nothing gets updated. When I change the insert staement into the following,
mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,'myname')", myConnection) then,
value 1 for customerID field and myname in the namefield is added.
I know I am doing something stupid, but can't figure it out.
I am confused. please help me.One possibility is this (presuming textbox is name):

mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,'" + name.text + "')", myConnection)

Better,use parameters, as the code above is subject to SQL Injection attacks, and as written will fail if the name.Text is "O'Reilly".|||Thanks, I used the parameters and it works fine.

Having difficulty creating a stored procedure

I am trying to create stored procedure i Query analyzer in visual studio 2005. I am having
difficulty though. Whenever I press the execute button, here is the error message I get:

Msg 102, Level 15, State 1, Procedure MarketCreate, Line 21
Incorrect syntax near 'MarketName'.

Here is the stored procedure. Note that the very first column in named "MarketId" but I did not
include it in the stored procedure since it should be auto generated.

USE [StockWatch]
GO
/****** Object: StoredProcedure [dbo].[MarketCreate] Script Date: 08/28/2007 15:49:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MarketCreate]

(
@.MarketCode nvarchar(20),
@.MarketName nvarchar(100),
@.LastUpdateDate nvarchar(2),
@.MarketDescription nvarchar(100)
)

AS
INSERT INTO Market
(
MarketCode
MarketName
LastUpdateDate
MarketDescription
)
VALUES
(
@.MarketCode
@.MarketName
@.LastUpdateUser
@.MarketDescription
)

You need to use comma's to separate the column names.

USE[StockWatch]

GO

/****** Object: StoredProcedure [dbo].[MarketCreate] Script Date: 08/28/2007 15:49:26 ******/

SET ANSI_NULLSON

GO

SET QUOTED_IDENTIFIERON

GO

CREATEPROCEDURE [dbo].[MarketCreate]

(

@.MarketCodenvarchar(20),

@.MarketNamenvarchar(100),

@.LastUpdateDatenvarchar(2),

@.MarketDescriptionnvarchar(100)

)

AS

INSERTINTO Market

(

MarketCode,

MarketName,

LastUpdateDate,

MarketDescription

)

VALUES

(

@.MarketCode,

@.MarketName,

@.LastUpdateUser,

@.MarketDescription

)

|||

Thanks !