deleting SQL Srv 2K5 publication with no Agents

I have the case where a SQL Srv 2K5 publication has no agents - agents were accidentally erased during backup/restore. I was abel to erase subscription.

The error is "The specified @.job_name <jab name> does not exist. (Microsft SQL Server, Error: 14262)

Is there any way to remove publication?

Help will be greatly appreciated!

Vladimir

Do you have trouble in dropping subscription/publication? Error looks like to happen when you drop a remote pull subscription.

Anyway, you may call SP directly to drop the subscription, then publication. Please refer to BOL.

Similar call will be like:

use [PubDB]
exec sp_dropmergesubscription

exec sp_mergesubscription_cleanup

exec sp_dropmergepublication

Hope it will be helpful.

Thanks

Yunjing

|||

Hello,

I am dropping xactional publication, not merge subscription. Normally, it is not a problem, but in this case all three agent jobs are gone.

When I try to delete it, I am getting message about absent snapshot agent, Error 20678 "|Could not find the regular snapshot job for the specified publication <publication name>. Supply either @.job_id or @.job name to idnetify the job."

But the jobs like I said is gone.

I have tried sp_droppublication. Got error: "Message 14013, This database is not enabled for publication", which does not make sense since publication is there already.

Thanks for trying.

Vladimir

|||

Hello, Vladimir,

I think meta data got messed up somehow. Could you check whether publications are still there?

please call the below two sql to see if you can get any thing.

On publisher server, select * from [PublicationDB]..syspublications

On distributor server, select * from [distributionDB]..MSpublications

Thanks

Yunjing

|||

Hi Yunjing,

The syspublications does not have any rows.

MSpublications does not list this "orphaned" publication, but it lists all other "healthy" publication I created.

Yesterday I backed up and restored the publisher database. There is the switch during restore 'keep_publication' which gave me hope that

if I will not set it during restore that will break link b/w db and publication. But after restore error still percists.

R-clicking on publication allows to create publication drop script. I ran it, and against every article in publication the message was "the publication does not exist'. This is while I see that publication in GUI.

Also, r-clicking, I tried to start Snapshot agent to create snapshot - the absent snapshot may be the root of the problem.

I guess it was erase together with erasure of snapshot agent.

It failed at the end with error "Cannot insert NULL into column "publisher_database_id", table "distribution.dbo.MSrepl_transactions".

Thanks Yunjing for keeping up.

Vladimir

sql

deleting backup history in order to allow for restore

Hello:
Is there an automated way of having SQL delete backup jobs? I ran
sp_delete_backuphistory against the msbd database in SQL Server 2000
8.00.2039 and I had to stop it because it was taking forever to run.
I'm surprised that SQL does not automatically delete backup history since
maintenance plans for automatically deleting backups. (Any relief in SQL
2005?)
The major reason that I ask about this is because when we conduct a manual
restore of a database, we cannot do so by right-clicking on that database in
Enterprise Manager. Enterprise Manager freezes when we choose All
Tasks...Restore. So, we end up having to instead run a script in Query
Analyzer to restore the database.
Someone on this message board told me to delete backup history. But, again,
that takes too long. And, when I tried to do a restore in Enterprise Manager
just now, I got the same result (though I did cancel the process).
Any ideas?
Thanks!
childofthe1980s
The first time you delete the history it will take a long time if it has
never been done before. Just let it run until it finishes otherwise you roll
it back and you are no better off than before. But once you clear out the
garbage that was in there it will only take a second or less to delete about
a weeks worth if you do it on a regular basis. So set up a scheduled job
that calls this once a week and pass in a datetime that leaves you the week
or two that you want to keep for history.
DECLARE @.Date DATETIME
SET @.Date = DATEADD(wk,-1,GETDATE())
EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
In 2005 they have added a task for the MP to dod this but it does absolutely
nothing more than what I show above and still needs to be done regulary. The
problem is there is data in 3 tables and they are not properly indexed so a
delete on lots of data takes a long time.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
> Hello:
> Is there an automated way of having SQL delete backup jobs? I ran
> sp_delete_backuphistory against the msbd database in SQL Server 2000
> 8.00.2039 and I had to stop it because it was taking forever to run.
> I'm surprised that SQL does not automatically delete backup history since
> maintenance plans for automatically deleting backups. (Any relief in SQL
> 2005?)
> The major reason that I ask about this is because when we conduct a manual
> restore of a database, we cannot do so by right-clicking on that database
> in
> Enterprise Manager. Enterprise Manager freezes when we choose All
> Tasks...Restore. So, we end up having to instead run a script in Query
> Analyzer to restore the database.
> Someone on this message board told me to delete backup history. But,
> again,
> that takes too long. And, when I tried to do a restore in Enterprise
> Manager
> just now, I got the same result (though I did cancel the process).
> Any ideas?
> Thanks!
> childofthe1980s
>
>
>
|||Thanks, Andrew!
childofthe1980s
"Andrew J. Kelly" wrote:

> The first time you delete the history it will take a long time if it has
> never been done before. Just let it run until it finishes otherwise you roll
> it back and you are no better off than before. But once you clear out the
> garbage that was in there it will only take a second or less to delete about
> a weeks worth if you do it on a regular basis. So set up a scheduled job
> that calls this once a week and pass in a datetime that leaves you the week
> or two that you want to keep for history.
> DECLARE @.Date DATETIME
> SET @.Date = DATEADD(wk,-1,GETDATE())
> EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
>
> In 2005 they have added a task for the MP to dod this but it does absolutely
> nothing more than what I show above and still needs to be done regulary. The
> problem is there is data in 3 tables and they are not properly indexed so a
> delete on lots of data takes a long time.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
>

deleting backup history in order to allow for restore

