making a local copy

hi I have a SQL DB hosted in a hosting company, I want to create a local copy
in my pc, what is the best way to do it.
I tried to make a backup but it seem that the backup can be done only in the
Hosting machine so I could not do it.
thks
Ken
Your best bet is to have them send you a copy of the latest full backup.
Maybe they have an FTP site where you can access it.
Andrew J. Kelly SQL MVP
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:87AEBE9D-C237-4679-8734-405905D7C3A9@.microsoft.com...
> hi I have a SQL DB hosted in a hosting company, I want to create a local
> copy
> in my pc, what is the best way to do it.
> I tried to make a backup but it seem that the backup can be done only in
> the
> Hosting machine so I could not do it.
> thks
>
> --
> Ken
|||I use DTS to copy the db from a web host to a local SQL Server. You're
right, backup requires the server to have access to the drive where you
store the backup file, and it can't get to your computer.
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:87AEBE9D-C237-4679-8734-405905D7C3A9@.microsoft.com...
> hi I have a SQL DB hosted in a hosting company, I want to create a local
> copy
> in my pc, what is the best way to do it.
> I tried to make a backup but it seem that the backup can be done only in
> the
> Hosting machine so I could not do it.
|||Hi
You may want to talk to the hosting company to make the backup downloadable
or have it ftp'd (with the appropriate security in place).
John
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:87AEBE9D-C237-4679-8734-405905D7C3A9@.microsoft.com...
> hi I have a SQL DB hosted in a hosting company, I want to create a local
> copy
> in my pc, what is the best way to do it.
> I tried to make a backup but it seem that the backup can be done only in
> the
> Hosting machine so I could not do it.
> thks
>
> --
> Ken
|||Thks all of you!!!
"Kenny M." wrote:

> hi I have a SQL DB hosted in a hosting company, I want to create a local copy
> in my pc, what is the best way to do it.
> I tried to make a backup but it seem that the backup can be done only in the
> Hosting machine so I could not do it.
> thks
>
> --
> Ken

Make a document repository application with blob fields?

Hello, my company is thinking in make a document repository application and I have read many times that its not recommended to save the files to the database, because it adds ovverhead to other users that are using transactions or something else.

I need to convince my boss that its a bad practice to put the files on BLOB fields inside the database.

Any ideas!!

Well, my first question would be "how would you plan on getting them OUT of the database?". That is always the problem with BLOBs.

In order to display the "file" from the BLOB, you will need to convert it back to a file. So just keep it a file, and use the database to index to it. If you are concerned about security, then create a process which copies the file from a secure area to a place the user can get it, and then delete it when done.

Make 2 or 1

Our company has its Departments And Services.

Now We are making it online.

Both have separate email list, phone numbers, and more.

Will I make one table and adds the field Type (Values: D or S).

Or make them separate.

Remember one thing If we merge them then Email And PhoneNumber Table will also me merge

other wise they will also separate.

What is better.

You should definitely separate these two tables!

Can a service belong to more than one department?

If the answer is yes, you will also need a third table that connects a department with one or more services. You can then use JOIN between these three tables to get a complete list of departments and services.

If the answer is no, then you can put a foreign key in the service table that links directly to a department.

Let us know if you need more assistance!

|||

Yes you are right.

I secondly ask that PhoneNumber or Email Table are Same in nature for Department and Service both.

Will we make PhoneNumber or Email Table separate for PhoneNumber or Email Table.

Table list

Department

DepartmentEmail

Service

ServiceEmail

Is it right?

|||

Well, that doesn't really make any sense if you only have "unique" departments and "unique" services stored, right?

The thing we are discussing here is called normalization, and it's all about reducing duplicate information. So if you don't have duplicate services or duplicate departments, then there's no need to break out email/phone in a separate table.

|||

I think you you don't understand my point.

Multiple EmailAddress and PhoneNumbers per department or service is allowed.

I have a table Department.

And one Department can have multiple Email.

On the other end I have a table Service

Which also can have multiple Email.

-------

Department

-------

Id

Name

-------

-------

Service

-------

Id

Name

-------

Now I have to make Email Tables

And I think the table structure will.

-------

EmailDepartment

-------

Id

Address

DepartmentId

-------

-------

EmailService

-------

Id

Address

DepartmentId

-------

This one is O.k.

or you suggest something else.

|||

Johram we can have multiple email addresses per department.

|||

Johram we can have multiple email addresses per department.

|||

Yes, if you can have multiple e-mail addresses then you are on the right track, dividing it further in email tables!

