How often to backup the Transaction log.

I do a full overwrite backup of the database at 3am, I also do an overwrite
backup of the transaction log at 3:30. Then during the 2 production shifts I
run differential backups every hour on the hour. I do a transaction log
backup every hour on the half-hour. Is this too often, what is a problem
manifests itself after the trans log is overwriten. The processes do not
take long and they are not a strain on the system. I am doing a standby
server to handle server crashes, seems to me that I should have as recent a
backup as possible.
thoughts?
thanks,
jc
jc
It definitely should not be a problem to backup your TLog every hour. I have many systems that back up every 5 minutes. As long as you are not stressing your system then backup as often as you see fit.
Jeff Duncan
MCDBA, MCSE+I

How often to backup the Transaction log.

I do a full overwrite backup of the database at 3am, I also do an overwrite
backup of the transaction log at 3:30. Then during the 2 production shifts I
run differential backups every hour on the hour. I do a transaction log
backup every hour on the half-hour. Is this too often, what is a problem
manifests itself after the trans log is overwriten. The processes do not
take long and they are not a strain on the system. I am doing a standby
server to handle server crashes, seems to me that I should have as recent a
backup as possible.
thoughts?
thanks,
jcjc
It definitely should not be a problem to backup your TLog every hour. I hav
e many systems that back up every 5 minutes. As long as you are not stressi
ng your system then backup as often as you see fit.
Jeff Duncan
MCDBA, MCSE+I

How often to backup the Transaction log.

I do a full overwrite backup of the database at 3am, I also do an overwrite
backup of the transaction log at 3:30. Then during the 2 production shifts I
run differential backups every hour on the hour. I do a transaction log
backup every hour on the half-hour. Is this too often, what is a problem
manifests itself after the trans log is overwriten. The processes do not
take long and they are not a strain on the system. I am doing a standby
server to handle server crashes, seems to me that I should have as recent a
backup as possible.
thoughts?
thanks,
jcj
It definitely should not be a problem to backup your TLog every hour. I have many systems that back up every 5 minutes. As long as you are not stressing your system then backup as often as you see fit
Jeff Dunca
MCDBA, MCSE+Isql

How often backup the master

Hello,
I would like to know how often to backup the system database. scheduled
or once a week.
Inaina
Once or twice a week
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1146724576.902041.82020@.u72g2000cwu.googlegroups.com...
> Hello,
> I would like to know how often to backup the system database. scheduled
> or once a week.
> Ina
>|||Thanks
and I can a create a device named system device and back them up all in
the same device .
master, model, msdb, tempdb in systemdevice
Ina|||Yes, actually I create a folder for system databases and backap them
directly to the folder
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1146725977.670195.190100@.u72g2000cwu.googlegroups.com...
> Thanks
> and I can a create a device named system device and back them up all in
> the same device .
> master, model, msdb, tempdb in systemdevice
> Ina
>|||Thank you Uri|||I do it once per day. For msdb, I tend to also do log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1146724576.902041.82020@.u72g2000cwu.googlegroups.com...
> Hello,
> I would like to know how often to backup the system database. scheduled
> or once a week.
> Ina
>|||Hello Tibor,
Thank you, ok I will to the transaction log for msdb but concerning the
backup I prefer to do once a week as you are a small company. do you
thing is it depend of the size of the company?
Ina|||No, backup frequency is all about the amount of data you can afford to lose.
The reason I do log
backups for msdb is that here is where backup history is stored and if I hav
e a crash where we lose
msdb as well as user database and some less experienced does the restore, th
at restore will probably
be done using the GUI and not all backups will be restored (since the restor
ed backup if msdb is
older than the other backups).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1146740293.765316.266630@.y43g2000cwc.googlegroups.com...
> Hello Tibor,
> Thank you, ok I will to the transaction log for msdb but concerning the
> backup I prefer to do once a week as you are a small company. do you
> thing is it depend of the size of the company?
> Ina
>|||ok I understand; but I launch my data backup already without having
launched the msdb backup; do you thing there is any problem if i
activated backup once a day for msbd since today?
Ina|||That is no problem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1146746837.973771.114230@.i40g2000cwc.googlegroups.com...
> ok I understand; but I launch my data backup already without having
> launched the msdb backup; do you thing there is any problem if i
> activated backup once a day for msbd since today?
> Ina
>

How often backup the master

