Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciate
Pooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate

Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciatePooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate

Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciatePooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate

Best Replication Method to Use

We want to allow our customer base to be able to access their account
information online. I want to setup a second SQL server so the customers can
use this for looks up. The front end to access this info is web based.
What replication method is the best one to use to update the database say
every 24 hours at night? Thanks!!
if your database is not too large, a snapshot replication maybe best for
you.
else somekind of logshipping will be good too, see the other thread on
simple log shipping.
justin
Scopus69 wrote:
> We want to allow our customer base to be able to access their account
> information online. I want to setup a second SQL server so the customers can
> use this for looks up. The front end to access this info is web based.
> What replication method is the best one to use to update the database say
> every 24 hours at night? Thanks!!
|||I think transactional replication would work for this. However this will
require each table you are replicating to have a primary key.
I am a little confused by the data flow. Are you saying data moves from the
web server SQL Server database to another SQL Server? Or is it moving
internally to the SQL Server supporting the web site.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Scopus69" <Scopus69@.nospam.postalias> wrote in message
news:3D5CC7EA-4702-417E-AE2D-9985B1E4B781@.microsoft.com...
> We want to allow our customer base to be able to access their account
> information online. I want to setup a second SQL server so the customers
> can
> use this for looks up. The front end to access this info is web based.
> What replication method is the best one to use to update the database say
> every 24 hours at night? Thanks!!
|||Sorry for the confusion. The GUI interface to the data is a web interface
that connects to the backend SQL server. What I would like to do is setup
another web & SQL server for our cutomers so they can use it for lookups. I
really don't want them in our prduction DB.
I was wondering what is the best way to get the data off the production SQL
server to the customer SQL server on a nightly basis? I don't think log
shipping will work because it will put the shipped DB in "read only"
So what method would be the best to use? Thanks!
"Hilary Cotter" wrote:

> I think transactional replication would work for this. However this will
> require each table you are replicating to have a primary key.
> I am a little confused by the data flow. Are you saying data moves from the
> web server SQL Server database to another SQL Server? Or is it moving
> internally to the SQL Server supporting the web site.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Scopus69" <Scopus69@.nospam.postalias> wrote in message
> news:3D5CC7EA-4702-417E-AE2D-9985B1E4B781@.microsoft.com...
>
>
|||I think transactional is your best bet.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Scopus69" <Scopus69@.nospam.postalias> wrote in message
news:76F950CE-B1EA-493F-8092-5ED6AA73EC75@.microsoft.com...[vbcol=seagreen]
> Sorry for the confusion. The GUI interface to the data is a web interface
> that connects to the backend SQL server. What I would like to do is
> setup
> another web & SQL server for our cutomers so they can use it for lookups.
> I
> really don't want them in our prduction DB.
> I was wondering what is the best way to get the data off the production
> SQL
> server to the customer SQL server on a nightly basis? I don't think log
> shipping will work because it will put the shipped DB in "read only"
> So what method would be the best to use? Thanks!
> "Hilary Cotter" wrote:
|||I also like Transactional Replication if the data is dynamic at the source
and the users who will be talking to your target server need updated
information as well for their lookups. If current data is not an issue, that
is they don't mind the data being static, then may be snapshot will work.
But then again it depends on how large the data is. For me one way
Transactional seems to fit the bill here.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:enpFYKCDGHA.1028@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> I think transactional is your best bet.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Scopus69" <Scopus69@.nospam.postalias> wrote in message
> news:76F950CE-B1EA-493F-8092-5ED6AA73EC75@.microsoft.com...
interface[vbcol=seagreen]
lookups.[vbcol=seagreen]
will[vbcol=seagreen]
based.
>
sql

Best Query/Search Method

Hi,

I'm wondering about the following:

I have come across an InfoPath Forms application who's code is scripted in javascript and who's data seems to be in XML files.
An analyst at that company told me they suspect the data is ALSO in SQL Server... somewhere. They can't seem to find it though. I have
reviewed the .js code and some methods are called for which I can find no source. I believe those methods execute OK because they're found
inside some DLL.

I'm thinking I would enter a new record using the form in InfoPath using some datavalue that I can expect will be unique.. like a lastname who's first three chars is ZZZ or something like that. Subsequently, I'd search each column in each table in each DB on the server to see if I can locate it somewhere.

So, my question is what is the best approach for this? I have access to the db, table and column names. I know I can write a small vb.net piece of code to execute my search. But, is there some better way using some sql procedure (or using the full text catalog) instead or any other tool(s)?

Thanks in advance for your advise.

Stewart

Go into sql server management studio and open up a new query window, then set the query window to the the database in question.

Execute this query:

select 'union select ''[' + colu.table_schema + '].[' + colu.table_name + '].[' + colu.column_name + ']'' as "Schema.Table.Column" '
+ ',[' + colu.column_name + '] as "Value" '
+ ' from [' + colu.table_schema + '].[' + colu.table_name + '] '
+ ' where [' + colu.column_name + '] like ''ZZZ%'' '
from INFORMATION_SCHEMA.columns as colu
where colu.data_type in ('varchar','nvarchar','char','nchar','text','ntext')

Below the query will be the results, one sql statement per text-based column.

You can click, then right-click on the top-left button-looking box in the grid header and copy the text into the buffer.

Paste it into a new query window.

Delete the first "union" on the first line and execute the query.

It will return one row per column value per table that matches ZZZ%.

Depending upon the number of tables/columns in the database, and the number of rows in the table, you might need to split the results into multiple, smaller queries.

Enjoy!

|||

Hi David,

Thanks very much for the assistance. It worked perfectly!

Regards,
Stewart

|||

The views in the master database are very powerful. Try the information_schema views first, and switch to the sys... views if the information_schema views don't have what you need.

Glad it helped!

sql

Best Practices/Provider connecting to an Oracle Database?

Are there generalized best practices with regards to which method/provider to use when accessing an Oracle database? I have used both the "Native OLE DB\Microsoft OLE DB Provider for Oracle" and the "Native OLE DB\Oracle Provider for OLE DB" and both seem to have their own quirks (requirement to convert to Unicode, etc) but I also have heard that I shouldn't be using an "OLE DB" source at all, but to set it up as an ADO .Net connection.

