now - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

now

Author

Topic

morphias0
Starting Member

20 Posts

Posted - 2006-02-08 : 11:08:47

Now ive found out that its will make the dir if its local but not if its a network drive

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-08 : 12:34:33

What is your question?

Tara Kizer
aka tduggan

morphias0
Starting Member

20 Posts

Posted - 2006-02-08 : 14:39:58

By accident i posted this as a new topic, it dealt with the last post i made which was dealing with checking if a folder exist and if not creating it. The difference between your script and my script is that in your blog your creating that folder locally, In mine i need to be able to check if the folder exist and if not create it both on the network and locally. With your script it will check on a network but it will not create it. This i beleive is an issue with the dos command mkdir, its not capable of doing that as far as i know. Heres what i have

--*************************************
--MS SQL Server Backup for WISEPKG1
--*************************************

USE master
go

DECLARE @FinalStatus INT, @ErrorStatus INT, @i INT, @ServerName VARCHAR(180), @DatabaseName VARCHAR(180), @DestinationDrive VARCHAR(180),@DestinationFolder VARCHAR(180), @FileName VARCHAR(180), @CompletePath VARCHAR(180), @Path VARCHAR(100), @Cmd sysname,@Result INT, @database varchar(100), @WriteDate smalldatetime, @Extension varchar(5), @dbcount int, @dbcount2 VARCHAR(180)

DECLARE @1 varchar(100),
@2 varchar(100),
@3 varchar(100),
@4 varchar(100),
@5 varchar(100),
@6 varchar(100),
@7 varchar(100)

SET @WriteDate = getdate()
SET @FinalStatus=0
SET @ErrorStatus=99

SET @ServerName = 'Wisepkg1'
SET @1 ='Wise Services Database'
SET @2 ='Workbench Database'
SET @3 ='Wise Services Database'
SET @4 ='Wise Services Database'
SET @5 ='Wise Services Database'
SET @6 ='Wise Services Database'
SET @7 ='Wise Services Database'
SET @dbcount = 1 ---------Set to the number of db that exist
SET @DestinationDrive = '\\MIS022000'
SET @DestinationFolder = '\Full Backup'
SET @Extension = '.BAK'

while @dbcount <> 0
begin

SET @DBCOUNT2 = CAST(@DBCOUNT AS VARCHAR(100))
SET @database= '@' + @DBCOUNT2
SET @DatabaseName = '\' + @Database
PRINT @DATABASENAME
SET @FileName = @DatabaseName + @extension
SET @Path = @DestinationDrive + @DestinationFolder
SET @CompletePath = @Path + @DatabaseName + @FileName

--checking if dir exist
--This checking and creating folder process will only work if its local, due to it
--using dos commands to make the directory, dos will not create a folder on a shared network
--folder
SET @cmd = 'dir ' + @path + @DatabaseName
EXEC @result = master.dbo.xp_cmdshell @cmd, NO_OUTPUT
--If it doesnt exist it creates it
IF @result <> 0
BEGIN
SET @cmd = 'mkdir '+ @path + @DatabaseName
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
END

PRINT '********************************************'
PRINT 'MS SQL BACKUP for WISEPKG1'
PRINT '********************************************'
PRINT 'Sarting backup'
PRINT @writedate
PRINT '********************************************'
PRINT 'Backing up and verifying the ' + @database
PRINT '********************************************'

--backing up the database----------------------------------------------------
BACKUP DATABASE @database TO DISK = @CompletePath WITH INIT , NOUNLOAD , NAME = N'CA Wise Services Database', NOSKIP , STATS = 10, NOFORMAT
IF @@ERROR <> 0
BEGIN
SET @FinalStatus=@ErrorStatus
PRINT '*************************************'
PRINT 'ERROR backing up the ' + @database
PRINT '*************************************'
END

--checking the database--------------------------------------------------------
SET @i=null
select @i = position from msdb..backupset where database_name=@database and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@database)
RESTORE VERIFYONLY FROM DISK = @CompletePath WITH FILE = @i
IF @@ERROR <> 0
BEGIN
SET @FinalStatus=@ErrorStatus
PRINT '******************************************'
PRINT 'ERROR verifying the ' + @database
PRINT '******************************************'

END
Print ' '
Print ' '
set @dbcount= @dbcount - 1

