Database Backups & Restores: an in-depth knowledge required

I have two servers.
Server A:
Dual 2.8 Xeon Processor
4 GB RAM
SQL Server 2000 Enterprise
Server B
Single 3.0 Xeon Processor
2.5 GB RAM
SQL Server 2000 Standard
I have an identical database on both. The backup file is 1.22 GB, the data
file is 1.44 GB and the log file is 504 KB.
When I restore these databases, the restore on server A takes 2.58 minutes,
but on server B it takes 6.48 minutes (over twice as long).
Similar discrepancies are found when I backup these databases.
I don't understand why!
Studying task manager shows that the CPU usage is minimal and the amount of
memory used is minimal, presumably ruling out the speed of the processors
and the amount of RAM available. If I had multiple data devices then I
could see the Enterprise version being quicker because it could create more
threads for parallel processing, but I don't so this should rule out the
Enterprise/Standard differences. Oh and both servers run on multiple
spindle, fast RAID5+1 disks, and these are identical.
So...what is occurring that actually takes the ~ 3 minutes or ~ 7 minutes?
Many thanks
Griff
What are the server manufacturer and model numbers? What are the FSB
speeds? What are the I/O controller speeds? What is the System:Worker
Queue counter throughput? What are the Physical/Logical disk counter
throughput?
Sincerely,
Anthony Thomas

"Griff" <Howling@.The.Moon> wrote in message
news:%23wUo43l8EHA.1564@.TK2MSFTNGP09.phx.gbl...
I have two servers.
Server A:
Dual 2.8 Xeon Processor
4 GB RAM
SQL Server 2000 Enterprise
Server B
Single 3.0 Xeon Processor
2.5 GB RAM
SQL Server 2000 Standard
I have an identical database on both. The backup file is 1.22 GB, the data
file is 1.44 GB and the log file is 504 KB.
When I restore these databases, the restore on server A takes 2.58 minutes,
but on server B it takes 6.48 minutes (over twice as long).
Similar discrepancies are found when I backup these databases.
I don't understand why!
Studying task manager shows that the CPU usage is minimal and the amount of
memory used is minimal, presumably ruling out the speed of the processors
and the amount of RAM available. If I had multiple data devices then I
could see the Enterprise version being quicker because it could create more
threads for parallel processing, but I don't so this should rule out the
Enterprise/Standard differences. Oh and both servers run on multiple
spindle, fast RAID5+1 disks, and these are identical.
So...what is occurring that actually takes the ~ 3 minutes or ~ 7 minutes?
Many thanks
Griff
|||
> What are the server manufacturer and model numbers? What are the FSB
> speeds? What are the I/O controller speeds? What is the System:Worker
> Queue counter throughput? What are the Physical/Logical disk counter
> throughput?
Gosh !
Not sure I can provide these answers. I know that they're both using RAID
5+1 10,000 RMP disks with independent raid controllers. The only
significant difference between the two could be the FSB (one is 800, other
is likely to be 533, but not sure how to identify this).
Whilst I could spend time providing these statistics, I may be more
profitable if I could identify what the real bottle-necks really are (if all
the ones you've mentioned, then in which order) and would they account for a
doubling of time?
Thanks
Griff
|||Well perhaps it's your specs
btw u can run CPUZ to identify bus speeds etc.
If the machines have same quality of equipment and are configured similarly
then I would suggest your ram and dual CPU vs single CPU
If you run the profiler on them as they are backing perhaps you will see
something like mem utilisation etc.
Another thought is how much are the files fragmented the dbs arent huge but
there may be a degree of fragmentation on the file system which slows things
down.
Also how are you restoring from Server A to Server B?
"Griff" <Howling@.The.Moon> wrote in message
news:%23wUo43l8EHA.1564@.TK2MSFTNGP09.phx.gbl...
> I have two servers.
> Server A:
> Dual 2.8 Xeon Processor
> 4 GB RAM
> SQL Server 2000 Enterprise
> Server B
> Single 3.0 Xeon Processor
> 2.5 GB RAM
> SQL Server 2000 Standard
> I have an identical database on both. The backup file is 1.22 GB, the
data
> file is 1.44 GB and the log file is 504 KB.
> When I restore these databases, the restore on server A takes 2.58
minutes,
> but on server B it takes 6.48 minutes (over twice as long).
> Similar discrepancies are found when I backup these databases.
> I don't understand why!
> Studying task manager shows that the CPU usage is minimal and the amount
of
> memory used is minimal, presumably ruling out the speed of the processors
> and the amount of RAM available. If I had multiple data devices then I
> could see the Enterprise version being quicker because it could create
more
> threads for parallel processing, but I don't so this should rule out the
> Enterprise/Standard differences. Oh and both servers run on multiple
> spindle, fast RAID5+1 disks, and these are identical.
> So...what is occurring that actually takes the ~ 3 minutes or ~ 7
minutes?
> Many thanks
> Griff
>
>
|||Well, you asked for possibilities, these are only a few, and they were
static, physical configuration differences. Then you have the runtime
environment differences.
I guess my point is that you are trying to compare apples to oranges. If
you run the backups several times in a row, over and over, on both boxes,
and then average out the times, do you then still see the same magnitude of
difference?
I would be concerned if you were running two identical servers with the
identical work load, but, in this case, you are running under very different
circumstances and it is these differences that will add up to what you are
experiencing.
Howerver, I suspect that the larger server has more robust subsystem
components. CPU speed is only one criterion you look at one analyzing a
server's performance. Moreover, what is the memory bus speed and flavor of
memory? DDR at 2800 or higher vs. only a PC 233. Obviously, they are
probably very similar, but I hope you get my point.
Sincerely,
Anthony Thomas

"Griff" <Howling@.The.Moon> wrote in message
news:O$1a2Xm8EHA.3236@.TK2MSFTNGP15.phx.gbl...

> What are the server manufacturer and model numbers? What are the FSB
> speeds? What are the I/O controller speeds? What is the System:Worker
> Queue counter throughput? What are the Physical/Logical disk counter
> throughput?
Gosh !
Not sure I can provide these answers. I know that they're both using RAID
5+1 10,000 RMP disks with independent raid controllers. The only
significant difference between the two could be the FSB (one is 800, other
is likely to be 533, but not sure how to identify this).
Whilst I could spend time providing these statistics, I may be more
profitable if I could identify what the real bottle-necks really are (if all
the ones you've mentioned, then in which order) and would they account for a
doubling of time?
Thanks
Griff
|||Got the latest version of CPU-Z
The slow machine...it won't report the BUS speeds (in fact, it reports very
little).
As for how I restore...I just use Enterprise Manager and do it from there.
Griff
|||Griff,
What I was referring to was where is the backup device you're restoring
from?
If one is local (ie on disk) and the other is over the network then you will
see slower restores.
TBH I suspect the single CPU job is not upto the same performance and you
are realising this in what you're doing.
evan
"Griff" <Howling@.The.Moon> wrote in message
news:edUZHjn8EHA.3640@.tk2msftngp13.phx.gbl...
> Got the latest version of CPU-Z
> The slow machine...it won't report the BUS speeds (in fact, it reports
very
> little).
> As for how I restore...I just use Enterprise Manager and do it from there.
> Griff
>
|||> What I was referring to was where is the backup device you're restoring
> from?
> If one is local (ie on disk) and the other is over the network then you
will
> see slower restores.
Oh, sorry. Everything is local...I tried to make the two tests as
identical as possible

> TBH I suspect the single CPU job is not upto the same performance and you
> are realising this in what you're doing.
Okay. Thanks for your help with this Evan
Griff

Database Backups & Restores: an in-depth knowledge required