We are just beginning to implement SSIS, and are trying to establish Best Practices/Standards etc.

Are there any gotchas - performance and/or otherwise I should know about?

Thanks in advance!

I'm assuming you've looked at the SSIS Connectivity whitepaper at http://ssis.wik.is/File:Connectivity_White_Paper/Connectivity_and_SQL_Server_Integration_Services_forum_post.doc (Oracle connectivity section).

We have plans to benchmark connectors in the future, when we might be able to share best practices and performance stats.

Best Practices Question?

Have a question about which method is the more accepted method.
I have two tables: table1 and table2.
They are joined by a primary and foreign key field:
table1
table1ID (Primary Key)
Room
NotAvailable (bit field: 1 will be not available and 0 will be available)
table2
table2ID (Primary Key)
table1ID (Foreign Key to table1)
DateUsed
There will only be records in table2 for rooms that are not available, so
when the two tables are inner joined, no matter how many records there are i
n
table1, the only records that will show up is what is matched in table2.
Question is: Is it good practice to use a field such as NotAvailable, to kno
w
that a room is not available, or use the results of the join to set a
NotAvailable property field in my code?
Thank you for any responses.
Note: I cannot use our company's actual field names, so disregard what the
names are and other ways to show a room as not available. Just want to show
the structure for the question I am asking.The problem with the NotAvailable field is that it requires modification
whenever there is an INSERT or Update in another table. And then the
question arises: "What exactly does NotAvailable mean since there is no time
period included". Is it NotAvailable today, tomorrow, next week, etc. That
provides opportunities for de-synching of the data -unless there is a
Trigger on the Table2.
Using a query joining the two tables (perhaps including a calendar table for
future dates) 'should' always provide an accurate presentation of data. (I'm
thinking of tables for Rooms and Reservations -therefore the Calendar table
is needed.)
It's not a 'Best Practice' to store data that is the result of some
manipulation of other data. But with careful planning, sometimes it has to
be done.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:AA6DB4DC-1E7E-4007-AEDA-C8355D385A28@.microsoft.com...
> Have a question about which method is the more accepted method.
> I have two tables: table1 and table2.
> They are joined by a primary and foreign key field:
> table1
> table1ID (Primary Key)
> Room
> NotAvailable (bit field: 1 will be not available and 0 will be available)
> table2
> table2ID (Primary Key)
> table1ID (Foreign Key to table1)
> DateUsed
> There will only be records in table2 for rooms that are not available, so
> when the two tables are inner joined, no matter how many records there are
> in
> table1, the only records that will show up is what is matched in table2.
> Question is: Is it good practice to use a field such as NotAvailable, to
> know
> that a room is not available, or use the results of the join to set a
> NotAvailable property field in my code?
> Thank you for any responses.
> Note: I cannot use our company's actual field names, so disregard what the
> names are and other ways to show a room as not available. Just want to
> show
> the structure for the question I am asking.|||Mike Collins wrote:
> Have a question about which method is the more accepted method.
> I have two tables: table1 and table2.
> They are joined by a primary and foreign key field:
> table1
> table1ID (Primary Key)
> Room
> NotAvailable (bit field: 1 will be not available and 0 will be available)
> table2
> table2ID (Primary Key)
> table1ID (Foreign Key to table1)
> DateUsed
> There will only be records in table2 for rooms that are not available, so
> when the two tables are inner joined, no matter how many records there are
in
> table1, the only records that will show up is what is matched in table2.
> Question is: Is it good practice to use a field such as NotAvailable, to k
now
> that a room is not available, or use the results of the join to set a
> NotAvailable property field in my code?
> Thank you for any responses.
> Note: I cannot use our company's actual field names, so disregard what the
> names are and other ways to show a room as not available. Just want to sho
w
> the structure for the question I am asking.
You should rely on the data in table2 to determine if a room is
available. Using the bit field, you're exposing yourself to potentially
out-of-sync data, and you're duplicating the room status.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you to both of you for your replies. That is the way I was thinking it
should be just needed a better way to explain it and get my point across.
"Mike Collins" wrote:

> Have a question about which method is the more accepted method.
> I have two tables: table1 and table2.
> They are joined by a primary and foreign key field:
> table1
> table1ID (Primary Key)
> Room
> NotAvailable (bit field: 1 will be not available and 0 will be available)
> table2
> table2ID (Primary Key)
> table1ID (Foreign Key to table1)
> DateUsed
> There will only be records in table2 for rooms that are not available, so
> when the two tables are inner joined, no matter how many records there are
in
> table1, the only records that will show up is what is matched in table2.
> Question is: Is it good practice to use a field such as NotAvailable, to k
now
> that a room is not available, or use the results of the join to set a
> NotAvailable property field in my code?
> Thank you for any responses.
> Note: I cannot use our company's actual field names, so disregard what the
> names are and other ways to show a room as not available. Just want to sho
w
> the structure for the question I am asking.

Best Practices Question?

