• sm_iransoftware (1/31/2015)


    Because The cur Database has about 200 GB.

    And we dont have enough space and we must Backup from it and then truncate tables.

    But Backup from this size with many connections and insert will be very long.

    then we choose fast way.

    Replace with empty database and then backup from it without live connection and ..

    I missed the "key" to all of this in the original post. This IS a database that contains historical "Log" tables almost exclusively, right?

    If so, I wouldn't do it the way that you propose because making even an empty copy of a problem is still making a copy of the problem. If it were me, I'd invest a little more time and partition the tables (either a partitioned view for Standard Edition or a partitioned table for the Enterprise Edition). That way, you can automate dropping off previous months in milliseconds rather than having to go through the small bit of hell that you're going through now. If you also take the time to make each partition (using either method) on it's own file in it's own filegroup, then you'll also be able to just backup the current month each night instead of backing up the whole shebang every night.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)