Problem removing old back up files....

I am having problems with removing the update files from a folder. When
a do a new backup, I want it to overwrite the old backup files with the
new one. Apparently the code I am using is wrong. Any suggestions?Hi,
Would you be able to post your code so that people can see if they can
see anything with it or if it's fine?
If you are using BACKUP statements then you have to add "INIT" to the
"WITH" section. This will overwrite whatever backup device (e.g. the
file name) that you specify in the statement. For example:
BACKUP DATABASE Test_DB
TO DISK = 'C:\Test_DB_Backup.bak'
WITH INIT
WIll overwrite the previous contents of Test_DB_Backup.bak. Note that
there are some caveats with using INIT (namely that it won't overwrite
in certain situations) but these are described in SQL Server Books
Online in the topic about the BACKUP statement.
Hope that helps a bit|||EXECUTE master.dbo.xp_sqlmaint N'-PlanID
88FF9022-F638-4939-99FF-2FD57140382C -Rpt "C:\Program Files\Microsoft
SQL Server\MSSQL\LOG\<Backup Sever Name> Backup4.txt" -DelTxtRpt 2WEEKS
-WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "\\<Backup Server
Name>\Backup Do Not Touch\<SQL Server Name>" -DelBkUps 1DAYS
-CrBkSubDir -BkExt "BAK"'
There is the one we are using.|||OK, I see...There is an Database Maintenance Plan in place that will
take a backup of the desired database and delete backups older than one
day.
I don't believe that the sqlmaint utility (the utility that will be
called by xp_sqlmaint) has a switch that orders backups to be
overwritten. If you want to take backups but overwrite them each day
then you'll probably need to define your own SQL Server Agent job that
executes the following T-SQL as per whatever schedule you desire:
BACKUP DATABASE <DB_Name>
TO DISK = '<Path Of Backup File (includes the file name)>'
WITH INIT -- Force an overwrite most of the time (some caveats as
described in Books Online)
BTW, I think that generally it is a good idea to keep a few backup
files just in case something goes wrong with the backup you are
currently taking there is something to fall back on.
Hope that helps a bit

Problem processing cube partition

I am getting the following error when trying to process a partition, as if
the process is trying to overwrite the database. How can I fix this?

TIA

Error 1 Errors in the metadata manager. The database with the name of
'ACRPROD_OLAP' already exists in the 'MBPENTSQL01R\ACR' server. 0 0

Are you deploying from BIDS? Did you rename a database that was previously deployed? The following post might shed some light. You might check the Name and ID properties of each deployed database and see whether any have the ID of ACRPROD_OLAP regardless of what Name they have.

http://geekswithblogs.net/darrengosbell/archive/2007/06/06/BIDSHelper-DeployMDXScript-feature-enhanced.aspx

Easy fix... delete the conflicting database off the server. More complex fix... instead of renaming a database on the server, backup the database, delete it, then restore it as the new name... that will make sure the new name matches the new ID.

sql

Problem ovewriting xp_sendmail

I am trying to overwrite xp_sendmail by a transact sql stored procedure. I delete the original one and write a new one with the same name but I keep receiving errors which seems to relate to the orinal one as if the security is kept in place even though I deleted the extended procedure.

Here are the details of what I did:

I created a stored procedure xp_sendmail in master database.

I called the procedure and receives the following error message:

Msg 15281, Level 16, State 1, Procedure xp_sendmail, Line 1

SQL Server blocked access to procedure 'sys.xp_sendmail' of component 'SQL Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'SQL Mail XPs' by using sp_configure. For more information about enabling 'SQL Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.

I tried deleting the extended stored procedure with the same name from the visual interface of "Microsoft ssql server management studio". The process did not work, I received the following message: "Cannot use SP_DROPEXTENDEDPROC or DBCC DROPEXTENDEDPROC with xp_sendmail because "xp_sendmail" is a procedure. Use DROP PROCEDURE (MSSQL ERROR 3751)"

I believe the interface does not do the distinction between my procedure and the orinal extended procedure. I therefore tried the following:

drop the stored procedure I wrote.

drop once more the extended procedure. I received a different message: "Cannot drop procedure because it does not exists or you do not have the permission (mssql error 3701)"

I do refresh, I reopen the management studio, no matter, the extended procedure is still there. Only way I find to delete it from the interface is recreating from transact sql before redropping it.

sp_addextendedproc 'xp_sendmail', [the path of the dll here]

Even aftter doing all those and recreating my own procedure, I still see the extended procedure reapearing in the sql server management studio interface and I still receive the security error.

It is as if sql server fails to notice I dropped the procedure.

I am trying all those with the sa user so I doubt the issue comes from the fact that I lack permission.

Is there something that can be done to force sql server to consider my procedure as a separate one that the original one. Why is the extended procedure still appearing and why do I still receive security error after I drop it. I tried calling the procedure without with both the extended procedure and transact sql stored procedure dropped or with the extended one droped and the other one present, still I get no success and still receive the security error. I even tried freeing the dll from memory but it makes no difference:

DBCC xp_sendmail (free)

Thanks for the help.

I am really sorry to say this, but unfortunately this is an unsupported scenario and we will not be able to help.

My only recommendation at this point is to try to backup any important data from your system, and reinstall SQL Server. Once you have a clean system, create your XP under a different name (i.e. xp_sendmail2)

I strongly recommend using only the supported mechanisms designed to extend the system (such as creating new XPs, CLR assemblies, etc.) instead of trying to modify the system objects.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks for your time.

the reason we try to do this is because we have a bunch of application calling xp_sendmail and want to move to a mail sending method which allows to to define the smtp server address to enforce an email policy.

It is therefore easier to just overwrite the xp_sendmail than to overwrite all our applications to call another procedure. This used to work in sql server 2000.

That said, I don't think it is necessary to rebuild the server. I can run sp_addextendedproc to rerister the dll and it works. All I need to find is how to tell sql server to forget that this extended procedure existed. Even if it is deleted, it is still visible in the management studio interface and my procedure gets mixed up with the extended procedure when it comes to permission and settings.

If this cannot be achieved, I'll move to plan B. However, if anybody knows of a method to resolve this issue, then it would be appreciated.

Thanks again.