I have two servers.
Server A:
Dual 2.8 Xeon Processor
4 GB RAM
SQL Server 2000 Enterprise
Server B
Single 3.0 Xeon Processor
2.5 GB RAM
SQL Server 2000 Standard
I have an identical database on both. The backup file is 1.22 GB, the data
file is 1.44 GB and the log file is 504 KB.
When I restore these databases, the restore on server A takes 2.58 minutes,
but on server B it takes 6.48 minutes (over twice as long).
Similar discrepancies are found when I backup these databases.
I don't understand why!
Studying task manager shows that the CPU usage is minimal and the amount of
memory used is minimal, presumably ruling out the speed of the processors
and the amount of RAM available. If I had multiple data devices then I
could see the Enterprise version being quicker because it could create more
threads for parallel processing, but I don't so this should rule out the
Enterprise/Standard differences. Oh and both servers run on multiple
spindle, fast RAID5+1 disks, and these are identical.
So...what is occurring that actually takes the ~ 3 minutes or ~ 7 minutes?
Many thanks
GriffWhat are the server manufacturer and model numbers? What are the FSB
speeds? What are the I/O controller speeds? What is the System:Worker
Queue counter throughput? What are the Physical/Logical disk counter
throughput?
Sincerely,
Anthony Thomas
"Griff" <Howling@.The.Moon> wrote in message
news:%23wUo43l8EHA.1564@.TK2MSFTNGP09.phx.gbl...
I have two servers.
Server A:
Dual 2.8 Xeon Processor
4 GB RAM
SQL Server 2000 Enterprise
Server B
Single 3.0 Xeon Processor
2.5 GB RAM
SQL Server 2000 Standard
I have an identical database on both. The backup file is 1.22 GB, the data
file is 1.44 GB and the log file is 504 KB.
When I restore these databases, the restore on server A takes 2.58 minutes,
but on server B it takes 6.48 minutes (over twice as long).
Similar discrepancies are found when I backup these databases.
I don't understand why!
Studying task manager shows that the CPU usage is minimal and the amount of
memory used is minimal, presumably ruling out the speed of the processors
and the amount of RAM available. If I had multiple data devices then I
could see the Enterprise version being quicker because it could create more
threads for parallel processing, but I don't so this should rule out the
Enterprise/Standard differences. Oh and both servers run on multiple
spindle, fast RAID5+1 disks, and these are identical.
So...what is occurring that actually takes the ~ 3 minutes or ~ 7 minutes?
Many thanks
Griff|||> What are the server manufacturer and model numbers? What are the FSB
> speeds? What are the I/O controller speeds? What is the System:Worker
> Queue counter throughput? What are the Physical/Logical disk counter
> throughput?
Gosh !
Not sure I can provide these answers. I know that they're both using RAID
5+1 10,000 RMP disks with independent raid controllers. The only
significant difference between the two could be the FSB (one is 800, other
is likely to be 533, but not sure how to identify this).
Whilst I could spend time providing these statistics, I may be more
profitable if I could identify what the real bottle-necks really are (if all
the ones you've mentioned, then in which order) and would they account for a
doubling of time?
Thanks
Griff|||Well perhaps it's your specs
btw u can run CPUZ to identify bus speeds etc.
If the machines have same quality of equipment and are configured similarly
then I would suggest your ram and dual CPU vs single CPU
If you run the profiler on them as they are backing perhaps you will see
something like mem utilisation etc.
Another thought is how much are the files fragmented the dbs arent huge but
there may be a degree of fragmentation on the file system which slows things
down.
Also how are you restoring from Server A to Server B?
"Griff" <Howling@.The.Moon> wrote in message
news:%23wUo43l8EHA.1564@.TK2MSFTNGP09.phx.gbl...
> I have two servers.
> Server A:
> Dual 2.8 Xeon Processor
> 4 GB RAM
> SQL Server 2000 Enterprise
> Server B
> Single 3.0 Xeon Processor
> 2.5 GB RAM
> SQL Server 2000 Standard
> I have an identical database on both. The backup file is 1.22 GB, the
data
> file is 1.44 GB and the log file is 504 KB.
> When I restore these databases, the restore on server A takes 2.58
minutes,
> but on server B it takes 6.48 minutes (over twice as long).
> Similar discrepancies are found when I backup these databases.
> I don't understand why!
> Studying task manager shows that the CPU usage is minimal and the amount
of
> memory used is minimal, presumably ruling out the speed of the processors
> and the amount of RAM available. If I had multiple data devices then I
> could see the Enterprise version being quicker because it could create
more
> threads for parallel processing, but I don't so this should rule out the
> Enterprise/Standard differences. Oh and both servers run on multiple
> spindle, fast RAID5+1 disks, and these are identical.
> So...what is occurring that actually takes the ~ 3 minutes or ~ 7
minutes?
> Many thanks
> Griff
>
>|||Well, you asked for possibilities, these are only a few, and they were
static, physical configuration differences. Then you have the runtime
environment differences.
I guess my point is that you are trying to compare apples to oranges. If
you run the backups several times in a row, over and over, on both boxes,
and then average out the times, do you then still see the same magnitude of
difference?
I would be concerned if you were running two identical servers with the
identical work load, but, in this case, you are running under very different
circumstances and it is these differences that will add up to what you are
experiencing.
Howerver, I suspect that the larger server has more robust subsystem
components. CPU speed is only one criterion you look at one analyzing a
server's performance. Moreover, what is the memory bus speed and flavor of
memory? DDR at 2800 or higher vs. only a PC 233. Obviously, they are
probably very similar, but I hope you get my point.
Sincerely,
Anthony Thomas
"Griff" <Howling@.The.Moon> wrote in message
news:O$1a2Xm8EHA.3236@.TK2MSFTNGP15.phx.gbl...
> What are the server manufacturer and model numbers? What are the FSB
> speeds? What are the I/O controller speeds? What is the System:Worker
> Queue counter throughput? What are the Physical/Logical disk counter
> throughput?
Gosh !
Not sure I can provide these answers. I know that they're both using RAID
5+1 10,000 RMP disks with independent raid controllers. The only
significant difference between the two could be the FSB (one is 800, other
is likely to be 533, but not sure how to identify this).
Whilst I could spend time providing these statistics, I may be more
profitable if I could identify what the real bottle-necks really are (if all
the ones you've mentioned, then in which order) and would they account for a
doubling of time?
Thanks
Griff|||Got the latest version of CPU-Z
The slow machine...it won't report the BUS speeds (in fact, it reports very
little).
As for how I restore...I just use Enterprise Manager and do it from there.
Griff|||Griff,
What I was referring to was where is the backup device you're restoring
from?
If one is local (ie on disk) and the other is over the network then you will
see slower restores.
TBH I suspect the single CPU job is not upto the same performance and you
are realising this in what you're doing.
evan
"Griff" <Howling@.The.Moon> wrote in message
news:edUZHjn8EHA.3640@.tk2msftngp13.phx.gbl...
> Got the latest version of CPU-Z
> The slow machine...it won't report the BUS speeds (in fact, it reports
very
> little).
> As for how I restore...I just use Enterprise Manager and do it from there.
> Griff
>|||> What I was referring to was where is the backup device you're restoring
> from?
> If one is local (ie on disk) and the other is over the network then you
will
> see slower restores.
Oh, sorry. Everything is local...I tried to make the two tests as
identical as possible
> TBH I suspect the single CPU job is not upto the same performance and you
> are realising this in what you're doing.
Okay. Thanks for your help with this Evan
Griffsql

