I currently back up the Master and MSDB sys databases, why do some people say
to back up Model as well? I though this was a standard non enhanceable
database. What are the reprocussions of not backing this database up?
Also what is the best way to back up transaction logs? I am doing an hourly
back up of a production database that has a lot changes through out the day.
I do a full back up once each night. The tran log seems to grow bigger than
the data file when I choose the append option for the log. Can I create
seperate tran log back ups or should I choose overwrite instead of append?
Thanks for your help
-ChrisHi Chris
Model is not non-enhanceable. Model is used as the template whenever a new
database is created, so you can put users, stored procedures, permissions,
datatypes, etc, into model, as well as setting the database properties of
model with ALTER DATABASE, and every new database will inherit those objects
and settings.
The tempdb database is built from model every time your SQL Server starts,
so if you want objects, users, user defined datatypes or special permissions
in tempdb, put them in model.
And then, if you do put any of these objects in model, you can backup model
to make it faster to recreate it in case of system failure.
In the future, I would suggest two different messages for different
questions like you have here. It will make it much easier for everybody, you
and anyone trying to help, to keep track of the discussion.
Do you really mean that your tran log is bigger than the data file, or that
the tran log backup file is bigger than the database backup file? Or
something else?
For a heavily updated database and a day's worth of log backups, it could
happen. But without knowing the size of the db, the rate of changes, the
types of changes and the size of the rows changing, the recovery model of
the database, and probably a few other things, there is no way to tell if it
is TOO big.
To restore from backups to the point of a failure, you need ALL the log
backups since the last full backup. If you use the overwrite option when
backing up the log, you will only have the most recent log back, not the
whole set, and you will be unable to recover anything after the last full
backup. Please read about transaction log backups in the Books Online.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:01280324-7A0E-4EAD-B9A7-567FD9480FFE@.microsoft.com...
>I currently back up the Master and MSDB sys databases, why do some people
>say
> to back up Model as well? I though this was a standard non enhanceable
> database. What are the reprocussions of not backing this database up?
> Also what is the best way to back up transaction logs? I am doing an
> hourly
> back up of a production database that has a lot changes through out the
> day.
> I do a full back up once each night. The tran log seems to grow bigger
> than
> the data file when I choose the append option for the log. Can I create
> seperate tran log back ups or should I choose overwrite instead of append?
> Thanks for your help
> -Chris
Showing posts with label master. Show all posts
Showing posts with label master. Show all posts
Thursday, March 29, 2012
Backing Up Sys Databases
I currently back up the Master and MSDB sys databases, why do some people sa
y
to back up Model as well? I though this was a standard non enhanceable
database. What are the reprocussions of not backing this database up?
Also what is the best way to back up transaction logs? I am doing an hourly
back up of a production database that has a lot changes through out the day.
I do a full back up once each night. The tran log seems to grow bigger than
the data file when I choose the append option for the log. Can I create
seperate tran log back ups or should I choose overwrite instead of append?
Thanks for your help
-ChrisHi Chris
Model is not non-enhanceable. Model is used as the template whenever a new
database is created, so you can put users, stored procedures, permissions,
datatypes, etc, into model, as well as setting the database properties of
model with ALTER DATABASE, and every new database will inherit those objects
and settings.
The tempdb database is built from model every time your SQL Server starts,
so if you want objects, users, user defined datatypes or special permissions
in tempdb, put them in model.
And then, if you do put any of these objects in model, you can backup model
to make it faster to recreate it in case of system failure.
In the future, I would suggest two different messages for different
questions like you have here. It will make it much easier for everybody, you
and anyone trying to help, to keep track of the discussion.
Do you really mean that your tran log is bigger than the data file, or that
the tran log backup file is bigger than the database backup file? Or
something else?
For a heavily updated database and a day's worth of log backups, it could
happen. But without knowing the size of the db, the rate of changes, the
types of changes and the size of the rows changing, the recovery model of
the database, and probably a few other things, there is no way to tell if it
is TOO big.
To restore from backups to the point of a failure, you need ALL the log
backups since the last full backup. If you use the overwrite option when
backing up the log, you will only have the most recent log back, not the
whole set, and you will be unable to recover anything after the last full
backup. Please read about transaction log backups in the Books Online.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:01280324-7A0E-4EAD-B9A7-567FD9480FFE@.microsoft.com...
>I currently back up the Master and MSDB sys databases, why do some people
>say
> to back up Model as well? I though this was a standard non enhanceable
> database. What are the reprocussions of not backing this database up?
> Also what is the best way to back up transaction logs? I am doing an
> hourly
> back up of a production database that has a lot changes through out the
> day.
> I do a full back up once each night. The tran log seems to grow bigger
> than
> the data file when I choose the append option for the log. Can I create
> seperate tran log back ups or should I choose overwrite instead of append?
> Thanks for your help
> -Chris
y
to back up Model as well? I though this was a standard non enhanceable
database. What are the reprocussions of not backing this database up?
Also what is the best way to back up transaction logs? I am doing an hourly
back up of a production database that has a lot changes through out the day.
I do a full back up once each night. The tran log seems to grow bigger than
the data file when I choose the append option for the log. Can I create
seperate tran log back ups or should I choose overwrite instead of append?
Thanks for your help
-ChrisHi Chris
Model is not non-enhanceable. Model is used as the template whenever a new
database is created, so you can put users, stored procedures, permissions,
datatypes, etc, into model, as well as setting the database properties of
model with ALTER DATABASE, and every new database will inherit those objects
and settings.
The tempdb database is built from model every time your SQL Server starts,
so if you want objects, users, user defined datatypes or special permissions
in tempdb, put them in model.
And then, if you do put any of these objects in model, you can backup model
to make it faster to recreate it in case of system failure.
In the future, I would suggest two different messages for different
questions like you have here. It will make it much easier for everybody, you
and anyone trying to help, to keep track of the discussion.
Do you really mean that your tran log is bigger than the data file, or that
the tran log backup file is bigger than the database backup file? Or
something else?
For a heavily updated database and a day's worth of log backups, it could
happen. But without knowing the size of the db, the rate of changes, the
types of changes and the size of the rows changing, the recovery model of
the database, and probably a few other things, there is no way to tell if it
is TOO big.
To restore from backups to the point of a failure, you need ALL the log
backups since the last full backup. If you use the overwrite option when
backing up the log, you will only have the most recent log back, not the
whole set, and you will be unable to recover anything after the last full
backup. Please read about transaction log backups in the Books Online.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:01280324-7A0E-4EAD-B9A7-567FD9480FFE@.microsoft.com...
>I currently back up the Master and MSDB sys databases, why do some people
>say
> to back up Model as well? I though this was a standard non enhanceable
> database. What are the reprocussions of not backing this database up?
> Also what is the best way to back up transaction logs? I am doing an
> hourly
> back up of a production database that has a lot changes through out the
> day.
> I do a full back up once each night. The tran log seems to grow bigger
> than
> the data file when I choose the append option for the log. Can I create
> seperate tran log back ups or should I choose overwrite instead of append?
> Thanks for your help
> -Chris
Backing up SQL Server
Hi,
I backup my database in SQL Server. Do I need to also backup the master
database or any other databases?
Thanks,
SamYes, you need to backup master, msdb, and possibly model if it has modified
default configurations.
"Sam" <sam@.globalwebcentral.com> wrote in message
news:e0F8f%23L9EHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I backup my database in SQL Server. Do I need to also backup the master
> database or any other databases?
> Thanks,
> Sam
>|||Just to be safe, I always do full backups of my master and msdb
databases daily. Model only needs to be backed up if you change
something in the database.
-Zach
Sam wrote:
> Hi,
> I backup my database in SQL Server. Do I need to also backup the master
> database or any other databases?
> Thanks,
> Sam
>
I backup my database in SQL Server. Do I need to also backup the master
database or any other databases?
Thanks,
SamYes, you need to backup master, msdb, and possibly model if it has modified
default configurations.
"Sam" <sam@.globalwebcentral.com> wrote in message
news:e0F8f%23L9EHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I backup my database in SQL Server. Do I need to also backup the master
> database or any other databases?
> Thanks,
> Sam
>|||Just to be safe, I always do full backups of my master and msdb
databases daily. Model only needs to be backed up if you change
something in the database.
-Zach
Sam wrote:
> Hi,
> I backup my database in SQL Server. Do I need to also backup the master
> database or any other databases?
> Thanks,
> Sam
>
Sunday, March 25, 2012
Backing up Encryption Master Key
Hello,
I have problems backing up the Master key. The message is cannot write into
file 'file path' verify that you have the write permissions. I don't
understand why because I am using user dbo and defaul schema dbo on the
database.
If someone can help me to clarify the permissions part.
Thanks for your help.Make sure the SQL Server service account has write access to the path where
you want to save the file.
Thanks
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Car" <Car@.discussions.microsoft.com> wrote in message
news:AF3C125B-D6D3-4304-87F2-C332DD562626@.microsoft.com...
> Hello,
> I have problems backing up the Master key. The message is cannot write
> into
> file 'file path' verify that you have the write permissions. I don't
> understand why because I am using user dbo and defaul schema dbo on the
> database.
> If someone can help me to clarify the permissions part.
> Thanks for your help.|||Well I am not login as sa. I am using windows authentication and the login
user I have is member of the Administrators group on the local computer wher
e
I am trying to save the file containing the backup of the master key.
Thanks for your help anyway.
"Laurentiu Cristofor [MSFT]" wrote:
> Make sure the SQL Server service account has write access to the path wher
e
> you want to save the file.
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Development Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Car" <Car@.discussions.microsoft.com> wrote in message
> news:AF3C125B-D6D3-4304-87F2-C332DD562626@.microsoft.com...
>
>|||How you connect and the permissions you have don't matter in this case -
when you go outside the server, you go out with the service account
credentials, not with the credentials you connected to the server.
So, find out what account SQL Server is running as, and make sure that
account has access to the location where you want to save the backup file.
Thanks
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Car" <Car@.discussions.microsoft.com> wrote in message
news:DFF1B05E-DE27-4C72-BCE5-16C640CDFCB3@.microsoft.com...[vbcol=seagreen]
> Well I am not login as sa. I am using windows authentication and the
> login
> user I have is member of the Administrators group on the local computer
> where
> I am trying to save the file containing the backup of the master key.
>
> Thanks for your help anyway.
> "Laurentiu Cristofor [MSFT]" wrote:
>|||I got it. I find out that the SQL Server Account I was running does not hav
e
permissions on the Local computer. Now I am running the backup statements a
s
a job under the right account and I have to create another job to restore th
e
backup of the master key into the suscriber database.
Thanks again for your assistance.
"Laurentiu Cristofor [MSFT]" wrote:
> How you connect and the permissions you have don't matter in this case -
> when you go outside the server, you go out with the service account
> credentials, not with the credentials you connected to the server.
> So, find out what account SQL Server is running as, and make sure that
> account has access to the location where you want to save the backup file.
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Development Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Car" <Car@.discussions.microsoft.com> wrote in message
> news:DFF1B05E-DE27-4C72-BCE5-16C640CDFCB3@.microsoft.com...
>
>sql
I have problems backing up the Master key. The message is cannot write into
file 'file path' verify that you have the write permissions. I don't
understand why because I am using user dbo and defaul schema dbo on the
database.
If someone can help me to clarify the permissions part.
Thanks for your help.Make sure the SQL Server service account has write access to the path where
you want to save the file.
Thanks
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Car" <Car@.discussions.microsoft.com> wrote in message
news:AF3C125B-D6D3-4304-87F2-C332DD562626@.microsoft.com...
> Hello,
> I have problems backing up the Master key. The message is cannot write
> into
> file 'file path' verify that you have the write permissions. I don't
> understand why because I am using user dbo and defaul schema dbo on the
> database.
> If someone can help me to clarify the permissions part.
> Thanks for your help.|||Well I am not login as sa. I am using windows authentication and the login
user I have is member of the Administrators group on the local computer wher
e
I am trying to save the file containing the backup of the master key.
Thanks for your help anyway.
"Laurentiu Cristofor [MSFT]" wrote:
> Make sure the SQL Server service account has write access to the path wher
e
> you want to save the file.
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Development Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Car" <Car@.discussions.microsoft.com> wrote in message
> news:AF3C125B-D6D3-4304-87F2-C332DD562626@.microsoft.com...
>
>|||How you connect and the permissions you have don't matter in this case -
when you go outside the server, you go out with the service account
credentials, not with the credentials you connected to the server.
So, find out what account SQL Server is running as, and make sure that
account has access to the location where you want to save the backup file.
Thanks
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Car" <Car@.discussions.microsoft.com> wrote in message
news:DFF1B05E-DE27-4C72-BCE5-16C640CDFCB3@.microsoft.com...[vbcol=seagreen]
> Well I am not login as sa. I am using windows authentication and the
> login
> user I have is member of the Administrators group on the local computer
> where
> I am trying to save the file containing the backup of the master key.
>
> Thanks for your help anyway.
> "Laurentiu Cristofor [MSFT]" wrote:
>|||I got it. I find out that the SQL Server Account I was running does not hav
e
permissions on the Local computer. Now I am running the backup statements a
s
a job under the right account and I have to create another job to restore th
e
backup of the master key into the suscriber database.
Thanks again for your assistance.
"Laurentiu Cristofor [MSFT]" wrote:
> How you connect and the permissions you have don't matter in this case -
> when you go outside the server, you go out with the service account
> credentials, not with the credentials you connected to the server.
> So, find out what account SQL Server is running as, and make sure that
> account has access to the location where you want to save the backup file.
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Development Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Car" <Car@.discussions.microsoft.com> wrote in message
> news:DFF1B05E-DE27-4C72-BCE5-16C640CDFCB3@.microsoft.com...
>
>sql
Backing up DPAPI keys in relation to SQL Server
Hi,
There alot of talk about Service Master and DB Master keys and the importance of backing them up seperately.
What about the DPAPI keys used to protect the Service Master keys. What procedures to I take to back those up during my database backup.
If I understand correctly, if I do a backup on one machine, and then try to restore in a different machine, I won't be able to decrypt data becuase the DPAPI keys don't "match".
This is assumming I don't use a password to protect my DB Master keys, I just rely on the service master to protect them.
Thanks for any help,
Database master keys(DbMK) are always protected by a password - you have no choice about that.
If you backup the service master key(SMK), there is no need to be concerned about DPAPI - those keys are managed by the OS and the database automatically uses them to encrypt a loaded SMK.
When you do a database backup, you actually don't need to backup anything else than the database - you just have to remember the database master key password. You don't need the SMK or the DPAPI keys to be able to restore the database.
Thanks
Laurentiu
|||Thanks,
I understand that I can restore the database without the SMK or DPAPI keys, but I'm surprised that I will be able decrypt data without them. I thought this was the whole purpose of the encryption heirarchy.
If I can indeed restore a database, and see the decrypted data with just the DbMK, then why do I need the DPAPI and SMK?
I thought they were in place to add additional "seperation of duties" for keys - so if someone stole a database backup tape, they wouldn't be able to restore the tape on a 'bad guy' server, and see by encrypted data - because they didn't have the SMK and DPAPI key.
Thanks for your help!
Backing up DPAPI keys in relation to SQL Server
Hi,
There alot of talk about Service Master and DB Master keys and the importance of backing them up seperately.
What about the DPAPI keys used to protect the Service Master keys. What procedures to I take to back those up during my database backup.
If I understand correctly, if I do a backup on one machine, and then try to restore in a different machine, I won't be able to decrypt data becuase the DPAPI keys don't "match".
This is assumming I don't use a password to protect my DB Master keys, I just rely on the service master to protect them.
Thanks for any help,
Database master keys(DbMK) are always protected by a password - you have no choice about that.
If you backup the service master key(SMK), there is no need to be concerned about DPAPI - those keys are managed by the OS and the database automatically uses them to encrypt a loaded SMK.
When you do a database backup, you actually don't need to backup anything else than the database - you just have to remember the database master key password. You don't need the SMK or the DPAPI keys to be able to restore the database.
Thanks
Laurentiu
|||Thanks,
I understand that I can restore the database without the SMK or DPAPI keys, but I'm surprised that I will be able decrypt data without them. I thought this was the whole purpose of the encryption heirarchy.
If I can indeed restore a database, and see the decrypted data with just the DbMK, then why do I need the DPAPI and SMK?
I thought they were in place to add additional "seperation of duties" for keys - so if someone stole a database backup tape, they wouldn't be able to restore the tape on a 'bad guy' server, and see by encrypted data - because they didn't have the SMK and DPAPI key.
Thanks for your help!
Monday, March 19, 2012
Back up system DBs
I noticed the transaction logs for master and other system dbs can't be backed up. What are some of the practices of backing up System DBs as far as method, frequency and what type of situation would there be a need to restore from them?
Thanks
You cannot backup logs for the syetm dbs because they are in simple =
recovery model.
Daily full backup should easily suffice unless you are in the habit of =
creating many databases or logins per day.
Mike John
"Niles" <Niles@.discussions.microsoft.com> wrote in message =
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't =
be backed up. What are some of the practices of backing up System DBs =
as far as method, frequency and what type of situation would there be a =
need to restore from them?
> Thanks
|||Hi,
Backup the system databases once a day. I recommend you to take a backup of
Master database inbetween
if you add a new database/new login/ adding roles / changing
configurations/....
Take the backup of MSDB inbetween if you add or modify a Job, add
operator/alert, replications , Logshipping...
Thanks
Hari
MCDBA
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be
backed up. What are some of the practices of backing up System DBs as far
as method, frequency and what type of situation would there be a need to
restore from them?
> Thanks
|||I prefer to do only db backup for master and model. For msdb, I also do log backup (because of
backup history is stored there). As Agent set msdb to simple recovery at startup, I just have a job
scheduled to autostart that set msdb to full recovery.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be backed up. What are some
of the practices of backing up System DBs as far as method, frequency and what type of situation
would there be a need to restore from them?
> Thanks
Thanks
You cannot backup logs for the syetm dbs because they are in simple =
recovery model.
Daily full backup should easily suffice unless you are in the habit of =
creating many databases or logins per day.
Mike John
"Niles" <Niles@.discussions.microsoft.com> wrote in message =
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't =
be backed up. What are some of the practices of backing up System DBs =
as far as method, frequency and what type of situation would there be a =
need to restore from them?
> Thanks
|||Hi,
Backup the system databases once a day. I recommend you to take a backup of
Master database inbetween
if you add a new database/new login/ adding roles / changing
configurations/....
Take the backup of MSDB inbetween if you add or modify a Job, add
operator/alert, replications , Logshipping...
Thanks
Hari
MCDBA
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be
backed up. What are some of the practices of backing up System DBs as far
as method, frequency and what type of situation would there be a need to
restore from them?
> Thanks
|||I prefer to do only db backup for master and model. For msdb, I also do log backup (because of
backup history is stored there). As Agent set msdb to simple recovery at startup, I just have a job
scheduled to autostart that set msdb to full recovery.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be backed up. What are some
of the practices of backing up System DBs as far as method, frequency and what type of situation
would there be a need to restore from them?
> Thanks
Back up system DBs
I noticed the transaction logs for master and other system dbs can't be back
ed up. What are some of the practices of backing up System DBs as far as me
thod, frequency and what type of situation would there be a need to restore
from them?
ThanksYou cannot backup logs for the syetm dbs because they are in simple =
recovery model.
Daily full backup should easily suffice unless you are in the habit of =
creating many databases or logins per day.
Mike John
"Niles" <Niles@.discussions.microsoft.com> wrote in message =
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't =
be backed up. What are some of the practices of backing up System DBs =
as far as method, frequency and what type of situation would there be a =
need to restore from them?
> Thanks|||Hi,
Backup the system databases once a day. I recommend you to take a backup of
Master database inbetween
if you add a new database/new login/ adding roles / changing
configurations/....
Take the backup of MSDB inbetween if you add or modify a Job, add
operator/alert, replications , Logshipping...
Thanks
Hari
MCDBA
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be
backed up. What are some of the practices of backing up System DBs as far
as method, frequency and what type of situation would there be a need to
restore from them?
> Thanks|||I prefer to do only db backup for master and model. For msdb, I also do log
backup (because of
backup history is stored there). As Agent set msdb to simple recovery at sta
rtup, I just have a job
scheduled to autostart that set msdb to full recovery.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be backed up.
What are some
of the practices of backing up System DBs as far as method, frequency and wh
at type of situation
would there be a need to restore from them?
> Thanks
ed up. What are some of the practices of backing up System DBs as far as me
thod, frequency and what type of situation would there be a need to restore
from them?
ThanksYou cannot backup logs for the syetm dbs because they are in simple =
recovery model.
Daily full backup should easily suffice unless you are in the habit of =
creating many databases or logins per day.
Mike John
"Niles" <Niles@.discussions.microsoft.com> wrote in message =
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't =
be backed up. What are some of the practices of backing up System DBs =
as far as method, frequency and what type of situation would there be a =
need to restore from them?
> Thanks|||Hi,
Backup the system databases once a day. I recommend you to take a backup of
Master database inbetween
if you add a new database/new login/ adding roles / changing
configurations/....
Take the backup of MSDB inbetween if you add or modify a Job, add
operator/alert, replications , Logshipping...
Thanks
Hari
MCDBA
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be
backed up. What are some of the practices of backing up System DBs as far
as method, frequency and what type of situation would there be a need to
restore from them?
> Thanks|||I prefer to do only db backup for master and model. For msdb, I also do log
backup (because of
backup history is stored there). As Agent set msdb to simple recovery at sta
rtup, I just have a job
scheduled to autostart that set msdb to full recovery.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be backed up.
What are some
of the practices of backing up System DBs as far as method, frequency and wh
at type of situation
would there be a need to restore from them?
> Thanks
Back up system DBs
I noticed the transaction logs for master and other system dbs can't be backed up. What are some of the practices of backing up System DBs as far as method, frequency and what type of situation would there be a need to restore from them?
ThanksYou cannot backup logs for the syetm dbs because they are in simple =recovery model.
Daily full backup should easily suffice unless you are in the habit of =creating many databases or logins per day.
Mike John
"Niles" <Niles@.discussions.microsoft.com> wrote in message =news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't =be backed up. What are some of the practices of backing up System DBs =as far as method, frequency and what type of situation would there be a =need to restore from them?
> Thanks|||Hi,
Backup the system databases once a day. I recommend you to take a backup of
Master database inbetween
if you add a new database/new login/ adding roles / changing
configurations/....
Take the backup of MSDB inbetween if you add or modify a Job, add
operator/alert, replications , Logshipping...
Thanks
Hari
MCDBA
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be
backed up. What are some of the practices of backing up System DBs as far
as method, frequency and what type of situation would there be a need to
restore from them?
> Thanks|||I prefer to do only db backup for master and model. For msdb, I also do log backup (because of
backup history is stored there). As Agent set msdb to simple recovery at startup, I just have a job
scheduled to autostart that set msdb to full recovery.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be backed up. What are some
of the practices of backing up System DBs as far as method, frequency and what type of situation
would there be a need to restore from them?
> Thanks
ThanksYou cannot backup logs for the syetm dbs because they are in simple =recovery model.
Daily full backup should easily suffice unless you are in the habit of =creating many databases or logins per day.
Mike John
"Niles" <Niles@.discussions.microsoft.com> wrote in message =news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't =be backed up. What are some of the practices of backing up System DBs =as far as method, frequency and what type of situation would there be a =need to restore from them?
> Thanks|||Hi,
Backup the system databases once a day. I recommend you to take a backup of
Master database inbetween
if you add a new database/new login/ adding roles / changing
configurations/....
Take the backup of MSDB inbetween if you add or modify a Job, add
operator/alert, replications , Logshipping...
Thanks
Hari
MCDBA
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be
backed up. What are some of the practices of backing up System DBs as far
as method, frequency and what type of situation would there be a need to
restore from them?
> Thanks|||I prefer to do only db backup for master and model. For msdb, I also do log backup (because of
backup history is stored there). As Agent set msdb to simple recovery at startup, I just have a job
scheduled to autostart that set msdb to full recovery.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be backed up. What are some
of the practices of backing up System DBs as far as method, frequency and what type of situation
would there be a need to restore from them?
> Thanks
Wednesday, March 7, 2012
AWE enabled ?
Hi All,
When i run the command,
use master
go
sp_configure 'awe enabled',1
go
reconfigure
go
I got the output,
name : awe enabled
minimum: 0
Maximum: 1
config_value: 0
run_value: 0
From the value of config_value ,it mean that the "awe" is not enabled.
But, when we see the System Configuration:
OS Win 2003 Standard sp1
Sql server 2005 sp1 ( I have only one instance )
System configuration = 3.83 GB of RAM
The total is 3.83GB of RAM, but When i right click the sql server
instance , select 'Properties' and then on the 'General' tab , i
see 'Memory' assigned to that instance as = 3927 (MB)
Does this mean that, someone already has enabled AWE option. That's
the reason its showing memory = 3927 MB. (or) i have to enable it
externally.
Can someone reply as soon as possible plz.
Thanks.
Here are the commands you need to run in SQL Server
sp_configure 'show advanced options', 1 RECONFIGURE GO sp_configure 'awe enabled', 1 RECONFIGURE GOYou also need to make sure your boot.ini file has the /PAE switch in it.|||Hi GlennAlanBerry,
Do u mean to say /3GB (or) /PAE.
Because my Operating System is Win 2003 standard sp1 and sql server 2005 sp1.
And without enabling AWE also , sql server instance properties shows full sql server RAM?
Thanks.
|||If you only have 4GB of RAM in the server, and you want the OS to be able to use 3GB for applications instead of a max of 2GB, you would want to use the /3GB switch. If you had, say 8GB of RAM in the server, and you wanted the OS to be able to use more than 4GB of memory, you would want to use the /PAE switch.
Subscribe to:
Posts (Atom)