Backup with TSQL

Question

Post reply

Backup with TSQL

Roust_m

SSCoach

Points: 17410
More actions
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?

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

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

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]

Roust_m SSCoach Points: 17410 More actions · Answer 2

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

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]

GRN Hall of Fame Points: 3260 More actions · Answer 4

This script should list all directories for you

USE PUBS

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

CREATE TABLE #Directories

(

DirectoryNamevarchar(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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

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]

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 6

Try xp_fileexist.

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