a database restore technique question...

background sql2k on nt4.
one of the disk stripe went bad during the weekend and our network admin
rebuilt the box and reinstalled sql2k w/ sp2 but he didnt' check what was
installed origionally. his restore is like this:
1. origional sql excutables and database files are located at d:\mssql\, he
renamed it to d:\mssql_x\
2. he reinstalled sql2k and system databases to d:\mssql\, after that he was
able to start the sql service again.
3. he then renamed d:\mssql\ to d:\mssql_y\, and renamed d:\mssql_x\ back to
d:\mssql\
but the sql service wouldn't start. he said that tenique worked a couple of
times at other occasions.
my questions:
1. what might go wrong this time?
2. what's wrong with this technique, there is no backup or restore involved.
almost sounds too easy to be true.
thank you.What he should have done was copy over the old database files to the new
folder. It appears that perhaps the old executables got corrupted.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:%234$J1ViJEHA.1392@.TK2MSFTNGP09.phx.gbl...
background sql2k on nt4.
one of the disk stripe went bad during the weekend and our network admin
rebuilt the box and reinstalled sql2k w/ sp2 but he didnt' check what was
installed origionally. his restore is like this:
1. origional sql excutables and database files are located at d:\mssql\, he
renamed it to d:\mssql_x\
2. he reinstalled sql2k and system databases to d:\mssql\, after that he was
able to start the sql service again.
3. he then renamed d:\mssql\ to d:\mssql_y\, and renamed d:\mssql_x\ back to
d:\mssql\
but the sql service wouldn't start. he said that tenique worked a couple of
times at other occasions.
my questions:
1. what might go wrong this time?
2. what's wrong with this technique, there is no backup or restore involved.
almost sounds too easy to be true.
thank you.|||"...copy over the old database files to the new..."
does that include system files such as master, msdb and so on?
if that's the case, wouldn't you need to 'attach' all the databases?
his tenique simple renamed the original directory to the new directory.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#cXyfpiJEHA.2412@.TK2MSFTNGP12.phx.gbl...
> What he should have done was copy over the old database files to the new
> folder. It appears that perhaps the old executables got corrupted.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:%234$J1ViJEHA.1392@.TK2MSFTNGP09.phx.gbl...
> background sql2k on nt4.
> one of the disk stripe went bad during the weekend and our network admin
> rebuilt the box and reinstalled sql2k w/ sp2 but he didnt' check what was
> installed origionally. his restore is like this:
> 1. origional sql excutables and database files are located at d:\mssql\,
he
> renamed it to d:\mssql_x\
> 2. he reinstalled sql2k and system databases to d:\mssql\, after that he
was
> able to start the sql service again.
> 3. he then renamed d:\mssql\ to d:\mssql_y\, and renamed d:\mssql_x\ back
to
> d:\mssql\
> but the sql service wouldn't start. he said that tenique worked a couple
of
> times at other occasions.
> my questions:
> 1. what might go wrong this time?
> 2. what's wrong with this technique, there is no backup or restore
involved.
> almost sounds too easy to be true.
> thank you.
>|||For everything to be in synch, then yes, you would have to move all
databases - assuming none were corrupted. Simply renaming the directory
would make it point to all of the old stuff - including the executables. If
the original SQL Server had been shut down properly, you wouldn't need to
attach anything.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:OsUOquiJEHA.228@.TK2MSFTNGP10.phx.gbl...
"...copy over the old database files to the new..."
does that include system files such as master, msdb and so on?
if that's the case, wouldn't you need to 'attach' all the databases?
his tenique simple renamed the original directory to the new directory.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#cXyfpiJEHA.2412@.TK2MSFTNGP12.phx.gbl...
> What he should have done was copy over the old database files to the new
> folder. It appears that perhaps the old executables got corrupted.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:%234$J1ViJEHA.1392@.TK2MSFTNGP09.phx.gbl...
> background sql2k on nt4.
> one of the disk stripe went bad during the weekend and our network admin
> rebuilt the box and reinstalled sql2k w/ sp2 but he didnt' check what was
> installed origionally. his restore is like this:
> 1. origional sql excutables and database files are located at d:\mssql\,
he
> renamed it to d:\mssql_x\
> 2. he reinstalled sql2k and system databases to d:\mssql\, after that he
was
> able to start the sql service again.
> 3. he then renamed d:\mssql\ to d:\mssql_y\, and renamed d:\mssql_x\ back
to
> d:\mssql\
> but the sql service wouldn't start. he said that tenique worked a couple
of
> times at other occasions.
> my questions:
> 1. what might go wrong this time?
> 2. what's wrong with this technique, there is no backup or restore
involved.
> almost sounds too easy to be true.
> thank you.
>

A couple LDF questions.