end

Ideally i want to be able to check and create a folder remotely or locally. The main goal of this is so that i can use the same script on mulitple servers to back up all db to a remote folder. Everything needs backed up except the temp db, and each server has different named db as well as a different ammount of db. If we had to enter in each name of the db on that server and the server name it isnt a big deal, this is why i put the variables at the beginging for all that. but if theres an easier way im welcome anything.

morphias0
Starting Member

20 Posts

Posted - 2006-02-08 : 14:46:12

So to sum it all up i cant get it to scroll through all the databases and back them up. And i cant create a folder on a computer other than the server

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-08 : 14:49:28

So everything is working except creating a folder on a remote server? If so, then you shouldn't be backing up directly to a remote server anyway. You should back everything up locally, then xcopy that file to your remote server. And yes you won't be able to create that remote folder.

Tara Kizer
aka tduggan

morphias0
Starting Member

20 Posts

Posted - 2006-02-08 : 14:54:00

ok, that works...I can just copy the backup but the other part was backing up each db on that server in my example i assumed there were 7 db (although i didnt have all the names filled in). I could have something like
*************************************************
while @dbcount <> 0
begin

if @dbcount = 1
begin
SET @database= @1
end --Keep doing this for each one

SET @DatabaseName = '\' + @Database
PRINT @DATABASENAME
*************************************************

but i know there is an easier way then making all those if statements, the reason that concerns me is that there may come a time when there are 10 or more db on one server and then the script will begin to get to long

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-08 : 14:55:16

Check out the code in the backup stored procedure that I wrote. It can handle any number of databases.
http://weblogs.sqlteam.com/tarad/archive/2004/08/04/1876.aspx

And here's my other useful DBA routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Tara Kizer
aka tduggan

morphias0
Starting Member

20 Posts

Posted - 2006-02-08 : 15:17:42

In using your code mixed with my code i get this error

Msg 178, Level 15, State 1, Line 81
A RETURN statement with a return value cannot be used in this context.

I'll point out where it is in the code so its easier to find

--*************************************
--MS SQL Server Backup for WISEPKG1
--*************************************

USE master
go

DECLARE @FinalStatus INT,
@ErrorStatus INT,
@i INT,
@ServerName VARCHAR(180),
@DatabaseName VARCHAR(180),
@DestinationDrive VARCHAR(180),
@DestinationFolder VARCHAR(180),
@FileName VARCHAR(180),
@CompletePath VARCHAR(180),
@Path VARCHAR(100),
@Cmd sysname,
@Result INT,
@database varchar(100),
@WriteDate smalldatetime,
@Extension varchar(5),
@dbcount int,
@dbType VARCHAR(6),
@DBName SYSNAME,
@RowCnt INT

SET @WriteDate = getdate()
SET @FinalStatus=0
SET @ErrorStatus=99
--Values below may need changed----------------------------------------------
SET @ServerName = 'Wisepkg1'
SET @DestinationDrive = '\\MIS022000'
SET @DestinationFolder = '\Full Backup'
SET @Extension = '.BAK'
--Values above may need changed-----------------------------------------------

CREATE TABLE #WhichDatabase
(
dbName SYSNAME NOT NULL
)

-- Get the list of the databases to be backed up
IF @dbType = 'All'

INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] <> 'tempdb' AND
[name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases)
ORDER BY [name]

ELSE
BEGIN

IF @dbType = 'System'

INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] IN ('master', 'model', 'msdb')
ORDER BY [name]

ELSE
BEGIN

IF @dbType = 'User'

INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb') AND
[name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases)
ORDER BY [name]

ELSE
BEGIN

DROP TABLE #WhichDatabase

RETURN -1 ---------ERROR IS OCCURING HERE

END

END

END

-- Get the database to be backed up
SELECT TOP 1 @DBName = dbName
FROM #WhichDatabase

SET @RowCnt = @@ROWCOUNT

-- Iterate throught the temp table until no more databases need to be backed up
WHILE @RowCnt <> 0
BEGIN

SET @DatabaseName = '\' + @Database
PRINT @DATABASENAME
SET @FileName = @DatabaseName + @extension
SET @Path = @DestinationDrive + @DestinationFolder
SET @CompletePath = @Path + @DatabaseName + @FileName