|||

Sir, I think you understand all the story.

One last thing I am asking from you.

Bascially Question is that dividing Email Tables according to Department And Service Table

has been done by different ways.

One is that

Make separate Email Table for Department and Service Table Named EmailDepartment And EmailService.

I share this with my one professional.

He said not do that.

Make one Email table

And add a column name type in which take D for Department and S for Service

And add one more column of Id in which take the DepartmentId And ServiceId.

I am confused what to choose.

Waiting for your kind help.

|||

Problem is that if you want to use real constraints in the database (Foreign key checks) then this solution is not viable, because you need to express the relationship from the Email table to either Services or Departments, you cannot have both. There is no simple solution to this problem, unless you want to introduce an extra table above Services and Departments (think of it as inheritance), which you could then point your emails at.

However, for this purpose it might be sufficient to skip constraints and don't use foreign keys at all in the Email table. The solution your colleague suggested is a common solution. Database purists would not like it (since constraints cannot be used in a simple manner), but for this purpose it will work well.

Sidenote: It is possible (in some databases) to create insert rules to assure that the reference id inserted in the Email table refers to a valid Service or Department, even if you don't have a FK relationship.

Good luck!

|||

Johram Please tell me what is good. Either 2 sparte email table is good or not.

I need the professional solution not the temporary.

If you have one in your mind.

Please reply. This will really help me.

Currently just becauase of that my work is stop.

|||

Then I suggest you go for two Email tables. It is a more pure solution, and it will be easier for you to use. Good luck!

|||

Yes you are right that is better.

But I have one more idea.

Assume.

We have Department Table with Id And Name Fields.

And Service Table with Id And Name Fields.

Make One Email Table with Id And EmailAddress Fields.

Now Make 2 Tables

DepartmentEmail with DepartmentId And EmailId Fields

And ServiceEmail with ServiceId And EmailId Fields

In comparision with this and old which you prefer in last post what do you prefer now?

Waiting for your reply.

|||

Well, there's really no wrong or right in this case. The previous solution is simpler to implement (less joins). Although this suggestion (having an Email table) is probably the most normalized solution of all. But having a table with only one id and a string could be considered unnecessary.

Hmmm... how should I explain this. If you have a table with employees, something that you could build a relation to in many situations (salaries, bookings, reports etc) then it would be good to have a id, name table. That way, you could for instance have a combo of employees that you choose from, and so on.

But email is just a value, a property of an address. If you always have 1:1 (one to one) relations between Departments and its email and Services and its email, then I think you should have go for the previous solition (having the email field inline). But if you can see that the same email could be used in many situations then you could maybe consider having a separate Email table containing only id and name.

Hmmm... Wonder if this made things any clearer for you.

If I were you, I would settle with DepartmentEmail (DepartmentId, Email) rather than (DepartmentId, EmailId).

Maintenance plans not deleting old transaction log backups

I'm the dba for a large company and have setup many maintenance plans for our sql servers. One of the options I have set is to delete old backups of databases and transaction logs after 2 days. For some reason, the old database backups are being deleted, but the old transaction logs backups are not. I have them being put in the same directory. Anybody have any ideas why this is happening and what if any fix is available?

Those are separate items that you check off in the options for each step - are you sure you asked the wizard to do that? If possible, drop the old plans and recreate them, checking that section to make sure you select that option.

Other than missing the check box, I don't know what else might cause this. Both delete operations use the same delete stored procedure, so if one works, the other one should.

If you do change your plans and this happens again, check the event logs and the SQL Server error logs.

Maintenance plans and SQL Server 2005