Hello,
I would like to know how often to backup the system database. scheduled
or once a week.
Inaina
Once or twice a week
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1146724576.902041.82020@.u72g2000cwu.googlegroups.com...
> Hello,
> I would like to know how often to backup the system database. scheduled
> or once a week.
> Ina
>|||Thanks :)
and I can a create a device named system device and back them up all in
the same device .
master, model, msdb, tempdb in systemdevice
Ina|||Yes, actually I create a folder for system databases and backap them
directly to the folder
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1146725977.670195.190100@.u72g2000cwu.googlegroups.com...
> Thanks :)
> and I can a create a device named system device and back them up all in
> the same device .
> master, model, msdb, tempdb in systemdevice
> Ina
>|||Thank you Uri|||I do it once per day. For msdb, I tend to also do log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1146724576.902041.82020@.u72g2000cwu.googlegroups.com...
> Hello,
> I would like to know how often to backup the system database. scheduled
> or once a week.
> Ina
>|||Hello Tibor,
Thank you, ok I will to the transaction log for msdb but concerning the
backup I prefer to do once a week as you are a small company. do you
thing is it depend of the size of the company?
Ina|||No, backup frequency is all about the amount of data you can afford to lose. The reason I do log
backups for msdb is that here is where backup history is stored and if I have a crash where we lose
msdb as well as user database and some less experienced does the restore, that restore will probably
be done using the GUI and not all backups will be restored (since the restored backup if msdb is
older than the other backups).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1146740293.765316.266630@.y43g2000cwc.googlegroups.com...
> Hello Tibor,
> Thank you, ok I will to the transaction log for msdb but concerning the
> backup I prefer to do once a week as you are a small company. do you
> thing is it depend of the size of the company?
> Ina
>|||ok I understand; but I launch my data backup already without having
launched the msdb backup; do you thing there is any problem if i
activated backup once a day for msbd since today?
Ina|||That is no problem.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1146746837.973771.114230@.i40g2000cwc.googlegroups.com...
> ok I understand; but I launch my data backup already without having
> launched the msdb backup; do you thing there is any problem if i
> activated backup once a day for msbd since today?
> Ina
>|||Ok thank you. :D|||"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1146724576.902041.82020@.u72g2000cwu.googlegroups.com...
> Hello,
> I would like to know how often to backup the system database. scheduled
> or once a week.
I have a maintainence job that backs up the system databases once a day.
Why? it's simple enough to do, and they're small and that away if I make a
change, it's more likely to be there.
> Ina
>|||But I prefer to do without that in order to understand the code, with
the maintenance the T-SQL is anunderstantable.
Ina

How much space does backup need?

Hello everyone:
I have a backup file about 9gig to be resored. The hard drive has less than 11gig space free. Is it enough for restore? Thanks.
ZYTMaybe and maybe not.

If the db has free space in the files of more that about 1 gb, you will probably fail.

Give it a try and see. Worst case is that you fail and have to find some place else to restore it.|||betcha you got a lot of old backups eating up the drive...also betcha you use maint plans|||Are you talking about adding this as a new DB or is it a restore to replace an existing one? Is it a shared drive or DB only? Are the log files on their own volume? If not, maybe put one (or more) there to free up some space - of course use care not to disturb some production log file that's happy on some dedicated volume. If it's not an existing DB (you're actually adding a 9+ GB database), it sure seems risky to run with that little of free space even if it were to succeed, considering that will impact all the stuff already on that disk. Also; suggest starting with a nicely defragged disk.

I don't know internals much so can't offer an answer to your question, but these questions do still seem relevant.|||Are you talking about adding this as a new DB or is it a restore to replace an existing one? Is it a shared drive or DB only? Are the log files on their own volume? If not, maybe put one (or more) there to free up some space - of course use care not to disturb some production log file that's happy on some dedicated volume. If it's not an existing DB (you're actually adding a 9+ GB database), it sure seems risky to run with that little of free space even if it were to succeed, considering that will impact all the stuff already on that disk. Also; suggest starting with a nicely defragged disk.

I don't know internals much so can't offer an answer to your question, but these questions do still seem relevant.

Hi, Vich:

Thanks for advise. I am restoring as a new DB. Hard drive has DB only, but data files and log files are in same hard drive. Does SQL Server calculate space before restoring, or during restoring. I worry SQL Server finds the space in not enough on the half way of restoring. That may bring server to be died. Is this corret thought.

ZYT|||Hi, Vich:

Thanks for advise. I am restoring as a new DB. Hard drive has DB only, but data files and log files are in same hard drive. Does SQL Server calculate space before restoring, or during restoring. I worry SQL Server finds the space in not enough on the half way of restoring. That may bring server to be died. Is this corret thought.

ZYT
Again; I do not know internals. I vaguely recall that the EM Restore Screen issues a "Checking for space" message while it's "thinking".

By "Hard drive has DB only, but data files and log files are in same hard drive", I gather you mean the drive is for the DB and all DB related files (log, DB backup, etc).

So; the very best case is that it fits. The DB may start out with a very small log file, but it will very quickly grow and then you'll be in trouble.

But Dude; you're nuts to be squeezing your main DB disk like this unless it's just an unaviodable emergency, and even then, your emergency may become a disaster.

For example; if your disk is highly fragmented then as free space nears it's end, even if there is theoretically enough space, the fragments may be too small. Also; you kill the chance for a decent defrag if you leave it so tight. Also; as databases and log files grow, they're sure to reach capacity quickly.

In other words, even if by some chance it does work, not for long.

Even with my shallow knowledge of all this, I believe that your solution will be to assign your log and backup files (for this and other databases) to another volume, preferably a dedicated volume. A simple mirrored volume is recommended. Ideally each DB will have it's own volume, but even sharing it will be better than having on the DB volume. If you have one huge production DB, you're probably best moving only it's log file there since sharing logs on volumes cause disk head thrashing that is very costly (like 10 times) to performance.

Let me know if you want instructions for this. I had to do it recently and have it all written into scripts. The others here (far more experienced thatn I) could bless the process before proceeding.|||Run this command on the backup file, to see what you are dealing with:

restore filelistonly from disk = 'your backup file goes here'

The output will list the size of each file.

How long will a differential backup take?

How can I determine how long a differential backup will take to complete? I
think reading the "differential change map" pages of the database would help
,
but I don't know how to do that or even if it is possible.
Tanks,
Bruce.bpettus wrote:
> How can I determine how long a differential backup will take to
> complete? I think reading the "differential change map" pages of the
> database would help, but I don't know how to do that or even if it is
> possible.
> Tanks,
> Bruce.
Since the operation depends on the amount of data to be backed up, the
utilization on the server, the speed of the disk array, and the backup
software you are using, it's really impossible to tell. What I suggest
you do is perform some tests on the system in question and monitor disk
and CPU activity at the same time using perfmon to better give you the
estimates you need. I would also consider you look into a third-party
backup/recovery solution that implements compression to shorten the
backup period. Quest sells LiteSpeed for SQL Server. There are solutions
from other vendors as well.
David Gugick
Quest Software
www.quest.com|||Maybe my question wasn't clear enough. I'm looking to find the percentage o
f
data that would be contained in a differential backup. I could compare that
with the duration of a full backup to fudge a duration estimate. For
example, given a database of 100GB and a differential backup that would
contain 50GB, I could then say "the differential backup would take about 50%
the time of the full backup". I'm not looking for anything precise but
rather an educated guess.
I could do some tests and obtain a benchmark but I'm in a time crunch and
testing is not an option at this point.
Thanks,
Bruce.
"David Gugick" wrote:

> bpettus wrote:
> Since the operation depends on the amount of data to be backed up, the
> utilization on the server, the speed of the disk array, and the backup
> software you are using, it's really impossible to tell. What I suggest
> you do is perform some tests on the system in question and monitor disk
> and CPU activity at the same time using perfmon to better give you the
> estimates you need. I would also consider you look into a third-party
> backup/recovery solution that implements compression to shorten the
> backup period. Quest sells LiteSpeed for SQL Server. There are solutions
> from other vendors as well.
> --
> David Gugick
> Quest Software
> www.quest.com
>|||Hi
Yes, roughly, 100/50GB gives you 50% of the time.
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/
"bpettus" <bpettus@.discussions.microsoft.com> wrote in message
news:D08FAB04-F368-4C48-9084-20DBB219273C@.microsoft.com...[vbcol=seagreen]
> Maybe my question wasn't clear enough. I'm looking to find the percentage
> of
> data that would be contained in a differential backup. I could compare
> that
> with the duration of a full backup to fudge a duration estimate. For
> example, given a database of 100GB and a differential backup that would
> contain 50GB, I could then say "the differential backup would take about
> 50%
> the time of the full backup". I'm not looking for anything precise but
> rather an educated guess.
> I could do some tests and obtain a benchmark but I'm in a time crunch and
> testing is not an option at this point.
> Thanks,
> Bruce.
> "David Gugick" wrote:
>

How long will a differential backup take?

How can I determine how long a differential backup will take to complete? I
think reading the "differential change map" pages of the database would help,
but I don't know how to do that or even if it is possible.
Tanks,
Bruce.
bpettus wrote:
> How can I determine how long a differential backup will take to
> complete? I think reading the "differential change map" pages of the
> database would help, but I don't know how to do that or even if it is
> possible.
> Tanks,
> Bruce.
Since the operation depends on the amount of data to be backed up, the
utilization on the server, the speed of the disk array, and the backup
software you are using, it's really impossible to tell. What I suggest
you do is perform some tests on the system in question and monitor disk
and CPU activity at the same time using perfmon to better give you the
estimates you need. I would also consider you look into a third-party
backup/recovery solution that implements compression to shorten the
backup period. Quest sells LiteSpeed for SQL Server. There are solutions
from other vendors as well.
David Gugick
Quest Software
www.quest.com
|||Maybe my question wasn't clear enough. I'm looking to find the percentage of
data that would be contained in a differential backup. I could compare that
with the duration of a full backup to fudge a duration estimate. For
example, given a database of 100GB and a differential backup that would
contain 50GB, I could then say "the differential backup would take about 50%
the time of the full backup". I'm not looking for anything precise but
rather an educated guess.
I could do some tests and obtain a benchmark but I'm in a time crunch and
testing is not an option at this point.
Thanks,
Bruce.
"David Gugick" wrote:

> bpettus wrote:
> Since the operation depends on the amount of data to be backed up, the
> utilization on the server, the speed of the disk array, and the backup
> software you are using, it's really impossible to tell. What I suggest
> you do is perform some tests on the system in question and monitor disk
> and CPU activity at the same time using perfmon to better give you the
> estimates you need. I would also consider you look into a third-party
> backup/recovery solution that implements compression to shorten the
> backup period. Quest sells LiteSpeed for SQL Server. There are solutions
> from other vendors as well.
> --
> David Gugick
> Quest Software
> www.quest.com
>
|||Hi
Yes, roughly, 100/50GB gives you 50% of the time.
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/
"bpettus" <bpettus@.discussions.microsoft.com> wrote in message
news:D08FAB04-F368-4C48-9084-20DBB219273C@.microsoft.com...[vbcol=seagreen]
> Maybe my question wasn't clear enough. I'm looking to find the percentage
> of
> data that would be contained in a differential backup. I could compare
> that
> with the duration of a full backup to fudge a duration estimate. For
> example, given a database of 100GB and a differential backup that would
> contain 50GB, I could then say "the differential backup would take about
> 50%
> the time of the full backup". I'm not looking for anything precise but
> rather an educated guess.
> I could do some tests and obtain a benchmark but I'm in a time crunch and
> testing is not an option at this point.
> Thanks,
> Bruce.
> "David Gugick" wrote:

How long will a differential backup take?

How can I determine how long a differential backup will take to complete? I
think reading the "differential change map" pages of the database would help,
but I don't know how to do that or even if it is possible.
Tanks,
Bruce.bpettus wrote:
> How can I determine how long a differential backup will take to
> complete? I think reading the "differential change map" pages of the
> database would help, but I don't know how to do that or even if it is
> possible.
> Tanks,
> Bruce.
Since the operation depends on the amount of data to be backed up, the
utilization on the server, the speed of the disk array, and the backup
software you are using, it's really impossible to tell. What I suggest
you do is perform some tests on the system in question and monitor disk
and CPU activity at the same time using perfmon to better give you the
estimates you need. I would also consider you look into a third-party
backup/recovery solution that implements compression to shorten the
backup period. Quest sells LiteSpeed for SQL Server. There are solutions
from other vendors as well.
--
David Gugick
Quest Software
www.quest.com|||Maybe my question wasn't clear enough. I'm looking to find the percentage of
data that would be contained in a differential backup. I could compare that
with the duration of a full backup to fudge a duration estimate. For
example, given a database of 100GB and a differential backup that would
contain 50GB, I could then say "the differential backup would take about 50%
the time of the full backup". I'm not looking for anything precise but
rather an educated guess.
I could do some tests and obtain a benchmark but I'm in a time crunch and
testing is not an option at this point.
Thanks,
Bruce.
"David Gugick" wrote:
> bpettus wrote:
> > How can I determine how long a differential backup will take to
> > complete? I think reading the "differential change map" pages of the
> > database would help, but I don't know how to do that or even if it is
> > possible.
> >
> > Tanks,
> > Bruce.
> Since the operation depends on the amount of data to be backed up, the
> utilization on the server, the speed of the disk array, and the backup
> software you are using, it's really impossible to tell. What I suggest
> you do is perform some tests on the system in question and monitor disk
> and CPU activity at the same time using perfmon to better give you the
> estimates you need. I would also consider you look into a third-party
> backup/recovery solution that implements compression to shorten the
> backup period. Quest sells LiteSpeed for SQL Server. There are solutions
> from other vendors as well.
> --
> David Gugick
> Quest Software
> www.quest.com
>|||Hi
Yes, roughly, 100/50GB gives you 50% of the time.
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/
"bpettus" <bpettus@.discussions.microsoft.com> wrote in message
news:D08FAB04-F368-4C48-9084-20DBB219273C@.microsoft.com...
> Maybe my question wasn't clear enough. I'm looking to find the percentage
> of
> data that would be contained in a differential backup. I could compare
> that
> with the duration of a full backup to fudge a duration estimate. For
> example, given a database of 100GB and a differential backup that would
> contain 50GB, I could then say "the differential backup would take about
> 50%
> the time of the full backup". I'm not looking for anything precise but
> rather an educated guess.
> I could do some tests and obtain a benchmark but I'm in a time crunch and
> testing is not an option at this point.
> Thanks,
> Bruce.
> "David Gugick" wrote:
>> bpettus wrote:
>> > How can I determine how long a differential backup will take to
>> > complete? I think reading the "differential change map" pages of the
>> > database would help, but I don't know how to do that or even if it is
>> > possible.
>> >
>> > Tanks,
>> > Bruce.
>> Since the operation depends on the amount of data to be backed up, the
>> utilization on the server, the speed of the disk array, and the backup
>> software you are using, it's really impossible to tell. What I suggest
>> you do is perform some tests on the system in question and monitor disk
>> and CPU activity at the same time using perfmon to better give you the
>> estimates you need. I would also consider you look into a third-party
>> backup/recovery solution that implements compression to shorten the
>> backup period. Quest sells LiteSpeed for SQL Server. There are solutions
>> from other vendors as well.
>> --
>> David Gugick
>> Quest Software
>> www.quest.com
>>

how long to restore

I am trying to restore an sql database it still showing SQL is currently in
the process of restoring a backup.set box
It been like this for over hour no incatation in progress bar yet.
My databse is 4gb so I except take a long time but is there something wrong
when it not showing any process or is this a bug or something? My second
question will show progress evntually or will just complete and finally apmx
how long should it take? on p4 1gb computerHi Frank,
I suggest you to go through BACKUP section in BOL. One of the parameter
called STATS gives the progress of the restore.
Thanks
Yogish

how long to restore

I am trying to restore an sql database it still showing SQL is currently in
the process of restoring a backup.set box
It been like this for over hour no incatation in progress bar yet.
My databse is 4gb so I except take a long time but is there something wrong
when it not showing any process or is this a bug or something? My second
question will show progress evntually or will just complete and finally apmx
how long should it take? on p4 1gb computer
Hi Frank,
I suggest you to go through BACKUP section in BOL. One of the parameter
called STATS gives the progress of the restore.
Thanks
Yogish

how long to restore

I am trying to restore an sql database it still showing SQL is currently in
the process of restoring a backup.set box
It been like this for over hour no incatation in progress bar yet.
My databse is 4gb so I except take a long time but is there something wrong
when it not showing any process or is this a bug or something? My second
question will show progress evntually or will just complete and finally apmx
how long should it take? on p4 1gb computerHi Frank,
I suggest you to go through BACKUP section in BOL. One of the parameter
called STATS gives the progress of the restore.
--
Thanks
Yogish

How long should a backup take ?

Backups suddenly went from about 20 minutes to 2 hours. W2K Server, 6gb, 4 1.9 P4 processors, SQL 2000 Enterprise Edition, SAN, 52 gb database. Backup is during off hours with little contention.Where are the backups going? Is it a network drive or a local array? You
should also check to be sure there isn't a failed drive in the array.
--
Andrew J. Kelly SQL MVP
"Cincy D" <anonymous@.discussions.microsoft.com> wrote in message
news:1FA8C398-37B2-4F66-9AB7-C276562E1363@.microsoft.com...
> Backups suddenly went from about 20 minutes to 2 hours. W2K Server, 6gb, 4
1.9 P4 processors, SQL 2000 Enterprise Edition, SAN, 52 gb database. Backup
is during off hours with little contention.|||It is going to a SAN.|||You should have the SAN people run diagnostics to ensure its running OK.
You might want to check to be sure they didn't reconfigure the SAN about
that same time. Can you copy a file of that size to the same location with
better results? There isn't much to a full backup that would cause slow
downs other than bandwith issues.
--
Andrew J. Kelly SQL MVP
"Cincy D" <anonymous@.discussions.microsoft.com> wrote in message
news:D81E8EB5-7677-49F4-A281-D9BF3C42552A@.microsoft.com...
> It is going to a SAN.

How long does a load take after a restore?

I performed a restore from a full backup and afterwards the database says it's loading and is greyed out insteady of being yellow. It won't let me do anything, but gives no indication how long it will take to finish loading. The database is about 950mb.
Does anyone have any idea how long it should take? It's been 3 hrs so far and I'm wanting to get back online as soon as possible.
Hi,
It seems you have resored the database with NORECOVERY option. This options
needs to be selected if you have more transaction logs to be loaded.
Incase if you do not have any tranasctions logs to load execute the below
command to make the database online:
Restore database <DBNAME> with recovery
Note:
Replace the DBNAME with the actual database name.
Thanks
Hari
MCDBA
"Curt" <byteofram@.yahoo.com> wrote in message
news:1CF913CC-57C4-40F5-AA51-1D5E387BC867@.microsoft.com...
> I performed a restore from a full backup and afterwards the database says
it's loading and is greyed out insteady of being yellow. It won't let me do
anything, but gives no indication how long it will take to finish loading.
The database is about 950mb. Does anyone have any idea how long it should
take? It's been 3 hrs so far and I'm wanting to get back online as soon as
possible.

How long does a load take after a restore?

I performed a restore from a full backup and afterwards the database says it
's loading and is greyed out insteady of being yellow. It won't let me do a
nything, but gives no indication how long it will take to finish loading. T
he database is about 950mb.
Does anyone have any idea how long it should take? It's been 3 hrs so far a
nd I'm wanting to get back online as soon as possible.Hi,
It seems you have resored the database with NORECOVERY option. This options
needs to be selected if you have more transaction logs to be loaded.
Incase if you do not have any tranasctions logs to load execute the below
command to make the database online:
Restore database <DBNAME> with recovery
Note:
Replace the DBNAME with the actual database name.
Thanks
Hari
MCDBA
"Curt" <byteofram@.yahoo.com> wrote in message
news:1CF913CC-57C4-40F5-AA51-1D5E387BC867@.microsoft.com...
> I performed a restore from a full backup and afterwards the database says
it's loading and is greyed out insteady of being yellow. It won't let me do
anything, but gives no indication how long it will take to finish loading.
The database is about 950mb. Does anyone have any idea how long it should
take? It's been 3 hrs so far and I'm wanting to get back online as soon as
possible.

How licences work in a backup computer

Hello,
I have found that if you have a clustered and log-shipping environtment you
don't have to buy licences for the passive computer. But in my environtment
I have the main server and then more computers with SQL Server installed but
not used (the service stopped), but in case of failure of the server, I can
restore the backup database on one of this other computers to continue
working.
I suppose that it's the same case than in clustered and log-shipping, if I
just use only one SQL Server at a time, I only have to buy licences for one
server. But I haven't found anything of than in Microsoft site. Does anyone
know how it works?
Thank you in advance,
Josep.The way I had it explained to me was that if the "standby" instance don't do any work normally, then
it doesn't need a license. And that this is regardless of what technology you are using. But I'd ask
an MS rep to be certain...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Josep" <jmartinez@.autec.es> wrote in message news:eWVpPoKoIHA.3900@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I have found that if you have a clustered and log-shipping environtment you don't have to buy
> licences for the passive computer. But in my environtment I have the main server and then more
> computers with SQL Server installed but not used (the service stopped), but in case of failure of
> the server, I can restore the backup database on one of this other computers to continue working.
> I suppose that it's the same case than in clustered and log-shipping, if I just use only one SQL
> Server at a time, I only have to buy licences for one server. But I haven't found anything of than
> in Microsoft site. Does anyone know how it works?
>
> Thank you in advance,
> Josep.
>

How I can restore to the certain LSN (not Point-In-Time)?

Dear Sir.
How I can restore to the certain LSN (not Point-In-Time)?
Also do you know program for viewing of Transaction Log and BackUp?
Thank you in advance.Have you taken a look at the stopatmark or stopbeforemark option in SQL2005
BOL
9ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f7b3de5b-198d-448d-8c71-1cdd9239676c.htm)?
Linchi
"RedFox" wrote:
> Dear Sir.
> How I can restore to the certain LSN (not Point-In-Time)?
> Also do you know program for viewing of Transaction Log and BackUp?
> Thank you in advance.|||Dear Linchi Shea,
Thank you for your answer, but I have asked about CERTAIN LSN (= Log
Sequence Number) - NOT MARKED Transaction.
P.S. link is not correct.
"Linchi Shea" wrote:
> Have you taken a look at the stopatmark or stopbeforemark option in SQL2005
> BOL
> 9ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f7b3de5b-198d-448d-8c71-1cdd9239676c.htm)?
> Linchi
> "RedFox" wrote:
> > Dear Sir.
> >
> > How I can restore to the certain LSN (not Point-In-Time)?
> > Also do you know program for viewing of Transaction Log and BackUp?
> >
> > Thank you in advance.|||The link I posted earlier is for BOL. You can also use this link to the Books
Online at the Microsoft site.
http://msdn2.microsoft.com/en-us/library/ms191459.aspx
> but I have asked about CERTAIN LSN (= Log
> Sequence Number) - NOT MARKED Transaction.
It has an option to allow you to restore to a log sequence number.
Linchi
"RedFox" wrote:
> Dear Linchi Shea,
> Thank you for your answer, but I have asked about CERTAIN LSN (= Log
> Sequence Number) - NOT MARKED Transaction.
> P.S. link is not correct.
> "Linchi Shea" wrote:
> > Have you taken a look at the stopatmark or stopbeforemark option in SQL2005
> > BOL
> > 9ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f7b3de5b-198d-448d-8c71-1cdd9239676c.htm)?
> >
> > Linchi
> >
> > "RedFox" wrote:
> >
> > > Dear Sir.
> > >
> > > How I can restore to the certain LSN (not Point-In-Time)?
> > > Also do you know program for viewing of Transaction Log and BackUp?
> > >
> > > Thank you in advance.

