Dear all,
How can I make a snapshot in sql25k? When I do click on the option 'Database
snapshots' only appears 'refresh'.
Thanks for any input,from bol
CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME =
'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\AdventureWorks_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks;
GO
"Enric" wrote:
> Dear all,
> How can I make a snapshot in sql25k? When I do click on the option 'Databa
se
> snapshots' only appears 'refresh'.
> Thanks for any input,
>|||thanks a lot
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Omnibuzz" wrote:
> from bol
> CREATE DATABASE AdventureWorks_dbss1800 ON
> ( NAME = AdventureWorks_Data, FILENAME =
> 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\AdventureWorks_data_1800.ss' )
> AS SNAPSHOT OF AdventureWorks;
> GO
> --
>
>
> "Enric" wrote:
>sql
Showing posts with label click. Show all posts
Showing posts with label click. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
database size
Dear all
I have a SQL abc database. Recently, I right click database properity,the
space avaliable is zero, but i see the database file size and transcation lo
g
has some free space. Why the space avaliable is zero in that case and have
any impact about this database?
thanksVerify that your database has the 'Automatic grow file' property checked in
both your data and transaction log files.
If it is not enabled, you have some choices, like expanding the size of your
files manually, or enabling automatic grow file. Perhaps you want to go with
the default of 10 percent.
Ben Nevarez, MCDBA, OCP
"123" <123@.discussions.microsoft.com> wrote in message
news:518D80E7-98FF-4729-9BB4-A6DA6ACFAD90@.microsoft.com...
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation
> log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>|||Check the fragmentation of your tables. If you haven't already it may be
best to set up a database maintenance plan to reorganise your data and index
pages. The frequency you do this depends on the workload of your server.
"123" wrote:
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation
log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>
I have a SQL abc database. Recently, I right click database properity,the
space avaliable is zero, but i see the database file size and transcation lo
g
has some free space. Why the space avaliable is zero in that case and have
any impact about this database?
thanksVerify that your database has the 'Automatic grow file' property checked in
both your data and transaction log files.
If it is not enabled, you have some choices, like expanding the size of your
files manually, or enabling automatic grow file. Perhaps you want to go with
the default of 10 percent.
Ben Nevarez, MCDBA, OCP
"123" <123@.discussions.microsoft.com> wrote in message
news:518D80E7-98FF-4729-9BB4-A6DA6ACFAD90@.microsoft.com...
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation
> log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>|||Check the fragmentation of your tables. If you haven't already it may be
best to set up a database maintenance plan to reorganise your data and index
pages. The frequency you do this depends on the workload of your server.
"123" wrote:
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation
log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>
database size
Dear all
I have a SQL abc database. Recently, I right click database properity,the
space avaliable is zero, but i see the database file size and transcation log
has some free space. Why the space avaliable is zero in that case and have
any impact about this database?
thanksVerify that your database has the 'Automatic grow file' property checked in
both your data and transaction log files.
If it is not enabled, you have some choices, like expanding the size of your
files manually, or enabling automatic grow file. Perhaps you want to go with
the default of 10 percent.
Ben Nevarez, MCDBA, OCP
"123" <123@.discussions.microsoft.com> wrote in message
news:518D80E7-98FF-4729-9BB4-A6DA6ACFAD90@.microsoft.com...
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation
> log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>|||Check the fragmentation of your tables. If you haven't already it may be
best to set up a database maintenance plan to reorganise your data and index
pages. The frequency you do this depends on the workload of your server.
"123" wrote:
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>
I have a SQL abc database. Recently, I right click database properity,the
space avaliable is zero, but i see the database file size and transcation log
has some free space. Why the space avaliable is zero in that case and have
any impact about this database?
thanksVerify that your database has the 'Automatic grow file' property checked in
both your data and transaction log files.
If it is not enabled, you have some choices, like expanding the size of your
files manually, or enabling automatic grow file. Perhaps you want to go with
the default of 10 percent.
Ben Nevarez, MCDBA, OCP
"123" <123@.discussions.microsoft.com> wrote in message
news:518D80E7-98FF-4729-9BB4-A6DA6ACFAD90@.microsoft.com...
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation
> log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>|||Check the fragmentation of your tables. If you haven't already it may be
best to set up a database maintenance plan to reorganise your data and index
pages. The frequency you do this depends on the workload of your server.
"123" wrote:
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>
Wednesday, March 21, 2012
database size
Dear all
I have a SQL abc database. Recently, I right click database properity,the
space avaliable is zero, but i see the database file size and transcation log
has some free space. Why the space avaliable is zero in that case and have
any impact about this database?
thanks
Verify that your database has the 'Automatic grow file' property checked in
both your data and transaction log files.
If it is not enabled, you have some choices, like expanding the size of your
files manually, or enabling automatic grow file. Perhaps you want to go with
the default of 10 percent.
Ben Nevarez, MCDBA, OCP
"123" <123@.discussions.microsoft.com> wrote in message
news:518D80E7-98FF-4729-9BB4-A6DA6ACFAD90@.microsoft.com...
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation
> log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>
|||Check the fragmentation of your tables. If you haven't already it may be
best to set up a database maintenance plan to reorganise your data and index
pages. The frequency you do this depends on the workload of your server.
"123" wrote:
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>
I have a SQL abc database. Recently, I right click database properity,the
space avaliable is zero, but i see the database file size and transcation log
has some free space. Why the space avaliable is zero in that case and have
any impact about this database?
thanks
Verify that your database has the 'Automatic grow file' property checked in
both your data and transaction log files.
If it is not enabled, you have some choices, like expanding the size of your
files manually, or enabling automatic grow file. Perhaps you want to go with
the default of 10 percent.
Ben Nevarez, MCDBA, OCP
"123" <123@.discussions.microsoft.com> wrote in message
news:518D80E7-98FF-4729-9BB4-A6DA6ACFAD90@.microsoft.com...
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation
> log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>
|||Check the fragmentation of your tables. If you haven't already it may be
best to set up a database maintenance plan to reorganise your data and index
pages. The frequency you do this depends on the workload of your server.
"123" wrote:
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>
Monday, March 19, 2012
database script error
I just upgraded from sql7.0 to sql2000.
everything seemed to be fine.
but when I click on master database it shows the following
error;
Internet Explorer error
line: 307
char: 2
error: unspecified error
code: 0
url: res://C:\Program%20Files\Microsoft%20SQL%20Server\80
\Tools\Binn\Resources\1033\sqlmmc.rll/Tabs.html
and asks me if I wish to conntinue running scripts.
if I create a new database and try to view it, it works
fine, so is this a problem because of the upgrade.
I'm using Internet Explorer 5.5
thanks in advance,
DarrinDarrin,
Had that problem sometime back.Dont know what fixed it - IE version or some
SQL Server service pack.Anyways, here is a workaround - try changing the
view to something other than 'taskpad' temporarily and then return back.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Darrin" <darrin.adams@.eei.ericsson.se> wrote in message
news:0ba001c35062$d936cc10$a501280a@.phx.gbl...
> I just upgraded from sql7.0 to sql2000.
> everything seemed to be fine.
> but when I click on master database it shows the following
> error;
> Internet Explorer error
> line: 307
> char: 2
> error: unspecified error
> code: 0
> url: res://C:\Program%20Files\Microsoft%20SQL%20Server\80
> \Tools\Binn\Resources\1033\sqlmmc.rll/Tabs.html
> and asks me if I wish to conntinue running scripts.
>
> if I create a new database and try to view it, it works
> fine, so is this a problem because of the upgrade.
> I'm using Internet Explorer 5.5
> thanks in advance,
> Darrin
everything seemed to be fine.
but when I click on master database it shows the following
error;
Internet Explorer error
line: 307
char: 2
error: unspecified error
code: 0
url: res://C:\Program%20Files\Microsoft%20SQL%20Server\80
\Tools\Binn\Resources\1033\sqlmmc.rll/Tabs.html
and asks me if I wish to conntinue running scripts.
if I create a new database and try to view it, it works
fine, so is this a problem because of the upgrade.
I'm using Internet Explorer 5.5
thanks in advance,
DarrinDarrin,
Had that problem sometime back.Dont know what fixed it - IE version or some
SQL Server service pack.Anyways, here is a workaround - try changing the
view to something other than 'taskpad' temporarily and then return back.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Darrin" <darrin.adams@.eei.ericsson.se> wrote in message
news:0ba001c35062$d936cc10$a501280a@.phx.gbl...
> I just upgraded from sql7.0 to sql2000.
> everything seemed to be fine.
> but when I click on master database it shows the following
> error;
> Internet Explorer error
> line: 307
> char: 2
> error: unspecified error
> code: 0
> url: res://C:\Program%20Files\Microsoft%20SQL%20Server\80
> \Tools\Binn\Resources\1033\sqlmmc.rll/Tabs.html
> and asks me if I wish to conntinue running scripts.
>
> if I create a new database and try to view it, it works
> fine, so is this a problem because of the upgrade.
> I'm using Internet Explorer 5.5
> thanks in advance,
> Darrin
Wednesday, March 7, 2012
Database Restore Error
I have a question about restoring a database using the GUI. If we log in as
the db_owner using an SQL account, click on databases and then restore we
get the message:
"RESTORE cannot process database 'XXX' because it is in use by this session.
It is recommended that the master database be used when performing this
operation."
We did not have any active connections and for the id we set the default
database to master database.
If I try doing the restore using transact-SQL it works but I am curious on
how to get it working using the GUI so our developers can do their own
restores.
Seems you have a bug in the GUI so it doesn't put the connection in the master database before
executing the RESTORE command. What GUI are you using? EM, SSMS, 3:rd party? Also, is it service
packed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:uTnOt3W7GHA.1012@.TK2MSFTNGP05.phx.gbl...
>I have a question about restoring a database using the GUI. If we log in as the db_owner using an
>SQL account, click on databases and then restore we get the message:
> "RESTORE cannot process database 'XXX' because it is in use by this session. It is recommended
> that the master database be used when performing this operation."
> We did not have any active connections and for the id we set the default database to master
> database.
> If I try doing the restore using transact-SQL it works but I am curious on how to get it working
> using the GUI so our developers can do their own restores.
>
|||We are using SSMS and it is patched with SQL Server 2005 SP1. The same
problem occurs on other servers here as well.
Thanks,
Loren Z
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e3Mn9SX7GHA.1560@.TK2MSFTNGP04.phx.gbl...
> Seems you have a bug in the GUI so it doesn't put the connection in the
> master database before executing the RESTORE command. What GUI are you
> using? EM, SSMS, 3:rd party? Also, is it service packed?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
> news:uTnOt3W7GHA.1012@.TK2MSFTNGP05.phx.gbl...
>
|||I just tired a couple of restores using SSMS on SP 1 and
didn't have any problems with the restore. The only time it
failed is if I had a connection in the database. Are you
sure you don't have any connections in the database you are
trying to restore?
Check all connections and make sure none are in the database
you want to restore. Open up SSMS. Right click on the
database, select Tasks, Restore, Database and restore from
there.
-Sue
On Wed, 11 Oct 2006 14:58:05 -0600, "Loren Z"
<anonymous@.discussions.microsoft.com> wrote:
>We are using SSMS and it is patched with SQL Server 2005 SP1. The same
>problem occurs on other servers here as well.
>Thanks,
>Loren Z
>
>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:e3Mn9SX7GHA.1560@.TK2MSFTNGP04.phx.gbl...
>
|||Don't know if it relates to this, but we use a medical database program
called Misys. It uses a service. If I want to restore the database from
a backup, I first have to stop the Misys Homecare Server service.
Otherwise I get an "in-use" message...
Regards,
Hank Arnold
Loren Z wrote:
> I have a question about restoring a database using the GUI. If we log in as
> the db_owner using an SQL account, click on databases and then restore we
> get the message:
> "RESTORE cannot process database 'XXX' because it is in use by this session.
> It is recommended that the master database be used when performing this
> operation."
> We did not have any active connections and for the id we set the default
> database to master database.
> If I try doing the restore using transact-SQL it works but I am curious on
> how to get it working using the GUI so our developers can do their own
> restores.
>
|||I checked the properties of the SQL ID and the default database is the
database which this ID owns. As soon as I open the restore window a
connection to this database is established. I changed the default database
to master and then tried opening the restore window and the connection was
not there. A restore was then performed successfully.
Is this the way SQL should function by design? That when you open the
restore window a connection is automatically established to the default
database of the SQL ID? In order for our users to perform simple backups
should I set the default database to master?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:fv9ri25n8b361pkvmsap0aaga96ujmgjug@.4ax.com...
>I just tired a couple of restores using SSMS on SP 1 and
> didn't have any problems with the restore. The only time it
> failed is if I had a connection in the database. Are you
> sure you don't have any connections in the database you are
> trying to restore?
> Check all connections and make sure none are in the database
> you want to restore. Open up SSMS. Right click on the
> database, select Tasks, Restore, Database and restore from
> there.
> -Sue
> On Wed, 11 Oct 2006 14:58:05 -0600, "Loren Z"
> <anonymous@.discussions.microsoft.com> wrote:
>
|||> Is this the way SQL should function by design?
Seems like an oversight in the tool (SSMS) you are using. Consider reporting it to
http://connect.microsoft.com/site/si...spx?SiteID=68.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:ecN6VRh7GHA.728@.TK2MSFTNGP04.phx.gbl...
>I checked the properties of the SQL ID and the default database is the database which this ID owns.
>As soon as I open the restore window a connection to this database is established. I changed the
>default database to master and then tried opening the restore window and the connection was not
>there. A restore was then performed successfully.
> Is this the way SQL should function by design? That when you open the restore window a connection
> is automatically established to the default database of the SQL ID? In order for our users to
> perform simple backups should I set the default database to master?
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:fv9ri25n8b361pkvmsap0aaga96ujmgjug@.4ax.com...
>
|||Lines: 100
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
NNTP-Posting-Host: n175en1.energy.gov.ab.ca 199.214.175.1
Xref: leafnode.mcse.ms microsoft.public.sqlserver.tools:1109
Thanks Tibor, have you confirmed this problem as well? If not would you be
able to replicate this behaviour?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e%23mW5jh7GHA.4996@.TK2MSFTNGP03.phx.gbl...
> Seems like an oversight in the tool (SSMS) you are using. Consider
> reporting it to http://connect.microsoft.com/site/si...spx?SiteID=68.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
> news:ecN6VRh7GHA.728@.TK2MSFTNGP04.phx.gbl...
>
|||I just did.
So if a users default database is the same as the database
they are going to restore, they will get this error when
using SSMS.
-Sue
On Thu, 12 Oct 2006 10:29:30 -0600, "Loren Z"
<anonymous@.discussions.microsoft.com> wrote:
>Thanks Tibor, have you confirmed this problem as well? If not would you be
>able to replicate this behaviour?
>
>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:e%23mW5jh7GHA.4996@.TK2MSFTNGP03.phx.gbl...
>
|||Chris Wood (anonymous@.discussions.microsoft.com) writes:
> Does anyone look at the Connect Site? I recorded the bug on October 12th
> and nobody appears to have even checked it out.
Patience, my dear friend!
The bugs you file there are sent to the internal bug database where the
developers deal with them. You may get a reply the next day, and it
may take several months. I can testify, as I have submitted quite a
few bugs.
It's a good idea to register a notification address so that you get
mail when the bug is changed. Not the least, because sometimes the
bug changes status without any comment. (Usually when this happens it
is due to that the developer forgot to fill in a crucial field in the
tool the devs are using. That is, they don't work directly against the
Connect site.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
the db_owner using an SQL account, click on databases and then restore we
get the message:
"RESTORE cannot process database 'XXX' because it is in use by this session.
It is recommended that the master database be used when performing this
operation."
We did not have any active connections and for the id we set the default
database to master database.
If I try doing the restore using transact-SQL it works but I am curious on
how to get it working using the GUI so our developers can do their own
restores.
Seems you have a bug in the GUI so it doesn't put the connection in the master database before
executing the RESTORE command. What GUI are you using? EM, SSMS, 3:rd party? Also, is it service
packed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:uTnOt3W7GHA.1012@.TK2MSFTNGP05.phx.gbl...
>I have a question about restoring a database using the GUI. If we log in as the db_owner using an
>SQL account, click on databases and then restore we get the message:
> "RESTORE cannot process database 'XXX' because it is in use by this session. It is recommended
> that the master database be used when performing this operation."
> We did not have any active connections and for the id we set the default database to master
> database.
> If I try doing the restore using transact-SQL it works but I am curious on how to get it working
> using the GUI so our developers can do their own restores.
>
|||We are using SSMS and it is patched with SQL Server 2005 SP1. The same
problem occurs on other servers here as well.
Thanks,
Loren Z
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e3Mn9SX7GHA.1560@.TK2MSFTNGP04.phx.gbl...
> Seems you have a bug in the GUI so it doesn't put the connection in the
> master database before executing the RESTORE command. What GUI are you
> using? EM, SSMS, 3:rd party? Also, is it service packed?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
> news:uTnOt3W7GHA.1012@.TK2MSFTNGP05.phx.gbl...
>
|||I just tired a couple of restores using SSMS on SP 1 and
didn't have any problems with the restore. The only time it
failed is if I had a connection in the database. Are you
sure you don't have any connections in the database you are
trying to restore?
Check all connections and make sure none are in the database
you want to restore. Open up SSMS. Right click on the
database, select Tasks, Restore, Database and restore from
there.
-Sue
On Wed, 11 Oct 2006 14:58:05 -0600, "Loren Z"
<anonymous@.discussions.microsoft.com> wrote:
>We are using SSMS and it is patched with SQL Server 2005 SP1. The same
>problem occurs on other servers here as well.
>Thanks,
>Loren Z
>
>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:e3Mn9SX7GHA.1560@.TK2MSFTNGP04.phx.gbl...
>
|||Don't know if it relates to this, but we use a medical database program
called Misys. It uses a service. If I want to restore the database from
a backup, I first have to stop the Misys Homecare Server service.
Otherwise I get an "in-use" message...
Regards,
Hank Arnold
Loren Z wrote:
> I have a question about restoring a database using the GUI. If we log in as
> the db_owner using an SQL account, click on databases and then restore we
> get the message:
> "RESTORE cannot process database 'XXX' because it is in use by this session.
> It is recommended that the master database be used when performing this
> operation."
> We did not have any active connections and for the id we set the default
> database to master database.
> If I try doing the restore using transact-SQL it works but I am curious on
> how to get it working using the GUI so our developers can do their own
> restores.
>
|||I checked the properties of the SQL ID and the default database is the
database which this ID owns. As soon as I open the restore window a
connection to this database is established. I changed the default database
to master and then tried opening the restore window and the connection was
not there. A restore was then performed successfully.
Is this the way SQL should function by design? That when you open the
restore window a connection is automatically established to the default
database of the SQL ID? In order for our users to perform simple backups
should I set the default database to master?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:fv9ri25n8b361pkvmsap0aaga96ujmgjug@.4ax.com...
>I just tired a couple of restores using SSMS on SP 1 and
> didn't have any problems with the restore. The only time it
> failed is if I had a connection in the database. Are you
> sure you don't have any connections in the database you are
> trying to restore?
> Check all connections and make sure none are in the database
> you want to restore. Open up SSMS. Right click on the
> database, select Tasks, Restore, Database and restore from
> there.
> -Sue
> On Wed, 11 Oct 2006 14:58:05 -0600, "Loren Z"
> <anonymous@.discussions.microsoft.com> wrote:
>
|||> Is this the way SQL should function by design?
Seems like an oversight in the tool (SSMS) you are using. Consider reporting it to
http://connect.microsoft.com/site/si...spx?SiteID=68.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:ecN6VRh7GHA.728@.TK2MSFTNGP04.phx.gbl...
>I checked the properties of the SQL ID and the default database is the database which this ID owns.
>As soon as I open the restore window a connection to this database is established. I changed the
>default database to master and then tried opening the restore window and the connection was not
>there. A restore was then performed successfully.
> Is this the way SQL should function by design? That when you open the restore window a connection
> is automatically established to the default database of the SQL ID? In order for our users to
> perform simple backups should I set the default database to master?
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:fv9ri25n8b361pkvmsap0aaga96ujmgjug@.4ax.com...
>
|||Lines: 100
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
NNTP-Posting-Host: n175en1.energy.gov.ab.ca 199.214.175.1
Xref: leafnode.mcse.ms microsoft.public.sqlserver.tools:1109
Thanks Tibor, have you confirmed this problem as well? If not would you be
able to replicate this behaviour?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e%23mW5jh7GHA.4996@.TK2MSFTNGP03.phx.gbl...
> Seems like an oversight in the tool (SSMS) you are using. Consider
> reporting it to http://connect.microsoft.com/site/si...spx?SiteID=68.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
> news:ecN6VRh7GHA.728@.TK2MSFTNGP04.phx.gbl...
>
|||I just did.
So if a users default database is the same as the database
they are going to restore, they will get this error when
using SSMS.
-Sue
On Thu, 12 Oct 2006 10:29:30 -0600, "Loren Z"
<anonymous@.discussions.microsoft.com> wrote:
>Thanks Tibor, have you confirmed this problem as well? If not would you be
>able to replicate this behaviour?
>
>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:e%23mW5jh7GHA.4996@.TK2MSFTNGP03.phx.gbl...
>
|||Chris Wood (anonymous@.discussions.microsoft.com) writes:
> Does anyone look at the Connect Site? I recorded the bug on October 12th
> and nobody appears to have even checked it out.
Patience, my dear friend!
The bugs you file there are sent to the internal bug database where the
developers deal with them. You may get a reply the next day, and it
may take several months. I can testify, as I have submitted quite a
few bugs.
It's a good idea to register a notification address so that you get
mail when the bug is changed. Not the least, because sometimes the
bug changes status without any comment. (Usually when this happens it
is due to that the developer forgot to fill in a crucial field in the
tool the devs are using. That is, they don't work directly against the
Connect site.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Friday, February 24, 2012
Database recovery option
On right click of database in ER, goto properties and Options tab, there is
Recovery model selection, what would happen if I select one of them ?Alan,
From Books Online:
You can select one of three recovery models for each database in
Microsoft® SQL Server? 2000 to determine how your data is backed up and
what your exposure to data loss is. The following recovery models are
available:
Simple Recovery
Simple Recovery allows the database to be recovered to the most recent
backup.
Full Recovery
Full Recovery allows the database to be recovered to the point of failure.
Bulk-Logged Recovery
Bulk-Logged Recovery allows bulk-logged operations.
The recovery model of a new database is inherited from the model
database when the new database is created.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Alan wrote:
> On right click of database in ER, goto properties and Options tab, there is
> Recovery model selection, what would happen if I select one of them ?
>|||The more recommeded model is Full Recovery. And switching between the
model could impact/break the continuity of your log and your overall
backup and recovery strategy. You might want to fully understand it
before you start thinking what you want to do with it.
Mark Allison wrote:
> Alan,
> From Books Online:
> You can select one of three recovery models for each database in
> Microsoft® SQL Server? 2000 to determine how your data is backed up and
> what your exposure to data loss is. The following recovery models are
> available:
> Simple Recovery
> Simple Recovery allows the database to be recovered to the most recent
> backup.
> Full Recovery
> Full Recovery allows the database to be recovered to the point of failure.
> Bulk-Logged Recovery
> Bulk-Logged Recovery allows bulk-logged operations.
> The recovery model of a new database is inherited from the model
> database when the new database is created.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Alan wrote:
>> On right click of database in ER, goto properties and Options tab,
>> there is
>> Recovery model selection, what would happen if I select one of them ?
>>|||I just wonder what would happen if:
1) choose full recovery mode, then create a maintenance plan, just backup
the database without transaction log backup ?
2)choose simple recovery mode, then create a maintenance plan, backup both
database and transaction log ?
Do I still get the data back from the point of failure ?
"Jonathan Yong" <dataerror@.someplace.com> wrote in message
news:OtyB47IpEHA.1992@.TK2MSFTNGP09.phx.gbl...
> The more recommeded model is Full Recovery. And switching between the
> model could impact/break the continuity of your log and your overall
> backup and recovery strategy. You might want to fully understand it
> before you start thinking what you want to do with it.
>
> Mark Allison wrote:
> > Alan,
> >
> > From Books Online:
> >
> > You can select one of three recovery models for each database in
> > Microsoft?SQL Server?2000 to determine how your data is backed up and
> > what your exposure to data loss is. The following recovery models are
> > available:
> >
> > Simple Recovery
> > Simple Recovery allows the database to be recovered to the most recent
> > backup.
> >
> > Full Recovery
> > Full Recovery allows the database to be recovered to the point of
failure.
> >
> > Bulk-Logged Recovery
> > Bulk-Logged Recovery allows bulk-logged operations.
> >
> > The recovery model of a new database is inherited from the model
> > database when the new database is created.
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602m.html
> >
> >
> > Alan wrote:
> >
> >> On right click of database in ER, goto properties and Options tab,
> >> there is
> >> Recovery model selection, what would happen if I select one of them ?
> >>
> >>|||In simple recovery mode, you do not get data back to the point of
failure because it does not support log backup.
If that is your requirement, choose full recovery model instead. You can
combine Full, Differential and Log backup in this model.
However, to really recover up to the point of failure, it is not as
straightforward as just restoring from the log. You need to be able to
get hold of the tail of the log of the database that fail.
Alan wrote:
> I just wonder what would happen if:
> 1) choose full recovery mode, then create a maintenance plan, just backup
> the database without transaction log backup ?
> 2)choose simple recovery mode, then create a maintenance plan, backup both
> database and transaction log ?
> Do I still get the data back from the point of failure ?
>
> "Jonathan Yong" <dataerror@.someplace.com> wrote in message
> news:OtyB47IpEHA.1992@.TK2MSFTNGP09.phx.gbl...
>>The more recommeded model is Full Recovery. And switching between the
>>model could impact/break the continuity of your log and your overall
>>backup and recovery strategy. You might want to fully understand it
>>before you start thinking what you want to do with it.
>>
>>Mark Allison wrote:
>>Alan,
>> From Books Online:
>>You can select one of three recovery models for each database in
>>Microsoft?SQL Server?2000 to determine how your data is backed up and
>>what your exposure to data loss is. The following recovery models are
>>available:
>>Simple Recovery
>>Simple Recovery allows the database to be recovered to the most recent
>>backup.
>>Full Recovery
>>Full Recovery allows the database to be recovered to the point of
> failure.
>>Bulk-Logged Recovery
>>Bulk-Logged Recovery allows bulk-logged operations.
>>The recovery model of a new database is inherited from the model
>>database when the new database is created.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602m.html
>>
>>Alan wrote:
>>
>>On right click of database in ER, goto properties and Options tab,
>>there is
>>Recovery model selection, what would happen if I select one of them ?
>>
>
>|||Is that mean if I choose simple recovery mode from the property page of a
database, eg. Northwind, I cannot create a maintenance plan that consists of
transaction log backup ?
"Jonathan Yong" <jyong@.someplace.net> wrote in message
news:%23DdP1iNrEHA.452@.TK2MSFTNGP09.phx.gbl...
> In simple recovery mode, you do not get data back to the point of
> failure because it does not support log backup.
> If that is your requirement, choose full recovery model instead. You can
> combine Full, Differential and Log backup in this model.
> However, to really recover up to the point of failure, it is not as
> straightforward as just restoring from the log. You need to be able to
> get hold of the tail of the log of the database that fail.
>
> Alan wrote:
> > I just wonder what would happen if:
> > 1) choose full recovery mode, then create a maintenance plan, just
backup
> > the database without transaction log backup ?
> >
> > 2)choose simple recovery mode, then create a maintenance plan, backup
both
> > database and transaction log ?
> >
> > Do I still get the data back from the point of failure ?
> >
> >
> >
> > "Jonathan Yong" <dataerror@.someplace.com> wrote in message
> > news:OtyB47IpEHA.1992@.TK2MSFTNGP09.phx.gbl...
> >
> >>The more recommeded model is Full Recovery. And switching between the
> >>model could impact/break the continuity of your log and your overall
> >>backup and recovery strategy. You might want to fully understand it
> >>before you start thinking what you want to do with it.
> >>
> >>
> >>Mark Allison wrote:
> >>
> >>Alan,
> >>
> >> From Books Online:
> >>
> >>You can select one of three recovery models for each database in
> >>Microsoft?SQL Server?2000 to determine how your data is backed up and
> >>what your exposure to data loss is. The following recovery models are
> >>available:
> >>
> >>Simple Recovery
> >>Simple Recovery allows the database to be recovered to the most recent
> >>backup.
> >>
> >>Full Recovery
> >>Full Recovery allows the database to be recovered to the point of
> >
> > failure.
> >
> >>Bulk-Logged Recovery
> >>Bulk-Logged Recovery allows bulk-logged operations.
> >>
> >>The recovery model of a new database is inherited from the model
> >>database when the new database is created.
> >>
> >>--
> >>Mark Allison, SQL Server MVP
> >>http://www.markallison.co.uk
> >>
> >>Looking for a SQL Server replication book?
> >>http://www.nwsu.com/0974973602m.html
> >>
> >>
> >>Alan wrote:
> >>
> >>
> >>On right click of database in ER, goto properties and Options tab,
> >>there is
> >>Recovery model selection, what would happen if I select one of them ?
> >>
> >>
> >
> >
> >
Recovery model selection, what would happen if I select one of them ?Alan,
From Books Online:
You can select one of three recovery models for each database in
Microsoft® SQL Server? 2000 to determine how your data is backed up and
what your exposure to data loss is. The following recovery models are
available:
Simple Recovery
Simple Recovery allows the database to be recovered to the most recent
backup.
Full Recovery
Full Recovery allows the database to be recovered to the point of failure.
Bulk-Logged Recovery
Bulk-Logged Recovery allows bulk-logged operations.
The recovery model of a new database is inherited from the model
database when the new database is created.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Alan wrote:
> On right click of database in ER, goto properties and Options tab, there is
> Recovery model selection, what would happen if I select one of them ?
>|||The more recommeded model is Full Recovery. And switching between the
model could impact/break the continuity of your log and your overall
backup and recovery strategy. You might want to fully understand it
before you start thinking what you want to do with it.
Mark Allison wrote:
> Alan,
> From Books Online:
> You can select one of three recovery models for each database in
> Microsoft® SQL Server? 2000 to determine how your data is backed up and
> what your exposure to data loss is. The following recovery models are
> available:
> Simple Recovery
> Simple Recovery allows the database to be recovered to the most recent
> backup.
> Full Recovery
> Full Recovery allows the database to be recovered to the point of failure.
> Bulk-Logged Recovery
> Bulk-Logged Recovery allows bulk-logged operations.
> The recovery model of a new database is inherited from the model
> database when the new database is created.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Alan wrote:
>> On right click of database in ER, goto properties and Options tab,
>> there is
>> Recovery model selection, what would happen if I select one of them ?
>>|||I just wonder what would happen if:
1) choose full recovery mode, then create a maintenance plan, just backup
the database without transaction log backup ?
2)choose simple recovery mode, then create a maintenance plan, backup both
database and transaction log ?
Do I still get the data back from the point of failure ?
"Jonathan Yong" <dataerror@.someplace.com> wrote in message
news:OtyB47IpEHA.1992@.TK2MSFTNGP09.phx.gbl...
> The more recommeded model is Full Recovery. And switching between the
> model could impact/break the continuity of your log and your overall
> backup and recovery strategy. You might want to fully understand it
> before you start thinking what you want to do with it.
>
> Mark Allison wrote:
> > Alan,
> >
> > From Books Online:
> >
> > You can select one of three recovery models for each database in
> > Microsoft?SQL Server?2000 to determine how your data is backed up and
> > what your exposure to data loss is. The following recovery models are
> > available:
> >
> > Simple Recovery
> > Simple Recovery allows the database to be recovered to the most recent
> > backup.
> >
> > Full Recovery
> > Full Recovery allows the database to be recovered to the point of
failure.
> >
> > Bulk-Logged Recovery
> > Bulk-Logged Recovery allows bulk-logged operations.
> >
> > The recovery model of a new database is inherited from the model
> > database when the new database is created.
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602m.html
> >
> >
> > Alan wrote:
> >
> >> On right click of database in ER, goto properties and Options tab,
> >> there is
> >> Recovery model selection, what would happen if I select one of them ?
> >>
> >>|||In simple recovery mode, you do not get data back to the point of
failure because it does not support log backup.
If that is your requirement, choose full recovery model instead. You can
combine Full, Differential and Log backup in this model.
However, to really recover up to the point of failure, it is not as
straightforward as just restoring from the log. You need to be able to
get hold of the tail of the log of the database that fail.
Alan wrote:
> I just wonder what would happen if:
> 1) choose full recovery mode, then create a maintenance plan, just backup
> the database without transaction log backup ?
> 2)choose simple recovery mode, then create a maintenance plan, backup both
> database and transaction log ?
> Do I still get the data back from the point of failure ?
>
> "Jonathan Yong" <dataerror@.someplace.com> wrote in message
> news:OtyB47IpEHA.1992@.TK2MSFTNGP09.phx.gbl...
>>The more recommeded model is Full Recovery. And switching between the
>>model could impact/break the continuity of your log and your overall
>>backup and recovery strategy. You might want to fully understand it
>>before you start thinking what you want to do with it.
>>
>>Mark Allison wrote:
>>Alan,
>> From Books Online:
>>You can select one of three recovery models for each database in
>>Microsoft?SQL Server?2000 to determine how your data is backed up and
>>what your exposure to data loss is. The following recovery models are
>>available:
>>Simple Recovery
>>Simple Recovery allows the database to be recovered to the most recent
>>backup.
>>Full Recovery
>>Full Recovery allows the database to be recovered to the point of
> failure.
>>Bulk-Logged Recovery
>>Bulk-Logged Recovery allows bulk-logged operations.
>>The recovery model of a new database is inherited from the model
>>database when the new database is created.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602m.html
>>
>>Alan wrote:
>>
>>On right click of database in ER, goto properties and Options tab,
>>there is
>>Recovery model selection, what would happen if I select one of them ?
>>
>
>|||Is that mean if I choose simple recovery mode from the property page of a
database, eg. Northwind, I cannot create a maintenance plan that consists of
transaction log backup ?
"Jonathan Yong" <jyong@.someplace.net> wrote in message
news:%23DdP1iNrEHA.452@.TK2MSFTNGP09.phx.gbl...
> In simple recovery mode, you do not get data back to the point of
> failure because it does not support log backup.
> If that is your requirement, choose full recovery model instead. You can
> combine Full, Differential and Log backup in this model.
> However, to really recover up to the point of failure, it is not as
> straightforward as just restoring from the log. You need to be able to
> get hold of the tail of the log of the database that fail.
>
> Alan wrote:
> > I just wonder what would happen if:
> > 1) choose full recovery mode, then create a maintenance plan, just
backup
> > the database without transaction log backup ?
> >
> > 2)choose simple recovery mode, then create a maintenance plan, backup
both
> > database and transaction log ?
> >
> > Do I still get the data back from the point of failure ?
> >
> >
> >
> > "Jonathan Yong" <dataerror@.someplace.com> wrote in message
> > news:OtyB47IpEHA.1992@.TK2MSFTNGP09.phx.gbl...
> >
> >>The more recommeded model is Full Recovery. And switching between the
> >>model could impact/break the continuity of your log and your overall
> >>backup and recovery strategy. You might want to fully understand it
> >>before you start thinking what you want to do with it.
> >>
> >>
> >>Mark Allison wrote:
> >>
> >>Alan,
> >>
> >> From Books Online:
> >>
> >>You can select one of three recovery models for each database in
> >>Microsoft?SQL Server?2000 to determine how your data is backed up and
> >>what your exposure to data loss is. The following recovery models are
> >>available:
> >>
> >>Simple Recovery
> >>Simple Recovery allows the database to be recovered to the most recent
> >>backup.
> >>
> >>Full Recovery
> >>Full Recovery allows the database to be recovered to the point of
> >
> > failure.
> >
> >>Bulk-Logged Recovery
> >>Bulk-Logged Recovery allows bulk-logged operations.
> >>
> >>The recovery model of a new database is inherited from the model
> >>database when the new database is created.
> >>
> >>--
> >>Mark Allison, SQL Server MVP
> >>http://www.markallison.co.uk
> >>
> >>Looking for a SQL Server replication book?
> >>http://www.nwsu.com/0974973602m.html
> >>
> >>
> >>Alan wrote:
> >>
> >>
> >>On right click of database in ER, goto properties and Options tab,
> >>there is
> >>Recovery model selection, what would happen if I select one of them ?
> >>
> >>
> >
> >
> >
Database recovery option
On right click of database in ER, goto properties and Options tab, there is
Recovery model selection, what would happen if I select one of them ?
Alan,
From Books Online:
You can select one of three recovery models for each database in
Microsoft SQL Server 2000 to determine how your data is backed up and
what your exposure to data loss is. The following recovery models are
available:
Simple Recovery
Simple Recovery allows the database to be recovered to the most recent
backup.
Full Recovery
Full Recovery allows the database to be recovered to the point of failure.
Bulk-Logged Recovery
Bulk-Logged Recovery allows bulk-logged operations.
The recovery model of a new database is inherited from the model
database when the new database is created.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Alan wrote:
> On right click of database in ER, goto properties and Options tab, there is
> Recovery model selection, what would happen if I select one of them ?
>
|||The more recommeded model is Full Recovery. And switching between the
model could impact/break the continuity of your log and your overall
backup and recovery strategy. You might want to fully understand it
before you start thinking what you want to do with it.
Mark Allison wrote:[vbcol=seagreen]
> Alan,
> From Books Online:
> You can select one of three recovery models for each database in
> Microsoft SQL Server 2000 to determine how your data is backed up and
> what your exposure to data loss is. The following recovery models are
> available:
> Simple Recovery
> Simple Recovery allows the database to be recovered to the most recent
> backup.
> Full Recovery
> Full Recovery allows the database to be recovered to the point of failure.
> Bulk-Logged Recovery
> Bulk-Logged Recovery allows bulk-logged operations.
> The recovery model of a new database is inherited from the model
> database when the new database is created.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Alan wrote:
|||I just wonder what would happen if:
1) choose full recovery mode, then create a maintenance plan, just backup
the database without transaction log backup ?
2)choose simple recovery mode, then create a maintenance plan, backup both
database and transaction log ?
Do I still get the data back from the point of failure ?
"Jonathan Yong" <dataerror@.someplace.com> wrote in message
news:OtyB47IpEHA.1992@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> The more recommeded model is Full Recovery. And switching between the
> model could impact/break the continuity of your log and your overall
> backup and recovery strategy. You might want to fully understand it
> before you start thinking what you want to do with it.
>
> Mark Allison wrote:
failure.[vbcol=seagreen]
|||In simple recovery mode, you do not get data back to the point of
failure because it does not support log backup.
If that is your requirement, choose full recovery model instead. You can
combine Full, Differential and Log backup in this model.
However, to really recover up to the point of failure, it is not as
straightforward as just restoring from the log. You need to be able to
get hold of the tail of the log of the database that fail.
Alan wrote:
> I just wonder what would happen if:
> 1) choose full recovery mode, then create a maintenance plan, just backup
> the database without transaction log backup ?
> 2)choose simple recovery mode, then create a maintenance plan, backup both
> database and transaction log ?
> Do I still get the data back from the point of failure ?
>
> "Jonathan Yong" <dataerror@.someplace.com> wrote in message
> news:OtyB47IpEHA.1992@.TK2MSFTNGP09.phx.gbl...
>
> failure.
>
>
|||Is that mean if I choose simple recovery mode from the property page of a
database, eg. Northwind, I cannot create a maintenance plan that consists of
transaction log backup ?
"Jonathan Yong" <jyong@.someplace.net> wrote in message
news:%23DdP1iNrEHA.452@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> In simple recovery mode, you do not get data back to the point of
> failure because it does not support log backup.
> If that is your requirement, choose full recovery model instead. You can
> combine Full, Differential and Log backup in this model.
> However, to really recover up to the point of failure, it is not as
> straightforward as just restoring from the log. You need to be able to
> get hold of the tail of the log of the database that fail.
>
> Alan wrote:
backup[vbcol=seagreen]
both[vbcol=seagreen]
Recovery model selection, what would happen if I select one of them ?
Alan,
From Books Online:
You can select one of three recovery models for each database in
Microsoft SQL Server 2000 to determine how your data is backed up and
what your exposure to data loss is. The following recovery models are
available:
Simple Recovery
Simple Recovery allows the database to be recovered to the most recent
backup.
Full Recovery
Full Recovery allows the database to be recovered to the point of failure.
Bulk-Logged Recovery
Bulk-Logged Recovery allows bulk-logged operations.
The recovery model of a new database is inherited from the model
database when the new database is created.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Alan wrote:
> On right click of database in ER, goto properties and Options tab, there is
> Recovery model selection, what would happen if I select one of them ?
>
|||The more recommeded model is Full Recovery. And switching between the
model could impact/break the continuity of your log and your overall
backup and recovery strategy. You might want to fully understand it
before you start thinking what you want to do with it.
Mark Allison wrote:[vbcol=seagreen]
> Alan,
> From Books Online:
> You can select one of three recovery models for each database in
> Microsoft SQL Server 2000 to determine how your data is backed up and
> what your exposure to data loss is. The following recovery models are
> available:
> Simple Recovery
> Simple Recovery allows the database to be recovered to the most recent
> backup.
> Full Recovery
> Full Recovery allows the database to be recovered to the point of failure.
> Bulk-Logged Recovery
> Bulk-Logged Recovery allows bulk-logged operations.
> The recovery model of a new database is inherited from the model
> database when the new database is created.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Alan wrote:
|||I just wonder what would happen if:
1) choose full recovery mode, then create a maintenance plan, just backup
the database without transaction log backup ?
2)choose simple recovery mode, then create a maintenance plan, backup both
database and transaction log ?
Do I still get the data back from the point of failure ?
"Jonathan Yong" <dataerror@.someplace.com> wrote in message
news:OtyB47IpEHA.1992@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> The more recommeded model is Full Recovery. And switching between the
> model could impact/break the continuity of your log and your overall
> backup and recovery strategy. You might want to fully understand it
> before you start thinking what you want to do with it.
>
> Mark Allison wrote:
failure.[vbcol=seagreen]
|||In simple recovery mode, you do not get data back to the point of
failure because it does not support log backup.
If that is your requirement, choose full recovery model instead. You can
combine Full, Differential and Log backup in this model.
However, to really recover up to the point of failure, it is not as
straightforward as just restoring from the log. You need to be able to
get hold of the tail of the log of the database that fail.
Alan wrote:
> I just wonder what would happen if:
> 1) choose full recovery mode, then create a maintenance plan, just backup
> the database without transaction log backup ?
> 2)choose simple recovery mode, then create a maintenance plan, backup both
> database and transaction log ?
> Do I still get the data back from the point of failure ?
>
> "Jonathan Yong" <dataerror@.someplace.com> wrote in message
> news:OtyB47IpEHA.1992@.TK2MSFTNGP09.phx.gbl...
>
> failure.
>
>
|||Is that mean if I choose simple recovery mode from the property page of a
database, eg. Northwind, I cannot create a maintenance plan that consists of
transaction log backup ?
"Jonathan Yong" <jyong@.someplace.net> wrote in message
news:%23DdP1iNrEHA.452@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> In simple recovery mode, you do not get data back to the point of
> failure because it does not support log backup.
> If that is your requirement, choose full recovery model instead. You can
> combine Full, Differential and Log backup in this model.
> However, to really recover up to the point of failure, it is not as
> straightforward as just restoring from the log. You need to be able to
> get hold of the tail of the log of the database that fail.
>
> Alan wrote:
backup[vbcol=seagreen]
both[vbcol=seagreen]
Sunday, February 19, 2012
database properties show incorrect information
The backup file size for database xyz shows the size of 200,000kb.
However when I click on the property from enterprise manager, it shows 3000MB. What is the reason that the backup file shows a much smaller size than the size that showed from EM db property?
Thanks for your input!
Database files have a "reserved" space which wil give you the availbility, as in your case to put data up to 3000MB to it, before it will grow (if set up). The backup file on the other side will only backup the data not the reserved space which might be not occupied. You will need to have a look in a procedure like sp_Spaceused or the proper view in Enterprise Manager to get the occupied values.HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Subscribe to:
Posts (Atom)