Post reply

Backup with TSQL

  • May 27, 2003 at 2:28 am

    #159973

    Hi!

    I am trying to implement the following algorithm:

    Having a list of databases in a cursor backup all of them in a separate directory on drive X: Each DB must backup into a directory named as the DB. We need to check if the directory exists and if not - create it.

    Is it possible to check the existence of a directory and to create one with TSQL statements?

  • May 27, 2003 at 2:35 am

    #458579

    Hi Roust_m.

    do you really have to reinvent the wheel?

    That what you are trying to implement can easily be done with a maintenance plan.

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • May 27, 2003 at 3:04 am

    #458585

    Maintenance plan does not allow me to choose all databases on all servers but a list of DBs that are not to backup. Maintenance plan does not allow me to do differential backups. (Some DBs are large, so I can not go with weekly full backups and 10 minutes log backups) This job will be multiserver in a dozen of servers environment. It has to be very flexible, so I can only do this all with TSQL...

  • May 27, 2003 at 3:12 am

    #458589

    Good point,

    take a look at BACKUP DATABASE in BOL.

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • May 27, 2003 at 3:15 am

    #458591

    This script should list all directories for you

    USE PUBS

    DECLARE @CmdStr varchar(100),@PathName varchar(200)

    CREATE TABLE #Directories

    (

    DirectoryName varchar(100)

    )

    SET @PathName = 'C:'

    SET @CmdStr = 'DIR ' + @PathName + ' /A:d /B'

    INSERT INTO #Directories

    EXEC master..xp_cmdshell @CmdStr

    SELECT * FROM #Directories

    DROP TABLE #Directories

    you can use the DOS MD command with xp_cmdshell to create the directory

  • May 27, 2003 at 3:15 am

    #458590

    ooh, and you can use xp_cmdshell for checking and creating

    DECLARE @result int

    EXEC @result = xp_cmdshell 'some dos_commands'

    IF (@result = 0)

    --go on

    ELSE

    --create dir and go on

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • May 27, 2003 at 3:27 pm

    #458741

    Try xp_fileexist.

    Assumes with a parameter like 'c:\.' one can check for a directory.

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

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