Database Backups & Restores: an in-depth knowledge required

I have two servers.
Server A:
Dual 2.8 Xeon Processor
4 GB RAM
SQL Server 2000 Enterprise
Server B
Single 3.0 Xeon Processor
2.5 GB RAM
SQL Server 2000 Standard
I have an identical database on both. The backup file is 1.22 GB, the data
file is 1.44 GB and the log file is 504 KB.
When I restore these databases, the restore on server A takes 2.58 minutes,
but on server B it takes 6.48 minutes (over twice as long).
Similar discrepancies are found when I backup these databases.
I don't understand why!
Studying task manager shows that the CPU usage is minimal and the amount of
memory used is minimal, presumably ruling out the speed of the processors
and the amount of RAM available. If I had multiple data devices then I
could see the Enterprise version being quicker because it could create more
threads for parallel processing, but I don't so this should rule out the
Enterprise/Standard differences. Oh and both servers run on multiple
spindle, fast RAID5+1 disks, and these are identical.
So...what is occurring that actually takes the ~ 3 minutes or ~ 7 minutes?
Many thanks
GriffWhat are the server manufacturer and model numbers? What are the FSB
speeds? What are the I/O controller speeds? What is the System:Worker
Queue counter throughput? What are the Physical/Logical disk counter
throughput?
Sincerely,
Anthony Thomas
"Griff" <Howling@.The.Moon> wrote in message
news:%23wUo43l8EHA.1564@.TK2MSFTNGP09.phx.gbl...
I have two servers.
Server A:
Dual 2.8 Xeon Processor
4 GB RAM
SQL Server 2000 Enterprise
Server B
Single 3.0 Xeon Processor
2.5 GB RAM
SQL Server 2000 Standard
I have an identical database on both. The backup file is 1.22 GB, the data
file is 1.44 GB and the log file is 504 KB.
When I restore these databases, the restore on server A takes 2.58 minutes,
but on server B it takes 6.48 minutes (over twice as long).
Similar discrepancies are found when I backup these databases.
I don't understand why!
Studying task manager shows that the CPU usage is minimal and the amount of
memory used is minimal, presumably ruling out the speed of the processors
and the amount of RAM available. If I had multiple data devices then I
could see the Enterprise version being quicker because it could create more
threads for parallel processing, but I don't so this should rule out the
Enterprise/Standard differences. Oh and both servers run on multiple
spindle, fast RAID5+1 disks, and these are identical.
So...what is occurring that actually takes the ~ 3 minutes or ~ 7 minutes?
Many thanks
Griff|||
> What are the server manufacturer and model numbers? What are the FSB
> speeds? What are the I/O controller speeds? What is the System:Worker
> Queue counter throughput? What are the Physical/Logical disk counter
> throughput?
Gosh !
Not sure I can provide these answers. I know that they're both using RAID
5+1 10,000 RMP disks with independent raid controllers. The only
significant difference between the two could be the FSB (one is 800, other
is likely to be 533, but not sure how to identify this).
Whilst I could spend time providing these statistics, I may be more
profitable if I could identify what the real bottle-necks really are (if all
the ones you've mentioned, then in which order) and would they account for a
doubling of time?
Thanks
Griff|||Well perhaps it's your specs
btw u can run CPUZ to identify bus speeds etc.
If the machines have same quality of equipment and are configured similarly
then I would suggest your ram and dual CPU vs single CPU
If you run the profiler on them as they are backing perhaps you will see
something like mem utilisation etc.
Another thought is how much are the files fragmented the dbs arent huge but
there may be a degree of fragmentation on the file system which slows things
down.
Also how are you restoring from Server A to Server B?
"Griff" <Howling@.The.Moon> wrote in message
news:%23wUo43l8EHA.1564@.TK2MSFTNGP09.phx.gbl...
> I have two servers.
> Server A:
> Dual 2.8 Xeon Processor
> 4 GB RAM
> SQL Server 2000 Enterprise
> Server B
> Single 3.0 Xeon Processor
> 2.5 GB RAM
> SQL Server 2000 Standard
> I have an identical database on both. The backup file is 1.22 GB, the
data
> file is 1.44 GB and the log file is 504 KB.
> When I restore these databases, the restore on server A takes 2.58
minutes,
> but on server B it takes 6.48 minutes (over twice as long).
> Similar discrepancies are found when I backup these databases.
> I don't understand why!
> Studying task manager shows that the CPU usage is minimal and the amount
of
> memory used is minimal, presumably ruling out the speed of the processors
> and the amount of RAM available. If I had multiple data devices then I
> could see the Enterprise version being quicker because it could create
more
> threads for parallel processing, but I don't so this should rule out the
> Enterprise/Standard differences. Oh and both servers run on multiple
> spindle, fast RAID5+1 disks, and these are identical.
> So...what is occurring that actually takes the ~ 3 minutes or ~ 7
minutes?
> Many thanks
> Griff
>
>|||Well, you asked for possibilities, these are only a few, and they were
static, physical configuration differences. Then you have the runtime
environment differences.
I guess my point is that you are trying to compare apples to oranges. If
you run the backups several times in a row, over and over, on both boxes,
and then average out the times, do you then still see the same magnitude of
difference?
I would be concerned if you were running two identical servers with the
identical work load, but, in this case, you are running under very different
circumstances and it is these differences that will add up to what you are
experiencing.
Howerver, I suspect that the larger server has more robust subsystem
components. CPU speed is only one criterion you look at one analyzing a
server's performance. Moreover, what is the memory bus speed and flavor of
memory? DDR at 2800 or higher vs. only a PC 233. Obviously, they are
probably very similar, but I hope you get my point.
Sincerely,
Anthony Thomas
"Griff" <Howling@.The.Moon> wrote in message
news:O$1a2Xm8EHA.3236@.TK2MSFTNGP15.phx.gbl...

> What are the server manufacturer and model numbers? What are the FSB
> speeds? What are the I/O controller speeds? What is the System:Worker
> Queue counter throughput? What are the Physical/Logical disk counter
> throughput?
Gosh !
Not sure I can provide these answers. I know that they're both using RAID
5+1 10,000 RMP disks with independent raid controllers. The only
significant difference between the two could be the FSB (one is 800, other
is likely to be 533, but not sure how to identify this).
Whilst I could spend time providing these statistics, I may be more
profitable if I could identify what the real bottle-necks really are (if all
the ones you've mentioned, then in which order) and would they account for a
doubling of time?
Thanks
Griff|||Got the latest version of CPU-Z
The slow machine...it won't report the BUS speeds (in fact, it reports very
little).
As for how I restore...I just use Enterprise Manager and do it from there.
Griff|||Griff,
What I was referring to was where is the backup device you're restoring
from?
If one is local (ie on disk) and the other is over the network then you will
see slower restores.
TBH I suspect the single CPU job is not upto the same performance and you
are realising this in what you're doing.
evan
"Griff" <Howling@.The.Moon> wrote in message
news:edUZHjn8EHA.3640@.tk2msftngp13.phx.gbl...
> Got the latest version of CPU-Z
> The slow machine...it won't report the BUS speeds (in fact, it reports
very
> little).
> As for how I restore...I just use Enterprise Manager and do it from there.
> Griff
>|||> What I was referring to was where is the backup device you're restoring
> from?
> If one is local (ie on disk) and the other is over the network then you
will
> see slower restores.
Oh, sorry. Everything is local...I tried to make the two tests as
identical as possible

> TBH I suspect the single CPU job is not upto the same performance and you
> are realising this in what you're doing.
Okay. Thanks for your help with this Evan
Griff

Database (Standby / Read Only)

Hi all,
This morning when I tried to test my restores, they all went well except for
one Database which when restored went into a "Standby / Read Only" mode, its
greyed out and I can't for the life of me get it to go back to Normal.
I have googled, searched through the two MS SQL 2005 books I have but I cant
seem to get any answer to why it happend or more importantly how to fix the
problem.
The only options I am given when I right click the Dbs is to restore the
transaction log but when I navigate to the my backup (which I restored from)
it wont show any logs. I get a " You must select a restore Source" error
message. But I am pointing to a *.trn file.
Im not a dba by any means more of a babysitter so this could be something
simple.
Could anyone shed some light please.Hello Adrian!
There are three Recovery State when you perform a restore operation.
You can choose one of them while restoring from GUI or T-SQL.
If you want to use GUI (as you said you do not know much about SQL Server,
I'll tell you this way) then go from the following path:
Open SQL Server Management Studio, connect to your instance and right click
on the Databases and click Restore Database.
Type some name to "To database:" or choose one. Click "From device" and go
find your *.bak (backup) file. (Do not forget to check "Restore" checkbox
from the backup sets listbox.
Go to Options and from the Recovery State (which is at the bottom of the
pane) be sure you select the first one (RESTORE WITH RECOVERY).
When you set this option as RESTORE WITH NORECOVERY, then your database will
be inaccessible.
When you set it as RESTORE WITH STANDBY, then your users can use this
database for reporting purposes. But they can not use UPDATE and DELETE,
they can only use SELECT against your database in STANDBY mode.
You can learn more about "Backing Up and Restoring Databases in SQL Server"
from BOL:
http://msdn2.microsoft.com/en-us/library/ms187048.aspx
Ekrem Ã?nsoy
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:88722291-F62F-4A91-96F5-91D9FCDD8EF5@.microsoft.com...
> Hi all,
> This morning when I tried to test my restores, they all went well except
> for
> one Database which when restored went into a "Standby / Read Only" mode,
> its
> greyed out and I can't for the life of me get it to go back to Normal.
> I have googled, searched through the two MS SQL 2005 books I have but I
> cant
> seem to get any answer to why it happend or more importantly how to fix
> the
> problem.
> The only options I am given when I right click the Dbs is to restore the
> transaction log but when I navigate to the my backup (which I restored
> from)
> it wont show any logs. I get a " You must select a restore Source" error
> message. But I am pointing to a *.trn file.
> Im not a dba by any means more of a babysitter so this could be something
> simple.
> Could anyone shed some light please.
>|||Hi Ekrem Ã?nsoy,
You were spot on! Thank you very much, I was getting worried for a while :)
Have a nice day!
"Ekrem Ã?nsoy" wrote:
> Hello Adrian!
>
> There are three Recovery State when you perform a restore operation.
> You can choose one of them while restoring from GUI or T-SQL.
> If you want to use GUI (as you said you do not know much about SQL Server,
> I'll tell you this way) then go from the following path:
> Open SQL Server Management Studio, connect to your instance and right click
> on the Databases and click Restore Database.
> Type some name to "To database:" or choose one. Click "From device" and go
> find your *.bak (backup) file. (Do not forget to check "Restore" checkbox
> from the backup sets listbox.
> Go to Options and from the Recovery State (which is at the bottom of the
> pane) be sure you select the first one (RESTORE WITH RECOVERY).
> When you set this option as RESTORE WITH NORECOVERY, then your database will
> be inaccessible.
> When you set it as RESTORE WITH STANDBY, then your users can use this
> database for reporting purposes. But they can not use UPDATE and DELETE,
> they can only use SELECT against your database in STANDBY mode.
> You can learn more about "Backing Up and Restoring Databases in SQL Server"
> from BOL:
> http://msdn2.microsoft.com/en-us/library/ms187048.aspx
>
> --
> Ekrem Ã?nsoy
>
>
> "Adrian" <Adrian@.discussions.microsoft.com> wrote in message
> news:88722291-F62F-4A91-96F5-91D9FCDD8EF5@.microsoft.com...
> > Hi all,
> >
> > This morning when I tried to test my restores, they all went well except
> > for
> > one Database which when restored went into a "Standby / Read Only" mode,
> > its
> > greyed out and I can't for the life of me get it to go back to Normal.
> >
> > I have googled, searched through the two MS SQL 2005 books I have but I
> > cant
> > seem to get any answer to why it happend or more importantly how to fix
> > the
> > problem.
> >
> > The only options I am given when I right click the Dbs is to restore the
> > transaction log but when I navigate to the my backup (which I restored
> > from)
> > it wont show any logs. I get a " You must select a restore Source" error
> > message. But I am pointing to a *.trn file.
> >
> > Im not a dba by any means more of a babysitter so this could be something
> > simple.
> >
> > Could anyone shed some light please.
> >
>