I do a bit a sql programming but do not handle the admin side. Our
server backups are becoming huge so I looked around to see why. A
couple of our databases (small... ~60Megs) have ldf files that are
almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
shows that there are almost 200 virtual logs in each ldf file, all
with active transactions. One of these databases is still attached but
hasn't been used for over a year. How can I determine more information
about these transactions, or force old ones to close? Our datacenter
handles the actual scheduled backups so I don't really want to mess
with too much but i'm hoping there's a way for me to shrink the log
size to something more 'appropriate' without losing any necessary
information. Is this something that they (datacenter) should handle
(they don't manage our databases, just the backups) or is this likely
to be a software issue not closing the transactions so they persist?
Any thoughts or comments greatly appreciated.
Thanks.
Mark<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
>
My guess is that they are NOT handling backups correctly. Sounds like they
may not be doing a transaction log backup.
Try DBCC opentran on each database. This should return the SPIDs of any
open transactions.
dbcc inputbuffer(spid)
and
sp_who2 spid
to find out what they are doing.
You might be able to kill the spids.

> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||As Greg mentioned, you have probably missed doing log backups for the databa
se. If the db is in full
recovery mode, and you don't do log backups, then the log file is never "emp
tied". Or, in other
words, the virtual log file will have a status of 2. If you don't want to do
log backups, you should
set the database to simple recovery mode.
I also want to point out that status 2 is not the same as open transactions.
It just mean that the
virtual log file cannot be re-used, because you haven't done a log backup ye
t.
Some more info at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>|||many thanks to both. that helps. i'll try the code to learn more about
the spid's and will call the datacenter to see what the backup
settings are. thanks again.

A couple LDF questions.

I do a bit a sql programming but do not handle the admin side. Our
server backups are becoming huge so I looked around to see why. A
couple of our databases (small... ~60Megs) have ldf files that are
almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
shows that there are almost 200 virtual logs in each ldf file, all
with active transactions. One of these databases is still attached but
hasn't been used for over a year. How can I determine more information
about these transactions, or force old ones to close? Our datacenter
handles the actual scheduled backups so I don't really want to mess
with too much but i'm hoping there's a way for me to shrink the log
size to something more 'appropriate' without losing any necessary
information. Is this something that they (datacenter) should handle
(they don't manage our databases, just the backups) or is this likely
to be a software issue not closing the transactions so they persist?
Any thoughts or comments greatly appreciated.
Thanks.
Mark
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegr oups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
>
My guess is that they are NOT handling backups correctly. Sounds like they
may not be doing a transaction log backup.
Try DBCC opentran on each database. This should return the SPIDs of any
open transactions.
dbcc inputbuffer(spid)
and
sp_who2 spid
to find out what they are doing.
You might be able to kill the spids.

> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||As Greg mentioned, you have probably missed doing log backups for the database. If the db is in full
recovery mode, and you don't do log backups, then the log file is never "emptied". Or, in other
words, the virtual log file will have a status of 2. If you don't want to do log backups, you should
set the database to simple recovery mode.
I also want to point out that status 2 is not the same as open transactions. It just mean that the
virtual log file cannot be re-used, because you haven't done a log backup yet.
Some more info at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegr oups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
|||many thanks to both. that helps. i'll try the code to learn more about
the spid's and will call the datacenter to see what the backup
settings are. thanks again.

A couple LDF questions.

I do a bit a sql programming but do not handle the admin side. Our
server backups are becoming huge so I looked around to see why. A
couple of our databases (small... ~60Megs) have ldf files that are
almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
shows that there are almost 200 virtual logs in each ldf file, all
with active transactions. One of these databases is still attached but
hasn't been used for over a year. How can I determine more information
about these transactions, or force old ones to close? Our datacenter
handles the actual scheduled backups so I don't really want to mess
with too much but i'm hoping there's a way for me to shrink the log
size to something more 'appropriate' without losing any necessary
information. Is this something that they (datacenter) should handle
(they don't manage our databases, just the backups) or is this likely
to be a software issue not closing the transactions so they persist?
Any thoughts or comments greatly appreciated.
Thanks.
Mark<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
>
My guess is that they are NOT handling backups correctly. Sounds like they
may not be doing a transaction log backup.
Try DBCC opentran on each database. This should return the SPIDs of any
open transactions.
dbcc inputbuffer(spid)
and
sp_who2 spid
to find out what they are doing.
You might be able to kill the spids.
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||As Greg mentioned, you have probably missed doing log backups for the database. If the db is in full
recovery mode, and you don't do log backups, then the log file is never "emptied". Or, in other
words, the virtual log file will have a status of 2. If you don't want to do log backups, you should
set the database to simple recovery mode.
I also want to point out that status 2 is not the same as open transactions. It just mean that the
virtual log file cannot be re-used, because you haven't done a log backup yet.
Some more info at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>|||many thanks to both. that helps. i'll try the code to learn more about
the spid's and will call the datacenter to see what the backup
settings are. thanks again.