DENY permission,SQL server: accidentalty

Showing posts with label accidentalty. Show all posts

Sunday, March 11, 2012

Deleted Transaction Logs!

I accidentalty deleted the transaction logs on a DB I had, and now I can get
it up on SQL enterprise, any ideas?
-GaryHi,
Try to attach the database with out the tranaction log using
sp_attach_single_file_db system stored procedure. This may not work since
your database is not detached.
Usage:
sp_attach_single_file_db 'dbname' , 'physical_name with the path of MDF
file'
If it fails restore the database from the last successful backup.
Thanks
Hari
MCDBA
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>|||Thx Hari,
I have replications running, and it won't let me detach, any ideas on how to
detach this?
-Gary
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OKQbWmMHEHA.3584@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Try to attach the database with out the tranaction log using
> sp_attach_single_file_db system stored procedure. This may not work since
> your database is not detached.
> Usage:
> sp_attach_single_file_db 'dbname' , 'physical_name with the path of MDF
> file'
> If it fails restore the database from the last successful backup.
> Thanks
> Hari
> MCDBA
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > I accidentalty deleted the transaction logs on a DB I had, and now I can
> get
> > it up on SQL enterprise, any ideas?
> >
> > -Gary
> >
> >
>|||is it safe to delete these files? I wont lose core data will I?
-Gary
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>|||and if I try to disable publishing, I get errors regarding the database
whose transaction logs are deleted.
-Gary
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uKy83nMHEHA.2924@.TK2MSFTNGP09.phx.gbl...
> Thx Hari,
> I have replications running, and it won't let me detach, any ideas on how
to
> detach this?
> -Gary
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OKQbWmMHEHA.3584@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > Try to attach the database with out the tranaction log using
> > sp_attach_single_file_db system stored procedure. This may not work
since
> > your database is not detached.
> >
> > Usage:
> >
> > sp_attach_single_file_db 'dbname' , 'physical_name with the path of
MDF
> > file'
> >
> > If it fails restore the database from the last successful backup.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> > news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > > I accidentalty deleted the transaction logs on a DB I had, and now I
can
> > get
> > > it up on SQL enterprise, any ideas?
> > >
> > > -Gary
> > >
> > >
> >
> >
>|||You will loose any uncomitted data and anything after the last CHECKPOINT,
do you have your recovery model full or simple? If its simple I think you
loose less.
You can also create a new database with the same name, stop sql service and
replace the data files with your original database and then start the
service again. If this didnt work set the database to emergency mode and
then try to copy objects using DTS to a new database.
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> is it safe to delete these files? I wont lose core data will I?
> -Gary
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > I accidentalty deleted the transaction logs on a DB I had, and now I can
> get
> > it up on SQL enterprise, any ideas?
> >
> > -Gary
> >
> >
>|||how can I go to emergency mode?
-Gary
"Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
news:%23jMoDfNHEHA.3356@.TK2MSFTNGP11.phx.gbl...
> You will loose any uncomitted data and anything after the last CHECKPOINT,
> do you have your recovery model full or simple? If its simple I think you
> loose less.
> You can also create a new database with the same name, stop sql service
and
> replace the data files with your original database and then start the
> service again. If this didnt work set the database to emergency mode and
> then try to copy objects using DTS to a new database.
>
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> > is it safe to delete these files? I wont lose core data will I?
> >
> > -Gary
> >
> > "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> > news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > > I accidentalty deleted the transaction logs on a DB I had, and now I
can
> > get
> > > it up on SQL enterprise, any ideas?
> > >
> > > -Gary
> > >
> > >
> >
> >
>|||UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uhpeyhNHEHA.2128@.TK2MSFTNGP11.phx.gbl...
> how can I go to emergency mode?
> -Gary
> "Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
> news:%23jMoDfNHEHA.3356@.TK2MSFTNGP11.phx.gbl...
> > You will loose any uncomitted data and anything after the last
CHECKPOINT,
> > do you have your recovery model full or simple? If its simple I think
you
> > loose less.
> >
> > You can also create a new database with the same name, stop sql service
> and
> > replace the data files with your original database and then start the
> > service again. If this didnt work set the database to emergency mode and
> > then try to copy objects using DTS to a new database.
> >
> >
> >
> > "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> > news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> > > is it safe to delete these files? I wont lose core data will I?
> > >
> > > -Gary
> > >
> > > "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> > > news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > > > I accidentalty deleted the transaction logs on a DB I had, and now I
> can
> > > get
> > > > it up on SQL enterprise, any ideas?
> > > >
> > > > -Gary
> > > >
> > > >
> > >
> > >
> >
> >
>|||You don't have any of the log files at all?
You're best bet is to open a call with PSS. Depending on the circumstances
PSS may be able to help you recover most of the data. There are a number of
things that you could do on your own, that are not documented. But,
honestly, the PSS call is pretty cheap and doing it on your own can be
tricky. I really think the PSS call is worth the price.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>|||Hi Gary,
Here is a technique suggested by Jasper Smith once. I
documented it for later use.
--
Here's a technique I have used in the past - you need to
change the file/database names as this was for a specific
case :
If you did not detach the database prior to copying it
then you will not be able to attach it easily (probably).
First thing to try is make sure the folder
'D:\DATA2K\MSSQL$SKINNER2K\DATA\ exists,if not
then create it,copy your MDF there and retry your statement
If that doesn't work then you're in a bit of trouble but
you can try this
1) Make sure you have a copy of PowerDVD301_2_Data.MDF
2) Create a new database called fake (default file
locations)
3) Stop SQL Service
4) Delete the fake_Data.MDF and copy PowerDVD301_2_Data.MDF
to where fake_Data.MDF used to be and rename the file
to fake_Data.MDF
5) Start SQL Service
6) Database fake will appear as suspect in EM
7) Open Query Analyser and in master database run the
following :
sp_configure 'allow updates',1
go
reconfigure with override
go
update sysdatabases set
status=-32768 where dbid=DB_ID('fake')
go
sp_configure 'allow updates',0
go
reconfigure with override
go
This will put the database in emergency recovery mode
8) Stop SQL Service
9) Delete the fake_Log.LDF file
10) Restart SQL Service
11) In QA run the following (with correct path for log)
dbcc rebuild_log('fake','h:\fake_log.ldf')
go
dbcc checkdb('fake') -- to check for errors
go
12) Now we need to rename the files, run the following
(make sure
there are no connections to it) in Query Analyser
(At this stage you can actually access the database so you
could use
DTS or bcp to move the data to another database .)
use master
go
sp_helpdb 'fake'
go
/* Make a note of the names of the files , you will need
them
in the next bit of the script to replace datafilename and
logfilename - it might be that they have the right names
*/
sp_renamedb 'fake','PowerDVD301'
go
alter database PowerDVD301
MODIFY FILE(NAME='datafilename', NEWNAME
= 'PowerDVD301_Data')
go
alter database PowerDVD301
MODIFY FILE(NAME='logfilename', NEWNAME
= 'PowerDVD301_Log')
go
dbcc checkdb('PowerDVD301')
go
sp_dboption 'PowerDVD301','dbo use only','false'
go
use PowerDVD301
go
sp_updatestats
go
13) You should now have a working database. However the
log file
will be small so it will be worth increasing its
size
Unfortunately your files will be called
fake_Data.MDF and
fake_Log.LDF but you can get round this by
detaching the
database properly and then renaming the files and
reattaching
it
14) Run the following in QA
sp_detach_db PowerDVD301
--now rename the files then reattach
sp_attach_db 'PowerDVD301','h:\dvd.mdf','h:\DVD.ldf'
Regards
Thirumal|||thanks for the help!
-Gary
"Thirumal" <treddym@.hotmail.nospam.com> wrote in message
news:19c8701c41d14$d4ea68e0$a501280a@.phx.gbl...
> Hi Gary,
> Here is a technique suggested by Jasper Smith once. I
> documented it for later use.
> --
> Here's a technique I have used in the past - you need to
> change the file/database names as this was for a specific
> case :
> If you did not detach the database prior to copying it
> then you will not be able to attach it easily (probably).
> First thing to try is make sure the folder
> 'D:\DATA2K\MSSQL$SKINNER2K\DATA\ exists,if not
> then create it,copy your MDF there and retry your statement
> If that doesn't work then you're in a bit of trouble but
> you can try this
> 1) Make sure you have a copy of PowerDVD301_2_Data.MDF
> 2) Create a new database called fake (default file
> locations)
> 3) Stop SQL Service
> 4) Delete the fake_Data.MDF and copy PowerDVD301_2_Data.MDF
> to where fake_Data.MDF used to be and rename the file
> to fake_Data.MDF
> 5) Start SQL Service
> 6) Database fake will appear as suspect in EM
> 7) Open Query Analyser and in master database run the
> following :
> sp_configure 'allow updates',1
> go
> reconfigure with override
> go
> update sysdatabases set
> status=-32768 where dbid=DB_ID('fake')
> go
> sp_configure 'allow updates',0
> go
> reconfigure with override
> go
> This will put the database in emergency recovery mode
> 8) Stop SQL Service
> 9) Delete the fake_Log.LDF file
> 10) Restart SQL Service
> 11) In QA run the following (with correct path for log)
> dbcc rebuild_log('fake','h:\fake_log.ldf')
> go
> dbcc checkdb('fake') -- to check for errors
> go
> 12) Now we need to rename the files, run the following
> (make sure
> there are no connections to it) in Query Analyser
> (At this stage you can actually access the database so you
> could use
> DTS or bcp to move the data to another database .)
> use master
> go
> sp_helpdb 'fake'
> go
> /* Make a note of the names of the files , you will need
> them
> in the next bit of the script to replace datafilename and
> logfilename - it might be that they have the right names
> */
> sp_renamedb 'fake','PowerDVD301'
> go
> alter database PowerDVD301
> MODIFY FILE(NAME='datafilename', NEWNAME
> = 'PowerDVD301_Data')
> go
> alter database PowerDVD301
> MODIFY FILE(NAME='logfilename', NEWNAME
> = 'PowerDVD301_Log')
> go
> dbcc checkdb('PowerDVD301')
> go
> sp_dboption 'PowerDVD301','dbo use only','false'
> go
> use PowerDVD301
> go
> sp_updatestats
> go
> 13) You should now have a working database. However the
> log file
> will be small so it will be worth increasing its
> size
> Unfortunately your files will be called
> fake_Data.MDF and
> fake_Log.LDF but you can get round this by
> detaching the
> database properly and then renaming the files and
> reattaching
> it
> 14) Run the following in QA
> sp_detach_db PowerDVD301
>
> --now rename the files then reattach
> sp_attach_db 'PowerDVD301','h:\dvd.mdf','h:\DVD.ldf'
>
> Regards
> Thirumal

Sunday, March 11, 2012

Deleted Transaction Logs!

Deleted Transaction Logs!

Deleted Transaction Logs!

DENY permission,SQL server

Blog Archive

About Me