Hey,
This is puzzling me ... I had a relative big database (60GB) full with
data. Now I got rid of most of the data (when outputing tables with
sp_spaceused proc I get total of only 90MB) to create a dev db with the
same procs and structure that the production is. However after
reindexing and executing maintenance plan, the database is still 20 GB.
I know I don't have 19GB worth of procedures and triggers or viewes and
tables.
What else do I have to clean up in order to cut the database size down?
DBCC SHRINKDATABASE
"laimis" wrote:
> Hey,
> This is puzzling me ... I had a relative big database (60GB) full with
> data. Now I got rid of most of the data (when outputing tables with
> sp_spaceused proc I get total of only 90MB) to create a dev db with the
> same procs and structure that the production is. However after
> reindexing and executing maintenance plan, the database is still 20 GB.
> I know I don't have 19GB worth of procedures and triggers or viewes and
> tables.
> What else do I have to clean up in order to cut the database size down?
>
Sunday, March 25, 2012
cleaning up database
Hey,
This is puzzling me ... I had a relative big database (60GB) full with
data. Now I got rid of most of the data (when outputing tables with
sp_spaceused proc I get total of only 90MB) to create a dev db with the
same procs and structure that the production is. However after
reindexing and executing maintenance plan, the database is still 20 GB.
I know I don't have 19GB worth of procedures and triggers or viewes and
tables.
What else do I have to clean up in order to cut the database size down?DBCC SHRINKDATABASE
"laimis" wrote:
> Hey,
> This is puzzling me ... I had a relative big database (60GB) full with
> data. Now I got rid of most of the data (when outputing tables with
> sp_spaceused proc I get total of only 90MB) to create a dev db with the
> same procs and structure that the production is. However after
> reindexing and executing maintenance plan, the database is still 20 GB.
> I know I don't have 19GB worth of procedures and triggers or viewes and
> tables.
> What else do I have to clean up in order to cut the database size down?
>
This is puzzling me ... I had a relative big database (60GB) full with
data. Now I got rid of most of the data (when outputing tables with
sp_spaceused proc I get total of only 90MB) to create a dev db with the
same procs and structure that the production is. However after
reindexing and executing maintenance plan, the database is still 20 GB.
I know I don't have 19GB worth of procedures and triggers or viewes and
tables.
What else do I have to clean up in order to cut the database size down?DBCC SHRINKDATABASE
"laimis" wrote:
> Hey,
> This is puzzling me ... I had a relative big database (60GB) full with
> data. Now I got rid of most of the data (when outputing tables with
> sp_spaceused proc I get total of only 90MB) to create a dev db with the
> same procs and structure that the production is. However after
> reindexing and executing maintenance plan, the database is still 20 GB.
> I know I don't have 19GB worth of procedures and triggers or viewes and
> tables.
> What else do I have to clean up in order to cut the database size down?
>
Cleaning up data files when removing instance (integr. inst.)
Hi everyone,
The Problem: While the service and the instance are removed, the instance's data directory is not, and the DB Datafiles are still there. Now if I reinstall the app (re-creating myNewInstance), it uses the same directory structure, and when I try to re-create the DB, I get the following error message:
So here's the Question: is there
a) any way to tell setup.exe to completely remove the datafiles when uninstalling the instance, or
b) any way to tell TSQL to overwrite the old datafiles if they exist?
I'm trying to integrate SQLEXPRESS in a custom application ("the app"). On installation, a new instance ("myNewInstance") is created, and a new DB ("myDB") is created on that instance.
Upon de-installation of "the app", I remove the new instance. To that end, I'm calling the SQLEXPRESS setup like this:
Code Snippet
setup.exe /qb REMOVE=SQL_Engine INSTANCENAME=myNewInstance
The Problem: While the service and the instance are removed, the instance's data directory is not, and the DB Datafiles are still there. Now if I reinstall the app (re-creating myNewInstance), it uses the same directory structure, and when I try to re-create the DB, I get the following error message:
Code Snippet
Msg 5170, Level 16, State 1, Line 1
Cannot create file '[...]\MSSQL.2\MSSQL\DATA\myDB.mdf' because it already exists. Change the file path or the file name, and retry the operation.
So here's the Question: is there
a) any way to tell setup.exe to completely remove the datafiles when uninstalling the instance, or
b) any way to tell TSQL to overwrite the old datafiles if they exist?
Thanks in advance,
Thorsten
hi Thorsten,
AFAIK, the setup/uninstall wizard does not provide a way to delete user's database, and this usually is a good thing as user's databases are actually the "real important things" to be preserved.. a setup option, SAVESYSDB=0/1 extend that feature to system databases as well, that are usually deleted in normal uninstall situations.. but "the contrary" is not available... but you can probably add a custom task to your uninstall (I do think via ORCA) to delete all "orphaned" remaining files...
regards
cleaning up conflicts table
We have 30 remote subscribers and a publisher.We have
experienced in past that the conflict tables grow to the
limit and for some reason after that we start having
replication issues.(It will take anywhere from 3 hr to 6
hrs compare to 10 mins)
becos of the timings the data gets replicated to all the
sites but it doesn't clear them up from the conflicts
tables. How do we manually purge the conflict records so
that it shouldn't cause us problems in long run.
thanks
delete table
where....
Conflict tables are just that tables. There is nothing special about them,
so you can freely insert/update/delete from them. (They are required for
merge so just playing with data isn't recommended.) But, you won't get a
failure on a transaction.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||HI should I use delte table name or I should use truncate
table name statement.
thanks
>--Original Message--
>delete table
>where....
>Conflict tables are just that tables. There is nothing
special about them,
>so you can freely insert/update/delete from them. (They
are required for
>merge so just playing with data isn't recommended.) But,
you won't get a
>failure on a transaction.
>--
>Mike
>Principal Mentor
>Solid Quality Learning
>"More than just Training"
>SQL Server MVP
>http://www.solidqualitylearning.com
>http://www.mssqlserver.com
>
>.
>
sqlsql
experienced in past that the conflict tables grow to the
limit and for some reason after that we start having
replication issues.(It will take anywhere from 3 hr to 6
hrs compare to 10 mins)
becos of the timings the data gets replicated to all the
sites but it doesn't clear them up from the conflicts
tables. How do we manually purge the conflict records so
that it shouldn't cause us problems in long run.
thanks
delete table
where....
Conflict tables are just that tables. There is nothing special about them,
so you can freely insert/update/delete from them. (They are required for
merge so just playing with data isn't recommended.) But, you won't get a
failure on a transaction.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||HI should I use delte table name or I should use truncate
table name statement.
thanks
>--Original Message--
>delete table
>where....
>Conflict tables are just that tables. There is nothing
special about them,
>so you can freely insert/update/delete from them. (They
are required for
>merge so just playing with data isn't recommended.) But,
you won't get a
>failure on a transaction.
>--
>Mike
>Principal Mentor
>Solid Quality Learning
>"More than just Training"
>SQL Server MVP
>http://www.solidqualitylearning.com
>http://www.mssqlserver.com
>
>.
>
sqlsql
Cleaning up bad Oracle 8i SQL; made it worse?
Hi a bit of a nagging query here. A little background I recently became employed at a company that uses SQR to do a lot of reporting on our Oracle 8i DBMS. Unfortunately, a lot of these reports were written by people with little/no SQL knowledge and so they are ugly (to say the least): they are all procedural with a SELECT * FROM user followed by different subroutines to execute queries to gather address and other data (obviously a JOIN would do!), a subroutine to determine if a user should be selected (instead of a where clause on the SQL statement!).
In any rate, Ive been tasked to help clean some of these up already Ive turned some 5 hour reports into 5 minute reports by removing the procedural stuff.
Ive come across a cross-tab type report that I have successfully converted but I dont know enough of 8is OLAP tricks (if there are any?) to help optimize the query.
The application is a packaged app so I cant modify the tables/views (I dont think we can add indexes or views either), nor for security reasons can I give out the DDL or exact SQL, but I can neuter the tables down to the important part, and really building the SQL doesnt need the entire structure anyway.
In a nutshell the tables are:
User stores user information.
User{ id, name, }
Type stores user type information. Should a user have more than one type code the priority can be used to determine which type is most significant. For example, if a user were both Admin and Guest, Admin would have a priority of 1 whereas Guest may be 10, so Admin will be chosen as their type.
Type{ code, name, priority }
Ties a user to a type. Users can be more than one type (as described above).
UserType{ id, code }
Address{ id, user_id }
Phone{ id, user_id }
Email{ id, user_id }
You get the point. All of the child tables relate back to the parent table. What were generating is something like this:
User Type Address Phone Email
-----------
Admin 123,222 80,000 90,000
SomeType 22,222 12,000 12,022
Etc.
So, for each user type, how many users of that type have an address record, how many have a phone record, etc. These are not correlated (e.g. I dont care if they have an address AND a phone, etc.). Also (here is where the priority comes into play) for the purposes of this report only count the user as being of the type considered if that type is their most significant type:
SELECT *
FROM usertype
WHERE user_id = 123
AND priority = ( SELECT MIN( priority )
FROM usertype
WHERE user_id = 123 )
So the query I have formulated looks something like this:
SELECT name,
( SELECT COUNT( DISTINCT user_id )
FROM address
WHERE user_id IN ( SELECT user_id
FROM user u1,
usertype ut2
WHERE ut2.code = ut1.code
AND ut2.priority = ( SELECT MIN( priority )
FROM usertype
WHERE user_id = u1.user_id )
)
) AS Address,
... AS Phone, -- etc.
FROM type
As you can see, it is nasty. The SQL to get users who are of a particular type is ugly and I dont like running it for each additional cross-tabbed column I add (Address, Phone, Email, etc.)
So I am wondering if there is a better way?
I think I could use a temp table to insert the user_id, code combo into so I can just join to that. The table could get quite large though and I am not sure if I can create an index on a temp table (anyone?).
Anyone? Thanks!
EDIT: Fixed tablenameOnly a small improvement (maybe):
SELECT t.name,
( SELECT COUNT( DISTINCT a.user_id )
FROM address a,
usertype ut2
WHERE a.user_id = ut2.user_id
AND ut2.code = t.code
AND ut2.priority = ( SELECT MIN( priority )
FROM usertype
WHERE user_id = ut2.user_id
)
) AS Address,
... AS Phone, -- etc.
FROM type t
i.e.
1) no need to join to table USER
2) join to usertype (ut2) rather than subquery
3) main query is on table TYPE not USERTYPE
A more radical rewrite which may work is:
SELECT t.name,
SUM( CASE WHEN a.user_id IS NULL THEN 0 ELSE 1 END ) AS Address,
SUM( CASE WHEN p.user_id IS NULL THEN 0 ELSE 1 END ) AS Phone,
...
FROM type t,
user_type ut,
address a,
phone p,
...
WHERE ut.code = t.code
AND ut.priority = ( SELECT MIN( ut2.priority )
FROM usertype ut2
WHERE ut2.user_id = ut.user_id
)
AND a.userid (+)= ut.userid
AND p.userid (+)= ut.userid
GROUP BY t.name;|||Thanks for the reply Tony!
3) main query on table TYPE not USERTYPE
That was a bug! :)
Have to run will check the query in a bit!|||I don't know if the last query will work.. Users can have more than one email address, so I don't want them to count as more than one user... Am I making sense? I just want to check for the existance of at least one email address, and then add that to the 'has email' total.|||Originally posted by MattR
I don't know if the last query will work.. Users can have more than one email address, so I don't want them to count as more than one user... Am I making sense? I just want to check for the existance of at least one email address, and then add that to the 'has email' total.
You are right: as written it relies on there only being one Address, one Phone, etc. per user. Not very useful!
Perhaps if you change the FROM clause to:
FROM type t,
user_type ut,
(select distinct userid from address) a,
(select distinct userid from phone) p,
I have no idea how fast that will run!|||You last code snippit is basically what I have that I would like to avoid. I'll make that JOIN change. I may just create another table with the joined info to avoid making it on every query.
In any rate, Ive been tasked to help clean some of these up already Ive turned some 5 hour reports into 5 minute reports by removing the procedural stuff.
Ive come across a cross-tab type report that I have successfully converted but I dont know enough of 8is OLAP tricks (if there are any?) to help optimize the query.
The application is a packaged app so I cant modify the tables/views (I dont think we can add indexes or views either), nor for security reasons can I give out the DDL or exact SQL, but I can neuter the tables down to the important part, and really building the SQL doesnt need the entire structure anyway.
In a nutshell the tables are:
User stores user information.
User{ id, name, }
Type stores user type information. Should a user have more than one type code the priority can be used to determine which type is most significant. For example, if a user were both Admin and Guest, Admin would have a priority of 1 whereas Guest may be 10, so Admin will be chosen as their type.
Type{ code, name, priority }
Ties a user to a type. Users can be more than one type (as described above).
UserType{ id, code }
Address{ id, user_id }
Phone{ id, user_id }
Email{ id, user_id }
You get the point. All of the child tables relate back to the parent table. What were generating is something like this:
User Type Address Phone Email
-----------
Admin 123,222 80,000 90,000
SomeType 22,222 12,000 12,022
Etc.
So, for each user type, how many users of that type have an address record, how many have a phone record, etc. These are not correlated (e.g. I dont care if they have an address AND a phone, etc.). Also (here is where the priority comes into play) for the purposes of this report only count the user as being of the type considered if that type is their most significant type:
SELECT *
FROM usertype
WHERE user_id = 123
AND priority = ( SELECT MIN( priority )
FROM usertype
WHERE user_id = 123 )
So the query I have formulated looks something like this:
SELECT name,
( SELECT COUNT( DISTINCT user_id )
FROM address
WHERE user_id IN ( SELECT user_id
FROM user u1,
usertype ut2
WHERE ut2.code = ut1.code
AND ut2.priority = ( SELECT MIN( priority )
FROM usertype
WHERE user_id = u1.user_id )
)
) AS Address,
... AS Phone, -- etc.
FROM type
As you can see, it is nasty. The SQL to get users who are of a particular type is ugly and I dont like running it for each additional cross-tabbed column I add (Address, Phone, Email, etc.)
So I am wondering if there is a better way?
I think I could use a temp table to insert the user_id, code combo into so I can just join to that. The table could get quite large though and I am not sure if I can create an index on a temp table (anyone?).
Anyone? Thanks!
EDIT: Fixed tablenameOnly a small improvement (maybe):
SELECT t.name,
( SELECT COUNT( DISTINCT a.user_id )
FROM address a,
usertype ut2
WHERE a.user_id = ut2.user_id
AND ut2.code = t.code
AND ut2.priority = ( SELECT MIN( priority )
FROM usertype
WHERE user_id = ut2.user_id
)
) AS Address,
... AS Phone, -- etc.
FROM type t
i.e.
1) no need to join to table USER
2) join to usertype (ut2) rather than subquery
3) main query is on table TYPE not USERTYPE
A more radical rewrite which may work is:
SELECT t.name,
SUM( CASE WHEN a.user_id IS NULL THEN 0 ELSE 1 END ) AS Address,
SUM( CASE WHEN p.user_id IS NULL THEN 0 ELSE 1 END ) AS Phone,
...
FROM type t,
user_type ut,
address a,
phone p,
...
WHERE ut.code = t.code
AND ut.priority = ( SELECT MIN( ut2.priority )
FROM usertype ut2
WHERE ut2.user_id = ut.user_id
)
AND a.userid (+)= ut.userid
AND p.userid (+)= ut.userid
GROUP BY t.name;|||Thanks for the reply Tony!
3) main query on table TYPE not USERTYPE
That was a bug! :)
Have to run will check the query in a bit!|||I don't know if the last query will work.. Users can have more than one email address, so I don't want them to count as more than one user... Am I making sense? I just want to check for the existance of at least one email address, and then add that to the 'has email' total.|||Originally posted by MattR
I don't know if the last query will work.. Users can have more than one email address, so I don't want them to count as more than one user... Am I making sense? I just want to check for the existance of at least one email address, and then add that to the 'has email' total.
You are right: as written it relies on there only being one Address, one Phone, etc. per user. Not very useful!
Perhaps if you change the FROM clause to:
FROM type t,
user_type ut,
(select distinct userid from address) a,
(select distinct userid from phone) p,
I have no idea how fast that will run!|||You last code snippit is basically what I have that I would like to avoid. I'll make that JOIN change. I may just create another table with the joined info to avoid making it on every query.
cleaning up backups
I recently took responsibility for a sharepoint 2003 server & corresponding
SQL 2000 server. The backups are working through a maintenance plan, but
I'd like to implement a richer plan and am having difficulties developing
the best approach. understand I've not done much care & feeding of SQL in
my life. (lots of other infrastructure work, through). I have read the
technet articles on sql 2000 backup & restore and the pocket consultant:
database backup & recovery. Perhaps you can refer me to a better book?
I'd like to implement a weekly full db backup,
nightly differentials
nightly transaction log backups.
All of this would be done to a file server
When I tried to do this in the maintenance plan wizard, it didn't offer the
differential option. So then I looked at creating a backup job directly and
there the option no (apparent) to limit the number of backup files kept in
the file system. Am I missing something?
Then on the transaction logs, I see when I configure the job through the
backup tool, it allows me to select "remove inactive entries from
transaction log". This option is not available when I create the back
through a maintenance plan. Do I need to do this and why can't I do it
through both tools.
Thanks in advance.
\\Greg> Perhaps you can refer me to a better book?
SQL Server Books Online (the documentation that comes with the product)? It is very good.
> I'd like to implement a weekly full db backup,
> nightly differentials
> nightly transaction log backups.
Hmm, why only nightly log backups? For above, I'd expect something like hourly or every 10 minutes
or so.
> When I tried to do this in the maintenance plan wizard, it didn't offer the differential option.
Correct.
> So then I looked at creating a backup job directly and there the option no (apparent) to limit the
> number of backup files kept in the file system. Am I missing something?
Nope, you are correct again. You would have to roll your own. Google is your friend, and you are
likely to find code "out there" for this. For instance
http://www.karaszi.com/SQLServer/util_backup_script_like_MP.asp, which is 2005 (shouldn't be too
hard to make 2000) and do not include code to remove old backup files.
> Then on the transaction logs, I see when I configure the job through the backup tool, it allows me
> to select "remove inactive entries from transaction log". This option is not available when I
> create the back through a maintenance plan. Do I need to do this and why can't I do it through
> both tools.
The GUI is confusing and badly designed. Un-checking this option will add the NO_TRUNCATE option to
your backup command. This option is badly named and should have been named
ALLOW_LOG_BACKUP_OF_A_CORRUPT_DATABASE. As you imagine, this option is *not* something you want to
specify for other than extreme cases. Leaving this checked in the GUI is same as not specifying the
option, which is same as how Maint Plan does it. Some more reading:
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"uSlackr" <gmartin@.gmartin.org> wrote in message news:%23jJDy2xeIHA.4164@.TK2MSFTNGP05.phx.gbl...
>I recently took responsibility for a sharepoint 2003 server & corresponding SQL 2000 server. The
>backups are working through a maintenance plan, but I'd like to implement a richer plan and am
>having difficulties developing the best approach. understand I've not done much care & feeding of
>SQL in my life. (lots of other infrastructure work, through). I have read the technet articles on
>sql 2000 backup & restore and the pocket consultant: database backup & recovery. Perhaps you can
>refer me to a better book?
> I'd like to implement a weekly full db backup,
> nightly differentials
> nightly transaction log backups.
> All of this would be done to a file server
> When I tried to do this in the maintenance plan wizard, it didn't offer the differential option.
> So then I looked at creating a backup job directly and there the option no (apparent) to limit the
> number of backup files kept in the file system. Am I missing something?
> Then on the transaction logs, I see when I configure the job through the backup tool, it allows me
> to select "remove inactive entries from transaction log". This option is not available when I
> create the back through a maintenance plan. Do I need to do this and why can't I do it through
> both tools.
> Thanks in advance.
> \\Greg|||On Mar 1, 3:59=A0am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > Perhaps you can refer me to a better book?
> SQL Server Books Online (the documentation that comes with the product)? I=t is very good.
> > I'd like to implement a weekly full dbbackup,
> > nightly differentials
> > nightly transaction log backups.
> Hmm, why only nightly log backups? For above, I'd expect something like ho=urly or every 10 minutes
> or so.
> > When I tried to do this in the maintenance plan wizard, it didn't offer =the differential option.
> Correct.
> > So then I looked at creating abackupjob directly and there the option no= (apparent) to limit the
> > number ofbackupfiles kept in the file system. =A0Am I missing something?=
> Nope, you are correct again. You would have to roll your own. Google is yo=ur friend, and you are
> likely to find code "out there" for this. For instancehttp://www.karaszi.c=
om/SQLServer/util_backup_script_like_MP.asp, which is 2005 (shouldn't be too=
> hard to make 2000) and do not include code to remove oldbackupfiles.
> > Then on the transaction logs, I see when I configure the job through the=backuptool, it allows me
> > to select "remove inactive entries from transaction log". =A0This option= is not available when I
> > create the back through a maintenance plan. =A0Do I need to do this and =why can't I do it through
> > both tools.
> The GUI is confusing and badly designed. Un-checking this option will add =the NO_TRUNCATE option to
> yourbackupcommand. This option is badly named and should have been named
> ALLOW_LOG_BACKUP_OF_A_CORRUPT_DATABASE. As you imagine, this option is *no=t* something you want to
> specify for other than extreme cases. Leaving this checked in the GUI is s=ame as not specifying the
> option, which is same as how Maint Plan does it. Some more reading:http://=
www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph=
ttp://sqlblog.com/blogs/tibor_karaszi
>
> "uSlackr" <gmar...@.gmartin.org> wrote in messagenews:%23jJDy2xeIHA.4164@.TK=2MSFTNGP05.phx.gbl...
> >I recently took responsibility for asharepoint2003 server & corresponding= SQL 2000 server. =A0The
> >backups are working through a maintenance plan, but I'd like to implement= a richer plan and am
> >having difficulties developing the best approach. =A0understand I've not =done much care & feeding of
> >SQL in my life. (lots of other infrastructure work, through). I have read= the technet articles on
> >sql 2000backup& restore and the pocket consultant: databasebackup& recove=ry. =A0Perhaps you can
> >refer me to a better book?
> > I'd like to implement a weekly full dbbackup,
> > nightly differentials
> > nightly transaction log backups.
> > All of this would be done to a file server
> > When I tried to do this in the maintenance plan wizard, it didn't offer =the differential option.
> > So then I looked at creating abackupjob directly and there the option no= (apparent) to limit the
> > number ofbackupfiles kept in the file system. =A0Am I missing something?=
> > Then on the transaction logs, I see when I configure the job through the=backuptool, it allows me
> > to select "remove inactive entries from transaction log". =A0This option= is not available when I
> > create the back through a maintenance plan. =A0Do I need to do this and =why can't I do it through
> > both tools.
> > Thanks in advance.
> > \\Greg- Hide quoted text -
> - Show quoted text -
Greg,
Why read books on how to use the native tools?
Go to our website www.avepoint.com and see if our backup solutions are
sufficient.
Give me a call or drop me an email if you have any questions.
Thanks,
John Hohenadel
312-558-1694
john.hohenadel@.avepoint.com
SQL 2000 server. The backups are working through a maintenance plan, but
I'd like to implement a richer plan and am having difficulties developing
the best approach. understand I've not done much care & feeding of SQL in
my life. (lots of other infrastructure work, through). I have read the
technet articles on sql 2000 backup & restore and the pocket consultant:
database backup & recovery. Perhaps you can refer me to a better book?
I'd like to implement a weekly full db backup,
nightly differentials
nightly transaction log backups.
All of this would be done to a file server
When I tried to do this in the maintenance plan wizard, it didn't offer the
differential option. So then I looked at creating a backup job directly and
there the option no (apparent) to limit the number of backup files kept in
the file system. Am I missing something?
Then on the transaction logs, I see when I configure the job through the
backup tool, it allows me to select "remove inactive entries from
transaction log". This option is not available when I create the back
through a maintenance plan. Do I need to do this and why can't I do it
through both tools.
Thanks in advance.
\\Greg> Perhaps you can refer me to a better book?
SQL Server Books Online (the documentation that comes with the product)? It is very good.
> I'd like to implement a weekly full db backup,
> nightly differentials
> nightly transaction log backups.
Hmm, why only nightly log backups? For above, I'd expect something like hourly or every 10 minutes
or so.
> When I tried to do this in the maintenance plan wizard, it didn't offer the differential option.
Correct.
> So then I looked at creating a backup job directly and there the option no (apparent) to limit the
> number of backup files kept in the file system. Am I missing something?
Nope, you are correct again. You would have to roll your own. Google is your friend, and you are
likely to find code "out there" for this. For instance
http://www.karaszi.com/SQLServer/util_backup_script_like_MP.asp, which is 2005 (shouldn't be too
hard to make 2000) and do not include code to remove old backup files.
> Then on the transaction logs, I see when I configure the job through the backup tool, it allows me
> to select "remove inactive entries from transaction log". This option is not available when I
> create the back through a maintenance plan. Do I need to do this and why can't I do it through
> both tools.
The GUI is confusing and badly designed. Un-checking this option will add the NO_TRUNCATE option to
your backup command. This option is badly named and should have been named
ALLOW_LOG_BACKUP_OF_A_CORRUPT_DATABASE. As you imagine, this option is *not* something you want to
specify for other than extreme cases. Leaving this checked in the GUI is same as not specifying the
option, which is same as how Maint Plan does it. Some more reading:
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"uSlackr" <gmartin@.gmartin.org> wrote in message news:%23jJDy2xeIHA.4164@.TK2MSFTNGP05.phx.gbl...
>I recently took responsibility for a sharepoint 2003 server & corresponding SQL 2000 server. The
>backups are working through a maintenance plan, but I'd like to implement a richer plan and am
>having difficulties developing the best approach. understand I've not done much care & feeding of
>SQL in my life. (lots of other infrastructure work, through). I have read the technet articles on
>sql 2000 backup & restore and the pocket consultant: database backup & recovery. Perhaps you can
>refer me to a better book?
> I'd like to implement a weekly full db backup,
> nightly differentials
> nightly transaction log backups.
> All of this would be done to a file server
> When I tried to do this in the maintenance plan wizard, it didn't offer the differential option.
> So then I looked at creating a backup job directly and there the option no (apparent) to limit the
> number of backup files kept in the file system. Am I missing something?
> Then on the transaction logs, I see when I configure the job through the backup tool, it allows me
> to select "remove inactive entries from transaction log". This option is not available when I
> create the back through a maintenance plan. Do I need to do this and why can't I do it through
> both tools.
> Thanks in advance.
> \\Greg|||On Mar 1, 3:59=A0am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > Perhaps you can refer me to a better book?
> SQL Server Books Online (the documentation that comes with the product)? I=t is very good.
> > I'd like to implement a weekly full dbbackup,
> > nightly differentials
> > nightly transaction log backups.
> Hmm, why only nightly log backups? For above, I'd expect something like ho=urly or every 10 minutes
> or so.
> > When I tried to do this in the maintenance plan wizard, it didn't offer =the differential option.
> Correct.
> > So then I looked at creating abackupjob directly and there the option no= (apparent) to limit the
> > number ofbackupfiles kept in the file system. =A0Am I missing something?=
> Nope, you are correct again. You would have to roll your own. Google is yo=ur friend, and you are
> likely to find code "out there" for this. For instancehttp://www.karaszi.c=
om/SQLServer/util_backup_script_like_MP.asp, which is 2005 (shouldn't be too=
> hard to make 2000) and do not include code to remove oldbackupfiles.
> > Then on the transaction logs, I see when I configure the job through the=backuptool, it allows me
> > to select "remove inactive entries from transaction log". =A0This option= is not available when I
> > create the back through a maintenance plan. =A0Do I need to do this and =why can't I do it through
> > both tools.
> The GUI is confusing and badly designed. Un-checking this option will add =the NO_TRUNCATE option to
> yourbackupcommand. This option is badly named and should have been named
> ALLOW_LOG_BACKUP_OF_A_CORRUPT_DATABASE. As you imagine, this option is *no=t* something you want to
> specify for other than extreme cases. Leaving this checked in the GUI is s=ame as not specifying the
> option, which is same as how Maint Plan does it. Some more reading:http://=
www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph=
ttp://sqlblog.com/blogs/tibor_karaszi
>
> "uSlackr" <gmar...@.gmartin.org> wrote in messagenews:%23jJDy2xeIHA.4164@.TK=2MSFTNGP05.phx.gbl...
> >I recently took responsibility for asharepoint2003 server & corresponding= SQL 2000 server. =A0The
> >backups are working through a maintenance plan, but I'd like to implement= a richer plan and am
> >having difficulties developing the best approach. =A0understand I've not =done much care & feeding of
> >SQL in my life. (lots of other infrastructure work, through). I have read= the technet articles on
> >sql 2000backup& restore and the pocket consultant: databasebackup& recove=ry. =A0Perhaps you can
> >refer me to a better book?
> > I'd like to implement a weekly full dbbackup,
> > nightly differentials
> > nightly transaction log backups.
> > All of this would be done to a file server
> > When I tried to do this in the maintenance plan wizard, it didn't offer =the differential option.
> > So then I looked at creating abackupjob directly and there the option no= (apparent) to limit the
> > number ofbackupfiles kept in the file system. =A0Am I missing something?=
> > Then on the transaction logs, I see when I configure the job through the=backuptool, it allows me
> > to select "remove inactive entries from transaction log". =A0This option= is not available when I
> > create the back through a maintenance plan. =A0Do I need to do this and =why can't I do it through
> > both tools.
> > Thanks in advance.
> > \\Greg- Hide quoted text -
> - Show quoted text -
Greg,
Why read books on how to use the native tools?
Go to our website www.avepoint.com and see if our backup solutions are
sufficient.
Give me a call or drop me an email if you have any questions.
Thanks,
John Hohenadel
312-558-1694
john.hohenadel@.avepoint.com
cleaning up backups
I recently took responsibility for a sharepoint 2003 server & corresponding
SQL 2000 server. The backups are working through a maintenance plan, but
I'd like to implement a richer plan and am having difficulties developing
the best approach. understand I've not done much care & feeding of SQL in
my life. (lots of other infrastructure work, through). I have read the
technet articles on sql 2000 backup & restore and the pocket consultant:
database backup & recovery. Perhaps you can refer me to a better book?
I'd like to implement a weekly full db backup,
nightly differentials
nightly transaction log backups.
All of this would be done to a file server
When I tried to do this in the maintenance plan wizard, it didn't offer the
differential option. So then I looked at creating a backup job directly and
there the option no (apparent) to limit the number of backup files kept in
the file system. Am I missing something?
Then on the transaction logs, I see when I configure the job through the
backup tool, it allows me to select "remove inactive entries from
transaction log". This option is not available when I create the back
through a maintenance plan. Do I need to do this and why can't I do it
through both tools.
Thanks in advance.
\\Greg
On Mar 1, 3:59Xam, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SQL Server Books Online (the documentation that comes with the product)? It is very good.
>
> Hmm, why only nightly log backups? For above, I'd expect something like hourly or every 10 minutes
> or so.
>
> Correct.
>
> Nope, you are correct again. You would have to roll your own. Google is your friend, and you are
> likely to find code "out there" for this. For instancehttp://www.karaszi.com/SQLServer/util_backup_script_like_MP.asp, which is 2005 (shouldn't be too
> hard to make 2000) and do not include code to remove oldbackupfiles.
>
> The GUI is confusing and badly designed. Un-checking this option will add the NO_TRUNCATE option to
> yourbackupcommand. This option is badly named and should have been named
> ALLOW_LOG_BACKUP_OF_A_CORRUPT_DATABASE. As you imagine, this option is *not* something you want to
> specify for other than extreme cases. Leaving this checked in the GUI is same as not specifying the
> option, which is same as how Maint Plan does it. Some more reading:http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
> "uSlackr" <gmar...@.gmartin.org> wrote in messagenews:%23jJDy2xeIHA.4164@.TK2MSFTNGP05.phx.gb l...
>
>
>
> - Show quoted text -
Greg,
Why read books on how to use the native tools?
Go to our website www.avepoint.com and see if our backup solutions are
sufficient.
Give me a call or drop me an email if you have any questions.
Thanks,
John Hohenadel
312-558-1694
john.hohenadel@.avepoint.com
SQL 2000 server. The backups are working through a maintenance plan, but
I'd like to implement a richer plan and am having difficulties developing
the best approach. understand I've not done much care & feeding of SQL in
my life. (lots of other infrastructure work, through). I have read the
technet articles on sql 2000 backup & restore and the pocket consultant:
database backup & recovery. Perhaps you can refer me to a better book?
I'd like to implement a weekly full db backup,
nightly differentials
nightly transaction log backups.
All of this would be done to a file server
When I tried to do this in the maintenance plan wizard, it didn't offer the
differential option. So then I looked at creating a backup job directly and
there the option no (apparent) to limit the number of backup files kept in
the file system. Am I missing something?
Then on the transaction logs, I see when I configure the job through the
backup tool, it allows me to select "remove inactive entries from
transaction log". This option is not available when I create the back
through a maintenance plan. Do I need to do this and why can't I do it
through both tools.
Thanks in advance.
\\Greg
On Mar 1, 3:59Xam, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SQL Server Books Online (the documentation that comes with the product)? It is very good.
>
> Hmm, why only nightly log backups? For above, I'd expect something like hourly or every 10 minutes
> or so.
>
> Correct.
>
> Nope, you are correct again. You would have to roll your own. Google is your friend, and you are
> likely to find code "out there" for this. For instancehttp://www.karaszi.com/SQLServer/util_backup_script_like_MP.asp, which is 2005 (shouldn't be too
> hard to make 2000) and do not include code to remove oldbackupfiles.
>
> The GUI is confusing and badly designed. Un-checking this option will add the NO_TRUNCATE option to
> yourbackupcommand. This option is badly named and should have been named
> ALLOW_LOG_BACKUP_OF_A_CORRUPT_DATABASE. As you imagine, this option is *not* something you want to
> specify for other than extreme cases. Leaving this checked in the GUI is same as not specifying the
> option, which is same as how Maint Plan does it. Some more reading:http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
> "uSlackr" <gmar...@.gmartin.org> wrote in messagenews:%23jJDy2xeIHA.4164@.TK2MSFTNGP05.phx.gb l...
>
>
>
> - Show quoted text -
Greg,
Why read books on how to use the native tools?
Go to our website www.avepoint.com and see if our backup solutions are
sufficient.
Give me a call or drop me an email if you have any questions.
Thanks,
John Hohenadel
312-558-1694
john.hohenadel@.avepoint.com
Subscribe to:
Posts (Atom)