Hi all,
Im currently investigating a SQL Server 2005 DB maintenence plan for my
company's software product.
I'm still learning this stuff so if I'd appreciate any comments on my
questions below :)
1) Assume the DB is using a FULL recovery model.
When I do a full backup of the database, will the transaction log be
truncated?
Or will it only be truncated when I perform a transaction log backup?
2) How often should transaction log backups be run? I'm thinking every
couple of hours... probably depends on the performance though (might
make it more frequent)
What is the performance hit when running a transaction log?
3) We currently do an index rebuild nightly. Would it be worth doing
this before a full backup (I assume that the rebuild would contribute
to the transaction log, so if the full backup clears these out then it
would be worth doing it before the backup? Or are there risks involved
with the rebuild so it is better practice to do it after the full
backup).
For one site, the db needs to be up 24/7.
I was thinking about running index rebuild in online mode for this, but
would this only work on Microsoft SQL Server 2005 Enterprise Edition?
BOL under "Guidelines for Performing Online Index Operations" says that
"Online index operations are available only in Microsoft SQL Server
2005 Enterprise Edition." so I'm thinking that this might not work'
4) We do not use auto update statistics, so run updates statistics
every night.
Since we "rebuild the indexes" in the maintenance plan prior to running
the update stats, is the update stats step necessary? I think I read
somewhere that rebuilding the indexes updates the statistics
automatically...
If we use INDEXDEFRAG instead, then we would need to run update stats
yeah? and if so, would it be best to run it before running indexdefrag
or after?
4) Just for your information, I am not going to shrink the log as from
what I've read this is not good for performance as the log file will no
doubt increase in size again (and it encourages fragmentation). But I'm
hoping that the combination of regular transaction log backups will
keep the log file at a consistent size.
I know there are a few questions in there, any comments would be really
appreciated!
Cheers!
Daviddavconts@.gmail.com wrote:
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below :)
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
No, a full backup will not truncate the log file
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
The frequency depends on your tolerance for data loss. If you can
afford to lose 2 hours of work, then backup the log every 2 hours. If
you can only afford to lose 5 minutes of work, backup the log every 5
minutes.
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
Consider only rebuilding those indexes that are badly fragmented. See
http://www.realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
You could use DBCC INDEXDEFRAG, or @.Mode = 'DEFRAG' if running the
script I linked to above.
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
I assume you have auto-stats turned off due to potential performance
problems? It's very rare to encounter such problems, I've never had
cause to turn off auto stats, even on OLTP databases approaching 200GB.
I'd turn them back on.
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
Correct, DBCC INDEXDEFRAG does not update stats. You'll need to
manually update them after defragging, or turn auto-stats on.
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
Do not shrink the log file. Size it properly, do a thorough
defragmentation of the OS volume, you'll enjoy optimal performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||1) Only when a TLog backup is run. This topic is much more in depth than
meets the eye, make sure you read all about it in BOL before implementing in
production.
2) I don't think there is usually much performance hit. How often you want
to do it depends on how much data loss is acceptable.
3) IMO it's never a bad idea to have a good backup before doing stuff like
this. You may want to write TSQL code to switch the recovery model from Full
to Bulk Logged before doing your rebuilds. It will make it faster and your
TLogs wont grow as fast. Again, read BOL thoroughly before doing this. Every
night seems a bit excessive too me, but if thats what you need to do
depending on Inserts/ Deletes....
Looks like it would work on EE to me, but I'll let others comment.
4) It shouldn't be necessary.
I'll usually do update stats after a defrag, but I have no idea why?
4) You are correct.
<davconts@.gmail.com> wrote in message
news:1151975288.606525.38030@.h44g2000cwa.googlegroups.com...
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below :)
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
> I know there are a few questions in there, any comments would be really
> appreciated!
> Cheers!
> David
>|||> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
NO. Only if you perform BACKUP LOG File
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
It depends on your business requirements . I do it every 15 minutes
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
Just make sure that REBUILD INDEX command will not concur with a BACKUP
statement
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
REBUILD INDEX updates statistics
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
After INDEXDEFRAG
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
Correct. Don't be afraid that the LOG file is huge ( I assume you perform
BACKUP LOG ) , that means SQL Server has enough room to perfom its work
<davconts@.gmail.com> wrote in message
news:1151975288.606525.38030@.h44g2000cwa.googlegroups.com...
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below :)
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
> I know there are a few questions in there, any comments would be really
> appreciated!
> Cheers!
> David
>|||Thanks for all the great replies above.. it has really clarified a lot
of the reading i've been doing to get up to speed on this! Really
appreciate it.
Thanks for the link too Tracy, that stored procedure looks interesting,
I'll give it a go.|||In addition to the other posts, check out the Books Online that comes with SQL Server 2005,
sys.dm_db_index_physical_stats. It has code you can use that depending on the fragmentation level
either does nothing, REBUILD or REORGANIZE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<davconts@.gmail.com> wrote in message news:1151975288.606525.38030@.h44g2000cwa.googlegroups.com...
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below :)
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
> I know there are a few questions in there, any comments would be really
> appreciated!
> Cheers!
> David
>|||Thanks Tibor
I tried that script in BOL as you mention above (with a USE dbname
clause at the top)
but I get an error message:
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near '('.
>From what I can see it relates to the following line in the script:
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL,
'LIMITED')
Not sure why this is failing - this is a straight copy from BOL so am a
bit confused'|||Its ok I got it working...
I added:
DECLARE @.db_id smallint;
SET @.db_id = DB_ID(N'RMHCMS');
at the top and then changed the following from:
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
to:
sys.dm_db_index_physical_stats (@.db_id, NULL, NULL , NULL, 'LIMITED')
Cheers,
David
davconts@.gmail.com wrote:
> Thanks Tibor
> I tried that script in BOL as you mention above (with a USE dbname
> clause at the top)
> but I get an error message:
> Msg 102, Level 15, State 1, Line 24
> Incorrect syntax near '('.
> >From what I can see it relates to the following line in the script:
> FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL,
> 'LIMITED')
> Not sure why this is failing - this is a straight copy from BOL so am a
> bit confused'|||<davconts@.gmail.com> wrote in message
news:1151975288.606525.38030@.h44g2000cwa.googlegroups.com...
> Hi all,
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
Not really. But in any case, think of it as a trade-off.
If you do it say every 2 hours vs every 15 minutes.
The 2 hour version would have 8 times as much info to backup as the 15
minute one.
So if there WERE a performance issue, it would last 8 times as long.
Generally though plan them according to what your DR model requires.
For example our Dev databases we backup nightly and that's about it. It's
really not worth the trouble to do it more often.
Our production ones we do transaction log backups every 15 minutes and full
backups 3 nights a week.

Maintenance Plan woes

I am very new to SQL Server, and inherited the database administration
for my company. It was setup by a very competent admin and has been
fairly self-sufficient so far. I am having a problem with my main
maintenance plan on my primary SQL server. The plan starts the backup
of all of our databases on that server at 8pm. To complete all of the
tasks in the maintenance plan is taking about 12 hours lately,
spilling into the work day. The maintenance plan first makes a backup
of all databases to a local RAID. Then it does the same thing to a
network server, which is then copied to tape using our backup
software. The maintenance plan then deletes all backup files older
than 5 days. Then it Reorgs the all User DB Indexes, and Updates all
User DB Stats. There are about 40 DBs total on this server, and at
least one of them I know for a fact is very large (> 200GB) What is
best practice in this situation to make it so that all maintenance
plans finish before the work day starts?
Thanks in advance.
>>...The maintenance plan first makes a backup
of all databases to a local RAID. Then it does the same thing to a
network server...<<
Why the double work? Redundancy is fine, but this seems like overkill.
You may also want to investigate Litespeed or a similar product for
compressed/faster backup and recovery...especially with that 200 GB
database.
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Matt" <matthewsatkins@.gmail.com> wrote in message
news:ac1d8d14-018c-4f3d-96c9-1870873024c1@.m34g2000hsf.googlegroups.com...
>I am very new to SQL Server, and inherited the database administration
> for my company. It was setup by a very competent admin and has been
> fairly self-sufficient so far. I am having a problem with my main
> maintenance plan on my primary SQL server. The plan starts the backup
> of all of our databases on that server at 8pm. To complete all of the
> tasks in the maintenance plan is taking about 12 hours lately,
> spilling into the work day. The maintenance plan first makes a backup
> of all databases to a local RAID. Then it does the same thing to a
> network server, which is then copied to tape using our backup
> software. The maintenance plan then deletes all backup files older
> than 5 days. Then it Reorgs the all User DB Indexes, and Updates all
> User DB Stats. There are about 40 DBs total on this server, and at
> least one of them I know for a fact is very large (> 200GB) What is
> best practice in this situation to make it so that all maintenance
> plans finish before the work day starts?
> Thanks in advance.
|||Hi,
Firts, Do you need the backups in local disk for 5 days, if you have one
copy in tape?
second. You software backup is not possible to integrate with SQL?
anyware,
The better is develop all tasks in differents plans, this mean
1.Perform backups every days (1 plan)
2. Reorg indexes, once per week or per month it isn't necessary reorg.
Indexes every day. (1 or more plan)
But not all in the same day for exampl, Yor will reorg. indexes in 5 or 6
databases per day, another day others databases.
3. You can Perform Update statistics every day (if you want) (1 or more plan)
4. Create Jobs with t-sql to perform maintenance on your databases, one Job
for database or task. in this way you can to take control of your maintenance
plans.
Regards,
"Matt" wrote:

> I am very new to SQL Server, and inherited the database administration
> for my company. It was setup by a very competent admin and has been
> fairly self-sufficient so far. I am having a problem with my main
> maintenance plan on my primary SQL server. The plan starts the backup
> of all of our databases on that server at 8pm. To complete all of the
> tasks in the maintenance plan is taking about 12 hours lately,
> spilling into the work day. The maintenance plan first makes a backup
> of all databases to a local RAID. Then it does the same thing to a
> network server, which is then copied to tape using our backup
> software. The maintenance plan then deletes all backup files older
> than 5 days. Then it Reorgs the all User DB Indexes, and Updates all
> User DB Stats. There are about 40 DBs total on this server, and at
> least one of them I know for a fact is very large (> 200GB) What is
> best practice in this situation to make it so that all maintenance
> plans finish before the work day starts?
> Thanks in advance.
>

Maintenance Plan woes

I am very new to SQL Server, and inherited the database administration
for my company. It was setup by a very competent admin and has been
fairly self-sufficient so far. I am having a problem with my main
maintenance plan on my primary SQL server. The plan starts the backup
of all of our databases on that server at 8pm. To complete all of the
tasks in the maintenance plan is taking about 12 hours lately,
spilling into the work day. The maintenance plan first makes a backup
of all databases to a local RAID. Then it does the same thing to a
network server, which is then copied to tape using our backup
software. The maintenance plan then deletes all backup files older
than 5 days. Then it Reorgs the all User DB Indexes, and Updates all
User DB Stats. There are about 40 DBs total on this server, and at
least one of them I know for a fact is very large (> 200GB) What is
best practice in this situation to make it so that all maintenance
plans finish before the work day starts'
Thanks in advance.>>...The maintenance plan first makes a backup
of all databases to a local RAID. Then it does the same thing to a
network server...<<
Why the double work? Redundancy is fine, but this seems like overkill.
You may also want to investigate Litespeed or a similar product for
compressed/faster backup and recovery...especially with that 200 GB
database.
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Matt" <matthewsatkins@.gmail.com> wrote in message
news:ac1d8d14-018c-4f3d-96c9-1870873024c1@.m34g2000hsf.googlegroups.com...
>I am very new to SQL Server, and inherited the database administration
> for my company. It was setup by a very competent admin and has been
> fairly self-sufficient so far. I am having a problem with my main
> maintenance plan on my primary SQL server. The plan starts the backup
> of all of our databases on that server at 8pm. To complete all of the
> tasks in the maintenance plan is taking about 12 hours lately,
> spilling into the work day. The maintenance plan first makes a backup
> of all databases to a local RAID. Then it does the same thing to a
> network server, which is then copied to tape using our backup
> software. The maintenance plan then deletes all backup files older
> than 5 days. Then it Reorgs the all User DB Indexes, and Updates all
> User DB Stats. There are about 40 DBs total on this server, and at
> least one of them I know for a fact is very large (> 200GB) What is
> best practice in this situation to make it so that all maintenance
> plans finish before the work day starts'
> Thanks in advance.|||Hi,
Firts, Do you need the backups in local disk for 5 days, if you have one
copy in tape?
second. You software backup is not possible to integrate with SQL?
anyware,
The better is develop all tasks in differents plans, this mean
1.Perform backups every days (1 plan)
2. Reorg indexes, once per week or per month it isn't necessary reorg.
Indexes every day. (1 or more plan)
But not all in the same day for exampl, Yor will reorg. indexes in 5 or 6
databases per day, another day others databases.
3. You can Perform Update statistics every day (if you want) (1 or more plan)
4. Create Jobs with t-sql to perform maintenance on your databases, one Job
for database or task. in this way you can to take control of your maintenance
plans.
Regards,
"Matt" wrote:
> I am very new to SQL Server, and inherited the database administration
> for my company. It was setup by a very competent admin and has been
> fairly self-sufficient so far. I am having a problem with my main
> maintenance plan on my primary SQL server. The plan starts the backup
> of all of our databases on that server at 8pm. To complete all of the
> tasks in the maintenance plan is taking about 12 hours lately,
> spilling into the work day. The maintenance plan first makes a backup
> of all databases to a local RAID. Then it does the same thing to a
> network server, which is then copied to tape using our backup
> software. The maintenance plan then deletes all backup files older
> than 5 days. Then it Reorgs the all User DB Indexes, and Updates all
> User DB Stats. There are about 40 DBs total on this server, and at
> least one of them I know for a fact is very large (> 200GB) What is
> best practice in this situation to make it so that all maintenance
> plans finish before the work day starts'
> Thanks in advance.
>