Friday, March 30, 2012
How to attach db with missing log file?
While moving the db to another server, a drive array onthe
first one went bad, taking one of my log files with it.
How can I re-attach the db and have it ignore that its
missing a log file?
Thank you in advance!Hi
You may be able to use sp_attach_single_file_db, although this would not be
guaranteed.
John
"Merlin" <anonymous@.discussions.microsoft.com> wrote in message
news:bc7201c40dfc$f3419e20$a001280a@.phx.gbl...
> I jad detached a larg db with multiple empty log files.
> While moving the db to another server, a drive array onthe
> first one went bad, taking one of my log files with it.
> How can I re-attach the db and have it ignore that its
> missing a log file?
> Thank you in advance!|||Thank you for your help. sp_attach_single_file_db is not
allowing me to get past the missing log file, either. is
there any other way?
>--Original Message--
>Hi
>You may be able to use sp_attach_single_file_db, although
this would not be
>guaranteed.
>John
>"Merlin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:bc7201c40dfc$f3419e20$a001280a@.phx.gbl...
onthe
>
>.
>|||Hi
Not that I know off. You may want to call Microsoft PSS if going back to the
last backup is not a viable option.
John
"Merlin" <anonymous@.discussions.microsoft.com> wrote in message
news:d87301c40e01$69052ec0$a601280a@.phx.gbl...
> Thank you for your help. sp_attach_single_file_db is not
> allowing me to get past the missing log file, either. is
> there any other way?
>
>
> this would not be
> message
> onthe|||Not supported etc and make sure you have a copy of the mdf before starting
this. Also replace the relavent database,drive letters and filenames with
your particulars as this answer was for a specific case.
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'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Merlin" <anonymous@.discussions.microsoft.com> wrote in message
news:d87301c40e01$69052ec0$a601280a@.phx.gbl...
> Thank you for your help. sp_attach_single_file_db is not
> allowing me to get past the missing log file, either. is
> there any other way?
>
>
> this would not be
> message
> onthe|||I fixed this by running dbcc rebuild_log and then running
dbcc checkdb. all is good now. sp_attach_single_file_db
didnt work for me, it was still looking for the missing
log file before letting me attach the db. Thank you for
the help, I appreciated it!!!
>--Original Message--
>Hi
>You may be able to use sp_attach_single_file_db, although
this would not be
>guaranteed.
>John
>"Merlin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:bc7201c40dfc$f3419e20$a001280a@.phx.gbl...
onthe
>
>.
>sql
Wednesday, March 21, 2012
How to add two integer columns in a select query?
select myintcol1+myintcol2 as sumcol from...
Thanks!
Nevermind, it does work like that, I had a typo!!
How to add two integer columns in a select query?
select myintcol1+myintcol2 as sumcol from...
Thanks!Nevermind, it does work like that, I had a typo!!sql
How to add two integer columns in a select query?
select myintcol1+myintcol2 as sumcol from...
Thanks!Nevermind, it does work like that, I had a typo!!
Wednesday, March 7, 2012
How to Add a Missing Index to a Table in Merge Replication?
index. Strangely, only the table in one of the subscriber of the merge
replication is missing the index; another subscriber and the publisher
of the merge replication don't have this problem.
How should I add the missing index back to that table? My understanding
is that making structural change on a table that is in
merge-replication is different from making change on a table that is
not merge-replicated. For example, when we need to add a column into a
table that is being merge-replicated, we must add the column by
changing the attributes in the properties of the published article
(table) instead of simply using CREATE INDEX command. I am wondering
whether there is a similar restriction on adding an index onto a table
that is merge replicated.
I have already added the index back to the table anyway. I am asking
here just in case doing this may get me into a problem later on.
Thanks in advance for any info.
Jay Chan<jaykchan@.hotmail.com> wrote in message
news:1106577312.674228.283880@.z14g2000cwz.googlegr oups.com...
> One of the table that is in a merge replication somehow is missing an
> index. Strangely, only the table in one of the subscriber of the merge
> replication is missing the index; another subscriber and the publisher
> of the merge replication don't have this problem.
> How should I add the missing index back to that table? My understanding
> is that making structural change on a table that is in
> merge-replication is different from making change on a table that is
> not merge-replicated. For example, when we need to add a column into a
> table that is being merge-replicated, we must add the column by
> changing the attributes in the properties of the published article
> (table) instead of simply using CREATE INDEX command. I am wondering
> whether there is a similar restriction on adding an index onto a table
> that is merge replicated.
> I have already added the index back to the table anyway. I am asking
> here just in case doing this may get me into a problem later on.
> Thanks in advance for any info.
> Jay Chan
I seem to remember that MSSQL only replicates indexes as part of an initial
snapshot, and after that they have to be maintained manually, so what you've
done would be fine. But I must admit that I'm not at all sure about the
details, so you might want to post in microsoft.public.sqlserver.replication
and see if you get a better answer.
Simon|||> I seem to remember that MSSQL only replicates indexes as part of an
initial
> snapshot, and after that they have to be maintained manually, so what
you've
> done would be fine. But I must admit that I'm not at all sure about
the
> details, so you might want to post in
microsoft.public.sqlserver.replication
> and see if you get a better answer.
Thanks for the confirmation. In any case, I cannot find a way to add an
index through the attributes of the replication publication anyway. I
am sure I am on the right track.
Jay Chan|||> I seem to remember that MSSQL only replicates indexes as part of an
initial
> snapshot, and after that they have to be maintained manually, so what
you've
> done would be fine. But I must admit that I'm not at all sure about
the
> details, so you might want to post in
microsoft.public.sqlserver.replication
> and see if you get a better answer.
Thanks for the confirmation. In any case, I cannot find a way to add an
index through the attributes of the replication publication anyway. I
am sure I am on the right track.
Jay Chan