Hello:
Is there an automated way of having SQL delete backup jobs? I ran
sp_delete_backuphistory against the msbd database in SQL Server 2000
8.00.2039 and I had to stop it because it was taking forever to run.
I'm surprised that SQL does not automatically delete backup history since
maintenance plans for automatically deleting backups. (Any relief in SQL
2005?)
The major reason that I ask about this is because when we conduct a manual
restore of a database, we cannot do so by right-clicking on that database in
Enterprise Manager. Enterprise Manager freezes when we choose All
Tasks...Restore. So, we end up having to instead run a script in Query
Analyzer to restore the database.
Someone on this message board told me to delete backup history. But, again,
that takes too long. And, when I tried to do a restore in Enterprise Manager
just now, I got the same result (though I did cancel the process).
Any ideas?
Thanks!
childofthe1980sThe first time you delete the history it will take a long time if it has
never been done before. Just let it run until it finishes otherwise you roll
it back and you are no better off than before. But once you clear out the
garbage that was in there it will only take a second or less to delete about
a weeks worth if you do it on a regular basis. So set up a scheduled job
that calls this once a week and pass in a datetime that leaves you the week
or two that you want to keep for history.
DECLARE @.Date DATETIME
SET @.Date = DATEADD(wk,-1,GETDATE())
EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
In 2005 they have added a task for the MP to dod this but it does absolutely
nothing more than what I show above and still needs to be done regulary. The
problem is there is data in 3 tables and they are not properly indexed so a
delete on lots of data takes a long time.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
> Hello:
> Is there an automated way of having SQL delete backup jobs? I ran
> sp_delete_backuphistory against the msbd database in SQL Server 2000
> 8.00.2039 and I had to stop it because it was taking forever to run.
> I'm surprised that SQL does not automatically delete backup history since
> maintenance plans for automatically deleting backups. (Any relief in SQL
> 2005?)
> The major reason that I ask about this is because when we conduct a manual
> restore of a database, we cannot do so by right-clicking on that database
> in
> Enterprise Manager. Enterprise Manager freezes when we choose All
> Tasks...Restore. So, we end up having to instead run a script in Query
> Analyzer to restore the database.
> Someone on this message board told me to delete backup history. But,
> again,
> that takes too long. And, when I tried to do a restore in Enterprise
> Manager
> just now, I got the same result (though I did cancel the process).
> Any ideas?
> Thanks!
> childofthe1980s
>
>
>|||Thanks, Andrew!
childofthe1980s
"Andrew J. Kelly" wrote:
> The first time you delete the history it will take a long time if it has
> never been done before. Just let it run until it finishes otherwise you roll
> it back and you are no better off than before. But once you clear out the
> garbage that was in there it will only take a second or less to delete about
> a weeks worth if you do it on a regular basis. So set up a scheduled job
> that calls this once a week and pass in a datetime that leaves you the week
> or two that you want to keep for history.
> DECLARE @.Date DATETIME
> SET @.Date = DATEADD(wk,-1,GETDATE())
> EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
>
> In 2005 they have added a task for the MP to dod this but it does absolutely
> nothing more than what I show above and still needs to be done regulary. The
> problem is there is data in 3 tables and they are not properly indexed so a
> delete on lots of data takes a long time.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
> > Hello:
> >
> > Is there an automated way of having SQL delete backup jobs? I ran
> > sp_delete_backuphistory against the msbd database in SQL Server 2000
> > 8.00.2039 and I had to stop it because it was taking forever to run.
> >
> > I'm surprised that SQL does not automatically delete backup history since
> > maintenance plans for automatically deleting backups. (Any relief in SQL
> > 2005?)
> >
> > The major reason that I ask about this is because when we conduct a manual
> > restore of a database, we cannot do so by right-clicking on that database
> > in
> > Enterprise Manager. Enterprise Manager freezes when we choose All
> > Tasks...Restore. So, we end up having to instead run a script in Query
> > Analyzer to restore the database.
> >
> > Someone on this message board told me to delete backup history. But,
> > again,
> > that takes too long. And, when I tried to do a restore in Enterprise
> > Manager
> > just now, I got the same result (though I did cancel the process).
> >
> > Any ideas?
> >
> > Thanks!
> >
> > childofthe1980s
> >
> >
> >
> >
> >
>sql

deleting a tx log backup

Hi,
I backed up my transaction log in order to perform a point in time restore.
I backed it up to an ad hoc folder. The database actually does backup the
log file once a night to a different location. I'm going to change this to
once every thirty minutes.
Can I delete this ad hoc transaction log file backup, or do future backups
depend on it?
Thanks very much for your ideas on this. I just want to be safe!
Many thanks!
AntHello,
If you have a FULL database backup taken after the specific trasnaction log
backup you could delete the log backup file mentioned. Otherwise you need
that specific log
backup file for while recovery.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> Hi,
> I backed up my transaction log in order to perform a point in time
> restore.
> I backed it up to an ad hoc folder. The database actually does backup the
> log file once a night to a different location. I'm going to change this to
> once every thirty minutes.
> Can I delete this ad hoc transaction log file backup, or do future backups
> depend on it?
> Thanks very much for your ideas on this. I just want to be safe!
> Many thanks!
> Ant|||Hi Hari,
Thanks fro the answer.
Best wishes
Ant
"Hari Prasad" wrote:
> Hello,
> If you have a FULL database backup taken after the specific trasnaction log
> backup you could delete the log backup file mentioned. Otherwise you need
> that specific log
> backup file for while recovery.
> Thanks
> Hari
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> > Hi,
> >
> > I backed up my transaction log in order to perform a point in time
> > restore.
> >
> > I backed it up to an ad hoc folder. The database actually does backup the
> > log file once a night to a different location. I'm going to change this to
> > once every thirty minutes.
> >
> > Can I delete this ad hoc transaction log file backup, or do future backups
> > depend on it?
> >
> > Thanks very much for your ideas on this. I just want to be safe!
> >
> > Many thanks!
> >
> > Ant
>
>

deleting a tx log backup

Hi,
I backed up my transaction log in order to perform a point in time restore.
I backed it up to an ad hoc folder. The database actually does backup the
log file once a night to a different location. I'm going to change this to
once every thirty minutes.
Can I delete this ad hoc transaction log file backup, or do future backups
depend on it?
Thanks very much for your ideas on this. I just want to be safe!
Many thanks!
Ant
Hello,
If you have a FULL database backup taken after the specific trasnaction log
backup you could delete the log backup file mentioned. Otherwise you need
that specific log
backup file for while recovery.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> Hi,
> I backed up my transaction log in order to perform a point in time
> restore.
> I backed it up to an ad hoc folder. The database actually does backup the
> log file once a night to a different location. I'm going to change this to
> once every thirty minutes.
> Can I delete this ad hoc transaction log file backup, or do future backups
> depend on it?
> Thanks very much for your ideas on this. I just want to be safe!
> Many thanks!
> Ant
|||Hi Hari,
Thanks fro the answer.
Best wishes
Ant
"Hari Prasad" wrote:

> Hello,
> If you have a FULL database backup taken after the specific trasnaction log
> backup you could delete the log backup file mentioned. Otherwise you need
> that specific log
> backup file for while recovery.
> Thanks
> Hari
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
>
>

deleting a tx log backup

Hi,
I backed up my transaction log in order to perform a point in time restore.
I backed it up to an ad hoc folder. The database actually does backup the
log file once a night to a different location. I'm going to change this to
once every thirty minutes.
Can I delete this ad hoc transaction log file backup, or do future backups
depend on it?
Thanks very much for your ideas on this. I just want to be safe!
Many thanks!
AntHello,
If you have a FULL database backup taken after the specific trasnaction log
backup you could delete the log backup file mentioned. Otherwise you need
that specific log
backup file for while recovery.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> Hi,
> I backed up my transaction log in order to perform a point in time
> restore.
> I backed it up to an ad hoc folder. The database actually does backup the
> log file once a night to a different location. I'm going to change this to
> once every thirty minutes.
> Can I delete this ad hoc transaction log file backup, or do future backups
> depend on it?
> Thanks very much for your ideas on this. I just want to be safe!
> Many thanks!
> Ant|||Hi Hari,
Thanks fro the answer.
Best wishes
Ant
"Hari Prasad" wrote:

> Hello,
> If you have a FULL database backup taken after the specific trasnaction lo
g
> backup you could delete the log backup file mentioned. Otherwise you need
> that specific log
> backup file for while recovery.
> Thanks
> Hari
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
>
>

deleting a replicated restore

I am running SQL 2K & Win 2K. I restored a backup, to a
different name, that was being replicated when it was
backed up. After the restore, I cannot detach the
backup, because SQL thinks it is being replicated. How
can I fix/detach this dB?
Larry,
have a look at sp_removedbreplication 'dbname' and sp_replicationdboption in
BOL.
Regards,
Paul Ibison
|||If sp_removedbreplication doesnt work you can update the category column in
sysdatabases to 0
Thanks
Gopal
|||If working inside the database, you may also have to manually set the
replinfo column in sysobjects to 0.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

deleted xp_cmdshell... how do I restore it?

For security reasons I dropped xp_cmdshell from my master database. Now
I find that I need it once again... is there a way to restore it or
somehow copy it from another database server? I've tried to use
enterprise manager to DTS it over but I can't DTS anything from the
master database. Is there any other way?
Thanks in advance.
Rsto create it back use the following and let know if it works:
Use Master
sp_addextendedproc xp_cmdshell, 'xplog70.dll'

deleted xp_cmdshell... how do I restore it?

For security reasons I dropped xp_cmdshell from my master database. Now
I find that I need it once again... is there a way to restore it or
somehow copy it from another database server? I've tried to use
enterprise manager to DTS it over but I can't DTS anything from the
master database. Is there any other way?
Thanks in advance.
Rsto create it back use the following and let know if it works:
Use Master
sp_addextendedproc xp_cmdshell, 'xplog70.dll'

Deleted my log file

I inadvertently deleted my log file, but still have my data file...is there
anyway to restore the database from the data file only?
ThanksSee if this helps:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Ooops" <anonymous@.discussions.microsoft.com> wrote in message
news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> I inadvertently deleted my log file, but still have my data file...is
there anyway to restore the database from the data file only?
> Thanks|||You can try sp_attach_single_file_db (see Books Online).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ooops" <anonymous@.discussions.microsoft.com> wrote in message
news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
>I inadvertently deleted my log file, but still have my data file...is there
>anyway to restore the database from the data file only?
> Thanks|||Neither one of these methods is working for me. Any ideas why not?
The second one (which is much more simple) looks like this:
EXEC sp_attach_single_file_db @.dbname = imBART,
@.physname = 'D:\MSSQL\Data\imBART_Data.MDF'
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'imBART'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\MSSQL\Data\imBART_Log.LD
F' may be incorrect.|||Hi
Have you tried to detach first the database and then run the Aaron's
solution?
"Oooops" <anonymous@.discussions.microsoft.com> wrote in message
news:DC865DE5-834B-4D4D-8D4E-3D68D0DF4478@.microsoft.com...
> Neither one of these methods is working for me. Any ideas why not?
> The second one (which is much more simple) looks like this:
> EXEC sp_attach_single_file_db @.dbname = imBART,
> @.physname = 'D:\MSSQL\Data\imBART_Data.MDF'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'imBART'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
'D:\MSSQL\Data\imBART_Log.LDF' may be incorrect.
>|||Hi,
Follow the steps defined below to recover the database:-
It seems your old database have mutiple LDF files. If you have mutilple LDF
files missed out you will not be able to use
the procedure sp_attach_single_file_db to attach the MDF file alone.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
(replace the dbname and log file name based on ur requirement)
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Ooops" <anonymous@.discussions.microsoft.com> wrote in message
news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> I inadvertently deleted my log file, but still have my data file...is
there anyway to restore the database from the data file only?
> Thanks|||Hari
I have not played with this situation (I mean that database have more than
one log file)
If you create a new database and then copy an old mdf file to the new
database location and then issue
sp_attach_db with those two log files. Will it be worked? Don't we need to
update as you suggested sysdatabase system table?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:euJg5srKEHA.2716@.tk2msftngp13.phx.gbl...
> Hi,
> Follow the steps defined below to recover the database:-
> It seems your old database have mutiple LDF files. If you have mutilple
LDF
> files missed out you will not be able to use
> the procedure sp_attach_single_file_db to attach the MDF file alone.
> A solution for this is:
> 1. Create a new database with the same name and same MDF and LDF files
> 2. Stop sql server and rename the existing MDF to a new one and copy the
> original MDF to this location and delete the LDF files.
> 3. Start SQL Server
> 4. Now your database will be marked suspect
> 5. Update the sysdatabases to update to Emergency mode. This will not use
> LOG files in start up
> update sysdatabases set status=32768 where name ='dbname'
> 6. Restart sql server. now the database will be in emergency mode
> 7. Now execute the undocumented DBCC to create a log file
> DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
> (replace the dbname and log file name based on ur requirement)
> 8. Execute sp_resetstatus <dbname>
> 9. Restart SQL server and see the database is online.
> Thanks
> Hari
> MCDBA
>
>
> "Ooops" <anonymous@.discussions.microsoft.com> wrote in message
> news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> there anyway to restore the database from the data file only?
>|||Neither operation are guaranteed to work except on a database that was
properly detached. The proper way to recover from something like this is to
restore from your last good backups. Without a backup these suggestions are
pretty much all you have although I haven't seen the one I posted fail
unless the file was corrupted. What is the error you get when trying the
first suggestion? You can always contact MS PSS if this doesn't work and
it's worth more than $250.00 or so. They may have some tricks up their
sleeve to help.
Andrew J. Kelly SQL MVP
"Oooops" <anonymous@.discussions.microsoft.com> wrote in message
news:DC865DE5-834B-4D4D-8D4E-3D68D0DF4478@.microsoft.com...
> Neither one of these methods is working for me. Any ideas why not?
> The second one (which is much more simple) looks like this:
> EXEC sp_attach_single_file_db @.dbname = imBART,
> @.physname = 'D:\MSSQL\Data\imBART_Data.MDF'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'imBART'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
'D:\MSSQL\Data\imBART_Log.LDF' may be incorrect.
>|||Hi Uri,
If we have more than 1 LDF file we will not be able to use
"sp_attach_single_file_db ". I have faced similar issue sime time back,
Myclient send only
1 MDF file not the LDF files. During that situation, I did the below 9 steps
to bring up the database.
If you create a new database and then copy an old mdf file to the new
database location and then issue sp_attach_db with those two log files.
Will it be worked?
I feel this may not work because of the log seqence number (LSN) change. The
LSN in the original file will be diffrent from the one created newly.
The DBCC REBULD_LOG command (mentioned earlier) will create a entirely new
log file with new LSN.
Thanks for the suggestion. I will try it out and get back tomorrow.
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#ukolLsKEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hari
> I have not played with this situation (I mean that database have more than
> one log file)
> If you create a new database and then copy an old mdf file to the new
> database location and then issue
> sp_attach_db with those two log files. Will it be worked? Don't we need to
> update as you suggested sysdatabase system table?
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:euJg5srKEHA.2716@.tk2msftngp13.phx.gbl...
> LDF
use[vbcol=seagreen]
>|||I don't have the error code right now, but I think that it was similar to th
e other error code about device activation...I would guess that the .mdf fil
e is corrupted.
I shall go back to my prior back-up and "Sin no more" ;-)
...a beginners mistake.
Thanks to everyone for your help.

Deleted my log file

I inadvertently deleted my log file, but still have my data file...is there anyway to restore the database from the data file only
ThanksSee if this helps:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
--
Andrew J. Kelly SQL MVP
"Ooops" <anonymous@.discussions.microsoft.com> wrote in message
news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> I inadvertently deleted my log file, but still have my data file...is
there anyway to restore the database from the data file only?
> Thanks|||You can try sp_attach_single_file_db (see Books Online).
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ooops" <anonymous@.discussions.microsoft.com> wrote in message
news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
>I inadvertently deleted my log file, but still have my data file...is there
>anyway to restore the database from the data file only?
> Thanks|||Neither one of these methods is working for me. Any ideas why not
The second one (which is much more simple) looks like this
EXEC sp_attach_single_file_db @.dbname = imBART,
@.physname = 'D:\MSSQL\Data\imBART_Data.MDF
Server: Msg 1813, Level 16, State 2, Line
Could not open new database 'imBART'. CREATE DATABASE is aborted
Device activation error. The physical file name 'D:\MSSQL\Data\imBART_Log.LDF' may be incorrect|||Hi
Have you tried to detach first the database and then run the Aaron's
solution?
"Oooops" <anonymous@.discussions.microsoft.com> wrote in message
news:DC865DE5-834B-4D4D-8D4E-3D68D0DF4478@.microsoft.com...
> Neither one of these methods is working for me. Any ideas why not?
> The second one (which is much more simple) looks like this:
> EXEC sp_attach_single_file_db @.dbname = imBART,
> @.physname = 'D:\MSSQL\Data\imBART_Data.MDF'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'imBART'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
'D:\MSSQL\Data\imBART_Log.LDF' may be incorrect.
>|||Hi,
Follow the steps defined below to recover the database:-
It seems your old database have mutiple LDF files. If you have mutilple LDF
files missed out you will not be able to use
the procedure sp_attach_single_file_db to attach the MDF file alone.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
(replace the dbname and log file name based on ur requirement)
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Ooops" <anonymous@.discussions.microsoft.com> wrote in message
news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> I inadvertently deleted my log file, but still have my data file...is
there anyway to restore the database from the data file only?
> Thanks|||Hari
I have not played with this situation (I mean that database have more than
one log file)
If you create a new database and then copy an old mdf file to the new
database location and then issue
sp_attach_db with those two log files. Will it be worked? Don't we need to
update as you suggested sysdatabase system table?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:euJg5srKEHA.2716@.tk2msftngp13.phx.gbl...
> Hi,
> Follow the steps defined below to recover the database:-
> It seems your old database have mutiple LDF files. If you have mutilple
LDF
> files missed out you will not be able to use
> the procedure sp_attach_single_file_db to attach the MDF file alone.
> A solution for this is:
> 1. Create a new database with the same name and same MDF and LDF files
> 2. Stop sql server and rename the existing MDF to a new one and copy the
> original MDF to this location and delete the LDF files.
> 3. Start SQL Server
> 4. Now your database will be marked suspect
> 5. Update the sysdatabases to update to Emergency mode. This will not use
> LOG files in start up
> update sysdatabases set status=32768 where name ='dbname'
> 6. Restart sql server. now the database will be in emergency mode
> 7. Now execute the undocumented DBCC to create a log file
> DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
> (replace the dbname and log file name based on ur requirement)
> 8. Execute sp_resetstatus <dbname>
> 9. Restart SQL server and see the database is online.
> Thanks
> Hari
> MCDBA
>
>
> "Ooops" <anonymous@.discussions.microsoft.com> wrote in message
> news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> > I inadvertently deleted my log file, but still have my data file...is
> there anyway to restore the database from the data file only?
> >
> > Thanks
>|||Neither operation are guaranteed to work except on a database that was
properly detached. The proper way to recover from something like this is to
restore from your last good backups. Without a backup these suggestions are
pretty much all you have although I haven't seen the one I posted fail
unless the file was corrupted. What is the error you get when trying the
first suggestion? You can always contact MS PSS if this doesn't work and
it's worth more than $250.00 or so. They may have some tricks up their
sleeve to help.
Andrew J. Kelly SQL MVP
"Oooops" <anonymous@.discussions.microsoft.com> wrote in message
news:DC865DE5-834B-4D4D-8D4E-3D68D0DF4478@.microsoft.com...
> Neither one of these methods is working for me. Any ideas why not?
> The second one (which is much more simple) looks like this:
> EXEC sp_attach_single_file_db @.dbname = imBART,
> @.physname = 'D:\MSSQL\Data\imBART_Data.MDF'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'imBART'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
'D:\MSSQL\Data\imBART_Log.LDF' may be incorrect.
>|||Hi Uri,
If we have more than 1 LDF file we will not be able to use
"sp_attach_single_file_db ". I have faced similar issue sime time back,
Myclient send only
1 MDF file not the LDF files. During that situation, I did the below 9 steps
to bring up the database.
If you create a new database and then copy an old mdf file to the new
database location and then issue sp_attach_db with those two log files.
Will it be worked?
I feel this may not work because of the log seqence number (LSN) change. The
LSN in the original file will be diffrent from the one created newly.
The DBCC REBULD_LOG command (mentioned earlier) will create a entirely new
log file with new LSN.
Thanks for the suggestion. I will try it out and get back tomorrow.
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#ukolLsKEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hari
> I have not played with this situation (I mean that database have more than
> one log file)
> If you create a new database and then copy an old mdf file to the new
> database location and then issue
> sp_attach_db with those two log files. Will it be worked? Don't we need to
> update as you suggested sysdatabase system table?
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:euJg5srKEHA.2716@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Follow the steps defined below to recover the database:-
> >
> > It seems your old database have mutiple LDF files. If you have mutilple
> LDF
> > files missed out you will not be able to use
> > the procedure sp_attach_single_file_db to attach the MDF file alone.
> >
> > A solution for this is:
> >
> > 1. Create a new database with the same name and same MDF and LDF files
> > 2. Stop sql server and rename the existing MDF to a new one and copy the
> > original MDF to this location and delete the LDF files.
> > 3. Start SQL Server
> > 4. Now your database will be marked suspect
> > 5. Update the sysdatabases to update to Emergency mode. This will not
use
> > LOG files in start up
> >
> > update sysdatabases set status=32768 where name ='dbname'
> >
> > 6. Restart sql server. now the database will be in emergency mode
> >
> > 7. Now execute the undocumented DBCC to create a log file
> >
> > DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
> >
> > (replace the dbname and log file name based on ur requirement)
> >
> > 8. Execute sp_resetstatus <dbname>
> >
> > 9. Restart SQL server and see the database is online.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> >
> > "Ooops" <anonymous@.discussions.microsoft.com> wrote in message
> > news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> > > I inadvertently deleted my log file, but still have my data file...is
> > there anyway to restore the database from the data file only?
> > >
> > > Thanks
> >
> >
>|||I don't have the error code right now, but I think that it was similar to the other error code about device activation...I would guess that the .mdf file is corrupted
I shall go back to my prior back-up and "Sin no more" ;-
...a beginners mistake
Thanks to everyone for your help.

Deleted my log file

I inadvertently deleted my log file, but still have my data file...is there anyway to restore the database from the data file only?
Thanks
See if this helps:
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Ooops" <anonymous@.discussions.microsoft.com> wrote in message
news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> I inadvertently deleted my log file, but still have my data file...is
there anyway to restore the database from the data file only?
> Thanks
|||You can try sp_attach_single_file_db (see Books Online).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ooops" <anonymous@.discussions.microsoft.com> wrote in message
news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
>I inadvertently deleted my log file, but still have my data file...is there
>anyway to restore the database from the data file only?
> Thanks
|||Neither one of these methods is working for me. Any ideas why not?
The second one (which is much more simple) looks like this:
EXEC sp_attach_single_file_db @.dbname = imBART,
@.physname = 'D:\MSSQL\Data\imBART_Data.MDF'
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'imBART'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\MSSQL\Data\imBART_Log.LDF' may be incorrect.
|||Hi
Have you tried to detach first the database and then run the Aaron's
solution?
"Oooops" <anonymous@.discussions.microsoft.com> wrote in message
news:DC865DE5-834B-4D4D-8D4E-3D68D0DF4478@.microsoft.com...
> Neither one of these methods is working for me. Any ideas why not?
> The second one (which is much more simple) looks like this:
> EXEC sp_attach_single_file_db @.dbname = imBART,
> @.physname = 'D:\MSSQL\Data\imBART_Data.MDF'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'imBART'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
'D:\MSSQL\Data\imBART_Log.LDF' may be incorrect.
>
|||Hi,
Follow the steps defined below to recover the database:-
It seems your old database have mutiple LDF files. If you have mutilple LDF
files missed out you will not be able to use
the procedure sp_attach_single_file_db to attach the MDF file alone.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
(replace the dbname and log file name based on ur requirement)
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Ooops" <anonymous@.discussions.microsoft.com> wrote in message
news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> I inadvertently deleted my log file, but still have my data file...is
there anyway to restore the database from the data file only?
> Thanks
|||Hari
I have not played with this situation (I mean that database have more than
one log file)
If you create a new database and then copy an old mdf file to the new
database location and then issue
sp_attach_db with those two log files. Will it be worked? Don't we need to
update as you suggested sysdatabase system table?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:euJg5srKEHA.2716@.tk2msftngp13.phx.gbl...
> Hi,
> Follow the steps defined below to recover the database:-
> It seems your old database have mutiple LDF files. If you have mutilple
LDF
> files missed out you will not be able to use
> the procedure sp_attach_single_file_db to attach the MDF file alone.
> A solution for this is:
> 1. Create a new database with the same name and same MDF and LDF files
> 2. Stop sql server and rename the existing MDF to a new one and copy the
> original MDF to this location and delete the LDF files.
> 3. Start SQL Server
> 4. Now your database will be marked suspect
> 5. Update the sysdatabases to update to Emergency mode. This will not use
> LOG files in start up
> update sysdatabases set status=32768 where name ='dbname'
> 6. Restart sql server. now the database will be in emergency mode
> 7. Now execute the undocumented DBCC to create a log file
> DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
> (replace the dbname and log file name based on ur requirement)
> 8. Execute sp_resetstatus <dbname>
> 9. Restart SQL server and see the database is online.
> Thanks
> Hari
> MCDBA
>
>
> "Ooops" <anonymous@.discussions.microsoft.com> wrote in message
> news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> there anyway to restore the database from the data file only?
>
|||Neither operation are guaranteed to work except on a database that was
properly detached. The proper way to recover from something like this is to
restore from your last good backups. Without a backup these suggestions are
pretty much all you have although I haven't seen the one I posted fail
unless the file was corrupted. What is the error you get when trying the
first suggestion? You can always contact MS PSS if this doesn't work and
it's worth more than $250.00 or so. They may have some tricks up their
sleeve to help.
Andrew J. Kelly SQL MVP
"Oooops" <anonymous@.discussions.microsoft.com> wrote in message
news:DC865DE5-834B-4D4D-8D4E-3D68D0DF4478@.microsoft.com...
> Neither one of these methods is working for me. Any ideas why not?
> The second one (which is much more simple) looks like this:
> EXEC sp_attach_single_file_db @.dbname = imBART,
> @.physname = 'D:\MSSQL\Data\imBART_Data.MDF'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'imBART'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
'D:\MSSQL\Data\imBART_Log.LDF' may be incorrect.
>
|||Hi Uri,
If we have more than 1 LDF file we will not be able to use
"sp_attach_single_file_db ". I have faced similar issue sime time back,
Myclient send only
1 MDF file not the LDF files. During that situation, I did the below 9 steps
to bring up the database.
If you create a new database and then copy an old mdf file to the new
database location and then issue sp_attach_db with those two log files.
Will it be worked?
I feel this may not work because of the log seqence number (LSN) change. The
LSN in the original file will be diffrent from the one created newly.
The DBCC REBULD_LOG command (mentioned earlier) will create a entirely new
log file with new LSN.
Thanks for the suggestion. I will try it out and get back tomorrow.
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#ukolLsKEHA.624@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Hari
> I have not played with this situation (I mean that database have more than
> one log file)
> If you create a new database and then copy an old mdf file to the new
> database location and then issue
> sp_attach_db with those two log files. Will it be worked? Don't we need to
> update as you suggested sysdatabase system table?
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:euJg5srKEHA.2716@.tk2msftngp13.phx.gbl...
> LDF
use
>
|||I don't have the error code right now, but I think that it was similar to the other error code about device activation...I would guess that the .mdf file is corrupted.
I shall go back to my prior back-up and "Sin no more" ;-)
...a beginners mistake.
Thanks to everyone for your help.

