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 a stored procedure copy tables & also preserve indexing/sch

Hello,
I created a stored procedure that renames a table to OLD_xxxxx and replaces
that table with another (copy) that resides on a different database. I pull
the
tablename names through the use of a cursor table and construct a SELECT INTO
statement as follows
'SELECT * INTO DB1.dbo.' + @.tableName + ' FROM DB2.dbo.' + @.tableName
It works great especially since there are 80+ tables that need to be copied
from one database to another. The drawback is that it doesn't preserve the
indexing/foriegn key constraints. Is there a way to do this without having to
deal with DTS or creating additional scripts? Ideally I would like to
replace
the "SELECT * INTO" statement with something that not only does a copy but
also preserves the indexing! Does such a command exist? Any help from the
Microsoft guru's would be greatly appreciated!!!!
SELECT ... INTO <tablename> doesn't create any of the PRIMARY KEY, UNIQUE,
FOREIGN KEY, CHECK, NOT NULL constraints and doesn't define DEFAULT and
IDENTITY column properties for the new table.
You will have to write seperate statements into your stored procedure to
create them.
--Vishal.
"Peter S." wrote:

> Hello,
> I created a stored procedure that renames a table to OLD_xxxxx and replaces
> that table with another (copy) that resides on a different database. I pull
> the
> tablename names through the use of a cursor table and construct a SELECT INTO
> statement as follows
> 'SELECT * INTO DB1.dbo.' + @.tableName + ' FROM DB2.dbo.' + @.tableName
> It works great especially since there are 80+ tables that need to be copied
> from one database to another. The drawback is that it doesn't preserve the
> indexing/foriegn key constraints. Is there a way to do this without having to
> deal with DTS or creating additional scripts? Ideally I would like to
> replace
> the "SELECT * INTO" statement with something that not only does a copy but
> also preserves the indexing! Does such a command exist? Any help from the
> Microsoft guru's would be greatly appreciated!!!!
>

Havent been able to execute a package on the server

Hello, I created a package on my machine, it deletes some files, then delete some rows, then copy files from a destination to a source and then process all those files and insert rows in a table, really simple.

When I click execute in VS 2005 it executes perfectly. (it took about 45 seconds because there are many files)

I connected to integration services and imported the package then I did the two following things.

1-Right click run package and it executes normally but it took less than 1 second, and when I saw the destination folder there were no files in there so it didnt do anything)

2. I created a job and on the first step I put to execute that package. I then executed the job and the same thing happens, it executed without errors but it took less than 1 send and when I saw the destination folder there were no files in there so it didnt do anything).

I noticed that the Integration services project has a property for creating a deployment utility, I changed this property to true but I dont know how to make the deployment utility.

Maybe the problem was that when I Imported the package,, the package was on another machine on my LAN?

Can the package on the server even see the files? The directories have to be the same, and the user account for the SQL Server service must have rights to that directory as well.

When you execute it on your machine, it's using your user account and currently accessible folders. When the package is promoted to the server, you will be using a different account.|||enable package logging to get more details of the execution. The package may be failing...|||

Hello, I finally could upload the package, and from the management studio interface I ran the package and it worked perfectly.

When I created a job, with one step only to execute that package, the job fails.

When I go to history it doesnt give me any details of what failed on the package or in the job

Date 24/01/2007 12:30:28
Log Job History (Carga datos ACH)

Step ID 1
Server ATLANTE\SQL2005
Job Name Carga datos ACH
Step Name Carga de datos de ach
Duration 00:00:02
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: ATLANTE\SYSTEM. The package execution failed. The step failed.

Maybe is the user that it tried to execute the package as?

|||

Luis Esteban Valencia Mu?oz wrote:

Hello, I finally could upload the package, and from the management studio interface I ran the package and it worked perfectly.

When I created a job, with one step only to execute that package, the job fails.

When I go to history it doesnt give me any details of what failed on the package or in the job

