Post reply

Rename a Live database

  • January 31, 2015 at 10:47 pm

    #295049

    Hi

    I want to Replace The Big Log database with A new one ( A database with same structure).

    But current DB has many connection .

    This is my plan :

    1- Create a new database with same structure.

    2- Rename current database to olddb with this code :

    USE master

    GO

    EXEC sp_dboption CurDataBase, 'Single User', True

    EXEC sp_renamedb 'CurDataBase', 'OldDataBase'

    GO

    3- Rename Newdb to current DB.

    USE master

    GO

    EXEC sp_renamedb 'NewDataBase', 'CurDataBase'

    is it true ? and Tsql code is ok ? (Please dont forget many of connection to curdatabase (that Is a log db) and loss some seconds data is not problems)

    Thank you

  • January 31, 2015 at 11:16 pm

    #1774605

    sm_iransoftware (1/31/2015)

    1- Create a new database with same structure.

    I guess my question is, if it's the same structure, why are you bothering to do this in the first place?

    --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)

  • January 31, 2015 at 11:25 pm

    #1774606

    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 ..

  • February 1, 2015 at 7:10 am

    #1774624

    is this database in an Availability group?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • February 1, 2015 at 11:22 am

    #1774642

    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)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply