Post reply

Scripting Backup, Restore and Shirink of DB

  • February 24, 2004 at 7:59 am

    #107065

    Hi,

    Can some one tell me how to do this in T-SQL or point me to a relevant URL.

    The problem I have is that I am not sure where the users DB will be located so how can I code for this.

    Thanks

    CCB

  • February 24, 2004 at 10:07 am

    #495362

    Use what you need.  I use these for moving and rearranging data. 

    --Backup is run on the Server where the database is

    USE MASTER

    GO

    DECLARE  @Description varchar(255)

            ,@Name        varchar(255)

            ,@Location    varchar(255)

    SET @Description = 'Backup of MyDB on ' + CAST(GETDATE() as varchar)

    SET @Name        = 'Backup of MyDB

    SET @Location    = '\\AnotherServerName\C$\Backups\MyDB.bak'

    BACKUP DATABASE MyDB

    TO

         DISK        = @Location

    WITH

         DESCRIPTION = @Description

        ,NAME        = @Name

        ,INIT

     

    --Restore is run on the server where the database is going

    USE MASTER

    GO

    DECLARE  @Location   varchar(255)

    SET @Location    = '\\AnotherServerName\C$\Backups\MyDB.bak'

    Restore DATABASE MyDB

    FROM DISK        = @Location

    WITH MOVE 'MyDB_Data' TO 'D:\SQL\MyDB_data.mdf'

        ,MOVE 'MyDB_Log'  TO 'E:\SQL\MyDB_Log.ldf'

        ,REPLACE

     

  • February 24, 2004 at 10:11 am

    #495363

    To shrink a database use

    DBCC ShrinkDatabase or DBCC ShrinkFile.  They are both in help.

    Remember, anything can be done via code.  The Enterprise Manager's GUI just generated the proper SQL and executes it.

  • February 24, 2004 at 10:19 am

    #495368

    Thanks for the info.

    I'll give it a go.

    CCB

  • February 24, 2004 at 12:43 pm

    #495404

    Also, If you create a backup device on each of the machines then they can go anywhere on the machine and your TSQL becomes much easier...

    BACKUP DATABASE MyDb TO MyDevice WITH NOINIT...




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • February 25, 2004 at 8:18 am

    #495575

    Refer to the BOL, use the Index tab and enter BACKUP DATABASE. That will show you all the commands and how to set up 'dump devices'.

    -SQLBill

    BOL=Books OnLine=Microsoft SQL Server's Help

    Installed as part of the SQL Server Client Tools

    Found at Start>Programs>Microsoft SQL Server>Books OnLine

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

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