deleted mdf file want to restore from logfile?

I have deleted my mdf file by accident en need to restore the db from a logfile
the size of the logfile is 54gb.
I am totally desparate this was critical data, I tried to get the data from
the subscription server but this file is totally messed up , so I bought a
app to recover the data but it only managed bits and pieces.
My only hope now is to restore the data from the logfile but I have no idea
how or where to start. Would appriciate any helpDo you have full backup and transaction files up to the time that the
mdf was deleted or just the ldf? If not, I'm afraid you're going to
be out of luck.
On Sep 25, 9:00 am, jules <ju...@.discussions.microsoft.com> wrote:
> I have deleted my mdf file by accident en need to restore the db from a logfile
> the size of the logfile is 54gb.
> I am totally desparate this was critical data, I tried to get the data from
> the subscription server but this file is totally messed up , so I bought a
> app to recover the data but it only managed bits and pieces.
> My only hope now is to restore the data from the logfile but I have no idea
> how or where to start. Would appriciate any help|||In article <0A70FE38-9F46-4EBC-BFE3-9ABA3D9D1985@.microsoft.com>,
jules@.discussions.microsoft.com says...
> I have deleted my mdf file by accident en need to restore the db from a logfile
> the size of the logfile is 54gb.
> I am totally desparate this was critical data, I tried to get the data from
> the subscription server but this file is totally messed up , so I bought a
> app to recover the data but it only managed bits and pieces.
> My only hope now is to restore the data from the logfile but I have no idea
> how or where to start. Would appriciate any help
>
Might investigate Lumigent Logreader -- can restore data from a log
file. There may be other products as well
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||Get in touch with Brian Lockwood at ApexSQL. They have done this type of
disaster recovery before, although I am not sure recovery is possible
without the database file as well as the log file. apexsql.com
"jules" <jules@.discussions.microsoft.com> wrote in message
news:0A70FE38-9F46-4EBC-BFE3-9ABA3D9D1985@.microsoft.com...
>I have deleted my mdf file by accident en need to restore the db from a
>logfile
> the size of the logfile is 54gb.
> I am totally desparate this was critical data, I tried to get the data
> from
> the subscription server but this file is totally messed up , so I bought a
> app to recover the data but it only managed bits and pieces.
> My only hope now is to restore the data from the logfile but I have no
> idea
> how or where to start. Would appriciate any help

Deleted entire DB by accident!

