dcsimg
Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: MS SQL 2000 Backup.

  1. #1
    Join Date
    Mar 2004
    Posts
    1

    MS SQL 2000 Backup.

    Hi All,

    Is there a way to back only specific tables from a database? I need to filter a few tables as they are not needed for backup.

    Is there any way to do this?

    Thanks in advance.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,918
    There are no backup table commands but you can implement it by putting all the tables you want to backup in a separate filegroup from other tables, then backup the filegroup.

  3. #3
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    you could also populate those tables into a new database, take backup directly on the new database.

    Or You can make use of DTS package which could carry information from SQL Server to either MS Access, Excel or Text file. You could stored this files as a backup.

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  5. #5
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    Other than writing script and populate tables. DTS to export tables into new database can be a slick way.

  6. #6
    Join Date
    Feb 2004
    Posts
    64

    Help with Creating Index

    What kind of Index I should create on following table.

    Mkt Aff fr_date to_date daypart1 cost1
    -------------------------------------

    101 A 2-23-04 2-27-04 6:00-15:00 125
    101 A 2-28-04 2-29-04 9:00-20:00 100
    101 A 3-1-04 3-5-04 6:00-15:00 150
    101 A 3-6-04 3-7-04 9:00-20:00 100
    101 B 2-23-04 2-27-04 6:00-15:00 125
    101 B 2-28-04 2-29-04 9:00-20:00 150
    101 B 3-1-04 3-5-04 6:00-15:00 150
    101 B 3-6-04 3-7-04 9:00-20:00 175
    102 A 2-23-04 2-27-04 6:00-15:00 125
    102 A 2-28-04 2-29-04 9:00-20:00 100
    102 A 3-1-04 3-5-04 6:00-15:00 150
    102 A 3-6-04 3-7-04 9:00-20:00 100
    102 B 2-23-04 2-27-04 6:00-15:00 125
    102 B 2-28-04 2-29-04 9:00-20:00 150
    102 B 3-1-04 3-5-04 6:00-15:00 150
    102 B 3-6-04 3-7-04 9:00-20:00 175

    Thanks in advance.

  7. #7
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,918
    It depends on the type of query you will be running against the table. Can't tell from the table structure alone.

  8. #8
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    Mostly people define index blindly in foreign key column due to the referencial integrity. But you actually need to do research on the where clause on your update, select and delete statement.

    Also join columns b/t tables to tables.

    If your auto create statistics is turned on, you could also look into the statistics SQL Server created for you. It's also a way to identify the possible missed index.
    Last edited by Claire; 03-15-2004 at 10:41 AM.

  9. #9
    Join Date
    Sep 2002
    Posts
    5,938
    Or run index tuning wizard against the table, let system make some recommendation for you.

  10. #10
    Join Date
    Feb 2004
    Posts
    64
    Thanks all to get back to me. I have created index as follows. I can't enter new row for same Mkt & Aff for diff dates (fr_date, to_date). I get error for duplicate key.

    CREATE UNIQUE CLUSTERED
    INDEX [mktaff_ind] ON [dbo].[cost_per_spot] ([Mkt], [Aff],[fr_date],[to_date])
    WITH
    IGNORE_DUP_KEY
    ,DROP_EXISTING
    ON [PRIMARY]

    any idea? Thanks once again.

  11. #11
    Join Date
    Sep 2002
    Posts
    5,938
    Ensure you don't have dup combination of [Mkt], [Aff],[fr_date],[to_date] in the table.

  12. #12
    Join Date
    Feb 2004
    Posts
    64

    Help with Query

    I have 2 tables Cost & log

    Cost table has data for date range and log table has data by date

    Cost table
    -----------
    DMA c_fr_date c_to_date Daypart1 c_spots1 daypart2 c_spots2
    ----------------------------------------------------------
    101 2-23-04 2-27-04 6:00-15:00 15 15:00-19:00 24
    101 2-28-04 2-29-04 6:00-15:00 6 15:00-19:00 8

    Log table
    -------------------------
    DMA fr_date Daypart1 spots1 daypart2 spots2
    -------------------------------------------------
    101 2-23-04 6:00-15:00 5 15:00-19:00 8
    101 2-24-04 6:00-15:00 2 15:00-19:00 4
    101 2-25-04 6:00-15:00 6 15:00-19:00 7
    101 2-26-04 6:00-15:00 1 15:00-19:00 2
    101 2-27-04 6:00-15:00 1 15:00-19:00 1
    101 2-28-04 6:00-15:00 4 15:00-19:00 4
    101 2-29-04 6:00-15:00 2 15:00-19:00 3

    I am looking for result as follows

    DMA c_fr_date c_to_date c_spots1 c_spots2 sum(spots1) sum(spots2)
    --------------------------------------------------------------------
    101 2-23-04 2-27-04 15 24 15 22

    I have sql statement as below.

    select DMA,sum(spots1),sum(spots2),c.c_spots1,c.c_spots2
    from log l ,cost c
    where c.dma = l.dma
    fr_date between '2-23-04' and '2-27-04'
    group by dma

    what is wrong here? I get diff result.

    Please help. Thanks in advance.

  13. #13
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,918
    Your original query can't run, because you don't have spots1 column in group by.

    select DMA,sum(spots1),sum(spots2),c.c_spots1,c.c_spots2
    from log l ,cost c
    where c.dma = l.dma
    fr_date between '2-23-04' and '2-27-04'
    group by dma, c.c_spots1, c.c_spots2

  14. #14
    Join Date
    Feb 2004
    Posts
    64
    Thanks all for the help.

  15. #15
    Join Date
    Feb 2004
    Posts
    64

    Help with Query

    In A table I have
    DMA Date
    ---------------------
    101 2-23-04
    101 2-24-04
    101 2-25-04
    101 2-26-04
    101 2-27-04
    101 2-28-04
    101 2-29-04
    101 3-1-04
    101 3-2-04
    101 3-3-04
    101 3-5-04
    101 3-6-04

    Is there a way I can diff weekday result and weekeknd result.

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •