I have a maintanence plan on all of my SQL servers, both SQl 2000 and SQL
2005. My question is, do I need to backup the Master. MDB, Model and tempdb,
databases or can I exclude them from my plan?
They are system databases. So you should backup them when major changes are
made on the SQL Server Instance.
For example you should backup your "master" database when you create new
logins, linked servers etc. You should backup your "msdb" when you create
new jobs... And "model" if you made changes on the model database which is
the kinda template database...
You do not need to backup tempdb as it will be deleted and recreated
everytime your SQL Server service starts...
I encourage you to take a look at the following link to learn more about
Backing Up and Restoring System Databases:
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
P.S.
You do not need to backup system databases regularly as your user databases
and do not include these system databases in your regular backup maintanence
plan. You don't need this...
Ekrem nsoy
"Mike" <Iseeyoulooking@.move.com> wrote in message
news:uYQV%233YPIHA.5400@.TK2MSFTNGP04.phx.gbl...
>I have a maintanence plan on all of my SQL servers, both SQl 2000 and SQL
>2005. My question is, do I need to backup the Master. MDB, Model and
>tempdb, databases or can I exclude them from my plan?
>
|||On Dec 13, 3:42 pm, "Mike" <Iseeyoulook...@.move.com> wrote:
> I have a maintanence plan on all of my SQL servers, both SQl 2000 and SQL
> 2005. My question is, do I need to backup the Master. MDB, Model and tempdb,
> databases or can I exclude them from my plan?
You can exclude the tempdb because there is no way to restore it
anyway. The tempdb just as its name implies is temporary and is being
created by the server each time that the service starts.
Model database will only be modified if you modify it. This
database is used as a model for the server to create databases. If
you want each new database to have some objects in it, then you create
it in Model database and then each time that you'll create a new
database, you'll have those objects in the new database. If you
create objects in the model database, then you can backup the model
database. In other cases I don't see a reason to do it.
Master and MSDB store important data about logins, server's
configuration, databases' location and configuration (master) and jobs
and DTS packages (MSDB). Those databases should be backed up on a
regular basis.
Adi
sql
Showing posts with label mdb. Show all posts
Showing posts with label mdb. Show all posts
Thursday, March 22, 2012
database backup question
I have a maintanence plan on all of my SQL servers, both SQl 2000 and SQL
2005. My question is, do I need to backup the Master. MDB, Model and tempdb,
databases or can I exclude them from my plan?They are system databases. So you should backup them when major changes are
made on the SQL Server Instance.
For example you should backup your "master" database when you create new
logins, linked servers etc. You should backup your "msdb" when you create
new jobs... And "model" if you made changes on the model database which is
the kinda template database...
You do not need to backup tempdb as it will be deleted and recreated
everytime your SQL Server service starts...
I encourage you to take a look at the following link to learn more about
Backing Up and Restoring System Databases:
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
P.S.
You do not need to backup system databases regularly as your user databases
and do not include these system databases in your regular backup maintanence
plan. You don't need this...
Ekrem nsoy
"Mike" <Iseeyoulooking@.move.com> wrote in message
news:uYQV%233YPIHA.5400@.TK2MSFTNGP04.phx.gbl...
>I have a maintanence plan on all of my SQL servers, both SQl 2000 and SQL
>2005. My question is, do I need to backup the Master. MDB, Model and
>tempdb, databases or can I exclude them from my plan?
>|||On Dec 13, 3:42 pm, "Mike" <Iseeyoulook...@.move.com> wrote:
> I have a maintanence plan on all of my SQL servers, both SQl 2000 and SQL
> 2005. My question is, do I need to backup the Master. MDB, Model and tempd
b,
> databases or can I exclude them from my plan?
You can exclude the tempdb because there is no way to restore it
anyway. The tempdb just as its name implies is temporary and is being
created by the server each time that the service starts.
Model database will only be modified if you modify it. This
database is used as a model for the server to create databases. If
you want each new database to have some objects in it, then you create
it in Model database and then each time that you'll create a new
database, you'll have those objects in the new database. If you
create objects in the model database, then you can backup the model
database. In other cases I don't see a reason to do it.
Master and MSDB store important data about logins, server's
configuration, databases' location and configuration (master) and jobs
and DTS packages (MSDB). Those databases should be backed up on a
regular basis.
Adi
2005. My question is, do I need to backup the Master. MDB, Model and tempdb,
databases or can I exclude them from my plan?They are system databases. So you should backup them when major changes are
made on the SQL Server Instance.
For example you should backup your "master" database when you create new
logins, linked servers etc. You should backup your "msdb" when you create
new jobs... And "model" if you made changes on the model database which is
the kinda template database...
You do not need to backup tempdb as it will be deleted and recreated
everytime your SQL Server service starts...
I encourage you to take a look at the following link to learn more about
Backing Up and Restoring System Databases:
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
P.S.
You do not need to backup system databases regularly as your user databases
and do not include these system databases in your regular backup maintanence
plan. You don't need this...
Ekrem nsoy
"Mike" <Iseeyoulooking@.move.com> wrote in message
news:uYQV%233YPIHA.5400@.TK2MSFTNGP04.phx.gbl...
>I have a maintanence plan on all of my SQL servers, both SQl 2000 and SQL
>2005. My question is, do I need to backup the Master. MDB, Model and
>tempdb, databases or can I exclude them from my plan?
>|||On Dec 13, 3:42 pm, "Mike" <Iseeyoulook...@.move.com> wrote:
> I have a maintanence plan on all of my SQL servers, both SQl 2000 and SQL
> 2005. My question is, do I need to backup the Master. MDB, Model and tempd
b,
> databases or can I exclude them from my plan?
You can exclude the tempdb because there is no way to restore it
anyway. The tempdb just as its name implies is temporary and is being
created by the server each time that the service starts.
Model database will only be modified if you modify it. This
database is used as a model for the server to create databases. If
you want each new database to have some objects in it, then you create
it in Model database and then each time that you'll create a new
database, you'll have those objects in the new database. If you
create objects in the model database, then you can backup the model
database. In other cases I don't see a reason to do it.
Master and MSDB store important data about logins, server's
configuration, databases' location and configuration (master) and jobs
and DTS packages (MSDB). Those databases should be backed up on a
regular basis.
Adi
database backup question
I have a maintanence plan on all of my SQL servers, both SQl 2000 and SQL
2005. My question is, do I need to backup the Master. MDB, Model and tempdb,
databases or can I exclude them from my plan?They are system databases. So you should backup them when major changes are
made on the SQL Server Instance.
For example you should backup your "master" database when you create new
logins, linked servers etc. You should backup your "msdb" when you create
new jobs... And "model" if you made changes on the model database which is
the kinda template database...
You do not need to backup tempdb as it will be deleted and recreated
everytime your SQL Server service starts...
I encourage you to take a look at the following link to learn more about
Backing Up and Restoring System Databases:
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
P.S.
You do not need to backup system databases regularly as your user databases
and do not include these system databases in your regular backup maintanence
plan. You don't need this...
--
Ekrem Önsoy
"Mike" <Iseeyoulooking@.move.com> wrote in message
news:uYQV%233YPIHA.5400@.TK2MSFTNGP04.phx.gbl...
>I have a maintanence plan on all of my SQL servers, both SQl 2000 and SQL
>2005. My question is, do I need to backup the Master. MDB, Model and
>tempdb, databases or can I exclude them from my plan?
>|||On Dec 13, 3:42 pm, "Mike" <Iseeyoulook...@.move.com> wrote:
> I have a maintanence plan on all of my SQL servers, both SQl 2000 and SQL
> 2005. My question is, do I need to backup the Master. MDB, Model and tempdb,
> databases or can I exclude them from my plan?
You can exclude the tempdb because there is no way to restore it
anyway. The tempdb just as its name implies is temporary and is being
created by the server each time that the service starts.
Model database will only be modified if you modify it. This
database is used as a model for the server to create databases. If
you want each new database to have some objects in it, then you create
it in Model database and then each time that you'll create a new
database, you'll have those objects in the new database. If you
create objects in the model database, then you can backup the model
database. In other cases I don't see a reason to do it.
Master and MSDB store important data about logins, server's
configuration, databases' location and configuration (master) and jobs
and DTS packages (MSDB). Those databases should be backed up on a
regular basis.
Adi
2005. My question is, do I need to backup the Master. MDB, Model and tempdb,
databases or can I exclude them from my plan?They are system databases. So you should backup them when major changes are
made on the SQL Server Instance.
For example you should backup your "master" database when you create new
logins, linked servers etc. You should backup your "msdb" when you create
new jobs... And "model" if you made changes on the model database which is
the kinda template database...
You do not need to backup tempdb as it will be deleted and recreated
everytime your SQL Server service starts...
I encourage you to take a look at the following link to learn more about
Backing Up and Restoring System Databases:
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
P.S.
You do not need to backup system databases regularly as your user databases
and do not include these system databases in your regular backup maintanence
plan. You don't need this...
--
Ekrem Önsoy
"Mike" <Iseeyoulooking@.move.com> wrote in message
news:uYQV%233YPIHA.5400@.TK2MSFTNGP04.phx.gbl...
>I have a maintanence plan on all of my SQL servers, both SQl 2000 and SQL
>2005. My question is, do I need to backup the Master. MDB, Model and
>tempdb, databases or can I exclude them from my plan?
>|||On Dec 13, 3:42 pm, "Mike" <Iseeyoulook...@.move.com> wrote:
> I have a maintanence plan on all of my SQL servers, both SQl 2000 and SQL
> 2005. My question is, do I need to backup the Master. MDB, Model and tempdb,
> databases or can I exclude them from my plan?
You can exclude the tempdb because there is no way to restore it
anyway. The tempdb just as its name implies is temporary and is being
created by the server each time that the service starts.
Model database will only be modified if you modify it. This
database is used as a model for the server to create databases. If
you want each new database to have some objects in it, then you create
it in Model database and then each time that you'll create a new
database, you'll have those objects in the new database. If you
create objects in the model database, then you can backup the model
database. In other cases I don't see a reason to do it.
Master and MSDB store important data about logins, server's
configuration, databases' location and configuration (master) and jobs
and DTS packages (MSDB). Those databases should be backed up on a
regular basis.
Adi
Monday, March 19, 2012
database attachement
I'm unable to attach a database in sql server which was detached previously
by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
Whenever I tried to attached the database the system goed hang.
Have you ensured that .mdb and .ldf files are available at the logical file
location path?
And do you see any error messages in errorlogs?
Thanks
GYK
"Paramjeet" wrote:
> I'm unable to attach a database in sql server which was detached previously
> by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
> Whenever I tried to attached the database the system goed hang.
>
|||You might try this techniquue:
Create a new databse with the same name and the same name for the data and
log files.
Stop SQL Server
Copy your database mdf and ldf files over the top of the new created files.
This should allow the database to be seen by SQL Server.
At this point I would run DBCC Checkdb to make sure there is no structural
problems with the database.
Rand
This posting is provided "as is" with no warranties and confers no rights.
by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
Whenever I tried to attached the database the system goed hang.
Have you ensured that .mdb and .ldf files are available at the logical file
location path?
And do you see any error messages in errorlogs?
Thanks
GYK
"Paramjeet" wrote:
> I'm unable to attach a database in sql server which was detached previously
> by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
> Whenever I tried to attached the database the system goed hang.
>
|||You might try this techniquue:
Create a new databse with the same name and the same name for the data and
log files.
Stop SQL Server
Copy your database mdf and ldf files over the top of the new created files.
This should allow the database to be seen by SQL Server.
At this point I would run DBCC Checkdb to make sure there is no structural
problems with the database.
Rand
This posting is provided "as is" with no warranties and confers no rights.
database attachement
I'm unable to attach a database in sql server which was detached previously
by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
Whenever I tried to attached the database the system goed hang.Have you ensured that .mdb and .ldf files are available at the logical file
location path?
And do you see any error messages in errorlogs?
Thanks
GYK
"Paramjeet" wrote:
> I'm unable to attach a database in sql server which was detached previously
> by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
> Whenever I tried to attached the database the system goed hang.
>|||You might try this techniquue:
Create a new databse with the same name and the same name for the data and
log files.
Stop SQL Server
Copy your database mdf and ldf files over the top of the new created files.
This should allow the database to be seen by SQL Server.
At this point I would run DBCC Checkdb to make sure there is no structural
problems with the database.
Rand
This posting is provided "as is" with no warranties and confers no rights.
by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
Whenever I tried to attached the database the system goed hang.Have you ensured that .mdb and .ldf files are available at the logical file
location path?
And do you see any error messages in errorlogs?
Thanks
GYK
"Paramjeet" wrote:
> I'm unable to attach a database in sql server which was detached previously
> by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
> Whenever I tried to attached the database the system goed hang.
>|||You might try this techniquue:
Create a new databse with the same name and the same name for the data and
log files.
Stop SQL Server
Copy your database mdf and ldf files over the top of the new created files.
This should allow the database to be seen by SQL Server.
At this point I would run DBCC Checkdb to make sure there is no structural
problems with the database.
Rand
This posting is provided "as is" with no warranties and confers no rights.
database attachement
I'm unable to attach a database in sql server which was detached previously
by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
Whenever I tried to attached the database the system goed hang.Have you ensured that .mdb and .ldf files are available at the logical file
location path?
And do you see any error messages in errorlogs?
Thanks
GYK
"Paramjeet" wrote:
> I'm unable to attach a database in sql server which was detached previousl
y
> by me. The size of the mdb file is 12 gb and size of the log file is 20 gb
.
> Whenever I tried to attached the database the system goed hang.
>|||You might try this techniquue:
Create a new databse with the same name and the same name for the data and
log files.
Stop SQL Server
Copy your database mdf and ldf files over the top of the new created files.
This should allow the database to be seen by SQL Server.
At this point I would run DBCC Checkdb to make sure there is no structural
problems with the database.
Rand
This posting is provided "as is" with no warranties and confers no rights.
by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
Whenever I tried to attached the database the system goed hang.Have you ensured that .mdb and .ldf files are available at the logical file
location path?
And do you see any error messages in errorlogs?
Thanks
GYK
"Paramjeet" wrote:
> I'm unable to attach a database in sql server which was detached previousl
y
> by me. The size of the mdb file is 12 gb and size of the log file is 20 gb
.
> Whenever I tried to attached the database the system goed hang.
>|||You might try this techniquue:
Create a new databse with the same name and the same name for the data and
log files.
Stop SQL Server
Copy your database mdf and ldf files over the top of the new created files.
This should allow the database to be seen by SQL Server.
At this point I would run DBCC Checkdb to make sure there is no structural
problems with the database.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Database attach failed
Here's the scoop, I got a generic-purpose database (in the form of
*.mdb and *.ldf) from a 'reputable' source on the net. But when I
attempted to attach it to my sql server 2000 with SP3 with EM, it
failed complaining "Could not find row in sysindexes for database ID
10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes."
Then, I attempted to command line attach (thought it may have some
option...), same outcome. Then, ran a search on this NG, and found
the following thread, the question is, MS most likely would not
support something like this, so now what? Also thought about
manually adding a row to sysindexes table to 'fool the attach process'
but after looking at some sample data in this table, I don't think
it's a good idea to try, what can I do? Thanks.
That message appears familiar. I seem to recall that is what happens
if you try to attach an SQL 2005 database on SQL 2000. As far as
SQL 2000 that is a database that is alien, and for which it cannot
really have any graceful handling off.
Which is from 1998, and applies to really old versions of SQL Server.
>
That message appears familiar. I seem to recall that is what happens
if you try to attach an SQL 2005 database on SQL 2000. As far as
SQL 2000 that is a database that is alien, and for which it cannot
really have any graceful handling off.
>
>
Which is from 1998, and applies to really old versions of SQL Server.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thanks for the follow-up, Erland. I tried to attach it with sql
server 2005 as well, the restored db seemed weired with "D:\aName
\blakdsm\blad..", empty, not workable. Now, suppose I can get a
clean/good copy for server 2005, what are the chances of success of
using DUMB database then BACKUP or LOAD back into server 2000? These
two boxes are not connected and they can't.
If you need to move a database from SQL 2005 to SQL 2000 you need to
create from scripts and copy data to file with bulk copy. If the database
uses features that do not exist in SQL 2000, you will have to make some
compromises.
>
If you need to move a database from SQL 2005 to SQL 2000 you need to
create from scripts and copy data to file with bulk copy. If the database
uses features that do not exist in SQL 2000, you will have to make some
compromises.
>
You cannot restore a backup from SQL 2005 on SQL 2000. For quite obvious
reasons: there are features in SQl 2005 for which SQL 2000 is not prepared.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thanks, Erland, bcp is clumsy or because I'm not an expert of it :)
Viable options for the task all seem to consume quite a bit of time
but hey got to do the job. Once again I appreciate it.
*.mdb and *.ldf) from a 'reputable' source on the net. But when I
attempted to attach it to my sql server 2000 with SP3 with EM, it
failed complaining "Could not find row in sysindexes for database ID
10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes."
Then, I attempted to command line attach (thought it may have some
option...), same outcome. Then, ran a search on this NG, and found
the following thread, the question is, MS most likely would not
support something like this, so now what? Also thought about
manually adding a row to sysindexes table to 'fool the attach process'
but after looking at some sample data in this table, I don't think
it's a good idea to try, what can I do? Thanks.
http://groups.google.com/group/comp...3bee910fa30aa9atime management (tatata9999@.gmail.com) writes:
Quote:
Originally Posted by
Here's the scoop, I got a generic-purpose database (in the form of
*.mdb and *.ldf) from a 'reputable' source on the net. But when I
attempted to attach it to my sql server 2000 with SP3 with EM, it
failed complaining "Could not find row in sysindexes for database ID
10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes."
That message appears familiar. I seem to recall that is what happens
if you try to attach an SQL 2005 database on SQL 2000. As far as
SQL 2000 that is a database that is alien, and for which it cannot
really have any graceful handling off.
Quote:
Originally Posted by
Then, I attempted to command line attach (thought it may have some
option...), same outcome. Then, ran a search on this NG, and found
the following thread,
Which is from 1998, and applies to really old versions of SQL Server.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Nov 19, 5:32 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
time management (tatata9...@.gmail.com) writes:
Quote:
Originally Posted by
Here's the scoop, I got a generic-purpose database (in the form of
*.mdb and *.ldf) from a 'reputable' source on the net. But when I
attempted to attach it to my sql server 2000 with SP3 with EM, it
failed complaining "Could not find row in sysindexes for database ID
10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes."
>
That message appears familiar. I seem to recall that is what happens
if you try to attach an SQL 2005 database on SQL 2000. As far as
SQL 2000 that is a database that is alien, and for which it cannot
really have any graceful handling off.
>
Quote:
Originally Posted by
Then, I attempted to command line attach (thought it may have some
option...), same outcome. Then, ran a search on this NG, and found
the following thread,
>
Which is from 1998, and applies to really old versions of SQL Server.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thanks for the follow-up, Erland. I tried to attach it with sql
server 2005 as well, the restored db seemed weired with "D:\aName
\blakdsm\blad..", empty, not workable. Now, suppose I can get a
clean/good copy for server 2005, what are the chances of success of
using DUMB database then BACKUP or LOAD back into server 2000? These
two boxes are not connected and they can't.
Don|||time management (tatata9999@.gmail.com) writes:
Quote:
Originally Posted by
Thanks for the follow-up, Erland. I tried to attach it with sql
server 2005 as well, the restored db seemed weired with "D:\aName
\blakdsm\blad..", empty, not workable. Now, suppose I can get a
clean/good copy for server 2005, what are the chances of success of
using DUMB database then BACKUP or LOAD back into server 2000? These
two boxes are not connected and they can't.
If you need to move a database from SQL 2005 to SQL 2000 you need to
create from scripts and copy data to file with bulk copy. If the database
uses features that do not exist in SQL 2000, you will have to make some
compromises.
You cannot restore a backup from SQL 2005 on SQL 2000. For quite obvious
reasons: there are features in SQl 2005 for which SQL 2000 is not prepared.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Nov 20, 5:31 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
time management (tatata9...@.gmail.com) writes:
Quote:
Originally Posted by
Thanks for the follow-up, Erland. I tried to attach it with sql
server 2005 as well, the restored db seemed weired with "D:\aName
\blakdsm\blad..", empty, not workable. Now, suppose I can get a
clean/good copy for server 2005, what are the chances of success of
using DUMB database then BACKUP or LOAD back into server 2000? These
two boxes are not connected and they can't.
>
If you need to move a database from SQL 2005 to SQL 2000 you need to
create from scripts and copy data to file with bulk copy. If the database
uses features that do not exist in SQL 2000, you will have to make some
compromises.
>
You cannot restore a backup from SQL 2005 on SQL 2000. For quite obvious
reasons: there are features in SQl 2005 for which SQL 2000 is not prepared.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thanks, Erland, bcp is clumsy or because I'm not an expert of it :)
Viable options for the task all seem to consume quite a bit of time
but hey got to do the job. Once again I appreciate it.
Don
Subscribe to:
Comments (Atom)