Gentlemen, Can anyone save my life at the moment!
I need to restore a DB that I accidentally erased using
the Delete command in the Enterprise admin. I was on the
wrong server and the DB names are the same.
(No Backups were on this box since it's a dev box.)
Can anyone tell me a way to get my DB back?
Thanks,
BradTake your last production backup and restore it to your dev server.
Or were you fibbing and delete a production database you had no backups for?
"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in message
news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
> Gentlemen, Can anyone save my life at the moment!
> I need to restore a DB that I accidentally erased using
> the Delete command in the Enterprise admin. I was on the
> wrong server and the DB names are the same.
> (No Backups were on this box since it's a dev box.)
> Can anyone tell me a way to get my DB back?
> Thanks,
> Brad|||The concept of Dev in our dept is that it it's Dev before
it goes to production. So, it's never actually seen
production. There is no actual restore .BAK file and
the .MDF file is missing.
Any ideas?
>--Original Message--
>Take your last production backup and restore it to your
dev server.
>Or were you fibbing and delete a production database you
had no backups for?
>"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
message
>news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
>> Gentlemen, Can anyone save my life at the moment!
>> I need to restore a DB that I accidentally erased using
>> the Delete command in the Enterprise admin. I was on
the
>> wrong server and the DB names are the same.
>> (No Backups were on this box since it's a dev box.)
>> Can anyone tell me a way to get my DB back?
>> Thanks,
>> Brad
>
>.
>|||Turn off the sql server and start searching for a file restore utility (I
don't have one or know of one in particular) that will work on your OS. If
the filespace is overwritten you will lose your data.
Just in case, though, did you select to delete the filegroups? If not they
will still be there as .ndf, .mdf, .ldf files.
"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in message
news:2774501c38f69$d9323470$a601280a@.phx.gbl...
> The concept of Dev in our dept is that it it's Dev before
> it goes to production. So, it's never actually seen
> production. There is no actual restore .BAK file and
> the .MDF file is missing.
> Any ideas?
>
> >--Original Message--
> >Take your last production backup and restore it to your
> dev server.
> >
> >Or were you fibbing and delete a production database you
> had no backups for?
> >
> >"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
> message
> >news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
> >> Gentlemen, Can anyone save my life at the moment!
> >> I need to restore a DB that I accidentally erased using
> >> the Delete command in the Enterprise admin. I was on
> the
> >> wrong server and the DB names are the same.
> >> (No Backups were on this box since it's a dev box.)
> >> Can anyone tell me a way to get my DB back?
> >>
> >> Thanks,
> >>
> >> Brad
> >
> >
> >.
> >|||Thanks for the help, unfortunately I've been trying to
recover the MDF files with recovery utilities but so far
I've had no luck. Is it even possible to track down an
MDF file once it's been deleted with Enterprise Manager?
>--Original Message--
>Turn off the sql server and start searching for a file
restore utility (I
>don't have one or know of one in particular) that will
work on your OS. If
>the filespace is overwritten you will lose your data.
>Just in case, though, did you select to delete the
filegroups? If not they
>will still be there as .ndf, .mdf, .ldf files.
>
>
>
>"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
message
>news:2774501c38f69$d9323470$a601280a@.phx.gbl...
>> The concept of Dev in our dept is that it it's Dev
before
>> it goes to production. So, it's never actually seen
>> production. There is no actual restore .BAK file and
>> the .MDF file is missing.
>> Any ideas?
>>
>> >--Original Message--
>> >Take your last production backup and restore it to your
>> dev server.
>> >
>> >Or were you fibbing and delete a production database
you
>> had no backups for?
>> >
>> >"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
>> message
>> >news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
>> >> Gentlemen, Can anyone save my life at the moment!
>> >> I need to restore a DB that I accidentally erased
using
>> >> the Delete command in the Enterprise admin. I was on
>> the
>> >> wrong server and the DB names are the same.
>> >> (No Backups were on this box since it's a dev box.)
>> >> Can anyone tell me a way to get my DB back?
>> >>
>> >> Thanks,
>> >>
>> >> Brad
>> >
>> >
>> >.
>> >
>
>.
>|||Deleting with EM means that EM executes the DROP DATABASE command. For the DROP DATABASE command,
SQL Server will delete the file physically. Working on the OS level is your last resort. And, if
keeping the data from the dev environment is crucial, make sure you have backup for the dev
environment next time. :-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in message
news:036001c38f72$ef2e2b90$a401280a@.phx.gbl...
> Thanks for the help, unfortunately I've been trying to
> recover the MDF files with recovery utilities but so far
> I've had no luck. Is it even possible to track down an
> MDF file once it's been deleted with Enterprise Manager?
> >--Original Message--
> >Turn off the sql server and start searching for a file
> restore utility (I
> >don't have one or know of one in particular) that will
> work on your OS. If
> >the filespace is overwritten you will lose your data.
> >
> >Just in case, though, did you select to delete the
> filegroups? If not they
> >will still be there as .ndf, .mdf, .ldf files.
> >
> >
> >
> >
> >
> >
> >"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
> message
> >news:2774501c38f69$d9323470$a601280a@.phx.gbl...
> >> The concept of Dev in our dept is that it it's Dev
> before
> >> it goes to production. So, it's never actually seen
> >> production. There is no actual restore .BAK file and
> >> the .MDF file is missing.
> >>
> >> Any ideas?
> >>
> >>
> >> >--Original Message--
> >> >Take your last production backup and restore it to your
> >> dev server.
> >> >
> >> >Or were you fibbing and delete a production database
> you
> >> had no backups for?
> >> >
> >> >"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
> >> message
> >> >news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
> >> >> Gentlemen, Can anyone save my life at the moment!
> >> >> I need to restore a DB that I accidentally erased
> using
> >> >> the Delete command in the Enterprise admin. I was on
> >> the
> >> >> wrong server and the DB names are the same.
> >> >> (No Backups were on this box since it's a dev box.)
> >> >> Can anyone tell me a way to get my DB back?
> >> >>
> >> >> Thanks,
> >> >>
> >> >> Brad
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Deleted all data files of an instance

I have an instance where all of the data files (mdf and ldf) for every
database including master and msdb have been deleted. I need to restore
all databases from (native sql) backup, however I'm not sure how to
connect to the instance to perform the database restore.
The only way I can think of achieving this is to un-install the
instance, re-install the instance with the same name and then restore
my existing databases including master, msdb over the new ones. It's a
dev/test environment so not critical, but I'd appreciate any comments
on this strategy..
Regards,
David WestRead BOL for restore system databases.It will be good help to u.
from
Doller|||If somebody else deleted the .mdf and .ldf files by mistake first look for
them on the Recycle Bin :-)
If the master database is not available, SQL Server will not start. Read
'Rebuild Master utility' from BOL. Run rebuildm and follow the instructions.
After this you will have your system databases restored.
Start SQL Server in single user mode using sqlservr -m to restore the master
database from your backup.
Then you can restore the other databases.
Ben Nevarez
<davidawest@.gmail.com> wrote in message
news:1131941368.575648.226390@.g47g2000cwa.googlegroups.com...
>I have an instance where all of the data files (mdf and ldf) for every
> database including master and msdb have been deleted. I need to restore
> all databases from (native sql) backup, however I'm not sure how to
> connect to the instance to perform the database restore.
> The only way I can think of achieving this is to un-install the
> instance, re-install the instance with the same name and then restore
> my existing databases including master, msdb over the new ones. It's a
> dev/test environment so not critical, but I'd appreciate any comments
> on this strategy..
> Regards,
> David West
>|||Thanks Ben.
The 'Rebuild Master' utility wants the source directory containing data
files. How do I extract the mdf and ldf files from my sql backup? ie. a
native sql backup which is a single file.
David|||rebuildm asks for the SQL Server CD. On the CD browse to the data directory.
You will restore your master database backup (*.bak) later when starting SQL
Server in single user mode using sqlservr -m.
Ben Nevarez
<davidawest@.gmail.com> wrote in message
news:1132023984.137926.54610@.g43g2000cwa.googlegroups.com...
> Thanks Ben.
> The 'Rebuild Master' utility wants the source directory containing data
> files. How do I extract the mdf and ldf files from my sql backup? ie. a
> native sql backup which is a single file.
> David
>|||OK. That makes sense. Now I get the below error is cnfgsvr.out
###############################################################################
Starting Service ...
SQL_Latin1_General_CP1_CI_AS
-m -Q -T4022 -T3659
An error occurred while attempting to start the service (1460)
SQL Server configuration failed.
###############################################################################|||Worked it out. Files copied from cd were read-only. I copied the files
locally, reset read-only bit and rebuilt from local files. Thanks for
all your help!
Cheers,
David|||Hi,
You can give a try to Active@. undelete or Uneraser (for DOS) tool. IT
worked great for me and never failed before, so I suppose it might
really help.
http://www.active-undelete.com/
http://www.uneraser.com/|||Hello,
EASEUS DataRecoveryWizard utility can help. Speaking about me, it was
easily able to restore deleted, lost file and unformat drive,
so I think you will also find it quite useful. Really recommended
tool, give it a try.
http://www.easeus.com/
--
Good work, Good day.
"davidawest@.gmail.com" wrote:
> I have an instance where all of the data files (mdf and ldf) for every
> database including master and msdb have been deleted. I need to restore
> all databases from (native sql) backup, however I'm not sure how to
> connect to the instance to perform the database restore.
> The only way I can think of achieving this is to un-install the
> instance, re-install the instance with the same name and then restore
> my existing databases including master, msdb over the new ones. It's a
> dev/test environment so not critical, but I'd appreciate any comments
> on this strategy..
> Regards,
> David West
>

delete open connection on database

Hi,
Does anyone know how i can disconnect an user who has a open session
on a database?
The reason is i'd like to restore a database in a job but the job
always fails because of users which have an open session.
Is there a stored procedure which i can use before i use mit "restore
database"-statement?
Thx 4 Help,
Look up the kill command in Books Online, though using this frequently will
make you a very unpopular person.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"holzi" <holzi@.bluemail.ch> wrote in message
news:ded7aa56.0406282334.52797e51@.posting.google.c om...
> Hi,
> Does anyone know how i can disconnect an user who has a open session
> on a database?
> The reason is i'd like to restore a database in a job but the job
> always fails because of users which have an open session.
> Is there a stored procedure which i can use before i use mit "restore
> database"-statement?
> Thx 4 Help,
|||Look up the kill command in Books Online, though using this frequently will
make you a very unpopular person.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"holzi" <holzi@.bluemail.ch> wrote in message
news:ded7aa56.0406282334.52797e51@.posting.google.c om...
> Hi,
> Does anyone know how i can disconnect an user who has a open session
> on a database?
> The reason is i'd like to restore a database in a job but the job
> always fails because of users which have an open session.
> Is there a stored procedure which i can use before i use mit "restore
> database"-statement?
> Thx 4 Help,
|||holzi,
Why not put it into single user mode during the restore?
i.e.
ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
RESTORE mydb...
go
ALTER DATABASE mydb SET MULTI_USER
go
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
holzi wrote:
> Hi,
> Does anyone know how i can disconnect an user who has a open session
> on a database?
> The reason is i'd like to restore a database in a job but the job
> always fails because of users which have an open session.
> Is there a stored procedure which i can use before i use mit "restore
> database"-statement?
> Thx 4 Help,
|||holzi,
Why not put it into single user mode during the restore?
i.e.
ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
RESTORE mydb...
go
ALTER DATABASE mydb SET MULTI_USER
go
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
holzi wrote:
> Hi,
> Does anyone know how i can disconnect an user who has a open session
> on a database?
> The reason is i'd like to restore a database in a job but the job
> always fails because of users which have an open session.
> Is there a stored procedure which i can use before i use mit "restore
> database"-statement?
> Thx 4 Help,

delete open connection on database

Hi,
Does anyone know how i can disconnect an user who has a open session
on a database?
The reason is i'd like to restore a database in a job but the job
always fails because of users which have an open session.
Is there a stored procedure which i can use before i use mit "restore
database"-statement?
Thx 4 Help,Look up the kill command in Books Online, though using this frequently will
make you a very unpopular person.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"holzi" <holzi@.bluemail.ch> wrote in message
news:ded7aa56.0406282334.52797e51@.posting.google.com...
> Hi,
> Does anyone know how i can disconnect an user who has a open session
> on a database?
> The reason is i'd like to restore a database in a job but the job
> always fails because of users which have an open session.
> Is there a stored procedure which i can use before i use mit "restore
> database"-statement?
> Thx 4 Help,|||holzi,
Why not put it into single user mode during the restore?
i.e.
ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
RESTORE mydb...
go
ALTER DATABASE mydb SET MULTI_USER
go
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
holzi wrote:
> Hi,
> Does anyone know how i can disconnect an user who has a open session
> on a database?
> The reason is i'd like to restore a database in a job but the job
> always fails because of users which have an open session.
> Is there a stored procedure which i can use before i use mit "restore
> database"-statement?
> Thx 4 Help,

delete open connection on database

Hi,
Does anyone know how i can disconnect an user who has a open session
on a database?
The reason is i'd like to restore a database in a job but the job
always fails because of users which have an open session.
Is there a stored procedure which i can use before i use mit "restore
database"-statement?
Thx 4 Help,Look up the kill command in Books Online, though using this frequently will
make you a very unpopular person.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"holzi" <holzi@.bluemail.ch> wrote in message
news:ded7aa56.0406282334.52797e51@.posting.google.com...
> Hi,
> Does anyone know how i can disconnect an user who has a open session
> on a database?
> The reason is i'd like to restore a database in a job but the job
> always fails because of users which have an open session.
> Is there a stored procedure which i can use before i use mit "restore
> database"-statement?
> Thx 4 Help,|||holzi,
Why not put it into single user mode during the restore?
i.e.
ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
RESTORE mydb...
go
ALTER DATABASE mydb SET MULTI_USER
go
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
holzi wrote:
> Hi,
> Does anyone know how i can disconnect an user who has a open session
> on a database?
> The reason is i'd like to restore a database in a job but the job
> always fails because of users which have an open session.
> Is there a stored procedure which i can use before i use mit "restore
> database"-statement?
> Thx 4 Help,