Can not create objects in master

Hi,
I'm trying to create a sp in the database master, but I
cann't, I got an error message
Server: Msg 2714, Level 16, State 5, Procedure Sp_Backup,
Line 41
There is already an object named 'Sp_Backup' in the
database.
but I login as sa user and I got the same error.
could somebody help me, please? I think is a configuration
parameter.
Thanks in advance,
Javier RosasHello Javier !
The error say itself that there is alwasy a SP named SP_BACKUP in there.
Just try this to find whos owning the sp:
USE MASTER
SELECT SO.name, SU.Name
FROM sysobjects SO INNER JOIN
sysusers SU ON SO.UID = SU.UID
WHERE SO.name = 'SP_BACKUP'
THEN CHECK you is logged on:
SELECT USER_NAME()
If they are both identical (Owner and LoggedON Persons), you have to drop
the SP first
or create it for antoher user
CREATE PROCEDURE OWNER.SP_NAME
HTH, Jens Süßmeyer.|||No, the problem is that the object doesn't exist!!!
I can change the name, but the problem is the same, in fact, if I were creating a table (or another object) I got the same mistake!!
Thanks.
>--Original Message--
>Hello Javier !
>The error say itself that there is alwasy a SP named SP_BACKUP in there.
>Just try this to find whos owning the sp:
>USE MASTER
>SELECT SO.name, SU.Name
>FROM sysobjects SO INNER JOIN
> sysusers SU ON SO.UID =3D SU.UID
>WHERE SO.name =3D 'SP_BACKUP'
>THEN CHECK you is logged on:
>SELECT USER_NAME()
>If they are both identical (Owner and LoggedON Persons), you have to drop
>the SP first
>or create it for antoher user
>CREATE PROCEDURE OWNER.SP_NAME
>HTH, Jens S=FC=DFmeyer.
>
>.
>|||It seems there is an issue with your stored procedure create script.
Note that the error message is referring to line 41. However, without
the script source, it's difficult to determine the exact cause of the
error.
Also, user stored procedures should not be prefixed with ''sp_' and it's
not a good practice to create user objects in the master database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Javier Rosas" <jrosashe@.accival.com.mx> wrote in message
news:003701c36fe8$764bf0d0$a501280a@.phx.gbl...
> Hi,
> I'm trying to create a sp in the database master, but I
> cann't, I got an error message
> Server: Msg 2714, Level 16, State 5, Procedure Sp_Backup,
> Line 41
> There is already an object named 'Sp_Backup' in the
> database.
> but I login as sa user and I got the same error.
> could somebody help me, please? I think is a configuration
> parameter.
> Thanks in advance,
> Javier Rosas

Can I restore database with just the mdf?

I need to restore a database to the state it was in six months ago. Though
backups were faithfully done, the backup files are not retained on the system
past one month. And the tape backups of the network, that do go back in
time, skipped the directories that held the bak and trn files.
What I have is an mdf file from that period. I tried doing an attach with
ATTACH_REBUILD_LOG, but it errors out because "the database was not cleanly
shut down." I'm guessing that this is because the database file was simply
copied as a physical file instead of being detached first.
In an act of desperation, I created an empty database of the same name, shut
down the server, and then copied over the old mdf file. I didn't really
expect it to work, and therefore was not disappointed.
Is there anything I can do to restore this old mdf?
I saw several posts advising people to try sp_attach_single_file_db. It did
this but got the same error message that I had been getting.
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file
"D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRinc_65App_ log.ldf". Operating system
error 5: "5(Access is denied.)".
File activation failure. The physical file name
"D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRinc_65App_ log.ldf" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'MCRinc_65App_Jun07'. CREATE DATABASE is aborted.
"Bev Kaufman" wrote:

> I need to restore a database to the state it was in six months ago. Though
> backups were faithfully done, the backup files are not retained on the system
> past one month. And the tape backups of the network, that do go back in
> time, skipped the directories that held the bak and trn files.
> What I have is an mdf file from that period. I tried doing an attach with
> ATTACH_REBUILD_LOG, but it errors out because "the database was not cleanly
> shut down." I'm guessing that this is because the database file was simply
> copied as a physical file instead of being detached first.
> In an act of desperation, I created an empty database of the same name, shut
> down the server, and then copied over the old mdf file. I didn't really
> expect it to work, and therefore was not disappointed.
> Is there anything I can do to restore this old mdf?
|||Bev,
This may not work, depending on many things. But, having said that, it did
save my skin in one disaster. Please read and follow carefully:
http://www.tech-archive.net/Archive/SQL-Server/microsoft.public.sqlserver.server/2004-03/3026.html
As Jasper Smith says, this is unsupported.
RLF
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:C5E520BD-5FD1-44EE-9963-399369BABA22@.microsoft.com...[vbcol=seagreen]
>I saw several posts advising people to try sp_attach_single_file_db. It
>did
> this but got the same error message that I had been getting.
> Msg 5120, Level 16, State 101, Line 1
> Unable to open the physical file
> "D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRinc_65App_ log.ldf". Operating
> system
> error 5: "5(Access is denied.)".
> File activation failure. The physical file name
> "D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRinc_65App_ log.ldf" may be
> incorrect.
> The log cannot be rebuilt because the database was not cleanly shut down.
> Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'MCRinc_65App_Jun07'. CREATE DATABASE is
> aborted.
>
> "Bev Kaufman" wrote:
|||I had to do something similar but I only thing was I couldn't get step 9 on
working. So what I did was after the database was in emergency state, I
created a new blank database. And used DTS services to transfer all objects
from original database to the new one. SQL was able to access the database
even though it had no log file; just didn't want me to make any transactions
against it where log was required I guess.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/
"Russell Fields" wrote:

> Bev,
> This may not work, depending on many things. But, having said that, it did
> save my skin in one disaster. Please read and follow carefully:
> http://www.tech-archive.net/Archive/SQL-Server/microsoft.public.sqlserver.server/2004-03/3026.html
> As Jasper Smith says, this is unsupported.
> RLF
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:C5E520BD-5FD1-44EE-9963-399369BABA22@.microsoft.com...
>
>

Can I restore database with just the mdf?

I need to restore a database to the state it was in six months ago. Though
backups were faithfully done, the backup files are not retained on the syste
m
past one month. And the tape backups of the network, that do go back in
time, skipped the directories that held the bak and trn files.
What I have is an mdf file from that period. I tried doing an attach with
ATTACH_REBUILD_LOG, but it errors out because "the database was not cleanly
shut down." I'm guessing that this is because the database file was simply
copied as a physical file instead of being detached first.
In an act of desperation, I created an empty database of the same name, shut
down the server, and then copied over the old mdf file. I didn't really
expect it to work, and therefore was not disappointed.
Is there anything I can do to restore this old mdf?I saw several posts advising people to try sp_attach_single_file_db. It did
this but got the same error message that I had been getting.
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file
" D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRi
nc_65App_log.ldf". Operating system
error 5: "5(Access is denied.)".
File activation failure. The physical file name
" D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRi
nc_65App_log.ldf" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'MCRinc_65App_Jun07'. CREATE DATABASE is aborted
.
"Bev Kaufman" wrote:

> I need to restore a database to the state it was in six months ago. Thoug
h
> backups were faithfully done, the backup files are not retained on the sys
tem
> past one month. And the tape backups of the network, that do go back in
> time, skipped the directories that held the bak and trn files.
> What I have is an mdf file from that period. I tried doing an attach with
> ATTACH_REBUILD_LOG, but it errors out because "the database was not cleanl
y
> shut down." I'm guessing that this is because the database file was simpl
y
> copied as a physical file instead of being detached first.
> In an act of desperation, I created an empty database of the same name, sh
ut
> down the server, and then copied over the old mdf file. I didn't really
> expect it to work, and therefore was not disappointed.
> Is there anything I can do to restore this old mdf?|||Bev,
This may not work, depending on many things. But, having said that, it did
save my skin in one disaster. Please read and follow carefully:
http://www.tech-archive.net/Archive...04-03/3026.html
As Jasper Smith says, this is unsupported.
RLF
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:C5E520BD-5FD1-44EE-9963-399369BABA22@.microsoft.com...[vbcol=seagreen]
>I saw several posts advising people to try sp_attach_single_file_db. It
>did
> this but got the same error message that I had been getting.
> Msg 5120, Level 16, State 101, Line 1
> Unable to open the physical file
> " D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRi
nc_65App_log.ldf". Operating
> system
> error 5: "5(Access is denied.)".
> File activation failure. The physical file name
> " D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRi
nc_65App_log.ldf" may be
> incorrect.
> The log cannot be rebuilt because the database was not cleanly shut down.
> Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'MCRinc_65App_Jun07'. CREATE DATABASE is
> aborted.
>
> "Bev Kaufman" wrote:
>|||I had to do something similar but I only thing was I couldn't get step 9 on
working. So what I did was after the database was in emergency state, I
created a new blank database. And used DTS services to transfer all objects
from original database to the new one. SQL was able to access the database
even though it had no log file; just didn't want me to make any transactions
against it where log was required I guess.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/
"Russell Fields" wrote:

> Bev,
> This may not work, depending on many things. But, having said that, it di
d
> save my skin in one disaster. Please read and follow carefully:
> http://www.tech-archive.net/Archive...04-03/3026.html
> As Jasper Smith says, this is unsupported.
> RLF
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:C5E520BD-5FD1-44EE-9963-399369BABA22@.microsoft.com...
>
>

Can I restore database with just the mdf?

I need to restore a database to the state it was in six months ago. Though
backups were faithfully done, the backup files are not retained on the system
past one month. And the tape backups of the network, that do go back in
time, skipped the directories that held the bak and trn files.
What I have is an mdf file from that period. I tried doing an attach with
ATTACH_REBUILD_LOG, but it errors out because "the database was not cleanly
shut down." I'm guessing that this is because the database file was simply
copied as a physical file instead of being detached first.
In an act of desperation, I created an empty database of the same name, shut
down the server, and then copied over the old mdf file. I didn't really
expect it to work, and therefore was not disappointed.
Is there anything I can do to restore this old mdf?I saw several posts advising people to try sp_attach_single_file_db. It did
this but got the same error message that I had been getting.
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file
"D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRinc_65App_log.ldf". Operating system
error 5: "5(Access is denied.)".
File activation failure. The physical file name
"D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRinc_65App_log.ldf" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'MCRinc_65App_Jun07'. CREATE DATABASE is aborted.
"Bev Kaufman" wrote:
> I need to restore a database to the state it was in six months ago. Though
> backups were faithfully done, the backup files are not retained on the system
> past one month. And the tape backups of the network, that do go back in
> time, skipped the directories that held the bak and trn files.
> What I have is an mdf file from that period. I tried doing an attach with
> ATTACH_REBUILD_LOG, but it errors out because "the database was not cleanly
> shut down." I'm guessing that this is because the database file was simply
> copied as a physical file instead of being detached first.
> In an act of desperation, I created an empty database of the same name, shut
> down the server, and then copied over the old mdf file. I didn't really
> expect it to work, and therefore was not disappointed.
> Is there anything I can do to restore this old mdf?|||Bev,
This may not work, depending on many things. But, having said that, it did
save my skin in one disaster. Please read and follow carefully:
http://www.tech-archive.net/Archive/SQL-Server/microsoft.public.sqlserver.server/2004-03/3026.html
As Jasper Smith says, this is unsupported.
RLF
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:C5E520BD-5FD1-44EE-9963-399369BABA22@.microsoft.com...
>I saw several posts advising people to try sp_attach_single_file_db. It
>did
> this but got the same error message that I had been getting.
> Msg 5120, Level 16, State 101, Line 1
> Unable to open the physical file
> "D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRinc_65App_log.ldf". Operating
> system
> error 5: "5(Access is denied.)".
> File activation failure. The physical file name
> "D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRinc_65App_log.ldf" may be
> incorrect.
> The log cannot be rebuilt because the database was not cleanly shut down.
> Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'MCRinc_65App_Jun07'. CREATE DATABASE is
> aborted.
>
> "Bev Kaufman" wrote:
>> I need to restore a database to the state it was in six months ago.
>> Though
>> backups were faithfully done, the backup files are not retained on the
>> system
>> past one month. And the tape backups of the network, that do go back in
>> time, skipped the directories that held the bak and trn files.
>> What I have is an mdf file from that period. I tried doing an attach with
>> ATTACH_REBUILD_LOG, but it errors out because "the database was not
>> cleanly
>> shut down." I'm guessing that this is because the database file was
>> simply
>> copied as a physical file instead of being detached first.
>> In an act of desperation, I created an empty database of the same name,
>> shut
>> down the server, and then copied over the old mdf file. I didn't really
>> expect it to work, and therefore was not disappointed.
>> Is there anything I can do to restore this old mdf?|||I had to do something similar but I only thing was I couldn't get step 9 on
working. So what I did was after the database was in emergency state, I
created a new blank database. And used DTS services to transfer all objects
from original database to the new one. SQL was able to access the database
even though it had no log file; just didn't want me to make any transactions
against it where log was required I guess.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/
"Russell Fields" wrote:
> Bev,
> This may not work, depending on many things. But, having said that, it did
> save my skin in one disaster. Please read and follow carefully:
> http://www.tech-archive.net/Archive/SQL-Server/microsoft.public.sqlserver.server/2004-03/3026.html
> As Jasper Smith says, this is unsupported.
> RLF
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:C5E520BD-5FD1-44EE-9963-399369BABA22@.microsoft.com...
> >I saw several posts advising people to try sp_attach_single_file_db. It
> >did
> > this but got the same error message that I had been getting.
> >
> > Msg 5120, Level 16, State 101, Line 1
> > Unable to open the physical file
> > "D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRinc_65App_log.ldf". Operating
> > system
> > error 5: "5(Access is denied.)".
> > File activation failure. The physical file name
> > "D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRinc_65App_log.ldf" may be
> > incorrect.
> > The log cannot be rebuilt because the database was not cleanly shut down.
> > Msg 1813, Level 16, State 2, Line 1
> > Could not open new database 'MCRinc_65App_Jun07'. CREATE DATABASE is
> > aborted.
> >
> >
> > "Bev Kaufman" wrote:
> >
> >> I need to restore a database to the state it was in six months ago.
> >> Though
> >> backups were faithfully done, the backup files are not retained on the
> >> system
> >> past one month. And the tape backups of the network, that do go back in
> >> time, skipped the directories that held the bak and trn files.
> >>
> >> What I have is an mdf file from that period. I tried doing an attach with
> >> ATTACH_REBUILD_LOG, but it errors out because "the database was not
> >> cleanly
> >> shut down." I'm guessing that this is because the database file was
> >> simply
> >> copied as a physical file instead of being detached first.
> >>
> >> In an act of desperation, I created an empty database of the same name,
> >> shut
> >> down the server, and then copied over the old mdf file. I didn't really
> >> expect it to work, and therefore was not disappointed.
> >>
> >> Is there anything I can do to restore this old mdf?
>
>