How full backup works

Hello. Take this simple backup statement:
Backup Database MyDB To Disk = N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
With Name = 'MyDB full backup'
Would you think that if MyDB.bak already existed it would be overwritten
with the new backup? Or would it double in size, effectively containing 2
backups? I've seen it both ways I think. I ask because I'm trying to backup
this monolithic DB each night and we're running out of disk space on this
command.
I've seen some talk around here of backup compression utilities. In lieu of
that would it make sense to schedule simple batch job that deletes MyDB.bak
before proceeding with the backup? I realize we could be left vulnerable if
the subsequent backup should fail.
TIA,
KenKen,
Look at BACKUP DATABASE ... WITH INIT to write over the backup file.
Not having a reliable backup scares me, so I hate to see you depending on
such an approach. Here are a couple of options:
1. Schedule a tape backup of your .BAK file during the day, between the
nightly backups.
2. Weekly BACKUP DATABASE ... WITH INIT, then nightly ... WITH DIFFERENTIAL.
Then maybe you can fit a week's worth of backups on the disk. (Do the
database backup on the weekend if nothing much is happening then. Then, if
it fails, you can get a second shot at it before a lot of work happens.)
3. But better is to buy enough disk space for the needed backups. Use a
maintenance plan (whether SQL Server Maintenance Plan or one you roll
yourself) that will name each file with the datetime and a plan to delete
the backups that are older than you want to keep.
RLF
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:39070F38-0083-4253-B29C-BEBFEFBE6915@.microsoft.com...
> Hello. Take this simple backup statement:
> Backup Database MyDB To Disk = N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
> With Name = 'MyDB full backup'
> Would you think that if MyDB.bak already existed it would be overwritten
> with the new backup? Or would it double in size, effectively containing 2
> backups? I've seen it both ways I think. I ask because I'm trying to
> backup
> this monolithic DB each night and we're running out of disk space on this
> command.
> I've seen some talk around here of backup compression utilities. In lieu
> of
> that would it make sense to schedule simple batch job that deletes
> MyDB.bak
> before proceeding with the backup? I realize we could be left vulnerable
> if
> the subsequent backup should fail.
> TIA,
> Ken|||It won't work one way one time and a different way the next if the commands
are always the same. If you don't specify the INIT option the default is
NOINIT which means to append. Decide which way you need it to be and specify
it even if it is the default behavior so there is no mistake down the road.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:39070F38-0083-4253-B29C-BEBFEFBE6915@.microsoft.com...
> Hello. Take this simple backup statement:
> Backup Database MyDB To Disk = N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
> With Name = 'MyDB full backup'
> Would you think that if MyDB.bak already existed it would be overwritten
> with the new backup? Or would it double in size, effectively containing 2
> backups? I've seen it both ways I think. I ask because I'm trying to
> backup
> this monolithic DB each night and we're running out of disk space on this
> command.
> I've seen some talk around here of backup compression utilities. In lieu
> of
> that would it make sense to schedule simple batch job that deletes
> MyDB.bak
> before proceeding with the backup? I realize we could be left vulnerable
> if
> the subsequent backup should fail.
> TIA,
> Ken|||Hi there,
Default action of BACKUP command is "NOINIT" which appends to the existing
backup set. If you want to overwrite the backup set,
then you could use "WITH INIT" param.
In your case (to overwrite the existed backup set):
Backup Database MyDB To Disk = N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
With Name = 'MyDB full backup', INIT
Ekrem Ã?nsoy
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:39070F38-0083-4253-B29C-BEBFEFBE6915@.microsoft.com...
> Hello. Take this simple backup statement:
> Backup Database MyDB To Disk = N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
> With Name = 'MyDB full backup'
> Would you think that if MyDB.bak already existed it would be overwritten
> with the new backup? Or would it double in size, effectively containing 2
> backups? I've seen it both ways I think. I ask because I'm trying to
> backup
> this monolithic DB each night and we're running out of disk space on this
> command.
> I've seen some talk around here of backup compression utilities. In lieu
> of
> that would it make sense to schedule simple batch job that deletes
> MyDB.bak
> before proceeding with the backup? I realize we could be left vulnerable
> if
> the subsequent backup should fail.
> TIA,
> Ken|||Shoot, I glossed right over that param when looking at BOL. Thanks guys for
pointing it out. In fact I'm rolling my own to do what you suggest in #2, do
differentials 6 days a week and full on the 7th.
BTW, it's nice that SQL Server can do differential backups. having said that
they don't appear to be totally efficient. Each day our data stroe grows a
little but the differential backup grows a lot.
Ken
"Russell Fields" wrote:
> Ken,
> Look at BACKUP DATABASE ... WITH INIT to write over the backup file.
> Not having a reliable backup scares me, so I hate to see you depending on
> such an approach. Here are a couple of options:
> 1. Schedule a tape backup of your .BAK file during the day, between the
> nightly backups.
> 2. Weekly BACKUP DATABASE ... WITH INIT, then nightly ... WITH DIFFERENTIAL.
> Then maybe you can fit a week's worth of backups on the disk. (Do the
> database backup on the weekend if nothing much is happening then. Then, if
> it fails, you can get a second shot at it before a lot of work happens.)
> 3. But better is to buy enough disk space for the needed backups. Use a
> maintenance plan (whether SQL Server Maintenance Plan or one you roll
> yourself) that will name each file with the datetime and a plan to delete
> the backups that are older than you want to keep.
> RLF
>
> "ktrock" <ktrock@.discussions.microsoft.com> wrote in message
> news:39070F38-0083-4253-B29C-BEBFEFBE6915@.microsoft.com...
> > Hello. Take this simple backup statement:
> >
> > Backup Database MyDB To Disk = N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
> > With Name = 'MyDB full backup'
> >
> > Would you think that if MyDB.bak already existed it would be overwritten
> > with the new backup? Or would it double in size, effectively containing 2
> > backups? I've seen it both ways I think. I ask because I'm trying to
> > backup
> > this monolithic DB each night and we're running out of disk space on this
> > command.
> >
> > I've seen some talk around here of backup compression utilities. In lieu
> > of
> > that would it make sense to schedule simple batch job that deletes
> > MyDB.bak
> > before proceeding with the backup? I realize we could be left vulnerable
> > if
> > the subsequent backup should fail.
> >
> > TIA,
> > Ken
>
>|||Ken,
Differentials are cumulative. So, assuming a Sunday full backup and daily
differentials, the Wednesday differential contains the Monday, Tuesday, and
Wednesday changes. If you know that you never have to go back in time more
than a couple of days, you can start timing them out.
RLF
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:88947E5E-4D56-4CCA-9114-C934A18F14B0@.microsoft.com...
> Shoot, I glossed right over that param when looking at BOL. Thanks guys
> for
> pointing it out. In fact I'm rolling my own to do what you suggest in #2,
> do
> differentials 6 days a week and full on the 7th.
> BTW, it's nice that SQL Server can do differential backups. having said
> that
> they don't appear to be totally efficient. Each day our data stroe grows a
> little but the differential backup grows a lot.
> Ken
>
> "Russell Fields" wrote:
>> Ken,
>> Look at BACKUP DATABASE ... WITH INIT to write over the backup file.
>> Not having a reliable backup scares me, so I hate to see you depending on
>> such an approach. Here are a couple of options:
>> 1. Schedule a tape backup of your .BAK file during the day, between the
>> nightly backups.
>> 2. Weekly BACKUP DATABASE ... WITH INIT, then nightly ... WITH
>> DIFFERENTIAL.
>> Then maybe you can fit a week's worth of backups on the disk. (Do the
>> database backup on the weekend if nothing much is happening then. Then,
>> if
>> it fails, you can get a second shot at it before a lot of work happens.)
>> 3. But better is to buy enough disk space for the needed backups. Use a
>> maintenance plan (whether SQL Server Maintenance Plan or one you roll
>> yourself) that will name each file with the datetime and a plan to delete
>> the backups that are older than you want to keep.
>> RLF
>>
>> "ktrock" <ktrock@.discussions.microsoft.com> wrote in message
>> news:39070F38-0083-4253-B29C-BEBFEFBE6915@.microsoft.com...
>> > Hello. Take this simple backup statement:
>> >
>> > Backup Database MyDB To Disk =>> > N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
>> > With Name = 'MyDB full backup'
>> >
>> > Would you think that if MyDB.bak already existed it would be
>> > overwritten
>> > with the new backup? Or would it double in size, effectively containing
>> > 2
>> > backups? I've seen it both ways I think. I ask because I'm trying to
>> > backup
>> > this monolithic DB each night and we're running out of disk space on
>> > this
>> > command.
>> >
>> > I've seen some talk around here of backup compression utilities. In
>> > lieu
>> > of
>> > that would it make sense to schedule simple batch job that deletes
>> > MyDB.bak
>> > before proceeding with the backup? I realize we could be left
>> > vulnerable
>> > if
>> > the subsequent backup should fail.
>> >
>> > TIA,
>> > Ken
>>|||In addition to what Russell stated the Diff's backup at the Extent (64K)
level not the row. Meaning that if 1 bit changes on the extent you get the
entire extent in the Diff backup. So they can tend to be much larger than
the changes would imply.
If you are running out of disk space for backups I suggest you purchase one
of the 3rd party backup compression utilities. They start as low as a few
hundred $ and can compress the backups as much as 80% or more. Not to
mention they are generally faster than native backups as well.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23KNsytz6HHA.2752@.TK2MSFTNGP06.phx.gbl...
> Ken,
> Differentials are cumulative. So, assuming a Sunday full backup and daily
> differentials, the Wednesday differential contains the Monday, Tuesday,
> and Wednesday changes. If you know that you never have to go back in time
> more than a couple of days, you can start timing them out.
> RLF
>
> "ktrock" <ktrock@.discussions.microsoft.com> wrote in message
> news:88947E5E-4D56-4CCA-9114-C934A18F14B0@.microsoft.com...
>> Shoot, I glossed right over that param when looking at BOL. Thanks guys
>> for
>> pointing it out. In fact I'm rolling my own to do what you suggest in #2,
>> do
>> differentials 6 days a week and full on the 7th.
>> BTW, it's nice that SQL Server can do differential backups. having said
>> that
>> they don't appear to be totally efficient. Each day our data stroe grows
>> a
>> little but the differential backup grows a lot.
>> Ken
>>
>> "Russell Fields" wrote:
>> Ken,
>> Look at BACKUP DATABASE ... WITH INIT to write over the backup file.
>> Not having a reliable backup scares me, so I hate to see you depending
>> on
>> such an approach. Here are a couple of options:
>> 1. Schedule a tape backup of your .BAK file during the day, between the
>> nightly backups.
>> 2. Weekly BACKUP DATABASE ... WITH INIT, then nightly ... WITH
>> DIFFERENTIAL.
>> Then maybe you can fit a week's worth of backups on the disk. (Do the
>> database backup on the weekend if nothing much is happening then. Then,
>> if
>> it fails, you can get a second shot at it before a lot of work happens.)
>> 3. But better is to buy enough disk space for the needed backups. Use a
>> maintenance plan (whether SQL Server Maintenance Plan or one you roll
>> yourself) that will name each file with the datetime and a plan to
>> delete
>> the backups that are older than you want to keep.
>> RLF
>>
>> "ktrock" <ktrock@.discussions.microsoft.com> wrote in message
>> news:39070F38-0083-4253-B29C-BEBFEFBE6915@.microsoft.com...
>> > Hello. Take this simple backup statement:
>> >
>> > Backup Database MyDB To Disk =>> > N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
>> > With Name = 'MyDB full backup'
>> >
>> > Would you think that if MyDB.bak already existed it would be
>> > overwritten
>> > with the new backup? Or would it double in size, effectively
>> > containing 2
>> > backups? I've seen it both ways I think. I ask because I'm trying to
>> > backup
>> > this monolithic DB each night and we're running out of disk space on
>> > this
>> > command.
>> >
>> > I've seen some talk around here of backup compression utilities. In
>> > lieu
>> > of
>> > that would it make sense to schedule simple batch job that deletes
>> > MyDB.bak
>> > before proceeding with the backup? I realize we could be left
>> > vulnerable
>> > if
>> > the subsequent backup should fail.
>> >
>> > TIA,
>> > Ken
>>
>