--checking if dir exist
--This checking and creating folder process will only work if its local, due to it
--using dos commands to make the directory, dos will not create a folder on a shared network
--folder
SET @cmd = 'dir ' + @path + @DatabaseName
EXEC @result = master.dbo.xp_cmdshell @cmd, NO_OUTPUT
--If it doesnt exist it creates it
IF @result <> 0
BEGIN
SET @cmd = 'mkdir '+ @path + @DatabaseName
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
END

PRINT '********************************************'
PRINT 'MS SQL BACKUP for WISEPKG1'
PRINT '********************************************'
PRINT 'Sarting backup'
PRINT @writedate
PRINT '********************************************'
PRINT 'Backing up and verifying the ' + @database
PRINT '********************************************'

--backing up the database----------------------------------------------------
BACKUP DATABASE @database TO DISK = @CompletePath WITH INIT , NOUNLOAD , NAME = N'CA Wise Services Database', NOSKIP , STATS = 10, NOFORMAT
IF @@ERROR <> 0
BEGIN
SET @FinalStatus=@ErrorStatus
PRINT '*************************************'
PRINT 'ERROR backing up the ' + @database
PRINT '*************************************'
END

--checking the database--------------------------------------------------------
SET @i=null
select @i = position from msdb..backupset where database_name=@database and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@database)
RESTORE VERIFYONLY FROM DISK = @CompletePath WITH FILE = @i
IF @@ERROR <> 0
BEGIN
SET @FinalStatus=@ErrorStatus
PRINT '******************************************'
PRINT 'ERROR verifying the ' + @database
PRINT '******************************************'

END
Print ' '
Print ' '
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-08 : 15:22:23

You can't use RETURN as you haven't wrapped your code into a stored procedure. Why not try running mine and see if it does what you want? If it does, then there's no more code that you need to type. We've had mine running in production for about 2 years now.

Here is a sample to execute it:

EXEC isp_Backup @Path = 'F:\MSSQL\Backup\', @dbType = 'All', @Retention = 5

Tara Kizer
aka tduggan

morphias0
Starting Member

20 Posts

Posted - 2006-02-08 : 15:33:16

quote:
Originally posted by tkizer

EXEC isp_Backup @Path = 'F:\MSSQL\Backup\', @dbType = 'All', @Retention = 5

Tara Kizer
aka tduggan

This may sound stupid (im just starting if you havent guessed) but
Where do i put the "exec isp_Backup @Path = 'F:\MSSQL\Backup\', @dbType = 'All', @Retention = 5" line in the code

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-08 : 15:34:53

You don't put it anywhere in your code. You run the query in Query Analyzer. But you must have created my stored procedure first. Mine backs up your databases to a specific drive and folder. It will also delete any files older than the @Retention variable so make sure the value is what you want.

Tara Kizer
aka tduggan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-08 : 18:17:32

You can create a folder on a network share like this:

mkdir  \\MyServer\MyShare\test

Make sure you are not trying to do something like this, because that would be attempting to create a share, not a directory:

mkdir  \\MyServer\test

If you are having trouble doing this, it is likely that the account the SQL Server CMDSHELL is using does not have permission to create a folder in the share.

If you are executing XP_CMDSHELL from a login in the SQL Server SYSADMIN role, then the SQL Server service account needs access, otherwise the SQL Server proxy account will need access.

CODO ERGO SUM

morphias0
Starting Member

20 Posts

Posted - 2006-02-10 : 10:00:21

thats not actually the case, you cant create a folder on a network drive in dos, i tried it manually, opening up the cmd prompt and typing it out myself. It wont work, and no im not trying to create a share, i was trying to create a subfolder in a share so i was doing as you were saying

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-10 : 10:08:47

quote:
Originally posted by morphias0

thats not actually the case, you cant create a folder on a network drive in dos, i tried it manually, opening up the cmd prompt and typing it out myself. It wont work, and no im not trying to create a share, i was trying to create a subfolder in a share so i was doing as you were saying

I tried it myself before I posted my first message, so I know it works.

My guess is that you have some permission problem on either the share or the directory it points at.

CODO ERGO SUM

morphias0
Starting Member

20 Posts

Posted - 2006-02-10 : 10:18:33

I stand corrected, You are right and i was wrong

sry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources