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

Hardware

is there a diffrence in the hardware needs of the machine if i setting up a
cluster?
If you are asking about performance or scalability, then its a little
bit complicated to give a good answer. Maybe you could provide more
information.
If you are asking about supportability, then it is best practices to by
a solution that is on the Windows Server Catalog list.
Go to Windows Server Catalog list home page
(http://www.microsoft.com/windows/catalog/server/default-v1.aspx) ,
choose Hardware and them Cluster. There you can choose a hardware
solution from any vendor.
Ari wrote:
> is there a diffrence in the hardware needs of the machine if i setting up a
> cluster?
|||"Ari" <Ari@.discussions.microsoft.com> wrote in message
news:CADD529B-ABB0-4406-982E-D6A4AFAAF9BA@.microsoft.com...
> is there a diffrence in the hardware needs of the machine if i setting up
> a
> cluster?
Yes, there is, and it is a huge difference.
I recommend looking at the Windows Server Catalog for further information
regarding hardware.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
|||Yes and no. In an environment like ours, we use standard servers from tier 1
system vendors (such as DL585) and connect all servers to a centralized SAN,
there is virtually no difference in hardware whether or not the sever is
clustered.
If you are just walking into the wild world of server market and try to pick
some servers to cluster, your choices are more limited than they are when you
don't plan to cluster them. But I would argue that you should always
standardize on a limited selection of servers for all your server needs.
Should that be the case, I don't see why you want to make a separate list for
non-clustered servers, which serves no purpose but to complicate life.
Linchi
"Ari" wrote:

> is there a diffrence in the hardware needs of the machine if i setting up a
> cluster?
|||Ari,
with clustering you need to stick to the HCL (now called catalog)
see: http://www.windowsservercatalog.com/
Also, please read up on clusters, a very good collection of reference
material can be found here : http://www.nw-america.com
HTH,
Edwin.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:41A810C5-9773-428E-892A-956F47D0B20D@.microsoft.com...
> Yes and no. In an environment like ours, we use standard servers from tier
1
> system vendors (such as DL585) and connect all servers to a centralized
SAN,
> there is virtually no difference in hardware whether or not the sever is
> clustered.
> If you are just walking into the wild world of server market and try to
pick
> some servers to cluster, your choices are more limited than they are when
you
> don't plan to cluster them. But I would argue that you should always
> standardize on a limited selection of servers for all your server needs.
> Should that be the case, I don't see why you want to make a separate list
for[vbcol=seagreen]
> non-clustered servers, which serves no purpose but to complicate life.
> Linchi
> "Ari" wrote:
up a[vbcol=seagreen]

H

Hi
How do I change the log shipping monitor to another server
without setting up log shipping again?
Thanks
HThese steps are probably worth a shot. I'd however carefully test it in a
test environment first:
The information about the monitoring of the log shipping is stored to the
following locations:
- On the Primary server, in the msdb.dbo.log_shipping_monitor table
- On the Standby server, in the msdb.dbo.log_shipping_monitor table
- On the log shipping Monitor, in the msdb.dbo.log_shipping_primaries and
the msdb.dbo.log_shipping_secondaries tables.
1. Each time a log shipping related job is executed, its completion status
is updated into the tables of the Monitor server. Obviously, if the log
shipping monitor server needs to be changed, the information about it must
be updated in all of the locations. Beware that in log shipping,should not
be allowed to specify different monitor servers if secondary has monitor
already defined.
2. Script the "Log Shipping Alert - Backup" and "Log Shipping Alert -
Restore" jobs from the current monitor server To do this in the Enterprise
manager go to SQL Server Agent -->Jobs --> "Log shipping Alert-Backup"
-->Right click-->Generate SQL Script.Run these scripts on the New Monitor
Server to create the Alerts there.
3. Run the following query on current monitor server and save the output
select maintenance_plan_id from msdb.dbo.log_shipping_primaries
select secondary_plan_id from msdb.dbo.log_shipping_secondaries
4. On the new log shipping Monitor, there are a couple of alternatives:
- Use Query Analyzer or other similar tool to execute
sp_add_log_shipping_primary and sp_add_log_shipping_secondary stored
procedures on the new Monitor server to define the monitored servers. This
alternative is suitable if the Monitor role is moved to another server.
- Restore the whole MSDB database to the new Monitor server. This
alternative is suitable if the server needs to be replaced or if MSDB
contains other crucial information that needs to be moved, too (DTS
packages, jobs, operators, alerts, etc.) Be aware about some complications
that may turn up in case the machine name is different than the original
one. For more information see KB article 281642.KB.EN-US: BUG: Error 14274
When Updating a SQL Agent Job After Renaming Windows Server
A sample execution of the sp_add_log_shipping_primary and
sp_add_log_shipping_secondary stored procedures may look like:
DECLARE @.myprimary_id int
SELECT @.myprimary_id = 1
EXEC msdb.dbo.sp_add_log_shipping_primary
@.primary_server_name = 'primary_server_name', -- the primary server name
@.primary_database_name = 'primary_database_name', -- the primary db name
@.maintenance_plan_id = 'C354D59F-FACF-43FA-8D52-CEE8DC62FCC2', -- ID of the
maintenance plan that backs up the transaction log
@.backup_threshold = 45, -- the length of time, in minutes, after the last
backup before a threshold_alert error is raised
@.threshold_alert = 14420, -- the error raised when the backup threshold is
exceeded
@.threshold_alert_enabled = 1, -- whether an alert will be raised when
backup_threshold is exceeded
@.planned_outage_start_time = 0, -- the time, in HHMMSS format, a planned
outage starts.
-- During a planned outage, alerts will not be raised if the backup
threshold is exceeded
@.planned_outage_end_time = 0, -- the time, in HHMMSS format, a planned
outage ends
@.planned_outage_weekday_mask = 0, -- the day of the week that a planned
outage occurs
@.primary_id = @.myprimary_id -- OUTPUT parameter, unique ID for the new
primary server and database pair
--Execute the following select statement on the New monitor Server to get
--the Primary_ID that needs to be used in the sp_add_log_shipping secondary
statement:
select primary_id from msdb.dbo.log_shipping_primaries where
primary_server_name = '<primary server name>' and primary_database_name ='<primary database name>'
EXEC msdb.dbo.sp_add_log_shipping_secondary
@.primary_id = @.myprimary_id, -- the ID of the primary server
@.secondary_server_name = 'secondary_server_name', -- the name of the
secondary server
@.secondary_database_name = 'seconday_database_name', -- the secondary
db name
@.secondary_plan_id = '616679D6-CA8A-4098-A694-9502C390D2CA', -- the
ID of the log shipping plan on the secondary server found in
msdb..log_shipping_plans table as obtained in Step 2.
@.copy_enabled = 1, -- whether the copy for the database is enabled on the
secondary server
@.load_enabled = 1, -- whether the load for the database is enabled on the
secondary server
@.out_of_sync_threshold = 45, -- the length of time, in minutes, after the
last load before an error is raised
@.threshold_alert = '14421', -- whether an alert will be raised when an
out-of-sync threshold is exceeded
@.threshold_alert_enabled = 1, -- whether an alert will be raised when
backup_threshold is exceeded
@.planned_outage_start_time = 0, -- the time, in HHMMSS format, a planned
outage starts.
-- During a planned outage, alerts will not be raised if the backup
threshold is exceeded
@.planned_outage_end_time = 0, -- the time, in HHMMSS format, a
planned outage ends
@.planned_outage_weekday_mask = 0 -- the day of the week that a
planned outage occurs
GOThe values for the parameters can be completed from the values in the
step 2 above
5. Execute the following statement on the Primary and secondary servers to
get the current monitor login information. Note down the Logon Type
information.
select * from msdb.dbo.log_shipping_monitor
go
6. Execute the following command on both Primary and all secondary servers
to change the monitor information. Use the logon type that was found in the
step above
sp_define_log_shipping_monitor @.monitor_name ='<New monitor server name>',
@.logon_type =logon_type, -- this was written down in the previous
step
@.password = NULL, -- if the logon type is 1 ( NT authentication) then
the password is ignored
@.delete_existing=1
go
7. On the old log shipping Monitor, delete the log shipping information
from the msdb.dbo.log_shipping_primaries and the
msdb.dbo.log_shipping_secondaries tables. Executing only the
sp_delete_log_shipping_primary stored procedure is sufficient to delete the
related entries from the msdb.dbo.log_shipping_secondaries table if the
@.delete_secondaries parameter is set. A sample execution of the procedure
may look like:
USE master
GO
EXEC sp_delete_log_shipping_primary
@.primary_server_name = 'primary_server_name', -- the primary server
name
@.primary_database_name = 'primary_database_name', -- the primary db name
@.delete_secondaries = 1 -- the delete action is also applied to
log_shipping_secondaries table. Bear in mind that the entries in the
Monitor's system tables are per server and also per database.
It is not recommended to do direct inserts into the
msdb.dbo.log_shipping_primaries and msdb.dbo.log_shipping_secondaries
tables. Direct updates of system tables is never recommended - use the
designated stored procedures instead. Note that the procedure is just the
same even if either the Primary or any of the Standby servers has been
assigned the Monitor role. Just treat it as a separate server and follow
the steps above.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.