Try using SQL Server Agent CmdExec job step sub-system so that Dtexec can be employed. This will cause more detailed error messages to be stored in the job history. It is also useful to implement the job step log.|||

Hi Luis,

This could be due to the ProtectionLevel setting for the individual packages - that's my guess.

By default, these are set to EncryptSensitiveWithUserKey. This means as long as you personally execute the packages, your credentials are picked up and the packages execute in your security context. This is true even if you're connected to a remote machine, so long as you're using the same AD credentials you used when you built the packages. Does this make sense?

When the job you created executes, it runs under the SQL Agent Service logon credentials.

My understanding of the "Sensitive" in EncryptSensitiveWithUserKey may be flawed, but I cannot find a way to tell my SSIS package "hey, this isn't sensitive so don't encrypt it." Although this sometimes gets in the way I like this feature because it keeps me from doing something I would likely later regret. Anyway, my point is the Sensitive label is applied to connection strings and I cannot find a way to un-apply it (and I'm cool with that).

One of the first things an SSIS package tries to do (after validation) is load up configuration file data. This uses a connection, which (you guessed it) uses your first connection string. Since yours are encrypted with your own personal SID on the domain and this is different from the SID on the account running the SQL Agent Service, the job-executed packages cannot connect to the configuration files to decrypt them.

There are a couple proper long-term solutions but the one that is simplest to implement is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when you set up the job step as well. But this should allow the package to run without needing your security credentials.

Note: You will also need this password to open the packages in BIDS (or Visual Studio) from now on... there's no free lunch.

Hope this helps,

Andy

Have a copy of a database

Hi
Is any way of making a copy of a database (copying the MDF and LDF files)
without detaching the database?
Thanks
Yuelin
Yes. Use the BACKUP DATABASE command. This will after you have performed RESTORE achieve the same
effect as copying the physical files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yuelin Liang" <yuelinliang@.hotmail.com> wrote in message
news:O1MSAHZHFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is any way of making a copy of a database (copying the MDF and LDF files) without detaching the
> database?
> Thanks
> Yuelin
>
|||You may use DTS to transfer data across. from em, right click the database,
import or export.
my 2c
Yifei
"Yuelin Liang" <yuelinliang@.hotmail.com> wrote in message
news:O1MSAHZHFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is any way of making a copy of a database (copying the MDF and LDF files)
> without detaching the database?
> Thanks
> Yuelin
>
sql

Have a copy of a database

Hi
Is any way of making a copy of a database (copying the MDF and LDF files)
without detaching the database?
Thanks
YuelinYes. Use the BACKUP DATABASE command. This will after you have performed RES
TORE achieve the same
effect as copying the physical files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yuelin Liang" <yuelinliang@.hotmail.com> wrote in message
news:O1MSAHZHFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is any way of making a copy of a database (copying the MDF and LDF files)
without detaching the
> database?
> Thanks
> Yuelin
>|||You may use DTS to transfer data across. from em, right click the database,
import or export.
my 2c
Yifei
"Yuelin Liang" <yuelinliang@.hotmail.com> wrote in message
news:O1MSAHZHFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is any way of making a copy of a database (copying the MDF and LDF files)
> without detaching the database?
> Thanks
> Yuelin
>

Have a copy of a database

Hi
Is any way of making a copy of a database (copying the MDF and LDF files)
without detaching the database?
Thanks
YuelinYes. Use the BACKUP DATABASE command. This will after you have performed RESTORE achieve the same
effect as copying the physical files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yuelin Liang" <yuelinliang@.hotmail.com> wrote in message
news:O1MSAHZHFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is any way of making a copy of a database (copying the MDF and LDF files) without detaching the
> database?
> Thanks
> Yuelin
>|||You may use DTS to transfer data across. from em, right click the database,
import or export.
my 2c
Yifei
"Yuelin Liang" <yuelinliang@.hotmail.com> wrote in message
news:O1MSAHZHFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is any way of making a copy of a database (copying the MDF and LDF files)
> without detaching the database?
> Thanks
> Yuelin
>