How full backup work ?

that what happens if during a full backup a transaction is started or a
transaction is running?
Data modified will be saved ?
It exists a microsoft document that explain how full backup work ?
ThanksThe full backup will contain ALL of the transactions which ended PRIOR to
the completion of the backup. This is because right before the end of the
backup, it goes back to the log and captures the changes made during the
backup.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"andrea favero" <andreafavero@.discussions.microsoft.com> wrote in message
news:052B33CA-065C-4A46-9CBB-0B5CEA2DE933@.microsoft.com...
> that what happens if during a full backup a transaction is started or a
> transaction is running?
> Data modified will be saved ?
> It exists a microsoft document that explain how full backup work ?
> Thanks|||Where I can find documentation that explain in deep "how work full backup" ?
Thanks
"Wayne Snyder" wrote:
> The full backup will contain ALL of the transactions which ended PRIOR to
> the completion of the backup. This is because right before the end of the
> backup, it goes back to the log and captures the changes made during the
> backup.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "andrea favero" <andreafavero@.discussions.microsoft.com> wrote in message
> news:052B33CA-065C-4A46-9CBB-0B5CEA2DE933@.microsoft.com...
> > that what happens if during a full backup a transaction is started or a
> > transaction is running?
> > Data modified will be saved ?
> > It exists a microsoft document that explain how full backup work ?
> >
> > Thanks
>
>

How full backup work ?

that what happens if during a full backup a transaction is started or a
transaction is running?
Data modified will be saved ?
It exists a microsoft document that explain how full backup work ?
Thanks
The full backup will contain ALL of the transactions which ended PRIOR to
the completion of the backup. This is because right before the end of the
backup, it goes back to the log and captures the changes made during the
backup.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"andrea favero" <andreafavero@.discussions.microsoft.com> wrote in message
news:052B33CA-065C-4A46-9CBB-0B5CEA2DE933@.microsoft.com...
> that what happens if during a full backup a transaction is started or a
> transaction is running?
> Data modified will be saved ?
> It exists a microsoft document that explain how full backup work ?
> Thanks
|||Where I can find documentation that explain in deep "how work full backup" ?
Thanks
"Wayne Snyder" wrote:

> The full backup will contain ALL of the transactions which ended PRIOR to
> the completion of the backup. This is because right before the end of the
> backup, it goes back to the log and captures the changes made during the
> backup.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "andrea favero" <andreafavero@.discussions.microsoft.com> wrote in message
> news:052B33CA-065C-4A46-9CBB-0B5CEA2DE933@.microsoft.com...
>
>