Hi Group,
I performed a restore operation on a database. The restore said it was
successful, however the database is in an unusable state. It is greyed out
and just says (Loading) after it. It stays in this status forever. I
restarted SQL Services, and even rebooted the server, which made a few
people cry but they'll get over it.
I also detached the database, since I have a copy of it stored on another
drive, but when I went to re-attach it to the copy, I get a Error 5173
Cannot associate files with different databases. That is all it says.
What am I missing here?
Thanks
CurtSounds like you did not recover the database when you restored it.
Try running the following from Query Analyzer (assuming your database is
called MyDB for example and you still have it in the "loading" state on your
server):
restore database MyDB with recovery
This should bring it online again.
Tony.
"C Fish" <cfish@.qwpi.com> wrote in message
news:D7XQa.1291$cX.480@.newssvr23.news.prodigy.com...
> Hi Group,
> I performed a restore operation on a database. The restore said it was
> successful, however the database is in an unusable state. It is greyed out
> and just says (Loading) after it. It stays in this status forever. I
> restarted SQL Services, and even rebooted the server, which made a few
> people cry but they'll get over it.
> I also detached the database, since I have a copy of it stored on another
> drive, but when I went to re-attach it to the copy, I get a Error 5173
> Cannot associate files with different databases. That is all it says.
> What am I missing here?
> Thanks
> Curt
>|||Hi Tony,
Well that got me this far
Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 65535. Database may not be activated yet or may
be in transition.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Does this mean anything to you' The database is on the list in enterprise
manager, however it is gray whereas the other databases are yellow and it
has (Loading) after the name in parenthesis. It does not however show up in
ISQL list nor the list in SQL Query Analyzer. FYI this is SQL Server 2000
with SP3 and the Backup set is from Veritas Back Up Exec ver 8.5 with SQL
Server plug in. I have restored before without any issues. I followed the
same procedures as I have in the past, but this time I am feeling like I'm
in some trouble.
Curt
"Tony Lewis" <tony.lewis@.unspam_dowcorning.com> wrote in message
news:OMHhz1vSDHA.2084@.TK2MSFTNGP11.phx.gbl...
> Sounds like you did not recover the database when you restored it.
> Try running the following from Query Analyzer (assuming your database is
> called MyDB for example and you still have it in the "loading" state on
your
> server):
> restore database MyDB with recovery
> This should bring it online again.
> Tony.
> "C Fish" <cfish@.qwpi.com> wrote in message
> news:D7XQa.1291$cX.480@.newssvr23.news.prodigy.com...
> > Hi Group,
> >
> > I performed a restore operation on a database. The restore said it was
> > successful, however the database is in an unusable state. It is greyed
out
> > and just says (Loading) after it. It stays in this status forever. I
> > restarted SQL Services, and even rebooted the server, which made a few
> > people cry but they'll get over it.
> >
> > I also detached the database, since I have a copy of it stored on
another
> > drive, but when I went to re-attach it to the copy, I get a Error 5173
> > Cannot associate files with different databases. That is all it says.
> >
> > What am I missing here?
> >
> > Thanks
> >
> > Curt
> >
> >
>
>
Showing posts with label group. Show all posts
Showing posts with label group. Show all posts
Friday, March 23, 2012
Friday, March 9, 2012
Restore points available
Hi group,
I have a SQL 2000 database with a backup schedule as so:
Monday: 7 AM - 10 PM: incremental log backups every five minutes to
MondayFilename.bak
Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
Same for Tuesday through Sunday, for a total of 14 .bak files.
With the way I have things setup now, if I choose to restore the database
from Enterprise manager, the restore points that I have to select from go
all the way back to when these backups were setup last year. Considering
that there is a backup every five minutes, and this has been going on for
months, there are pretty many options listed...
What I would like to have as my options is just a list of the past seven
days and all the times that backups ran during those seven days. To get it
this way, I believe what I have to do is add another step in the backup that
would run prior to the first incremental log backup that would initialize
the .bak files for that day. ? So, it would be more like:
Monday: 6:30 AM: Initialize Monday*.bak and make it so SQL Server forgets
last Monday's backups existed.
Monday: 7 AM - 10 PM: incremental log backups every five minutes to
MondayFilename.bak
Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
Same for Tuesday through Sunday, for a total of 14 .bak files.
Does this make sense? If so, can anyone tell me how I can do this? Would I
just do a BACKUP LOG|DATABASE WITH INIT as its own job at 6:30 AM?
In the BOL, it states:
INIT : Specifies that all backup sets should be overwritten, but preserves
the media header. If INIT is specified, any existing backup set data on that
device is overwritten.
Will this clear out the history of all these backups so that when I choose
to do a restore from Enterprise Manager, it doesn't take half an hour to
list all the restore points? (This half hour isn't the reason that I want
to change this, btw. I want to change it because it's setup wrong, imo.)
Thanks,
Ray at workA couple of points here.
First, the frequency of log backups has nothing to do with restore points.
As long as log continuity is unbroken, you can restore to any point in time
you have backup files for.
Second, there is a sp_delete_backuphistory procedure to clean out old unused
backup jobs so you can keep the size of the backup list down to a managable
level.
These tools should give you a few more options.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:OZz%23$59%23DHA.2552@.TK2MSFTNGP09.phx.gbl...
> Hi group,
> I have a SQL 2000 database with a backup schedule as so:
> Monday: 7 AM - 10 PM: incremental log backups every five minutes to
> MondayFilename.bak
> Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
> Same for Tuesday through Sunday, for a total of 14 .bak files.
> With the way I have things setup now, if I choose to restore the database
> from Enterprise manager, the restore points that I have to select from go
> all the way back to when these backups were setup last year. Considering
> that there is a backup every five minutes, and this has been going on for
> months, there are pretty many options listed...
> What I would like to have as my options is just a list of the past seven
> days and all the times that backups ran during those seven days. To get
it
> this way, I believe what I have to do is add another step in the backup
that
> would run prior to the first incremental log backup that would initialize
> the .bak files for that day. ? So, it would be more like:
>
> Monday: 6:30 AM: Initialize Monday*.bak and make it so SQL Server
forgets
> last Monday's backups existed.
> Monday: 7 AM - 10 PM: incremental log backups every five minutes to
> MondayFilename.bak
> Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
> Same for Tuesday through Sunday, for a total of 14 .bak files.
>
> Does this make sense? If so, can anyone tell me how I can do this? Would
I
> just do a BACKUP LOG|DATABASE WITH INIT as its own job at 6:30 AM?
> In the BOL, it states:
> INIT : Specifies that all backup sets should be overwritten, but preserves
> the media header. If INIT is specified, any existing backup set data on
that
> device is overwritten.
> Will this clear out the history of all these backups so that when I choose
> to do a restore from Enterprise Manager, it doesn't take half an hour to
> list all the restore points? (This half hour isn't the reason that I want
> to change this, btw. I want to change it because it's setup wrong, imo.)
> Thanks,
> Ray at work
>|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...
> A couple of points here.
> First, the frequency of log backups has nothing to do with restore points.
> As long as log continuity is unbroken, you can restore to any point in
time
> you have backup files for.
This makes sense. Thank you.
> Second, there is a sp_delete_backuphistory procedure to clean out old
unused
> backup jobs so you can keep the size of the backup list down to a
managable
> level.
I suppose that I was under the impression that there would be an implied
deletion of backup history if I were "INIT'ing" my backup devices properly.
If this is not the case, what I will do is add a step that uses
sp_delete_backuphistory to my jobs.
Thanks Geoff.
Ray at work|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...
> Second, there is a sp_delete_backuphistory procedure to clean out old
unused
> backup jobs so you can keep the size of the backup list down to a
managable
> level.
My execution of this SP has been running for over seven minutes. I get
nervous when I have to do anything that I don't know about like this.
Whoever had the brilliant idea of tacking DBA onto my job title should be
fired. For some reason, "knowing some things about Access" apparently
qualified me to be a SQL Server DBA, in the eyes of others. As far as I'm
concerned, what this really adds to my title is "fraud." I'm just thinking
out loud for the sake of distracting myself. Ignore this post.
--
Ray at work
Microsoft ASP MVP|||You might want to brush up on the training or polish the resume. DBA
without training is a complicated way of saying 'scapegoat'.
FYI, sp_delete_backuphistory runs very slowly. If you kill it in the
middle, it is OK. Find the oldest entry and let it chew a few days off at a
time.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:%23EqOwf%23%23DHA.1464@.tk2msftngp13.phx.gbl...
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...
> >
> > Second, there is a sp_delete_backuphistory procedure to clean out old
> unused
> > backup jobs so you can keep the size of the backup list down to a
> managable
> > level.
> My execution of this SP has been running for over seven minutes. I get
> nervous when I have to do anything that I don't know about like this.
> Whoever had the brilliant idea of tacking DBA onto my job title should be
> fired. For some reason, "knowing some things about Access" apparently
> qualified me to be a SQL Server DBA, in the eyes of others. As far as I'm
> concerned, what this really adds to my title is "fraud." I'm just
thinking
> out loud for the sake of distracting myself. Ignore this post.
> --
> Ray at work
> Microsoft ASP MVP
>
>|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OCF1Dx%23%23DHA.1548@.TK2MSFTNGP12.phx.gbl...
> You might want to brush up on the training or polish the resume. DBA
> without training is a complicated way of saying 'scapegoat'.
The most I'd want on my resume, as of now, is just something that indicates
that I have a small amount of SQL Server knowedge. I'd like to continue
learning more about it, but only for my own personal interest and to make
things easier on myself. But, this DBA thing, I am just so wrong for it.
I'm probably more qualified to be a neurosurgeon.
> FYI, sp_delete_backuphistory runs very slowly. If you kill it in the
> middle, it is OK. Find the oldest entry and let it chew a few days off at
a
> time.
This I will do. Thanks again Geoff.
--
Ray at home
Microsoft ASP MVP
I have a SQL 2000 database with a backup schedule as so:
Monday: 7 AM - 10 PM: incremental log backups every five minutes to
MondayFilename.bak
Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
Same for Tuesday through Sunday, for a total of 14 .bak files.
With the way I have things setup now, if I choose to restore the database
from Enterprise manager, the restore points that I have to select from go
all the way back to when these backups were setup last year. Considering
that there is a backup every five minutes, and this has been going on for
months, there are pretty many options listed...
What I would like to have as my options is just a list of the past seven
days and all the times that backups ran during those seven days. To get it
this way, I believe what I have to do is add another step in the backup that
would run prior to the first incremental log backup that would initialize
the .bak files for that day. ? So, it would be more like:
Monday: 6:30 AM: Initialize Monday*.bak and make it so SQL Server forgets
last Monday's backups existed.
Monday: 7 AM - 10 PM: incremental log backups every five minutes to
MondayFilename.bak
Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
Same for Tuesday through Sunday, for a total of 14 .bak files.
Does this make sense? If so, can anyone tell me how I can do this? Would I
just do a BACKUP LOG|DATABASE WITH INIT as its own job at 6:30 AM?
In the BOL, it states:
INIT : Specifies that all backup sets should be overwritten, but preserves
the media header. If INIT is specified, any existing backup set data on that
device is overwritten.
Will this clear out the history of all these backups so that when I choose
to do a restore from Enterprise Manager, it doesn't take half an hour to
list all the restore points? (This half hour isn't the reason that I want
to change this, btw. I want to change it because it's setup wrong, imo.)
Thanks,
Ray at workA couple of points here.
First, the frequency of log backups has nothing to do with restore points.
As long as log continuity is unbroken, you can restore to any point in time
you have backup files for.
Second, there is a sp_delete_backuphistory procedure to clean out old unused
backup jobs so you can keep the size of the backup list down to a managable
level.
These tools should give you a few more options.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:OZz%23$59%23DHA.2552@.TK2MSFTNGP09.phx.gbl...
> Hi group,
> I have a SQL 2000 database with a backup schedule as so:
> Monday: 7 AM - 10 PM: incremental log backups every five minutes to
> MondayFilename.bak
> Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
> Same for Tuesday through Sunday, for a total of 14 .bak files.
> With the way I have things setup now, if I choose to restore the database
> from Enterprise manager, the restore points that I have to select from go
> all the way back to when these backups were setup last year. Considering
> that there is a backup every five minutes, and this has been going on for
> months, there are pretty many options listed...
> What I would like to have as my options is just a list of the past seven
> days and all the times that backups ran during those seven days. To get
it
> this way, I believe what I have to do is add another step in the backup
that
> would run prior to the first incremental log backup that would initialize
> the .bak files for that day. ? So, it would be more like:
>
> Monday: 6:30 AM: Initialize Monday*.bak and make it so SQL Server
forgets
> last Monday's backups existed.
> Monday: 7 AM - 10 PM: incremental log backups every five minutes to
> MondayFilename.bak
> Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
> Same for Tuesday through Sunday, for a total of 14 .bak files.
>
> Does this make sense? If so, can anyone tell me how I can do this? Would
I
> just do a BACKUP LOG|DATABASE WITH INIT as its own job at 6:30 AM?
> In the BOL, it states:
> INIT : Specifies that all backup sets should be overwritten, but preserves
> the media header. If INIT is specified, any existing backup set data on
that
> device is overwritten.
> Will this clear out the history of all these backups so that when I choose
> to do a restore from Enterprise Manager, it doesn't take half an hour to
> list all the restore points? (This half hour isn't the reason that I want
> to change this, btw. I want to change it because it's setup wrong, imo.)
> Thanks,
> Ray at work
>|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...
> A couple of points here.
> First, the frequency of log backups has nothing to do with restore points.
> As long as log continuity is unbroken, you can restore to any point in
time
> you have backup files for.
This makes sense. Thank you.
> Second, there is a sp_delete_backuphistory procedure to clean out old
unused
> backup jobs so you can keep the size of the backup list down to a
managable
> level.
I suppose that I was under the impression that there would be an implied
deletion of backup history if I were "INIT'ing" my backup devices properly.
If this is not the case, what I will do is add a step that uses
sp_delete_backuphistory to my jobs.
Thanks Geoff.
Ray at work|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...
> Second, there is a sp_delete_backuphistory procedure to clean out old
unused
> backup jobs so you can keep the size of the backup list down to a
managable
> level.
My execution of this SP has been running for over seven minutes. I get
nervous when I have to do anything that I don't know about like this.
Whoever had the brilliant idea of tacking DBA onto my job title should be
fired. For some reason, "knowing some things about Access" apparently
qualified me to be a SQL Server DBA, in the eyes of others. As far as I'm
concerned, what this really adds to my title is "fraud." I'm just thinking
out loud for the sake of distracting myself. Ignore this post.
--
Ray at work
Microsoft ASP MVP|||You might want to brush up on the training or polish the resume. DBA
without training is a complicated way of saying 'scapegoat'.
FYI, sp_delete_backuphistory runs very slowly. If you kill it in the
middle, it is OK. Find the oldest entry and let it chew a few days off at a
time.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:%23EqOwf%23%23DHA.1464@.tk2msftngp13.phx.gbl...
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...
> >
> > Second, there is a sp_delete_backuphistory procedure to clean out old
> unused
> > backup jobs so you can keep the size of the backup list down to a
> managable
> > level.
> My execution of this SP has been running for over seven minutes. I get
> nervous when I have to do anything that I don't know about like this.
> Whoever had the brilliant idea of tacking DBA onto my job title should be
> fired. For some reason, "knowing some things about Access" apparently
> qualified me to be a SQL Server DBA, in the eyes of others. As far as I'm
> concerned, what this really adds to my title is "fraud." I'm just
thinking
> out loud for the sake of distracting myself. Ignore this post.
> --
> Ray at work
> Microsoft ASP MVP
>
>|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OCF1Dx%23%23DHA.1548@.TK2MSFTNGP12.phx.gbl...
> You might want to brush up on the training or polish the resume. DBA
> without training is a complicated way of saying 'scapegoat'.
The most I'd want on my resume, as of now, is just something that indicates
that I have a small amount of SQL Server knowedge. I'd like to continue
learning more about it, but only for my own personal interest and to make
things easier on myself. But, this DBA thing, I am just so wrong for it.
I'm probably more qualified to be a neurosurgeon.
> FYI, sp_delete_backuphistory runs very slowly. If you kill it in the
> middle, it is OK. Find the oldest entry and let it chew a few days off at
a
> time.
This I will do. Thanks again Geoff.
--
Ray at home
Microsoft ASP MVP
Restore points available
Hi group,
I have a SQL 2000 database with a backup schedule as so:
Monday: 7 AM - 10 PM: incremental log backups every five minutes to
MondayFilename.bak
Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
Same for Tuesday through Sunday, for a total of 14 .bak files.
With the way I have things setup now, if I choose to restore the database
from Enterprise manager, the restore points that I have to select from go
all the way back to when these backups were setup last year. Considering
that there is a backup every five minutes, and this has been going on for
months, there are pretty many options listed...
What I would like to have as my options is just a list of the past seven
days and all the times that backups ran during those seven days. To get it
this way, I believe what I have to do is add another step in the backup that
would run prior to the first incremental log backup that would initialize
the .bak files for that day. ? So, it would be more like:
Monday: 6:30 AM: Initialize Monday*.bak and make it so SQL Server forgets
last Monday's backups existed.
Monday: 7 AM - 10 PM: incremental log backups every five minutes to
MondayFilename.bak
Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
Same for Tuesday through Sunday, for a total of 14 .bak files.
Does this make sense? If so, can anyone tell me how I can do this? Would I
just do a BACKUP LOG|DATABASE WITH INIT as its own job at 6:30 AM?
In the BOL, it states:
INIT : Specifies that all backup sets should be overwritten, but preserves
the media header. If INIT is specified, any existing backup set data on that
device is overwritten.
Will this clear out the history of all these backups so that when I choose
to do a restore from Enterprise Manager, it doesn't take half an hour to
list all the restore points? (This half hour isn't the reason that I want
to change this, btw. I want to change it because it's setup wrong, imo.)
Thanks,
Ray at workA couple of points here.
First, the frequency of log backups has nothing to do with restore points.
As long as log continuity is unbroken, you can restore to any point in time
you have backup files for.
Second, there is a sp_delete_backuphistory procedure to clean out old unused
backup jobs so you can keep the size of the backup list down to a managable
level.
These tools should give you a few more options.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:OZz%23$59%23DHA.2552@.TK2MSFTNGP09.phx.gbl...
> Hi group,
> I have a SQL 2000 database with a backup schedule as so:
> Monday: 7 AM - 10 PM: incremental log backups every five minutes to
> MondayFilename.bak
> Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
> Same for Tuesday through Sunday, for a total of 14 .bak files.
> With the way I have things setup now, if I choose to restore the database
> from Enterprise manager, the restore points that I have to select from go
> all the way back to when these backups were setup last year. Considering
> that there is a backup every five minutes, and this has been going on for
> months, there are pretty many options listed...
> What I would like to have as my options is just a list of the past seven
> days and all the times that backups ran during those seven days. To get
it
> this way, I believe what I have to do is add another step in the backup
that
> would run prior to the first incremental log backup that would initialize
> the .bak files for that day. ? So, it would be more like:
>
> Monday: 6:30 AM: Initialize Monday*.bak and make it so SQL Server
forgets
> last Monday's backups existed.
> Monday: 7 AM - 10 PM: incremental log backups every five minutes to
> MondayFilename.bak
> Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
> Same for Tuesday through Sunday, for a total of 14 .bak files.
>
> Does this make sense? If so, can anyone tell me how I can do this? Would
I
> just do a BACKUP LOG|DATABASE WITH INIT as its own job at 6:30 AM?
> In the BOL, it states:
> INIT : Specifies that all backup sets should be overwritten, but preserves
> the media header. If INIT is specified, any existing backup set data on
that
> device is overwritten.
> Will this clear out the history of all these backups so that when I choose
> to do a restore from Enterprise Manager, it doesn't take half an hour to
> list all the restore points? (This half hour isn't the reason that I want
> to change this, btw. I want to change it because it's setup wrong, imo.)
> Thanks,
> Ray at work
>|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...
> A couple of points here.
> First, the frequency of log backups has nothing to do with restore points.
> As long as log continuity is unbroken, you can restore to any point in
time
> you have backup files for.
This makes sense. Thank you.
> Second, there is a sp_delete_backuphistory procedure to clean out old
unused
> backup jobs so you can keep the size of the backup list down to a
managable
> level.
I suppose that I was under the impression that there would be an implied
deletion of backup history if I were "INIT'ing" my backup devices properly.
If this is not the case, what I will do is add a step that uses
sp_delete_backuphistory to my jobs.
Thanks Geoff.
Ray at work|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...
> Second, there is a sp_delete_backuphistory procedure to clean out old
unused
> backup jobs so you can keep the size of the backup list down to a
managable
> level.
My execution of this SP has been running for over seven minutes. I get
nervous when I have to do anything that I don't know about like this.
Whoever had the brilliant idea of tacking DBA onto my job title should be
fired. For some reason, "knowing some things about Access" apparently
qualified me to be a SQL Server DBA, in the eyes of others. As far as I'm
concerned, what this really adds to my title is "fraud." I'm just thinking
out loud for the sake of distracting myself. Ignore this post.
Ray at work
Microsoft ASP MVP|||You might want to brush up on the training or polish the resume. DBA
without training is a complicated way of saying 'scapegoat'.
FYI, sp_delete_backuphistory runs very slowly. If you kill it in the
middle, it is OK. Find the oldest entry and let it chew a few days off at a
time.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:%23EqOwf%23%23DHA.1464@.tk2msftngp13.phx.gbl...
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...
>
> unused
> managable
> My execution of this SP has been running for over seven minutes. I get
> nervous when I have to do anything that I don't know about like this.
> Whoever had the brilliant idea of tacking DBA onto my job title should be
> fired. For some reason, "knowing some things about Access" apparently
> qualified me to be a SQL Server DBA, in the eyes of others. As far as I'm
> concerned, what this really adds to my title is "fraud." I'm just
thinking
> out loud for the sake of distracting myself. Ignore this post.
> --
> Ray at work
> Microsoft ASP MVP
>
>|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OCF1Dx%23%23DHA.1548@.TK2MSFTNGP12.phx.gbl...
> You might want to brush up on the training or polish the resume. DBA
> without training is a complicated way of saying 'scapegoat'.
The most I'd want on my resume, as of now, is just something that indicates
that I have a small amount of SQL Server knowedge. I'd like to continue
learning more about it, but only for my own personal interest and to make
things easier on myself. But, this DBA thing, I am just so wrong for it.
I'm probably more qualified to be a neurosurgeon.
> FYI, sp_delete_backuphistory runs very slowly. If you kill it in the
> middle, it is OK. Find the oldest entry and let it chew a few days off at
a
> time.
This I will do. Thanks again Geoff.
Ray at home
Microsoft ASP MVP
I have a SQL 2000 database with a backup schedule as so:
Monday: 7 AM - 10 PM: incremental log backups every five minutes to
MondayFilename.bak
Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
Same for Tuesday through Sunday, for a total of 14 .bak files.
With the way I have things setup now, if I choose to restore the database
from Enterprise manager, the restore points that I have to select from go
all the way back to when these backups were setup last year. Considering
that there is a backup every five minutes, and this has been going on for
months, there are pretty many options listed...
What I would like to have as my options is just a list of the past seven
days and all the times that backups ran during those seven days. To get it
this way, I believe what I have to do is add another step in the backup that
would run prior to the first incremental log backup that would initialize
the .bak files for that day. ? So, it would be more like:
Monday: 6:30 AM: Initialize Monday*.bak and make it so SQL Server forgets
last Monday's backups existed.
Monday: 7 AM - 10 PM: incremental log backups every five minutes to
MondayFilename.bak
Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
Same for Tuesday through Sunday, for a total of 14 .bak files.
Does this make sense? If so, can anyone tell me how I can do this? Would I
just do a BACKUP LOG|DATABASE WITH INIT as its own job at 6:30 AM?
In the BOL, it states:
INIT : Specifies that all backup sets should be overwritten, but preserves
the media header. If INIT is specified, any existing backup set data on that
device is overwritten.
Will this clear out the history of all these backups so that when I choose
to do a restore from Enterprise Manager, it doesn't take half an hour to
list all the restore points? (This half hour isn't the reason that I want
to change this, btw. I want to change it because it's setup wrong, imo.)
Thanks,
Ray at workA couple of points here.
First, the frequency of log backups has nothing to do with restore points.
As long as log continuity is unbroken, you can restore to any point in time
you have backup files for.
Second, there is a sp_delete_backuphistory procedure to clean out old unused
backup jobs so you can keep the size of the backup list down to a managable
level.
These tools should give you a few more options.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:OZz%23$59%23DHA.2552@.TK2MSFTNGP09.phx.gbl...
> Hi group,
> I have a SQL 2000 database with a backup schedule as so:
> Monday: 7 AM - 10 PM: incremental log backups every five minutes to
> MondayFilename.bak
> Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
> Same for Tuesday through Sunday, for a total of 14 .bak files.
> With the way I have things setup now, if I choose to restore the database
> from Enterprise manager, the restore points that I have to select from go
> all the way back to when these backups were setup last year. Considering
> that there is a backup every five minutes, and this has been going on for
> months, there are pretty many options listed...
> What I would like to have as my options is just a list of the past seven
> days and all the times that backups ran during those seven days. To get
it
> this way, I believe what I have to do is add another step in the backup
that
> would run prior to the first incremental log backup that would initialize
> the .bak files for that day. ? So, it would be more like:
>
> Monday: 6:30 AM: Initialize Monday*.bak and make it so SQL Server
forgets
> last Monday's backups existed.
> Monday: 7 AM - 10 PM: incremental log backups every five minutes to
> MondayFilename.bak
> Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
> Same for Tuesday through Sunday, for a total of 14 .bak files.
>
> Does this make sense? If so, can anyone tell me how I can do this? Would
I
> just do a BACKUP LOG|DATABASE WITH INIT as its own job at 6:30 AM?
> In the BOL, it states:
> INIT : Specifies that all backup sets should be overwritten, but preserves
> the media header. If INIT is specified, any existing backup set data on
that
> device is overwritten.
> Will this clear out the history of all these backups so that when I choose
> to do a restore from Enterprise Manager, it doesn't take half an hour to
> list all the restore points? (This half hour isn't the reason that I want
> to change this, btw. I want to change it because it's setup wrong, imo.)
> Thanks,
> Ray at work
>|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...
> A couple of points here.
> First, the frequency of log backups has nothing to do with restore points.
> As long as log continuity is unbroken, you can restore to any point in
time
> you have backup files for.
This makes sense. Thank you.
> Second, there is a sp_delete_backuphistory procedure to clean out old
unused
> backup jobs so you can keep the size of the backup list down to a
managable
> level.
I suppose that I was under the impression that there would be an implied
deletion of backup history if I were "INIT'ing" my backup devices properly.
If this is not the case, what I will do is add a step that uses
sp_delete_backuphistory to my jobs.
Thanks Geoff.
Ray at work|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...
> Second, there is a sp_delete_backuphistory procedure to clean out old
unused
> backup jobs so you can keep the size of the backup list down to a
managable
> level.
My execution of this SP has been running for over seven minutes. I get
nervous when I have to do anything that I don't know about like this.
Whoever had the brilliant idea of tacking DBA onto my job title should be
fired. For some reason, "knowing some things about Access" apparently
qualified me to be a SQL Server DBA, in the eyes of others. As far as I'm
concerned, what this really adds to my title is "fraud." I'm just thinking
out loud for the sake of distracting myself. Ignore this post.
Ray at work
Microsoft ASP MVP|||You might want to brush up on the training or polish the resume. DBA
without training is a complicated way of saying 'scapegoat'.
FYI, sp_delete_backuphistory runs very slowly. If you kill it in the
middle, it is OK. Find the oldest entry and let it chew a few days off at a
time.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:%23EqOwf%23%23DHA.1464@.tk2msftngp13.phx.gbl...
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...
>
> unused
> managable
> My execution of this SP has been running for over seven minutes. I get
> nervous when I have to do anything that I don't know about like this.
> Whoever had the brilliant idea of tacking DBA onto my job title should be
> fired. For some reason, "knowing some things about Access" apparently
> qualified me to be a SQL Server DBA, in the eyes of others. As far as I'm
> concerned, what this really adds to my title is "fraud." I'm just
thinking
> out loud for the sake of distracting myself. Ignore this post.
> --
> Ray at work
> Microsoft ASP MVP
>
>|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OCF1Dx%23%23DHA.1548@.TK2MSFTNGP12.phx.gbl...
> You might want to brush up on the training or polish the resume. DBA
> without training is a complicated way of saying 'scapegoat'.
The most I'd want on my resume, as of now, is just something that indicates
that I have a small amount of SQL Server knowedge. I'd like to continue
learning more about it, but only for my own personal interest and to make
things easier on myself. But, this DBA thing, I am just so wrong for it.
I'm probably more qualified to be a neurosurgeon.
> FYI, sp_delete_backuphistory runs very slowly. If you kill it in the
> middle, it is OK. Find the oldest entry and let it chew a few days off at
a
> time.
This I will do. Thanks again Geoff.
Ray at home
Microsoft ASP MVP
Restore of the SQL Server Program Files
Hello Group,
My Network Admin an myself are trying to come up with a way to quickly
restore our SQL Server in case of partial disk failure. What I am looking to
do is to resore only the Program Files Folder on the Database Server (if that
is possible). We have had good success with using NT Back Up and restoring
the complete server (OS and all).
What he wants me to do next is use a Shadow Copy to restore just the
Programs Folder holding the SQL Server install. This ahs not worked very
well. We can restore the folders and instances and databases but the SQL
Server Service does not start.
Does Microsoft have any instructions on this? The other option is I just
install SQL Server again and dump my BAK files back into the server but they
want a solution that does not include using the SQL Server install CD.
Rich
Hi
There are registry settings that need to come along too.
Files need to be registered also.
If you setup your SQL Server instances using the unattended install scripts
and you save them away, you could possibly install SQL Server from a scratch
faster than looking for a tape.
AFAIK, only full machine restores are supported to restore program files.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C865BFA9-33DE-4E99-9CA3-42AE7DFF3F84@.microsoft.com...
> Hello Group,
> My Network Admin an myself are trying to come up with a way to quickly
> restore our SQL Server in case of partial disk failure. What I am looking
> to
> do is to resore only the Program Files Folder on the Database Server (if
> that
> is possible). We have had good success with using NT Back Up and
> restoring
> the complete server (OS and all).
> What he wants me to do next is use a Shadow Copy to restore just the
> Programs Folder holding the SQL Server install. This ahs not worked very
> well. We can restore the folders and instances and databases but the SQL
> Server Service does not start.
> Does Microsoft have any instructions on this? The other option is I just
> install SQL Server again and dump my BAK files back into the server but
> they
> want a solution that does not include using the SQL Server install CD.
> Rich
>
My Network Admin an myself are trying to come up with a way to quickly
restore our SQL Server in case of partial disk failure. What I am looking to
do is to resore only the Program Files Folder on the Database Server (if that
is possible). We have had good success with using NT Back Up and restoring
the complete server (OS and all).
What he wants me to do next is use a Shadow Copy to restore just the
Programs Folder holding the SQL Server install. This ahs not worked very
well. We can restore the folders and instances and databases but the SQL
Server Service does not start.
Does Microsoft have any instructions on this? The other option is I just
install SQL Server again and dump my BAK files back into the server but they
want a solution that does not include using the SQL Server install CD.
Rich
Hi
There are registry settings that need to come along too.
Files need to be registered also.
If you setup your SQL Server instances using the unattended install scripts
and you save them away, you could possibly install SQL Server from a scratch
faster than looking for a tape.
AFAIK, only full machine restores are supported to restore program files.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C865BFA9-33DE-4E99-9CA3-42AE7DFF3F84@.microsoft.com...
> Hello Group,
> My Network Admin an myself are trying to come up with a way to quickly
> restore our SQL Server in case of partial disk failure. What I am looking
> to
> do is to resore only the Program Files Folder on the Database Server (if
> that
> is possible). We have had good success with using NT Back Up and
> restoring
> the complete server (OS and all).
> What he wants me to do next is use a Shadow Copy to restore just the
> Programs Folder holding the SQL Server install. This ahs not worked very
> well. We can restore the folders and instances and databases but the SQL
> Server Service does not start.
> Does Microsoft have any instructions on this? The other option is I just
> install SQL Server again and dump my BAK files back into the server but
> they
> want a solution that does not include using the SQL Server install CD.
> Rich
>
Restore of the SQL Server Program Files
Hello Group,
My Network Admin an myself are trying to come up with a way to quickly
restore our SQL Server in case of partial disk failure. What I am looking t
o
do is to resore only the Program Files Folder on the Database Server (if tha
t
is possible). We have had good success with using NT Back Up and restoring
the complete server (OS and all).
What he wants me to do next is use a Shadow Copy to restore just the
Programs Folder holding the SQL Server install. This ahs not worked very
well. We can restore the folders and instances and databases but the SQL
Server Service does not start.
Does Microsoft have any instructions on this? The other option is I just
install SQL Server again and dump my BAK files back into the server but they
want a solution that does not include using the SQL Server install CD.
RichHi
There are registry settings that need to come along too.
Files need to be registered also.
If you setup your SQL Server instances using the unattended install scripts
and you save them away, you could possibly install SQL Server from a scratch
faster than looking for a tape.
AFAIK, only full machine restores are supported to restore program files.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C865BFA9-33DE-4E99-9CA3-42AE7DFF3F84@.microsoft.com...
> Hello Group,
> My Network Admin an myself are trying to come up with a way to quickly
> restore our SQL Server in case of partial disk failure. What I am looking
> to
> do is to resore only the Program Files Folder on the Database Server (if
> that
> is possible). We have had good success with using NT Back Up and
> restoring
> the complete server (OS and all).
> What he wants me to do next is use a Shadow Copy to restore just the
> Programs Folder holding the SQL Server install. This ahs not worked very
> well. We can restore the folders and instances and databases but the SQL
> Server Service does not start.
> Does Microsoft have any instructions on this? The other option is I just
> install SQL Server again and dump my BAK files back into the server but
> they
> want a solution that does not include using the SQL Server install CD.
> Rich
>
My Network Admin an myself are trying to come up with a way to quickly
restore our SQL Server in case of partial disk failure. What I am looking t
o
do is to resore only the Program Files Folder on the Database Server (if tha
t
is possible). We have had good success with using NT Back Up and restoring
the complete server (OS and all).
What he wants me to do next is use a Shadow Copy to restore just the
Programs Folder holding the SQL Server install. This ahs not worked very
well. We can restore the folders and instances and databases but the SQL
Server Service does not start.
Does Microsoft have any instructions on this? The other option is I just
install SQL Server again and dump my BAK files back into the server but they
want a solution that does not include using the SQL Server install CD.
RichHi
There are registry settings that need to come along too.
Files need to be registered also.
If you setup your SQL Server instances using the unattended install scripts
and you save them away, you could possibly install SQL Server from a scratch
faster than looking for a tape.
AFAIK, only full machine restores are supported to restore program files.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C865BFA9-33DE-4E99-9CA3-42AE7DFF3F84@.microsoft.com...
> Hello Group,
> My Network Admin an myself are trying to come up with a way to quickly
> restore our SQL Server in case of partial disk failure. What I am looking
> to
> do is to resore only the Program Files Folder on the Database Server (if
> that
> is possible). We have had good success with using NT Back Up and
> restoring
> the complete server (OS and all).
> What he wants me to do next is use a Shadow Copy to restore just the
> Programs Folder holding the SQL Server install. This ahs not worked very
> well. We can restore the folders and instances and databases but the SQL
> Server Service does not start.
> Does Microsoft have any instructions on this? The other option is I just
> install SQL Server again and dump my BAK files back into the server but
> they
> want a solution that does not include using the SQL Server install CD.
> Rich
>
Restore of the SQL Server Program Files
Hello Group,
My Network Admin an myself are trying to come up with a way to quickly
restore our SQL Server in case of partial disk failure. What I am looking to
do is to resore only the Program Files Folder on the Database Server (if that
is possible). We have had good success with using NT Back Up and restoring
the complete server (OS and all).
What he wants me to do next is use a Shadow Copy to restore just the
Programs Folder holding the SQL Server install. This ahs not worked very
well. We can restore the folders and instances and databases but the SQL
Server Service does not start.
Does Microsoft have any instructions on this? The other option is I just
install SQL Server again and dump my BAK files back into the server but they
want a solution that does not include using the SQL Server install CD.
RichHi
There are registry settings that need to come along too.
Files need to be registered also.
If you setup your SQL Server instances using the unattended install scripts
and you save them away, you could possibly install SQL Server from a scratch
faster than looking for a tape.
AFAIK, only full machine restores are supported to restore program files.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C865BFA9-33DE-4E99-9CA3-42AE7DFF3F84@.microsoft.com...
> Hello Group,
> My Network Admin an myself are trying to come up with a way to quickly
> restore our SQL Server in case of partial disk failure. What I am looking
> to
> do is to resore only the Program Files Folder on the Database Server (if
> that
> is possible). We have had good success with using NT Back Up and
> restoring
> the complete server (OS and all).
> What he wants me to do next is use a Shadow Copy to restore just the
> Programs Folder holding the SQL Server install. This ahs not worked very
> well. We can restore the folders and instances and databases but the SQL
> Server Service does not start.
> Does Microsoft have any instructions on this? The other option is I just
> install SQL Server again and dump my BAK files back into the server but
> they
> want a solution that does not include using the SQL Server install CD.
> Rich
>
My Network Admin an myself are trying to come up with a way to quickly
restore our SQL Server in case of partial disk failure. What I am looking to
do is to resore only the Program Files Folder on the Database Server (if that
is possible). We have had good success with using NT Back Up and restoring
the complete server (OS and all).
What he wants me to do next is use a Shadow Copy to restore just the
Programs Folder holding the SQL Server install. This ahs not worked very
well. We can restore the folders and instances and databases but the SQL
Server Service does not start.
Does Microsoft have any instructions on this? The other option is I just
install SQL Server again and dump my BAK files back into the server but they
want a solution that does not include using the SQL Server install CD.
RichHi
There are registry settings that need to come along too.
Files need to be registered also.
If you setup your SQL Server instances using the unattended install scripts
and you save them away, you could possibly install SQL Server from a scratch
faster than looking for a tape.
AFAIK, only full machine restores are supported to restore program files.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C865BFA9-33DE-4E99-9CA3-42AE7DFF3F84@.microsoft.com...
> Hello Group,
> My Network Admin an myself are trying to come up with a way to quickly
> restore our SQL Server in case of partial disk failure. What I am looking
> to
> do is to resore only the Program Files Folder on the Database Server (if
> that
> is possible). We have had good success with using NT Back Up and
> restoring
> the complete server (OS and all).
> What he wants me to do next is use a Shadow Copy to restore just the
> Programs Folder holding the SQL Server install. This ahs not worked very
> well. We can restore the folders and instances and databases but the SQL
> Server Service does not start.
> Does Microsoft have any instructions on this? The other option is I just
> install SQL Server again and dump my BAK files back into the server but
> they
> want a solution that does not include using the SQL Server install CD.
> Rich
>
Subscribe to:
Posts (Atom)