Have a question about which method is the more accepted method.
I have two tables: table1 and table2.
They are joined by a primary and foreign key field:
table1
table1ID (Primary Key)
Room
NotAvailable (bit field: 1 will be not available and 0 will be available)
table2
table2ID (Primary Key)
table1ID (Foreign Key to table1)
DateUsed
There will only be records in table2 for rooms that are not available, so
when the two tables are inner joined, no matter how many records there are in
table1, the only records that will show up is what is matched in table2.
Question is: Is it good practice to use a field such as NotAvailable, to know
that a room is not available, or use the results of the join to set a
NotAvailable property field in my code?
Thank you for any responses.
Note: I cannot use our company's actual field names, so disregard what the
names are and other ways to show a room as not available. Just want to show
the structure for the question I am asking.The problem with the NotAvailable field is that it requires modification
whenever there is an INSERT or Update in another table. And then the
question arises: "What exactly does NotAvailable mean since there is no time
period included". Is it NotAvailable today, tomorrow, next week, etc. That
provides opportunities for de-synching of the data -unless there is a
Trigger on the Table2.
Using a query joining the two tables (perhaps including a calendar table for
future dates) 'should' always provide an accurate presentation of data. (I'm
thinking of tables for Rooms and Reservations -therefore the Calendar table
is needed.)
It's not a 'Best Practice' to store data that is the result of some
manipulation of other data. But with careful planning, sometimes it has to
be done.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:AA6DB4DC-1E7E-4007-AEDA-C8355D385A28@.microsoft.com...
> Have a question about which method is the more accepted method.
> I have two tables: table1 and table2.
> They are joined by a primary and foreign key field:
> table1
> table1ID (Primary Key)
> Room
> NotAvailable (bit field: 1 will be not available and 0 will be available)
> table2
> table2ID (Primary Key)
> table1ID (Foreign Key to table1)
> DateUsed
> There will only be records in table2 for rooms that are not available, so
> when the two tables are inner joined, no matter how many records there are
> in
> table1, the only records that will show up is what is matched in table2.
> Question is: Is it good practice to use a field such as NotAvailable, to
> know
> that a room is not available, or use the results of the join to set a
> NotAvailable property field in my code?
> Thank you for any responses.
> Note: I cannot use our company's actual field names, so disregard what the
> names are and other ways to show a room as not available. Just want to
> show
> the structure for the question I am asking.|||Mike Collins wrote:
> Have a question about which method is the more accepted method.
> I have two tables: table1 and table2.
> They are joined by a primary and foreign key field:
> table1
> table1ID (Primary Key)
> Room
> NotAvailable (bit field: 1 will be not available and 0 will be available)
> table2
> table2ID (Primary Key)
> table1ID (Foreign Key to table1)
> DateUsed
> There will only be records in table2 for rooms that are not available, so
> when the two tables are inner joined, no matter how many records there are in
> table1, the only records that will show up is what is matched in table2.
> Question is: Is it good practice to use a field such as NotAvailable, to know
> that a room is not available, or use the results of the join to set a
> NotAvailable property field in my code?
> Thank you for any responses.
> Note: I cannot use our company's actual field names, so disregard what the
> names are and other ways to show a room as not available. Just want to show
> the structure for the question I am asking.
You should rely on the data in table2 to determine if a room is
available. Using the bit field, you're exposing yourself to potentially
out-of-sync data, and you're duplicating the room status.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you to both of you for your replies. That is the way I was thinking it
should be just needed a better way to explain it and get my point across.
"Mike Collins" wrote:
> Have a question about which method is the more accepted method.
> I have two tables: table1 and table2.
> They are joined by a primary and foreign key field:
> table1
> table1ID (Primary Key)
> Room
> NotAvailable (bit field: 1 will be not available and 0 will be available)
> table2
> table2ID (Primary Key)
> table1ID (Foreign Key to table1)
> DateUsed
> There will only be records in table2 for rooms that are not available, so
> when the two tables are inner joined, no matter how many records there are in
> table1, the only records that will show up is what is matched in table2.
> Question is: Is it good practice to use a field such as NotAvailable, to know
> that a room is not available, or use the results of the join to set a
> NotAvailable property field in my code?
> Thank you for any responses.
> Note: I cannot use our company's actual field names, so disregard what the
> names are and other ways to show a room as not available. Just want to show
> the structure for the question I am asking.sql

Best Practices Database Owner, Database Connection Method (asp)

Hi-

I have a sql server database, and am wring web apps to access it.

I've created databases different ways, and ended up with different owners (eg dbo, nt authority\network services...)

I also have connection strings using windows authentication, and some using a user name and password.

I have read that using windows authentication is the best way to go, as far as security goes, but I have noticed some connectivity issues when I upload the site to the server, and test it remotely.

What is the safest 'owner' of the database, and what's the safest way to connect?

Thanks

Dan

You may get somewhat different details from different people but I think most will agree with what I'm about to say (I may live to regret those words!). Remember that the goal is give your users a little privileges as possible

owner of the database should be dbo

|||

Create a login which has an entry in your Active Directory (AD)*, and give it the needed permissions.

Map that login to a database user (name it MyAppUser), this user has only needed permisions on the database (e.g. execute stored procedures and maybe SELECTing some fields from some tables).

Use Windows Authentication if it is possible.

Encrypt your ConnectionString in your Web.Config file.

*: you can enforce some policies like password has to be strong and changed every two weeks or months. Old password can not be used and some policies that can increase the security.

Remember: Too much security doesn't always good.

Good luck.

|||

One more thing I would like to mention is try to use stored procedures ONLY as much as you can.

This will increase the performance (usually) and make your App secure (e.g. SQL Injuction).

Try to not thatMyAppUserother thatEXECstored procedures.

Insred of sending a lot of T-SQL statments over the network, you will just send the stored procedure name.. and once it is executed it will be cached (better performance for later execution).

Make you logic in the stored procedure, allow you to change the logic later -if needed- without redeploying the application or compiling it.

Good luck.

|||

OK, so stored procedures seems to be a common theme.

hodw do I best use them(SP), and use the GUI advantage of visual studio.net?

Do I write, say a SP called "SP_Update_Client()" Then have the asp.net page call

"SP_Update_Client("Param1","Param2")

and how do I get a hold of the stored procedure IN Visual studio?

thanks

dan

(Im getting lazzy in this GUI world)

|||

You don't "get hold" of a proc like you would, say, a dll. You create a sql command and attach parameters to it as in this example http://www.codeproject.com/useritems/simplecodeasp.asp

Note esp their use of output parameters to return data

|||

hummm-

I think Im starting to get it.

If I am writing a small app (500 users, connecting 10 - 25 x a week) will I notice a benifit of procs? in speed? Or is it more of a security issue at this size?

Thanks so much for the discussion an the artilce

|||

Harperator:

If I am writing a small app (500 users, connecting 10 - 25 x a week) will I notice a benifit of procs? in speed? Or is it more of a security issue at this size?

Stored Procedure = Both security + performance, but the main thing here is the security especially SQL Injuction.

Good luck.

