I've created an index defrag script, based on a set of criteria it will
choose whether to do a reorganise or rebuild (online or offline) for a
selected index.. ..I've set that when it does a rebuild to set the recovery
model to bulk logged.. ..I set the recovery model just before the rebuild and
revert just after, I've since been thinking if it needs to 100 rebuilds it
will switch the recovery model 100 times for the duration of each rebuild..
...would this be bad practice?
Ben
What is your current recover model? Is it FULL? Do you perform BACKUP LOG
operation?
Have you noticed a big difference in terms of performance if you run it on
FULL or BULK LOGGED recover model?
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:5E7989D2-89AD-4AD1-9753-FB8BCE8BFD5F@.microsoft.com...
> I've created an index defrag script, based on a set of criteria it will
> choose whether to do a reorganise or rebuild (online or offline) for a
> selected index.. ..I've set that when it does a rebuild to set the
> recovery
> model to bulk logged.. ..I set the recovery model just before the rebuild
> and
> revert just after, I've since been thinking if it needs to 100 rebuilds it
> will switch the recovery model 100 times for the duration of each
> rebuild..
> ..would this be bad practice?
|||Current recovery model is Full, yes we do back up the logs.. ..this is to be
used on multiple servers with multiple databases so the frequency of log
backups can very greatly, the indexes this would be rebuilding are
potentially very large so I'm trying to avoid hammering the log in between
the log backups...
"Uri Dimant" wrote:
> Ben
> What is your current recover model? Is it FULL? Do you perform BACKUP LOG
> operation?
> Have you noticed a big difference in terms of performance if you run it on
> FULL or BULK LOGGED recover model?
>
>
> "Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
> news:5E7989D2-89AD-4AD1-9753-FB8BCE8BFD5F@.microsoft.com...
>
>
|||Ben
Well , if you have enuogh free space , I don't think you need to switch
between FULL and BULL LOGGED as well as i have not seem such performance
issues.
However , can you rebuild your indexes at night for example where workload
is low.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:5AF71BFA-7B0A-4692-B227-AB66F44442AE@.microsoft.com...[vbcol=seagreen]
> Current recovery model is Full, yes we do back up the logs.. ..this is to
> be
> used on multiple servers with multiple databases so the frequency of log
> backups can very greatly, the indexes this would be rebuilding are
> potentially very large so I'm trying to avoid hammering the log in between
> the log backups...
> "Uri Dimant" wrote:
|||Ben UK wrote:
> I've created an index defrag script, based on a set of criteria it will
> choose whether to do a reorganise or rebuild (online or offline) for a
> selected index.. ..I've set that when it does a rebuild to set the recovery
> model to bulk logged.. ..I set the recovery model just before the rebuild and
> revert just after, I've since been thinking if it needs to 100 rebuilds it
> will switch the recovery model 100 times for the duration of each rebuild..
> ..would this be bad practice?
I wouldn't say it's "bad practice". I would, however, be concerned
about the possibility of some bulk operation being performed while you
have the recovery model switched. In other words, if you're automating
this switch between Full and Bulk-Logged, how will you control or
prevent some other process from doing something non-logged? A scheduled
maintenance window seems like a much "safer" way of managing reindexes.
Just my 2-cents.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||That makes sense, thanks for the input Tracy
"Tracy McKibben" wrote:
> Ben UK wrote:
> I wouldn't say it's "bad practice". I would, however, be concerned
> about the possibility of some bulk operation being performed while you
> have the recovery model switched. In other words, if you're automating
> this switch between Full and Bulk-Logged, how will you control or
> prevent some other process from doing something non-logged? A scheduled
> maintenance window seems like a much "safer" way of managing reindexes.
> Just my 2-cents.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts
Wednesday, March 28, 2012
Wednesday, March 21, 2012
Recovering data from a mdf file
Hi,
This is a very long shot. I'm not an sql expert by any means but I have a question that I've been told to ask - someone may be able to answer, even if the answer is "no".
A sql table has been emptied of all rows. There is no backup of the .mdf prior to this happening. No transaction logging is in place. The server on which the .mdf resides has not been re-booted since the table was emptied. The .mdf has not been re-organis
ed.
Is there any way that the data from that table can be recovered? Does it still physically exist in the .mdf, albeit in a marked for deletion form?
Thanks & regards,
Paul
Paul Burton
Manchester,
England
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
From my point of view the answer is: no
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> a crit dans le message de
news:uNUw4LuTEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Hi,
> This is a very long shot. I'm not an sql expert by any means but I have a
question that I've been told to ask - someone may be able to answer, even if
the answer is "no".
> A sql table has been emptied of all rows. There is no backup of the .mdf
prior to this happening. No transaction logging is in place. The server on
which the .mdf resides has not been re-booted since the table was emptied.
The .mdf has not been re-organised.
> Is there any way that the data from that table can be recovered? Does it
still physically exist in the .mdf, albeit in a marked for deletion form?
> Thanks & regards,
> Paul
> Paul Burton
> Manchester,
> England
>
>
>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.
|||You don't have the ldf file anywhere?
-John Oakes
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:uNUw4LuTEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Hi,
> This is a very long shot. I'm not an sql expert by any means but I have a
question that I've been told to ask - someone may be able to answer, even if
the answer is "no".
> A sql table has been emptied of all rows. There is no backup of the .mdf
prior to this happening. No transaction logging is in place. The server on
which the .mdf resides has not been re-booted since the table was emptied.
The .mdf has not been re-organised.
> Is there any way that the data from that table can be recovered? Does it
still physically exist in the .mdf, albeit in a marked for deletion form?
> Thanks & regards,
> Paul
> Paul Burton
> Manchester,
> England
>
>
>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.
sql
This is a very long shot. I'm not an sql expert by any means but I have a question that I've been told to ask - someone may be able to answer, even if the answer is "no".
A sql table has been emptied of all rows. There is no backup of the .mdf prior to this happening. No transaction logging is in place. The server on which the .mdf resides has not been re-booted since the table was emptied. The .mdf has not been re-organis
ed.
Is there any way that the data from that table can be recovered? Does it still physically exist in the .mdf, albeit in a marked for deletion form?
Thanks & regards,
Paul
Paul Burton
Manchester,
England
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
From my point of view the answer is: no
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> a crit dans le message de
news:uNUw4LuTEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Hi,
> This is a very long shot. I'm not an sql expert by any means but I have a
question that I've been told to ask - someone may be able to answer, even if
the answer is "no".
> A sql table has been emptied of all rows. There is no backup of the .mdf
prior to this happening. No transaction logging is in place. The server on
which the .mdf resides has not been re-booted since the table was emptied.
The .mdf has not been re-organised.
> Is there any way that the data from that table can be recovered? Does it
still physically exist in the .mdf, albeit in a marked for deletion form?
> Thanks & regards,
> Paul
> Paul Burton
> Manchester,
> England
>
>
>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.
|||You don't have the ldf file anywhere?
-John Oakes
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:uNUw4LuTEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Hi,
> This is a very long shot. I'm not an sql expert by any means but I have a
question that I've been told to ask - someone may be able to answer, even if
the answer is "no".
> A sql table has been emptied of all rows. There is no backup of the .mdf
prior to this happening. No transaction logging is in place. The server on
which the .mdf resides has not been re-booted since the table was emptied.
The .mdf has not been re-organised.
> Is there any way that the data from that table can be recovered? Does it
still physically exist in the .mdf, albeit in a marked for deletion form?
> Thanks & regards,
> Paul
> Paul Burton
> Manchester,
> England
>
>
>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.
sql
Recovering (local) server and Restoring a Dabase from a clients backup
Hi all,
I'm running sql server 2000.
I've a client who has given me a large database to develope against.
I don't have a (local) server anymore. Can't remember why.
Whats the easiest way to recover (local) - I'm happy to reinstall and start
from a fresh (local).
Also, it looks as though the client has backed up their db.
Am I able to restore to my (local) from their backup?
Thanks,
baz
Yes you can restore it. You don't need to restore it locally unless you
want to. I would just place the backup some place where your development
server can see it, and then use EM to restore from the database backup.
Make sure you type in the name of the database you want to create from the
restore, so you don't overlay some other database.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
news:ev7qjfVXEHA.2964@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I'm running sql server 2000.
> I've a client who has given me a large database to develope against.
> I don't have a (local) server anymore. Can't remember why.
> Whats the easiest way to recover (local) - I'm happy to reinstall and
start
> from a fresh (local).
> Also, it looks as though the client has backed up their db.
> Am I able to restore to my (local) from their backup?
> Thanks,
> baz
>
>
|||Yes you can restore it. You don't need to restore it locally unless you
want to. I would just place the backup some place where your development
server can see it, and then use EM to restore from the database backup.
Make sure you type in the name of the database you want to create from the
restore, so you don't overlay some other database.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
news:ev7qjfVXEHA.2964@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I'm running sql server 2000.
> I've a client who has given me a large database to develope against.
> I don't have a (local) server anymore. Can't remember why.
> Whats the easiest way to recover (local) - I'm happy to reinstall and
start
> from a fresh (local).
> Also, it looks as though the client has backed up their db.
> Am I able to restore to my (local) from their backup?
> Thanks,
> baz
>
>
|||Hi Greg,
Thanks for the quick reply.
Thats great news.
I'm trying to imulate the client setup as closely as possible.
I'm modifying legacy VB 6 with ODBC connections thru a DSN (yuck!)
And running legacy Crystal Reports 8.5 thru the same DSN (yucker!)
So, I do want a (local) server.
Anyone have any ideas on how to reinstall or recover the (local) server?
I reinstalled sql server last week but the (local) didn't re-appear.
And it looks like I have to have a database name already there to be able to
recover to it.
Any suggestions?
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:#AuHurVXEHA.3972@.TK2MSFTNGP12.phx.gbl...
> Yes you can restore it. You don't need to restore it locally unless you
> want to. I would just place the backup some place where your development
> server can see it, and then use EM to restore from the database backup.
> Make sure you type in the name of the database you want to create from the
> restore, so you don't overlay some other database.
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
> news:ev7qjfVXEHA.2964@.TK2MSFTNGP09.phx.gbl...
> start
>
|||Hi Greg,
Thanks for the quick reply.
Thats great news.
I'm trying to imulate the client setup as closely as possible.
I'm modifying legacy VB 6 with ODBC connections thru a DSN (yuck!)
And running legacy Crystal Reports 8.5 thru the same DSN (yucker!)
So, I do want a (local) server.
Anyone have any ideas on how to reinstall or recover the (local) server?
I reinstalled sql server last week but the (local) didn't re-appear.
And it looks like I have to have a database name already there to be able to
recover to it.
Any suggestions?
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:#AuHurVXEHA.3972@.TK2MSFTNGP12.phx.gbl...
> Yes you can restore it. You don't need to restore it locally unless you
> want to. I would just place the backup some place where your development
> server can see it, and then use EM to restore from the database backup.
> Make sure you type in the name of the database you want to create from the
> restore, so you don't overlay some other database.
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
> news:ev7qjfVXEHA.2964@.TK2MSFTNGP09.phx.gbl...
> start
>
|||Attempting to sort out the Database name.
Just added a new database with the same name as the clients.
And used the All Task | Restore Database.
3.5 gigs available on the H: drive where I have the backed up database.
H: is where the .mdf resides as well.
3.1 gigs available of the c: drive
The backup itself is 734 megs.
All drives are NTFS
I'm getting this error:
Microsoft SQL-DMO (ODBC SQLState: 42000)
Modify File encountered operating system error 112(There is not enought
space on the disk.) while attempting to expand the physical file.
Could not adjust the space allocation for file 'x-Log'.
Restore Datbase is terminating abnormally.
Thanks,
baz
"Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
news:uvMji9VXEHA.2572@.TK2MSFTNGP11.phx.gbl...
> Hi Greg,
> Thanks for the quick reply.
> Thats great news.
> --
> I'm trying to imulate the client setup as closely as possible.
> I'm modifying legacy VB 6 with ODBC connections thru a DSN (yuck!)
> And running legacy Crystal Reports 8.5 thru the same DSN (yucker!)
> So, I do want a (local) server.
> Anyone have any ideas on how to reinstall or recover the (local) server?
> I reinstalled sql server last week but the (local) didn't re-appear.
>
> And it looks like I have to have a database name already there to be able
to[vbcol=seagreen]
> recover to it.
> Any suggestions?
>
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:#AuHurVXEHA.3972@.TK2MSFTNGP12.phx.gbl...
development[vbcol=seagreen]
the
> ----
> --
> ----
> --
>
|||Attempting to sort out the Database name.
Just added a new database with the same name as the clients.
And used the All Task | Restore Database.
3.5 gigs available on the H: drive where I have the backed up database.
H: is where the .mdf resides as well.
3.1 gigs available of the c: drive
The backup itself is 734 megs.
All drives are NTFS
I'm getting this error:
Microsoft SQL-DMO (ODBC SQLState: 42000)
Modify File encountered operating system error 112(There is not enought
space on the disk.) while attempting to expand the physical file.
Could not adjust the space allocation for file 'x-Log'.
Restore Datbase is terminating abnormally.
Thanks,
baz
"Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
news:uvMji9VXEHA.2572@.TK2MSFTNGP11.phx.gbl...
> Hi Greg,
> Thanks for the quick reply.
> Thats great news.
> --
> I'm trying to imulate the client setup as closely as possible.
> I'm modifying legacy VB 6 with ODBC connections thru a DSN (yuck!)
> And running legacy Crystal Reports 8.5 thru the same DSN (yucker!)
> So, I do want a (local) server.
> Anyone have any ideas on how to reinstall or recover the (local) server?
> I reinstalled sql server last week but the (local) didn't re-appear.
>
> And it looks like I have to have a database name already there to be able
to[vbcol=seagreen]
> recover to it.
> Any suggestions?
>
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:#AuHurVXEHA.3972@.TK2MSFTNGP12.phx.gbl...
development[vbcol=seagreen]
the
> ----
> --
> ----
> --
>
|||Make sure you are restoring the data and log files to a valid drive on your
local server. When your customer made the backup, the paths where the data
files resided on the customer's server are also recorded. When you now
restore the backup, SQL Server will by default try to restore the files to
the same location. You may need to change these paths. Since you are using
Enterprise Manager, look in the Options tab in the Restore database dialog.
If using Query Analyzer, look up the RESTORE FILELISTONLY .. and RESTORE
DATABASE ... WITH MOVE ... syntax.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
news:%23mPKjQWXEHA.736@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Attempting to sort out the Database name.
> Just added a new database with the same name as the clients.
> And used the All Task | Restore Database.
> 3.5 gigs available on the H: drive where I have the backed up database.
> H: is where the .mdf resides as well.
> 3.1 gigs available of the c: drive
> The backup itself is 734 megs.
> All drives are NTFS
> I'm getting this error:
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> Modify File encountered operating system error 112(There is not enought
> space on the disk.) while attempting to expand the physical file.
> Could not adjust the space allocation for file 'x-Log'.
> Restore Datbase is terminating abnormally.
> Thanks,
> baz
> "Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
> news:uvMji9VXEHA.2572@.TK2MSFTNGP11.phx.gbl...
able[vbcol=seagreen]
> to
you[vbcol=seagreen]
> development
backup.[vbcol=seagreen]
> the
> ----
> ----
and
>
|||Make sure you are restoring the data and log files to a valid drive on your
local server. When your customer made the backup, the paths where the data
files resided on the customer's server are also recorded. When you now
restore the backup, SQL Server will by default try to restore the files to
the same location. You may need to change these paths. Since you are using
Enterprise Manager, look in the Options tab in the Restore database dialog.
If using Query Analyzer, look up the RESTORE FILELISTONLY .. and RESTORE
DATABASE ... WITH MOVE ... syntax.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
news:%23mPKjQWXEHA.736@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Attempting to sort out the Database name.
> Just added a new database with the same name as the clients.
> And used the All Task | Restore Database.
> 3.5 gigs available on the H: drive where I have the backed up database.
> H: is where the .mdf resides as well.
> 3.1 gigs available of the c: drive
> The backup itself is 734 megs.
> All drives are NTFS
> I'm getting this error:
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> Modify File encountered operating system error 112(There is not enought
> space on the disk.) while attempting to expand the physical file.
> Could not adjust the space allocation for file 'x-Log'.
> Restore Datbase is terminating abnormally.
> Thanks,
> baz
> "Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
> news:uvMji9VXEHA.2572@.TK2MSFTNGP11.phx.gbl...
able[vbcol=seagreen]
> to
you[vbcol=seagreen]
> development
backup.[vbcol=seagreen]
> the
> ----
> ----
and
>
|||Hi Peter,
Thanks for the reply.
You have to have a database already setup on your server to restore to.
So my database was only 1 k .
Ten after the failed attempt it is was 750megs.
Had another look thru google.
Looks like some others were having the same prob.
Theirs turned out to be a severely defragmented hard drive.
I've had a look thru mine with DiskKeeper.
Mine is also severely defragmented as well.
I've been defraggin all day but I can't get DiskKeeper to continue defraggin
on its own
I have to keep starting it and it only defrage 50 frags at a time? Who
knows ...
I'll update a followup when I have some news...
I'm running sql server 2000.
I've a client who has given me a large database to develope against.
I don't have a (local) server anymore. Can't remember why.
Whats the easiest way to recover (local) - I'm happy to reinstall and start
from a fresh (local).
Also, it looks as though the client has backed up their db.
Am I able to restore to my (local) from their backup?
Thanks,
baz
Yes you can restore it. You don't need to restore it locally unless you
want to. I would just place the backup some place where your development
server can see it, and then use EM to restore from the database backup.
Make sure you type in the name of the database you want to create from the
restore, so you don't overlay some other database.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
news:ev7qjfVXEHA.2964@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I'm running sql server 2000.
> I've a client who has given me a large database to develope against.
> I don't have a (local) server anymore. Can't remember why.
> Whats the easiest way to recover (local) - I'm happy to reinstall and
start
> from a fresh (local).
> Also, it looks as though the client has backed up their db.
> Am I able to restore to my (local) from their backup?
> Thanks,
> baz
>
>
|||Yes you can restore it. You don't need to restore it locally unless you
want to. I would just place the backup some place where your development
server can see it, and then use EM to restore from the database backup.
Make sure you type in the name of the database you want to create from the
restore, so you don't overlay some other database.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
news:ev7qjfVXEHA.2964@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I'm running sql server 2000.
> I've a client who has given me a large database to develope against.
> I don't have a (local) server anymore. Can't remember why.
> Whats the easiest way to recover (local) - I'm happy to reinstall and
start
> from a fresh (local).
> Also, it looks as though the client has backed up their db.
> Am I able to restore to my (local) from their backup?
> Thanks,
> baz
>
>
|||Hi Greg,
Thanks for the quick reply.
Thats great news.
I'm trying to imulate the client setup as closely as possible.
I'm modifying legacy VB 6 with ODBC connections thru a DSN (yuck!)
And running legacy Crystal Reports 8.5 thru the same DSN (yucker!)
So, I do want a (local) server.
Anyone have any ideas on how to reinstall or recover the (local) server?
I reinstalled sql server last week but the (local) didn't re-appear.
And it looks like I have to have a database name already there to be able to
recover to it.
Any suggestions?
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:#AuHurVXEHA.3972@.TK2MSFTNGP12.phx.gbl...
> Yes you can restore it. You don't need to restore it locally unless you
> want to. I would just place the backup some place where your development
> server can see it, and then use EM to restore from the database backup.
> Make sure you type in the name of the database you want to create from the
> restore, so you don't overlay some other database.
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
> news:ev7qjfVXEHA.2964@.TK2MSFTNGP09.phx.gbl...
> start
>
|||Hi Greg,
Thanks for the quick reply.
Thats great news.
I'm trying to imulate the client setup as closely as possible.
I'm modifying legacy VB 6 with ODBC connections thru a DSN (yuck!)
And running legacy Crystal Reports 8.5 thru the same DSN (yucker!)
So, I do want a (local) server.
Anyone have any ideas on how to reinstall or recover the (local) server?
I reinstalled sql server last week but the (local) didn't re-appear.
And it looks like I have to have a database name already there to be able to
recover to it.
Any suggestions?
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:#AuHurVXEHA.3972@.TK2MSFTNGP12.phx.gbl...
> Yes you can restore it. You don't need to restore it locally unless you
> want to. I would just place the backup some place where your development
> server can see it, and then use EM to restore from the database backup.
> Make sure you type in the name of the database you want to create from the
> restore, so you don't overlay some other database.
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
> news:ev7qjfVXEHA.2964@.TK2MSFTNGP09.phx.gbl...
> start
>
|||Attempting to sort out the Database name.
Just added a new database with the same name as the clients.
And used the All Task | Restore Database.
3.5 gigs available on the H: drive where I have the backed up database.
H: is where the .mdf resides as well.
3.1 gigs available of the c: drive
The backup itself is 734 megs.
All drives are NTFS
I'm getting this error:
Microsoft SQL-DMO (ODBC SQLState: 42000)
Modify File encountered operating system error 112(There is not enought
space on the disk.) while attempting to expand the physical file.
Could not adjust the space allocation for file 'x-Log'.
Restore Datbase is terminating abnormally.
Thanks,
baz
"Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
news:uvMji9VXEHA.2572@.TK2MSFTNGP11.phx.gbl...
> Hi Greg,
> Thanks for the quick reply.
> Thats great news.
> --
> I'm trying to imulate the client setup as closely as possible.
> I'm modifying legacy VB 6 with ODBC connections thru a DSN (yuck!)
> And running legacy Crystal Reports 8.5 thru the same DSN (yucker!)
> So, I do want a (local) server.
> Anyone have any ideas on how to reinstall or recover the (local) server?
> I reinstalled sql server last week but the (local) didn't re-appear.
>
> And it looks like I have to have a database name already there to be able
to[vbcol=seagreen]
> recover to it.
> Any suggestions?
>
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:#AuHurVXEHA.3972@.TK2MSFTNGP12.phx.gbl...
development[vbcol=seagreen]
the
> ----
> --
> ----
> --
>
|||Attempting to sort out the Database name.
Just added a new database with the same name as the clients.
And used the All Task | Restore Database.
3.5 gigs available on the H: drive where I have the backed up database.
H: is where the .mdf resides as well.
3.1 gigs available of the c: drive
The backup itself is 734 megs.
All drives are NTFS
I'm getting this error:
Microsoft SQL-DMO (ODBC SQLState: 42000)
Modify File encountered operating system error 112(There is not enought
space on the disk.) while attempting to expand the physical file.
Could not adjust the space allocation for file 'x-Log'.
Restore Datbase is terminating abnormally.
Thanks,
baz
"Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
news:uvMji9VXEHA.2572@.TK2MSFTNGP11.phx.gbl...
> Hi Greg,
> Thanks for the quick reply.
> Thats great news.
> --
> I'm trying to imulate the client setup as closely as possible.
> I'm modifying legacy VB 6 with ODBC connections thru a DSN (yuck!)
> And running legacy Crystal Reports 8.5 thru the same DSN (yucker!)
> So, I do want a (local) server.
> Anyone have any ideas on how to reinstall or recover the (local) server?
> I reinstalled sql server last week but the (local) didn't re-appear.
>
> And it looks like I have to have a database name already there to be able
to[vbcol=seagreen]
> recover to it.
> Any suggestions?
>
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:#AuHurVXEHA.3972@.TK2MSFTNGP12.phx.gbl...
development[vbcol=seagreen]
the
> ----
> --
> ----
> --
>
|||Make sure you are restoring the data and log files to a valid drive on your
local server. When your customer made the backup, the paths where the data
files resided on the customer's server are also recorded. When you now
restore the backup, SQL Server will by default try to restore the files to
the same location. You may need to change these paths. Since you are using
Enterprise Manager, look in the Options tab in the Restore database dialog.
If using Query Analyzer, look up the RESTORE FILELISTONLY .. and RESTORE
DATABASE ... WITH MOVE ... syntax.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
news:%23mPKjQWXEHA.736@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Attempting to sort out the Database name.
> Just added a new database with the same name as the clients.
> And used the All Task | Restore Database.
> 3.5 gigs available on the H: drive where I have the backed up database.
> H: is where the .mdf resides as well.
> 3.1 gigs available of the c: drive
> The backup itself is 734 megs.
> All drives are NTFS
> I'm getting this error:
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> Modify File encountered operating system error 112(There is not enought
> space on the disk.) while attempting to expand the physical file.
> Could not adjust the space allocation for file 'x-Log'.
> Restore Datbase is terminating abnormally.
> Thanks,
> baz
> "Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
> news:uvMji9VXEHA.2572@.TK2MSFTNGP11.phx.gbl...
able[vbcol=seagreen]
> to
you[vbcol=seagreen]
> development
backup.[vbcol=seagreen]
> the
> ----
> ----
and
>
|||Make sure you are restoring the data and log files to a valid drive on your
local server. When your customer made the backup, the paths where the data
files resided on the customer's server are also recorded. When you now
restore the backup, SQL Server will by default try to restore the files to
the same location. You may need to change these paths. Since you are using
Enterprise Manager, look in the Options tab in the Restore database dialog.
If using Query Analyzer, look up the RESTORE FILELISTONLY .. and RESTORE
DATABASE ... WITH MOVE ... syntax.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
news:%23mPKjQWXEHA.736@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Attempting to sort out the Database name.
> Just added a new database with the same name as the clients.
> And used the All Task | Restore Database.
> 3.5 gigs available on the H: drive where I have the backed up database.
> H: is where the .mdf resides as well.
> 3.1 gigs available of the c: drive
> The backup itself is 734 megs.
> All drives are NTFS
> I'm getting this error:
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> Modify File encountered operating system error 112(There is not enought
> space on the disk.) while attempting to expand the physical file.
> Could not adjust the space allocation for file 'x-Log'.
> Restore Datbase is terminating abnormally.
> Thanks,
> baz
> "Barry G. Sumpter" <barrysum@.bigpond.net.au> wrote in message
> news:uvMji9VXEHA.2572@.TK2MSFTNGP11.phx.gbl...
able[vbcol=seagreen]
> to
you[vbcol=seagreen]
> development
backup.[vbcol=seagreen]
> the
> ----
> ----
and
>
|||Hi Peter,
Thanks for the reply.
You have to have a database already setup on your server to restore to.
So my database was only 1 k .
Ten after the failed attempt it is was 750megs.
Had another look thru google.
Looks like some others were having the same prob.
Theirs turned out to be a severely defragmented hard drive.
I've had a look thru mine with DiskKeeper.
Mine is also severely defragmented as well.
I've been defraggin all day but I can't get DiskKeeper to continue defraggin
on its own
I have to keep starting it and it only defrage 50 frags at a time? Who
knows ...
I'll update a followup when I have some news...
Friday, March 9, 2012
Recover Analysis Server from crash recovery
I've server where I recover...\Data and ...\Bin\*.mdb. from backup tape. My
server came on-line with no error, i've not detect lost data, but, I don't
know if AS is now all fonctional or i need some configurations for finish
recover. The books on-line from Microsoft site have not information about
crash recovery.
Please can you tell me if i need some other restore files or other
configurations on AS ?
Thanks.
Sylvain H.
You don't need anything else...
Bye
Francesco Anti
"SHuet" <SHuet@.discussions.microsoft.com> ha scritto nel messaggio
news:CEAE3231-6CCA-421B-AF82-D255ADFB3EA5@.microsoft.com...
> I've server where I recover...\Data and ...\Bin\*.mdb. from backup tape.
> My
> server came on-line with no error, i've not detect lost data, but, I don't
> know if AS is now all fonctional or i need some configurations for finish
> recover. The books on-line from Microsoft site have not information about
> crash recovery.
> Please can you tell me if i need some other restore files or other
> configurations on AS ?
> Thanks.
> Sylvain H.
>
server came on-line with no error, i've not detect lost data, but, I don't
know if AS is now all fonctional or i need some configurations for finish
recover. The books on-line from Microsoft site have not information about
crash recovery.
Please can you tell me if i need some other restore files or other
configurations on AS ?
Thanks.
Sylvain H.
You don't need anything else...
Bye
Francesco Anti
"SHuet" <SHuet@.discussions.microsoft.com> ha scritto nel messaggio
news:CEAE3231-6CCA-421B-AF82-D255ADFB3EA5@.microsoft.com...
> I've server where I recover...\Data and ...\Bin\*.mdb. from backup tape.
> My
> server came on-line with no error, i've not detect lost data, but, I don't
> know if AS is now all fonctional or i need some configurations for finish
> recover. The books on-line from Microsoft site have not information about
> crash recovery.
> Please can you tell me if i need some other restore files or other
> configurations on AS ?
> Thanks.
> Sylvain H.
>
Wednesday, March 7, 2012
recover - how do I tell if it is really happening?
Hello all -
this is the first restore I've attempted on a real production database.
I used EM to execute the restore, and it correctly found my last full backup, diffs and tran log files.
(I run full recovery mode).
I can see in the log where things are happening, and the status of the database is being displayed as loading.
But after 3.5 hours you just start to wonder if anything is really happening... Any advice?
How long did the backup take? Restore is usually longer. Sometimes 2 or 3x
longer.
Run sp_who2 and see if the CPU and IO counters change for the restore task.
Watch the blinkey lights on the case.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"randyvol" <randy.volters@.whaleyfoodservice.com> wrote in message
news:81408CF6-B061-4514-87C7-D582E3DF9058@.microsoft.com...
> Hello all -
> this is the first restore I've attempted on a real production database.
> I used EM to execute the restore, and it correctly found my last full
backup, diffs and tran log files.
> (I run full recovery mode).
> I can see in the log where things are happening, and the status of the
database is being displayed as loading.
> But after 3.5 hours you just start to wonder if anything is really
happening... Any advice?
|||Hi Randy.
For future reference, you might find RESTORE's STATS option useful. I use
this pretty much on autopilot - RESTORE.. WITH STATS=10 - it writes a
progress response to the console / query analyser every 10 percent. That way
you get an idea of how far the restore's progressed.
HTH
Regards,
Greg Linwood
SQL Server MVP
"randyvol" <randy.volters@.whaleyfoodservice.com> wrote in message
news:81408CF6-B061-4514-87C7-D582E3DF9058@.microsoft.com...
> Hello all -
> this is the first restore I've attempted on a real production database.
> I used EM to execute the restore, and it correctly found my last full
backup, diffs and tran log files.
> (I run full recovery mode).
> I can see in the log where things are happening, and the status of the
database is being displayed as loading.
> But after 3.5 hours you just start to wonder if anything is really
happening... Any advice?
|||Hi Randy,
I am reviewing your post and we have not heard from you for some time. I
want to know if your restore is fine now. Our community members have
provide some informaitons by using WITH STATS in recovery and it might be
longer than backup. If you still have questions, please feel free to let me
know and we are glad to help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
this is the first restore I've attempted on a real production database.
I used EM to execute the restore, and it correctly found my last full backup, diffs and tran log files.
(I run full recovery mode).
I can see in the log where things are happening, and the status of the database is being displayed as loading.
But after 3.5 hours you just start to wonder if anything is really happening... Any advice?
How long did the backup take? Restore is usually longer. Sometimes 2 or 3x
longer.
Run sp_who2 and see if the CPU and IO counters change for the restore task.
Watch the blinkey lights on the case.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"randyvol" <randy.volters@.whaleyfoodservice.com> wrote in message
news:81408CF6-B061-4514-87C7-D582E3DF9058@.microsoft.com...
> Hello all -
> this is the first restore I've attempted on a real production database.
> I used EM to execute the restore, and it correctly found my last full
backup, diffs and tran log files.
> (I run full recovery mode).
> I can see in the log where things are happening, and the status of the
database is being displayed as loading.
> But after 3.5 hours you just start to wonder if anything is really
happening... Any advice?
|||Hi Randy.
For future reference, you might find RESTORE's STATS option useful. I use
this pretty much on autopilot - RESTORE.. WITH STATS=10 - it writes a
progress response to the console / query analyser every 10 percent. That way
you get an idea of how far the restore's progressed.
HTH
Regards,
Greg Linwood
SQL Server MVP
"randyvol" <randy.volters@.whaleyfoodservice.com> wrote in message
news:81408CF6-B061-4514-87C7-D582E3DF9058@.microsoft.com...
> Hello all -
> this is the first restore I've attempted on a real production database.
> I used EM to execute the restore, and it correctly found my last full
backup, diffs and tran log files.
> (I run full recovery mode).
> I can see in the log where things are happening, and the status of the
database is being displayed as loading.
> But after 3.5 hours you just start to wonder if anything is really
happening... Any advice?
|||Hi Randy,
I am reviewing your post and we have not heard from you for some time. I
want to know if your restore is fine now. Our community members have
provide some informaitons by using WITH STATS in recovery and it might be
longer than backup. If you still have questions, please feel free to let me
know and we are glad to help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
Subscribe to:
Posts (Atom)