Moving transaction log file

Hi folks,
Currently I have data file and transaction log file on the same drive. I
want to move the transaction log file to a different drive without any
outage to the database. What is the best way to do this.
Thanks in advance.
*** Sent via Developersdex http://www.examnotes.net ***You will have to detach the database first , then to separate a mdf and ldf
file and re-attach the database
Pls refer to the BOL for more details
sp_detach ,sp_attach stored procedures
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:eLi8zCTBGHA.2356@.tk2msftngp13.phx.gbl...
> Hi folks,
> Currently I have data file and transaction log file on the same drive. I
> want to move the transaction log file to a different drive without any
> outage to the database. What is the best way to do this.
> Thanks in advance.
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||http://support.microsoft.com/defaul...kb;en-us;224071
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:eLi8zCTBGHA.2356@.tk2msftngp13.phx.gbl...
> Hi folks,
> Currently I have data file and transaction log file on the same drive. I
> want to move the transaction log file to a different drive without any
> outage to the database. What is the best way to do this.
> Thanks in advance.
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||Hi,
Detach and Attach will have some outage to the database. I am looking
for an option which does not invlove any outage to the database.
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***|||No such option. You could create another log file on the other drive, meanin
g for a while you have
two log files. Then use DBCC LOGINFO etc to watch and see when your original
log file is unused by
virtual log file and try DBCC SHRINKFILE with the empty file option to final
ly remove the original
log file using ALTER DATABASE. I'm not sure whether SQL Server allow you to
remove the very first
log file that a database had, though, so make sure you first try on a test s
ystem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:%23TdysOTBGHA.3488@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Detach and Attach will have some outage to the database. I am looking
> for an option which does not invlove any outage to the database.
> Thanks.
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||Tibor wrote:
* Use DBCC SHRINKFILE with the empty file option to finally remove the
original
* log file using ALTER DATABASE. I'm not sure whether SQL Server allow
you to remove the very first
* log file that a database had, though, so make sure you first try on a
test system.
No, SQL Server won't allow you to remove the primary transaction
logfile. Tried it just last w, you'll get the following error:
Server: Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database.
Markus|||Last time I tried to move log file to other disk I forgot to set database
offline and it went corrupted. I couldn't recover database, fortunately it
was only a test one.
BOL states clearly that you have to set db offline before mooving files:
1.. Run ALTER DATABASE database_name SET OFFLINE.
2.. Move the file to the new location.
3.. Run ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name,
FILENAME = 'new_path/os_file_name'.
4.. Run ALTER DATABASE database_name SET ONLINE.
Peter|||Another option is to backup database and then restore with "move" option.
Before backing up, set "read only" option on your database and remove "read
only" after you did a restore. May this will be a minimal outage on your
database.
Ramunas Balukonis
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Ockc1BUBGHA.3400@.TK2MSFTNGP10.phx.gbl...
> No such option. You could create another log file on the other drive,
meaning for a while you have
> two log files. Then use DBCC LOGINFO etc to watch and see when your
original log file is unused by
> virtual log file and try DBCC SHRINKFILE with the empty file option to
finally remove the original
> log file using ALTER DATABASE. I'm not sure whether SQL Server allow you
to remove the very first
> log file that a database had, though, so make sure you first try on a test
system.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
> news:%23TdysOTBGHA.3488@.TK2MSFTNGP10.phx.gbl...
>

moving replication to new servers

I've got a publisher, distributor and subscriber on different servers and I
want to move all 3 to different servers. Is there a way to restore the
publishing database
on the new publisher, the subscribing database on the new subscriber and
then continue replication? knowing, of course, that I'd have to set up the
new distributor as well.
I know I can create the new publication, backup the database and restore
that on the new subscriber and initialize replication backup, but the
databases are fairly large and if I can restore both the publisher and the
subscriber at the same time, it would save much time. I need to minimize
down time.
Thanks!
There is no real way to do this. What Microsoft does support is restoring
master, distribution, msdb, and the publication database on a new server,
restoring the last tlog of the publication database on the new server with
the keep_replication switch, taking the first server offline, rebooting the
standby after renaming it as the original publisher name, and then issuing a
sp_replrestart to get everything working again.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Steve" <Steve.sam@.hotmail.com> wrote in message
news:eb2nTSzaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> I've got a publisher, distributor and subscriber on different servers and
> I want to move all 3 to different servers. Is there a way to restore the
> publishing database
> on the new publisher, the subscribing database on the new subscriber and
> then continue replication? knowing, of course, that I'd have to set up
> the new distributor as well.
> I know I can create the new publication, backup the database and restore
> that on the new subscriber and initialize replication backup, but the
> databases are fairly large and if I can restore both the publisher and the
> subscriber at the same time, it would save much time. I need to minimize
> down time.
> Thanks!
>

Moving publisher db to another server. (any steps on how to do this without reinitializing

I will be doing this all via EM since I don't know much about sql. I
want to move a db that is being published on one server to another. I
will configure them both to use the same distributor so I am pretty sure
this is possible.
tia
-comb
Will the other server have the same name? If not, then unless you are going
to hack the entries in replication system tables, you have to reinitialize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <uhQ7aK1wFHA.3720@.TK2MSFTNGP11.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> Will the other server have the same name? If not, then unless you are going
> to hack the entries in replication system tables, you have to reinitialize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
no it will be moving from instance vsql13/ep01 to vsql22/ep03. Can, I
not just move the db over and check or uncheck the box that says
"subscriber already has schema/data"?
Thanks for the response paul!
-comb
|||Comb,
ah - I misunderstood you . I was thinking you were talking about taking
the publication as well. If you're recreating the publication, then doing a
no-sync initialization should be ok. Still, I'd script the stored procs,
remove the remnants of replication on the subscriber first using
sp_removedbreplication, then script back in the stored procs.
Cheers,
Paul
|||In article <ugiD#T4wFHA.2232@.TK2MSFTNGP11.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> Comb,
> ah - I misunderstood you . I was thinking you were talking about taking
> the publication as well. If you're recreating the publication, then doing a
> no-sync initialization should be ok. Still, I'd script the stored procs,
> remove the remnants of replication on the subscriber first using
> sp_removedbreplication, then script back in the stored procs.
> Cheers,
> Paul
>
>
Man you are talking to a network guy forced into a sql enviroment
because i can spell sql. hehe. Do you have instructions on how to do
this on your website? http://www.replicationanswers.com
I will use BOL to see if i can figure out how to run
sp_removedbreplication.
Effectivly what I am doing is what you are describing. I will be
dropping replication from the current db. Detatching that db from one
server and move it to another server, and then recreating the
publications. I can do this the 1 way like you said where I script out
the pubs before I delete them. Then edit what I need to in those
scripts and run them on the new server. However, I would have to know
where in the sql script to tell replication that the subscriber already
has everything. I do not know sql well enough (at all really) to do
this.
Will doing it in the gui (EM) give me the same result? I have seen like
two area's where it looked like an option not to do a new snapshot. One
place is under where you choose with table you are going to choose for a
certain publication. In there there is a little ... box to click and i
think inside there you can choose "subscriber already has data". Then
also I think some where in the wizard you can choose something to the
same effect. like the 2nd picture in this article on your site.
http://www.replicationanswers.com/No...alizations.asp
Thanks for all your help.
-comb
|||Comb,
you're definitely almost there - it's the option "No, subscriber has already
has the schema and data" that you'll need (nosync initialization).
( http://www.replicationanswers.com/No...alizations.asp)
Cheers,
Paul Ibison
|||In article <ehh0wy6wFHA.3892@.TK2MSFTNGP12.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> Comb,
> you're definitely almost there - it's the option "No, subscriber has already
> has the schema and data" that you'll need (nosync initialization).
> ( http://www.replicationanswers.com/No...alizations.asp)
> Cheers,
> Paul Ibison
>
>
ok cool thanks for the responses as usual. Wish me luck!
|||You won't need it - it's easy
Cheers,
Paul
"combfilter" <asdf@.adsf.com> wrote in message
news:MPG.1da4cc05bcaa7b979896bf@.news.newsreader.co m...
> In article <ehh0wy6wFHA.3892@.TK2MSFTNGP12.phx.gbl>,
> Paul.Ibison@.Pygmalion.Com says...
> ok cool thanks for the responses as usual. Wish me luck!
>