Batch File in SQL Enterprise Manager

onechewy

Hi, hopefully someone might be able to help me out on this..

We have SQL 2000 on Windows Server 2000 and I do a backup of our database 4 times a day. This requires me having to kick everyone out of the application we use on top of SQL for about ten minutes or so. I'd just been shouting at everyone to get out of the application but then I realised I could use net send. So I have a little batch file which I want to run in SQL Enterprise Manager as step 1 of the backup job so as to warn everyone via net send that the backup is about to take place.

Thing is, I can't get it to work from Ent Man, but the batch will work fine on its own, it just fails everytime I start the job from Ent Man. I don't really know anything about SQL but I had presumed that I just needed to enter the location of the exe/bat file and Enterprise Manager will execute as it sees it.

So do I need to do something different for a batch file?

Any suggestions much appreciated, thanks.

Ponster

Without answering your question directly, why do you have to disconnect the users first? SQL2000 will let you carry out both full and differential backups with people connected.

If it's of any use I'm pretty sure that you can get the system to disconnect people automatically before the backup begins should you want to. Have it do it at fixed hours that everyone knows and stop shouting at people

As for the DOS command :

xp_cmdshell 'net send * Time to log off guys'

You can run dos commands direct in tsql via the xp_cmdshell command.

onechewy

Ponster, thanks a million for that. I presume that the DOS shell command is server 2003 based? What command would I use in server 2000?

I actually do these backups at set hours each day, but the problem is that people always forget and start running processes that take a while to do so I can't just kick off a backup without checking first.

I hear what your saying about SQL being able to carry out full backups while there's people attached to the database, but a lot of wierd reports and processes get run here and it's always been the done thing to make sure everyone is fully out prior to backup so as to (attempt to) maintain complete integrity.

Thanks again.

onechewy

Hi, sorry - I had a little google and saw that xp_cmdshell is actually used as far back as Win98 - I figured the xp part was something to do with the operating system.

Thanks again!

Tom Dunne

onechewy wrote:

Hi, sorry - I had a little google and saw that xp_cmdshell is actually used as far back as Win98 - I figured the xp part was something to do with the operating system.

Thanks again!

No, xp_cmdshell is a stored procedure within SQL Server that allows you to run operating system commands such as Net Send.

I hear what your saying about SQL being able to carry out full backups while there's people attached to the database, but a lot of wierd reports and processes get run here and it's always been the done thing to make sure everyone is fully out prior to backup so as to (attempt to) maintain complete integrity.

Dusting off my DBA hat from a while back, it would be more worth your while looking into this aspect of the problem rather than sending a message to everyone.

Another alternative is to write some SQL code to stop new logins to the database and then run the backup script when there is nobody logged in.

onechewy

tom dunne wrote:

Dusting off my DBA hat from a while back, it would be more worth your while looking into this aspect of the problem rather than sending a message to everyone.

Another alternative is to write some SQL code to stop new logins to the database and then run the backup script when there is nobody logged in.

The situation here is that a lot of mistakes get made by people using the application that sits on top of sql.. Access is also used a lot, and by having a full and proper backup every two hours, we can restore the database to a previous copy if the repair of the mistake is just going to take too long.

The database itself is big and complicated and I steer clear of it. The backups are a pain in the bee-hind but being able to net send from Enterprise Manager makes it a bit easier. So thanks!

Ponster

Use Net send to warn everyone of a shutdown in 2 minutes time. Add in contact details in case someone really needs more time.

2 minutes later run the following code to disconnect all the users that are still using the database :

Code robbed from : http://www.sqlservercentral.com/columnists/bknight/uspkillusers.asp

CREATE PROCEDURE kill_database_users @arg_dbname sysname with recompile
AS

-- kills all the users in a particular database
-- dlhatheway/3M, 11-Jun-2000

declare @a_spid	smallint
declare @msg	varchar(255)
declare @a_dbid	int

select
	@a_dbid = sdb.dbid
from	master..sysdatabases sdb
where	sdb.name = @arg_dbname

declare db_users insensitive cursor for
select
	sp.spid
from	master..sysprocesses sp
where	sp.dbid = @a_dbid

open db_users

fetch next from db_users into @a_spid
while @@fetch_status = 0
	begin
	select @msg = 'kill '+convert(char(5),@a_spid)
	print @msg
	execute (@msg)
	fetch next from db_users into @a_spid
	end

close db_users
deallocate db_users
GO

Then perform backup followed by a Net send to let everyone know it's ok again.

Tom Dunne

Ponster wrote:

2 minutes later run the following code to disconnect all the users that are still using the database

But do you want to go killing people's sessions within the database? It would be preferable for them to exit out cleanly. That's why I think my method would be better.

The database itself is big and complicated and I steer clear of it. The backups are a pain in the bee-hind but being able to net send from Enterprise Manager makes it a bit easier. So thanks!

But they don't have to be such a pain in the behind. There are much easier ways to do what you are trying to achieve.

Ginger

To be extra safe you could also set the db into single user mode. That will stop anyone connecting to it while you are doing the backup.

Being honest, i dont see the need to kill the users out for the sake of a backup. Unless you are getting them to commit their info to the database

You could set up another instance of the database and restore to that if something went wrong and import the old data from the backup instance to the live one.

Ponster

onechewy wrote:

I hear what your saying about SQL being able to carry out full backups while there's people attached to the database, but a lot of wierd reports and processes get run here and it's always been the done thing to make sure everyone is fully out prior to backup so as to (attempt to) maintain complete integrity.

Still sounds like a process that was put in place by someone who wasn't trained in SQL.

As a certified MSQL DBA I think it's a little overkill to need to disconnect everyone. SQL will take into account all modifications to the database during the backup period.

What size db are we talking about here and are you doing full or partial backups?

onechewy

Ponster wrote:

Still sounds like a process that was put in place by someone who wasn't trained in SQL.

As a certified MSQL DBA I think it's a little overkill to need to disconnect everyone. SQL will take into account all modifications to the database during the backup period.

What size db are we talking about here and are you doing full or partial backups?

The database is 2.5GB in size and it's a full backup that's done each time.

I tried out the xp_cmdshell but it wouldn't work... a bit of googling showed that xp_cmdshell is an extended stored procedure in the Master Database, but unfortunately, there are no extended procedures in the database I'm working with, and there's no option to create any.

So for xp_cmdshell to work, I have to swap databases during the job.. not so much of a problem, but when it attempts to net send, it throws out the error that 'A specified logon session does not exist. It may already have been terminated'.

I think when SQL starts net send, Windows doesn't see it as a properly logged on session... a job for something like 'net logon'.. but that doesn't exist in 2000/XP.

This is slightly more trouble than it's worth I think, and what your saying about SQL being able to spot changes while backing up means I may be able to do away with kicking everyone out completely.. which would be deadly, so I think I'll just have to run a few tests on that to check it out!

Ginger

Ok

What you could do,

Do a full backup every morning before the start of business.

Then do differential backups to a file share (these would be smaller) and then if the worst came to the worst, restore the full backup to another database as well as the latest differential and then copy in the data that is missing.. Or you could restore over the existing db if you so wished.

THis would be a 2 step process but would mean that only data changed since the last full backup would be backed up thus making it quicker to do for you and would mean less downtime. The biggest one would be the last differential backup taken before the mornings backup