|||

Agree with CS4Ever's statement

Best practice method for shrinking the log file in dev environments

Hi all,
Can someone tell me what the best way to reduce my log file size is when
it gets too big. I can't switch recovery mode to Simple but every so
often I'd like to go in and clear it out.
What is the preffered command to do this?
I've heard the backup command with the TRUNCATE_ONLY isnt the best way
to do this? Is that the case and if so, whats the alternative?
Also, could someone tell me if doing a full backup automatically
truncates the transaction log?
Many thanks
SimonHello,
Could someone tell me if doing a full backup automatically truncates the
transaction log?
NO, FULL database backup will not clear the transaction log. You need to
backup the transaction log backup using BACKUP LOG to clear the log or else
if you do not
want the transaction log backup you could use Backup LOG with TRUNCATE_ONLY
to clear the transaction log from LDF file.
If you do not require a Transaction log backup then change the recovery
model for the database to "SIMPLE", in this
case after the commit the transaction log will be cleared. This recovery
mode will not allow transaction log backup.
In the otherway around, if your data is very critical / production data, set
the recovery model to "FULL". This allows you to perform
a transaction log backup. In this model after the commit the transaction log
still remains in the log file and will get cleared
when you perform a backup of log or issue "Truncate_only". So Truncate_only
is not a good option in production server.
If it is production / critical database follow the steps:-
1. Set the database recovery model to "FULL"
2. Perform a Full database backup once
3. Schedule Transaction log backup using (Backup Log dbname to
disk='d:\backup\dbname.tr1'
4. Perform the step 3 every 30 minutes (decide up on the volume of
transaction), but give new file names each backup dbname.tr1,...tr2...tr3
5. After the step 3 and 4 the transaction log will be cleared from
transaction log file
if you follow this step, even if yor database creach you can recover till
the last transaction log backup as well you can do a PINT_IN_TIME recovery
if needed
If it is non production or data is not critical
1. Set the recovery model to "SIMPLE"
2. Perform a Full database backup daily
3. If needed once in a while you can execute backup log dbname with
truncate_only
If you this methodology we can restore only till last backup.
Thanks
Hari
"Simon" <simon@.nothanks.com> wrote in message
news:%23$ozNe8RHHA.3440@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Can someone tell me what the best way to reduce my log file size is when
> it gets too big. I can't switch recovery mode to Simple but every so often
> I'd like to go in and clear it out.
> What is the preffered command to do this?
> I've heard the backup command with the TRUNCATE_ONLY isnt the best way to
> do this? Is that the case and if so, whats the alternative?
> Also, could someone tell me if doing a full backup automatically truncates
> the transaction log?
> Many thanks
> Simon|||Thats a great answer - thanks sincerely for your time and advice
Kindest Regards
Simon

Best practice method for shrinking the log file in dev environments

Hi all,
Can someone tell me what the best way to reduce my log file size is when
it gets too big. I can't switch recovery mode to Simple but every so
often I'd like to go in and clear it out.
What is the preffered command to do this?
I've heard the backup command with the TRUNCATE_ONLY isnt the best way
to do this? Is that the case and if so, whats the alternative?
Also, could someone tell me if doing a full backup automatically
truncates the transaction log?
Many thanks
SimonHello,
Could someone tell me if doing a full backup automatically truncates the
transaction log?
NO, FULL database backup will not clear the transaction log. You need to
backup the transaction log backup using BACKUP LOG to clear the log or else
if you do not
want the transaction log backup you could use Backup LOG with TRUNCATE_ONLY
to clear the transaction log from LDF file.
If you do not require a Transaction log backup then change the recovery
model for the database to "SIMPLE", in this
case after the commit the transaction log will be cleared. This recovery
mode will not allow transaction log backup.
In the otherway around, if your data is very critical / production data, set
the recovery model to "FULL". This allows you to perform
a transaction log backup. In this model after the commit the transaction log
still remains in the log file and will get cleared
when you perform a backup of log or issue "Truncate_only". So Truncate_only
is not a good option in production server.
If it is production / critical database follow the steps:-
1. Set the database recovery model to "FULL"
2. Perform a Full database backup once
3. Schedule Transaction log backup using (Backup Log dbname to
disk='d:\backup\dbname.tr1'
4. Perform the step 3 every 30 minutes (decide up on the volume of
transaction), but give new file names each backup dbname.tr1,...tr2...tr3
5. After the step 3 and 4 the transaction log will be cleared from
transaction log file
if you follow this step, even if yor database creach you can recover till
the last transaction log backup as well you can do a PINT_IN_TIME recovery
if needed
If it is non production or data is not critical
1. Set the recovery model to "SIMPLE"
2. Perform a Full database backup daily
3. If needed once in a while you can execute backup log dbname with
truncate_only
If you this methodology we can restore only till last backup.
Thanks
Hari
"Simon" <simon@.nothanks.com> wrote in message
news:%23$ozNe8RHHA.3440@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Can someone tell me what the best way to reduce my log file size is when
> it gets too big. I can't switch recovery mode to Simple but every so often
> I'd like to go in and clear it out.
> What is the preffered command to do this?
> I've heard the backup command with the TRUNCATE_ONLY isnt the best way to
> do this? Is that the case and if so, whats the alternative?
> Also, could someone tell me if doing a full backup automatically truncates
> the transaction log?
> Many thanks
> Simon|||Thats a great answer - thanks sincerely for your time and advice
Kindest Regards
Simon

Best practice method for shrinking the log file in dev environments

Hi all,
Can someone tell me what the best way to reduce my log file size is when
it gets too big. I can't switch recovery mode to Simple but every so
often I'd like to go in and clear it out.
What is the preffered command to do this?
I've heard the backup command with the TRUNCATE_ONLY isnt the best way
to do this? Is that the case and if so, whats the alternative?
Also, could someone tell me if doing a full backup automatically
truncates the transaction log?
Many thanks
Simon
Hello,
Could someone tell me if doing a full backup automatically truncates the
transaction log?
NO, FULL database backup will not clear the transaction log. You need to
backup the transaction log backup using BACKUP LOG to clear the log or else
if you do not
want the transaction log backup you could use Backup LOG with TRUNCATE_ONLY
to clear the transaction log from LDF file.
If you do not require a Transaction log backup then change the recovery
model for the database to "SIMPLE", in this
case after the commit the transaction log will be cleared. This recovery
mode will not allow transaction log backup.
In the otherway around, if your data is very critical / production data, set
the recovery model to "FULL". This allows you to perform
a transaction log backup. In this model after the commit the transaction log
still remains in the log file and will get cleared
when you perform a backup of log or issue "Truncate_only". So Truncate_only
is not a good option in production server.
If it is production / critical database follow the steps:-
1. Set the database recovery model to "FULL"
2. Perform a Full database backup once
3. Schedule Transaction log backup using (Backup Log dbname to
disk='d:\backup\dbname.tr1'
4. Perform the step 3 every 30 minutes (decide up on the volume of
transaction), but give new file names each backup dbname.tr1,...tr2...tr3
5. After the step 3 and 4 the transaction log will be cleared from
transaction log file
if you follow this step, even if yor database creach you can recover till
the last transaction log backup as well you can do a PINT_IN_TIME recovery
if needed
If it is non production or data is not critical
1. Set the recovery model to "SIMPLE"
2. Perform a Full database backup daily
3. If needed once in a while you can execute backup log dbname with
truncate_only
If you this methodology we can restore only till last backup.
Thanks
Hari
"Simon" <simon@.nothanks.com> wrote in message
news:%23$ozNe8RHHA.3440@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Can someone tell me what the best way to reduce my log file size is when
> it gets too big. I can't switch recovery mode to Simple but every so often
> I'd like to go in and clear it out.
> What is the preffered command to do this?
> I've heard the backup command with the TRUNCATE_ONLY isnt the best way to
> do this? Is that the case and if so, whats the alternative?
> Also, could someone tell me if doing a full backup automatically truncates
> the transaction log?
> Many thanks
> Simon
|||Thats a great answer - thanks sincerely for your time and advice
Kindest Regards
Simon

Best method: TOP 1 or DISTINCT or MAX

'TOP 1' or 'DISTINCT' or 'MAX'
Any sugestions on which is better to use if I need to select a record that has the highest value - could be a INT or sometimes a DATETIME.Distinct will not get you a max value, if you use top make sure you use the order by.

HTH|||To select the entire record, use TOP 1 on a sorted recordset. To get just the highest value for the field, use MAX().|||[blindman]: someone else suggested that TOP is more efficient then MAX is that true?|||I don't know. It probably depends on a lot of factors and makes little difference either way.|||Best way to test this is to use the "set stistics IO on" command to check your logical IO (number of times you hit a page)|||Originally posted by rhigdon
Best way to test this is to use the "set stistics IO on" command to check your logical IO (number of times you hit a page)

I used "SET STATISTICS IO ON" command and got a line for each table in query...

Table 'tblUser'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.
Table 'ctsJrn_Location'. Scan count 7, logical reads 14, physical reads 2, read-ahead reads 0.
Table 'ctsIndex'. Scan count 8, logical reads 16, physical reads 0, read-ahead reads 0.

Can anyone tell me what does each count of 'reads' mean?

Thanks,
Lito|||Scan count - number of times data or clustered index pages were scanned;
Logical reads - total number of records read from cache (I think);
Physical reads - total number of pages read from disk (I think);
Read-ahead reads - number of pages optimizer chose to read ahead (I think)

But the point is, you want to minimize the first 2 indicators. And, BTW, scan count does not always mean that the actual scan occurred. It just means that the optimizer had to look at data/clustered index pages of the corresponding table so many times.|||Originally posted by rdjabarov
... But the point is, you want to minimize the first 2 indicators...

What range should those indicators be in, are mine ok?|||It depends on number of rows the tables have vs. number of rows returned.|||but is there a ratio?

I am selecting one row from 9 joint tables with approx. 18k records each|||Then your numbers are actually good.|||The only counter I truly look at is logical IO as it is the number of times a page is hit (not number of pages) the lower you canb get this the better. The problem with physical and read-aheads is they can be optimistic and not exactly accurate.

HTH|||your physical reads should be zero or as close to zero as possible.
this means that you are reading pages from disk into memory.. that is something that you want as little of as possible.

you will want logical reads to be as low as possible as well but those numbers are based on the actual work that SQLSVR had to perform to retrieve your query. so the number is academic based on your query, statistics, indexing etc.

typically you should only retrive the rows that you need in a query result, so if the question is which would be the best query to perform? so if you want to just get one row the logical answer would be an aggregate function

Select Max(Col1) as 'MAXNUM' from table2
this will retrieve a scalar value for you (one row one column)
ex
MAXNUM
=====
100

as far as distinct and top, are concerned
DISTINCT does not give you a max value, it removes duplicates from the columns gueried which i guess you could then sort decending to get the largest value
""select distinct state from table2 order by state Desc""
ex
STATE
====
TX
GA
FL
CA

TOP 'n' is designed to return an restricted set of values
""select TOP 5 col1 from table2 order by col1 desc""

COL1
====
5
4
3
2
1

your best method here would be to run the query with each of the different types of commands
view the stats io and compare all three.|||Thank you all for your comments and sugestions, this helped me alot. Learn something new every day...

Lito

Best Method to update table...

Hy everyone.
I've got a little question regarding the speed of an update query...

situation:
I've got different tables containing information wich i want to add to one big table trough a schedule (or as fast as possible).

Bigtable size:
est. 180000 records with 25 fields (most varchar).

Currently I've tried two different methods:
delete all rows in the big table and add the ones from the little tables again. (trough union all query)
-> Speed ~ 15 Seconds

refresh all changed rows (trough timestamp <>) and add new titles (trough union all query)
-> Speed ~ 20 Seconds

Does anybody know a faster solution? The union queries block the table for those 20 Seconds...

Thanks for any reply!RE: situation: I've got different tables containing information which i want to add to one big table trough a schedule (or as fast as possible).
Bigtable size:
est. 180000 records with 25 fields (most varchar).

Currently I've tried two different methods:
delete all rows in the big table and add the ones from the little tables again. (trough union all query)
-> Speed ~ 15 Seconds

refresh all changed rows (trough timestamp <>) and add new titles (trough union all query)
-> Speed ~ 20 Seconds

Q1 Does anybody know a faster solution? The union queries block the table for those 20 Seconds... Thanks for any reply!

A1 Maybe.

As with many things, it depends on the requirements. For example, some possible considerations may include various permutations and combinations of any of the following: (not an exhaustive list)
a using a lower isolation level for the union queries, and conditionally unioning only updated tables
b implementing triggers to update the target as dml is commited at the source tables
c a create, populate, and rename table scheme (dropping the old table)

Best method to transfer data

Hi Group,
I just started at a company and am trying to come up with a solution
to streamline the datawarehouse.
The problem is, we have two databases. Database1 (548 tables) is
generated from user input and we cannot control the schema. Database2
(40 tables) is a staging DB that optimally will contain some of the
Creates and Updates from the previous day from within Database1.
Database2 is built from a conglomeration of tables in Database1,
therefore we have created 40 views which encapsulates data from
multiple tables in Database1 and are using DTS to call these views and
populate Database2 with a snapshot.
There are 2 problems with the above setup. First is, we do not need
to take an entire snapshot of the views to populate Database2, we only
need the previous days changes (the DB is growing and we cannot afford
it). Second, DTS is a pain because we are using a separate view for
every table and a separate DTS package to copy every view to
Database2. Maintenance is tough.
Currently, we are investigating the use of triggers, but I think this
will end up being a maintenance nightmare also. Is there anyway to
use replication in conjunction with views to copy *only* the previous
days changes to the other Database? Or does anyone have any other
suggestions to the best way to set this up? *Any* insight or advice
on a better setup is welcome.
Thanks much,
Derek
Derek,
I haven't set this up for a while, but transactional replication of indexed
views would seem to meet your requirements.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
|||Thanks very much Paul. Because of your suggestion, I am investigating
using this method.
I read that indexed views tax the system it runs on, so I'm looking at
using transactional replication to replicate the data to another box
which maintains the indexed views, then publish that data to the box
that needs it.
Thanks again,
Derek
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<OrKxNNUtEHA.1548@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> Derek,
> I haven't set this up for a while, but transactional replication of indexed
> views would seem to meet your requirements.
> HTH,
> Paul Ibison (SQL Server MVP)
|||Derek take a look at Trey Johnsons DTS Best Practices for Business
Intelligence white paper in msdn online it should steer you in the right
direction as far as coming up with a standard data capture methodology
"derek" wrote:

> Hi Group,
> I just started at a company and am trying to come up with a solution
> to streamline the datawarehouse.
> The problem is, we have two databases. Database1 (548 tables) is
> generated from user input and we cannot control the schema. Database2
> (40 tables) is a staging DB that optimally will contain some of the
> Creates and Updates from the previous day from within Database1.
> Database2 is built from a conglomeration of tables in Database1,
> therefore we have created 40 views which encapsulates data from
> multiple tables in Database1 and are using DTS to call these views and
> populate Database2 with a snapshot.
> There are 2 problems with the above setup. First is, we do not need
> to take an entire snapshot of the views to populate Database2, we only
> need the previous days changes (the DB is growing and we cannot afford
> it). Second, DTS is a pain because we are using a separate view for
> every table and a separate DTS package to copy every view to
> Database2. Maintenance is tough.
> Currently, we are investigating the use of triggers, but I think this
> will end up being a maintenance nightmare also. Is there anyway to
> use replication in conjunction with views to copy *only* the previous
> days changes to the other Database? Or does anyone have any other
> suggestions to the best way to set this up? *Any* insight or advice
> on a better setup is welcome.
> Thanks much,
> Derek
>
|||Wow... About 95% of that article is over my head. I have a lot of
research to do. I was actually beginning to thing that SQL server was
limited in it's DataWarehousing. How wrong was I.
Thanks,
Derek
Richard S. Hale <RichardSHale@.discussions.microsoft.com> wrote in message news:<351947F4-60EE-426D-9F94-D0EE55083C93@.microsoft.com>...[vbcol=seagreen]
> Derek take a look at Trey Johnsons DTS Best Practices for Business
> Intelligence white paper in msdn online it should steer you in the right
> direction as far as coming up with a standard data capture methodology
> "derek" wrote:

Best method to transfer data

Hi Group,
I just started at a company and am trying to come up with a solution
to streamline the datawarehouse.
The problem is, we have two databases. Database1 (548 tables) is
generated from user input and we cannot control the schema. Database2
(40 tables) is a staging DB that optimally will contain some of the
Creates and Updates from the previous day from within Database1.
Database2 is built from a conglomeration of tables in Database1,
therefore we have created 40 views which encapsulates data from
multiple tables in Database1 and are using DTS to call these views and
populate Database2 with a snapshot.
There are 2 problems with the above setup. First is, we do not need
to take an entire snapshot of the views to populate Database2, we only
need the previous days changes (the DB is growing and we cannot afford
it). Second, DTS is a pain because we are using a separate view for
every table and a separate DTS package to copy every view to
Database2. Maintenance is tough.
Currently, we are investigating the use of triggers, but I think this
will end up being a maintenance nightmare also. Is there anyway to
use replication in conjunction with views to copy *only* the previous
days changes to the other Database? Or does anyone have any other
suggestions to the best way to set this up? *Any* insight or advice
on a better setup is welcome.
Thanks much,
Derek
Derek,
I haven't set this up for a while, but transactional replication of indexed
views would seem to meet your requirements.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
|||Thanks very much Paul. Because of your suggestion, I am investigating
using this method.
I read that indexed views tax the system it runs on, so I'm looking at
using transactional replication to replicate the data to another box
which maintains the indexed views, then publish that data to the box
that needs it.
Thanks again,
Derek
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<OrKxNNUtEHA.1548@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> Derek,
> I haven't set this up for a while, but transactional replication of indexed
> views would seem to meet your requirements.
> HTH,
> Paul Ibison (SQL Server MVP)
|||Derek take a look at Trey Johnsons DTS Best Practices for Business
Intelligence white paper in msdn online it should steer you in the right
direction as far as coming up with a standard data capture methodology
"derek" wrote:

> Hi Group,
> I just started at a company and am trying to come up with a solution
> to streamline the datawarehouse.
> The problem is, we have two databases. Database1 (548 tables) is
> generated from user input and we cannot control the schema. Database2
> (40 tables) is a staging DB that optimally will contain some of the
> Creates and Updates from the previous day from within Database1.
> Database2 is built from a conglomeration of tables in Database1,
> therefore we have created 40 views which encapsulates data from
> multiple tables in Database1 and are using DTS to call these views and
> populate Database2 with a snapshot.
> There are 2 problems with the above setup. First is, we do not need
> to take an entire snapshot of the views to populate Database2, we only
> need the previous days changes (the DB is growing and we cannot afford
> it). Second, DTS is a pain because we are using a separate view for
> every table and a separate DTS package to copy every view to
> Database2. Maintenance is tough.
> Currently, we are investigating the use of triggers, but I think this
> will end up being a maintenance nightmare also. Is there anyway to
> use replication in conjunction with views to copy *only* the previous
> days changes to the other Database? Or does anyone have any other
> suggestions to the best way to set this up? *Any* insight or advice
> on a better setup is welcome.
> Thanks much,
> Derek
>
|||Wow... About 95% of that article is over my head. I have a lot of
research to do. I was actually beginning to thing that SQL server was
limited in it's DataWarehousing. How wrong was I.
Thanks,
Derek
Richard S. Hale <RichardSHale@.discussions.microsoft.com> wrote in message news:<351947F4-60EE-426D-9F94-D0EE55083C93@.microsoft.com>...[vbcol=seagreen]
> Derek take a look at Trey Johnsons DTS Best Practices for Business
> Intelligence white paper in msdn online it should steer you in the right
> direction as far as coming up with a standard data capture methodology
> "derek" wrote:

Best method to transfer data

Hi Group,
I just started at a company and am trying to come up with a solution
to streamline the datawarehouse.
The problem is, we have two databases. Database1 (548 tables) is
generated from user input and we cannot control the schema. Database2
(40 tables) is a staging DB that optimally will contain some of the
Creates and Updates from the previous day from within Database1.
Database2 is built from a conglomeration of tables in Database1,
therefore we have created 40 views which encapsulates data from
multiple tables in Database1 and are using DTS to call these views and
populate Database2 with a snapshot.
There are 2 problems with the above setup. First is, we do not need
to take an entire snapshot of the views to populate Database2, we only
need the previous days changes (the DB is growing and we cannot afford
it). Second, DTS is a pain because we are using a separate view for
every table and a separate DTS package to copy every view to
Database2. Maintenance is tough.
Currently, we are investigating the use of triggers, but I think this
will end up being a maintenance nightmare also. Is there anyway to
use replication in conjunction with views to copy *only* the previous
days changes to the other Database? Or does anyone have any other
suggestions to the best way to set this up? *Any* insight or advice
on a better setup is welcome.
Thanks much,
DerekDerek,
I haven't set this up for a while, but transactional replication of indexed
views would seem to meet your requirements.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]|||Thanks very much Paul. Because of your suggestion, I am investigating
using this method.
I read that indexed views tax the system it runs on, so I'm looking at
using transactional replication to replicate the data to another box
which maintains the indexed views, then publish that data to the box
that needs it.
Thanks again,
Derek
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<OrKxNNUtEHA.1548@.TK2MSFTNGP
10.phx.gbl>...[vbcol=seagreen]
> Derek,
> I haven't set this up for a while, but transactional replication of indexe
d
> views would seem to meet your requirements.
> HTH,
> Paul Ibison (SQL Server MVP)
>|||Derek take a look at Trey Johnsons DTS Best Practices for Business
Intelligence white paper in msdn online it should steer you in the right
direction as far as coming up with a standard data capture methodology
"derek" wrote:

> Hi Group,
> I just started at a company and am trying to come up with a solution
> to streamline the datawarehouse.
> The problem is, we have two databases. Database1 (548 tables) is
> generated from user input and we cannot control the schema. Database2
> (40 tables) is a staging DB that optimally will contain some of the
> Creates and Updates from the previous day from within Database1.
> Database2 is built from a conglomeration of tables in Database1,
> therefore we have created 40 views which encapsulates data from
> multiple tables in Database1 and are using DTS to call these views and
> populate Database2 with a snapshot.
> There are 2 problems with the above setup. First is, we do not need
> to take an entire snapshot of the views to populate Database2, we only
> need the previous days changes (the DB is growing and we cannot afford
> it). Second, DTS is a pain because we are using a separate view for
> every table and a separate DTS package to copy every view to
> Database2. Maintenance is tough.
> Currently, we are investigating the use of triggers, but I think this
> will end up being a maintenance nightmare also. Is there anyway to
> use replication in conjunction with views to copy *only* the previous
> days changes to the other Database? Or does anyone have any other
> suggestions to the best way to set this up? *Any* insight or advice
> on a better setup is welcome.
> Thanks much,
> Derek
>|||Wow... About 95% of that article is over my head. I have a lot of
research to do. I was actually beginning to thing that SQL server was
limited in it's DataWarehousing. How wrong was I.
Thanks,
Derek
Richard S. Hale <RichardSHale@.discussions.microsoft.com> wrote in message news:<351947F4-60E
E-426D-9F94-D0EE55083C93@.microsoft.com>...[vbcol=seagreen]
> Derek take a look at Trey Johnsons DTS Best Practices for Business
> Intelligence white paper in msdn online it should steer you in the right
> direction as far as coming up with a standard data capture methodology
> "derek" wrote:
>

Best method to migrate data?

Hello all:
The task: migrate large amounts of data from multiple machines to
corresponding databases on one machine, which will serve as a data
warehouse. The warehouse machine will have no transactions, so I plan
to have logging turned off for it.
I understand that, although tables on the warehouse will be heavily
indexed, that I will want to disable the indices and constraints prior
to the bulk loads.
My question: what is the best methodology to actually move the data?
Should I use DTS packages, or export to files and use BCP, or ?
Also, should I be content to clear out and reload the warehouse tables
to be sure to catch any changes to existing records from the production
database, or is it more feasible from a performance standpoint to
update existing records and only insert new records?
Many thanks,
zdrakec"zdrakec" <zdrakec@.yahoo.com> wrote in message
news:1147096374.882929.83610@.i39g2000cwa.googlegroups.com...
> Hello all:
> The task: migrate large amounts of data from multiple machines to
> corresponding databases on one machine, which will serve as a data
> warehouse. The warehouse machine will have no transactions, so I plan
> to have logging turned off for it.
You can't turn off logging.
> I understand that, although tables on the warehouse will be heavily
> indexed, that I will want to disable the indices and constraints prior
> to the bulk loads.
> My question: what is the best methodology to actually move the data?
> Should I use DTS packages, or export to files and use BCP, or ?
Use SSIS. No question. It doesn't matter what versions of SQL Server you
are using. SSIS is the right tool and it can load whatever you have.
> Also, should I be content to clear out and reload the warehouse tables
> to be sure to catch any changes to existing records from the production
> database, or is it more feasible from a performance standpoint to
> update existing records and only insert new records?
>
It depends. Consider loading a staging table. Then you can mix and match
INSERT, UPDATE, DELETE to fit your needs.
David|||Hello David:
Thank you for your remarks.
I was under the impression that the database could be started in a
no-logging mode. Am I mistaken, then, in this impression?
Also, I am unfamiliar with SSIS. Can you point me towards information
about it?
Thanks much,
zdrakec|||"zdrakec" <zdrakec@.yahoo.com> wrote in message
news:1147100405.620218.169540@.j33g2000cwa.googlegroups.com...
> Hello David:
> Thank you for your remarks.
> I was under the impression that the database could be started in a
> no-logging mode. Am I mistaken, then, in this impression?
Yes. In the Simple recovery model the log is still written. It's just
truncated occasionally so it doesn't grow.
This doc is 2005, but the recovery models are the same in 2000.
Overview of the Recovery Models
http://msdn2.microsoft.com/en-us/library/ms189275.aspx
> Also, I am unfamiliar with SSIS. Can you point me towards information
> about it?
>
http://www.microsoft.com/sql/technologies/integration/default.mspx
http://msdn2.microsoft.com/en-us/library/ms141263.aspx
http://www.sqlis.com/
David|||Thank you sir!!

Best method to migrate data?

Hello all:
The task: migrate large amounts of data from multiple machines to
corresponding databases on one machine, which will serve as a data
warehouse. The warehouse machine will have no transactions, so I plan
to have logging turned off for it.
I understand that, although tables on the warehouse will be heavily
indexed, that I will want to disable the indices and constraints prior
to the bulk loads.
My question: what is the best methodology to actually move the data?
Should I use DTS packages, or export to files and use BCP, or ?
Also, should I be content to clear out and reload the warehouse tables
to be sure to catch any changes to existing records from the production
database, or is it more feasible from a performance standpoint to
update existing records and only insert new records?
Many thanks,
zdrakec"zdrakec" <zdrakec@.yahoo.com> wrote in message
news:1147096374.882929.83610@.i39g2000cwa.googlegroups.com...
> Hello all:
> The task: migrate large amounts of data from multiple machines to
> corresponding databases on one machine, which will serve as a data
> warehouse. The warehouse machine will have no transactions, so I plan
> to have logging turned off for it.
You can't turn off logging.

> I understand that, although tables on the warehouse will be heavily
> indexed, that I will want to disable the indices and constraints prior
> to the bulk loads.
> My question: what is the best methodology to actually move the data?
> Should I use DTS packages, or export to files and use BCP, or ?
Use SSIS. No question. It doesn't matter what versions of SQL Server you
are using. SSIS is the right tool and it can load whatever you have.

> Also, should I be content to clear out and reload the warehouse tables
> to be sure to catch any changes to existing records from the production
> database, or is it more feasible from a performance standpoint to
> update existing records and only insert new records?
>
It depends. Consider loading a staging table. Then you can mix and match
INSERT, UPDATE, DELETE to fit your needs.
David|||Hello David:
Thank you for your remarks.
I was under the impression that the database could be started in a
no-logging mode. Am I mistaken, then, in this impression?
Also, I am unfamiliar with SSIS. Can you point me towards information
about it?
Thanks much,
zdrakec|||"zdrakec" <zdrakec@.yahoo.com> wrote in message
news:1147100405.620218.169540@.j33g2000cwa.googlegroups.com...
> Hello David:
> Thank you for your remarks.
> I was under the impression that the database could be started in a
> no-logging mode. Am I mistaken, then, in this impression?
Yes. In the Simple recovery model the log is still written. It's just
truncated occasionally so it doesn't grow.
This doc is 2005, but the recovery models are the same in 2000.
Overview of the Recovery Models
http://msdn2.microsoft.com/en-us/library/ms189275.aspx

> Also, I am unfamiliar with SSIS. Can you point me towards information
> about it?
>
http://www.microsoft.com/sql/techno...on/default.mspx
http://msdn2.microsoft.com/en-us/library/ms141263.aspx
http://www.sqlis.com/
David|||Thank you sir!!

Best method to identify broken view is schemabind?

I am looking for the best way to know if table changes are going to break a view. Is the best way to do this by using the schemabinding option when creating the view? What are the disadvantages of using schemabinding for a view?

Are there other methods to evaluate views as broken? Can you run a sp_ proc or a custom SQL procedure to validate all views?

Thanks.

Schemabinding is the best way to prevent broken views. It will make sure that dependency information is always up to date.

One method I have used is to write a simple query like:

select 'select ''' + name + '''; select top 1 * from ' + name + ';' + char(13) + char(10) + 'G0'

from sys.views

and then just run the output. Broken views will cause errors that should be easy enough to follow...