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 Administration (2000)
 ANY suggestions welcome

Author  Topic 

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-06-17 : 03:25:15
I've just been told by our network admins that they haven't been backing up our production server! They then added that they didn't know when the last backup was and that they hadn't ever done a restore anyway. Their managers don't see this as a major issue and at the same time declared that the admins would all be too busy to provide any support until at least August as they are working on a major project. As I'm sure you can imagine I was pleased about this!!

So now I have a production database (SQL Server 7) sitting on a box which I have very limited access to other than through EM/QA and a few folders I am allowed to see on it. At the moment there are daily backups onto the same box (because a) they were accessible to the DBA's and b) I was being told that the network admins were backing up the box), there are also incremental backups onto the same box.

To be fair to them (and I'm not sure why I should be at the moment) there has been no problem with this server for the last 4 years other than a power failure and once when they needed to switch it off at 30 mins notice just so they could move it into a different cabinet (they seemed to think that this was sufficient notice for a non-critical issue)

My first question is an I being unreasonably harsh on them? My second and more pressing question is how the hell do I back this up on a daily basis! I have no access to any backup devices. I have no access to the server physically. I do have a SQL server 2000 box which I have more access to (can log into the server via the network) but that is physically in the same location as the other box and the two machines can't 'see' each other without some input from the network admins.

The only options I can see are these and I would be interested in any comments or suggestions

1) I do nothing and hope and start looking for another job
2) I pull the data through to the other server on a regular basis (assuming I don't need input from the network admins)
3) I buy some sort of backup device (say a big hard disc) and copy the database down to it, I can see two problems with this though, first, the file is 2Gb, not enormous I know but the only machine I can do this on is a Win 95 one (I'm not allowed to use Windows Authentication to access this server from my XP machine so I can't schedule this I think), which means I could detach the database, copy the file to a network location and then schedule a job on my XP machine which pulls this file from the network onto the backup device. The second problem is suggested above i.e. part of this process will have to be manual and the only way I can think of copying this data is by detaching the database which means taking the system down every morning which will get me serious grief
4) Move the server onto a part of the network that they have no control over, this is feasible but would need co-operation from them and would have major political implications which my bosses would have to argue over, unfortunately this doesn't solve the immediate problem either
5) I have a laptop with SQL Server on it, I could copy the data to that (e.g. with a DTS) but then I would either rapidly fill up the laptop or only have a backup of the most recent data

The only consolation to this if it is one is that the backups only need to be done daily on weekdays though having said that the solution needs to be one that a non-techy can follow as over the next week or two there will be days where I will not be in work - on courses, in hospital etc.

I'm sorry this goes on so much, I hope I have explained it fairly clearly. I am at a loss as to what to do

Steve no function beer well without

mr_mist
Grunnio

1870 Posts

Posted - 2004-06-17 : 03:37:53
Can you not use the sql backup to stick the backup where you need it to be?

Really though I'm thinking just go higher up the chain of command to get the network people to actually do their job, or at least empower you to do yours.

-------
Moo. :)
Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-06-17 : 04:52:53
That's a good question, the only other server I have is SQL server 2k, can I restore to that from a SQL server 7 backup, guess I'll let you know in a little while

thanks

Steve no function beer well without
Go to Top of Page

mr_mist
Grunnio

1870 Posts

Posted - 2004-06-17 : 05:04:25
Yes, though not the other way around IIRC.

-------
Moo. :)
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-17 : 09:28:19
Do you have access to the agent?
If so schedule a backup command.

The networky guys must be doing backups to tape so you just need to tell them to copy the contents of that directory to tape.

>> Their managers don't see this as a major issue and at the same time declared that the admins would all be too busy to provide any support until at least August as they are working on a major project.

Doesn't really matter then as if there is a crash you won't be able to restore the system as you would need them for that.
My advice would be to take down the system until you can be guaranteed some support or at least inform the users that all the work may be lost.
Make sure you can rebuild the system from scripts so that you can restart after a crash.

A system is only really committed up to the last restorable backup. If you don't have backups or none have been tested then you are saying that you don't mind losing the system. You should have installation scripts so that's probably limitted to losing the data - and you should inform users and managers of that fact.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-17 : 09:42:50
It's your job to document a solution and recommend it. When it's not followed (and have both the recommendation and denial on email), let the users know like Nigel said that there is no recoverability should you have that failure. I can almost guarantee you the "priorities" of your network group will change after this. Sounds like the network manager needs fired; and in this case I wouldn't have a problem in the world making sure it happens.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-06-17 : 13:29:58
quote:
Originally posted by nr

The networky guys must be doing backups to tape so you just need to ...

Oh yes, absolutely and without question

I can't wait for the next installment of the Soap from elwoos ...

Kristen


Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-06-18 : 03:46:49
In response to nr.

The network guys AREN'T doing backups to the tape and if they were their hardware has failed anyway.

There is another twist that I haven't mentioned (should help to keep Kristen entertained). These guys don't actually run the whole of the network, they actually run a Virtual LAN, which the central guys don't normally touch and 'my' admins refuse to touch the main LAN. Ironically I have pretty much full access to the main LAN but almost none to the VLAN. The thing that is obvious to me is to move to the main LAN (which is being backed up) but there are serious political etc implications of that which means my boss would like to but probably can't do this. I get on very well with the central guys and find them exceptionally helpful, in a worst case scenario they may be in a position to 'lend' us a SQL server.

"...you are saying that you don't mind losing the system"

I think you were being ironic here but just in case. I work in the public sector. This system has confidential information on around 200,000 people (you could be on there if you live in this area of the UK). It's loss would have a very significant impact on the organisation I work for - you may even here about it in the local media if it happened.

I've devised a solution which is not ideal by any means but I think will work - and more importantly I won't need input from the admins.
1) The database is backed up to the discs on the box itself.
2) The data is then copied (DTS or whatever) overnight to the SQL server 2000
3) This SQL Server 2000 box is backed up after the data transfer
4) As the boxes are both in the same physical location, the backup file is going to be copied to an external hard disc (hopefully) arriving this morning, through the use of a scheduled job using XP's task scheduler.

This backup file is about 2Gb and will have to be copied over the main network. The main network guys tell me there is no problem as long as it is done in the evening. Which would be fine but I only have one XP machine that I can use so I'm having kittens about that failing, especially as I'll have to leave it logged in all night every night even on the days when I am away on courses, weekends etc.

My consolation prize is that I'm going on holiday in a few weeks. Caribbean here we come!

I'm sure further episodes in the saga will follow

steve

Steve no function beer well without
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-18 : 08:45:50
I think you were being ironic here but just in case. I work in the public sector. This system has confidential information on around 200,000 people (you could be on there if you live in this area of the UK). It's loss would have a very significant impact on the organisation I work for - you may even here about it in the local media if it happened.



Oh, we'll read about it sooner or later. Someone is going to get fired someday. :) I'm an optimist about these kinds of things, especially when someone SHOULD get fired for this kind of stupidity.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-06-18 : 09:00:46
Just to show that this is endemic they were refused permission to buy a new backup system when they pointed out that the existing one would fail.

The Saga continues...

I now find my windows account locked out of the server ie I can only access the SQL server as long as I don't use Windows Authentication(thanks god we set it as mixed mode). So now I have my shiny external hard disc and can't access the backup file to put onto it.

I can setup the job to copy to the second server but is there any point when it's in the same physical location? I've just retrieved a laptop with SQL server desktop edition on it. I presume I could DTS the data onto that then do the backup which I could copy to my new hard disc, what do people think of this strategy?



Steve no function beer well without
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-18 : 09:04:05
I really am a nice guy. You SERIOUSLY need to get someone fired.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-06-18 : 10:02:20
quote:
Originally posted by elwoos

In response to nr.

The network guys AREN'T doing backups to the tape and if they were their hardware has failed anyway.


Hehehehe I am just SO surprised!

elwoos, thanks for the entertainment. Keep you pecker up and your nose clean. Give me a shout if you want a job in Suffolk instead!

Is there any way you can PUSH a backup from the SQL box somewhere else in the organisation?

Can you run xp_cmdshell?

Can you make a public share on your PC, and thus push to that? (xp_cmdshell can, hopefully, do "COPY D:\MSSQL\BACKUP\MyDb.BAK \\MYPC\MyShare\*.*")

Does the SQL box have FTP or IIS on it that you might be able to access? I've HTTP'd a backup file before now ...

Using DTS to pump 2GB of data out is going to take far longer than pushing a backup file ...

I suppose replication would be a bad idea?

If you are stuck with DTS and the 2GB size becomes a problem the only other suggestion I have is to host a copy database on the main server and use this to produce a "delta" of differences that you then pump to the remote server (and update the local second-copy ready for tomorrows copy-over). Sort of poor-man's-replication.

Who owns the server (in a "budegetary" sense)? If it is owned by your department you might be able to wade in there and walk off with it - and plug it in somewhere sensible. I've had heads of [non-IT] departments that I've worked for, in ex-nationalised industries, [so not too far removed from public sector!], go berserk when treated this badly by IT and in one case the boss went to the petty cash tin, took out a thousand quid and sent a secretary down to Tottenham Court Road to buy "the best tape backup unit you can find". [For you lot no longer living in the Mother Country that's a road in London that sells "mostly-personal" IT stuff; oh, and "quid" is the good old English Pound, most strangers in here don't even get told what BOL is, so there!]

Once you've got it properly backed up the collective minds here can figure out the best way to trash the database and make it someone else's problem ...

Good luck

Kristen

Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-06-18 : 11:00:48
Thanks for the advice Kristen, if nothing else you've cheered me up! Suffolk aint so far away, maybe a few hours on the train ;-)

I'll try the xp_cmdshell first I think.

Just found out that these guys are having major problems with one or two of their servers now (which aren't backed up!!). Is that what they call divine justice ;-)

thanks

Steve no function beer well without
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-18 : 12:54:03
Buy'em a beer...and put visine in it....


use msdb
go

create proc dbsync_backup_databases
as
begin
BACKUP LOG appdb WITH TRUNCATE_ONLY
BACKUP DATABASE appdb
TO disk='d:\ctsdba\dbdump\db\appdb.dmp' WITH INIT

end

create procedure make_network_connection with ENCRYPTION
as
begin

exec master..xp_cmdshell "net use v: \\server\sharename /USER:usprune1\id pwd"
end
go

create proc dbsync_copy_databases with ENCRYPTION
as
begin
exec master..xp_cmdshell "del v:\dbdump\db\*.dmp"
exec master..xp_cmdshell "copy d:\ctsdba\dbdump\db\appdb.dmp v:\dbdump\db\appdb.dmp"
end

create procedure remove_network_connection with ENCRYPTION
as
begin
exec master..xp_cmdshell "net use v: /delete"
end

create procedure dbsync_restore_databases
as
begin
RESTORE DATABASE appdb
FROM disk='d:\ctsdba\dbdump\db\appdb.dmp' WITH DBO_ONLY,
STANDBY = 'd:\ctsdba\dbdump\standby\appdb.ldf'
end




Brett

8-)
Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-06-21 : 06:31:06
Thanks to all who have replied. It's nice to know that my life amuses you all ;-) Seriously though, sincere thanks to all. I'm sure this isn't over yet but I've just discovered that I can now get into the server again with my Windows account so it was presumably an issue with one of the servers.

I'd like to think that this was a one off with these guys but not so long ago, they shut our production system down with half an hours notice just so they could move it to a different cabinet. While this only took 15 mins or so, people were trying to use the system at the time (strange I know!). If it had been for something critical or if they had given a day or two's notice I wouldn't have minded. Am I just picky?! When my manager e-mailed there's, he didn't bother to reply. Then a few days later when I was at a meeting he chairs, he accused people of backstabbing and sniping and said that there was an issue with user expectations.

This one could run and run - watch this space

Steve no function beer well without
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-06-21 : 06:52:56
quote:
Originally posted by elwoos

This one could run and run - watch this space

I'm sat firmly in my chair waiting with baited breath!

Kristen
Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-06-23 : 09:24:53
The next generation-

I'm still having some issues which I think are caused by the fact that my favourite people are having network/server problems elsewhere, which means that I am having trouble getting an adequate backup i.e. a backup on one of the boxes in the room where both servers are and also a backup on a different site (where I am based).

I'm sure this will be sorted eventually (Yeah right!!) but I am interested in what people think about who is responsible here for all this should the system die in the very near future. As I see it there are a few possibilities

1) the Senior managers who refused a backup system some months ago when warned this was pending
2) the not quite so senior managers who didn't tell everyone else formally that there were issues with the backups
3) the network admins who didn't tell everyone else informally that there were issues with the backups
4) people like me who have heard rumours about the backups and then asked the network admins who have repeatedly stated that the backups were being run. And, now that I know they aren't being run have taken a while to get a system in place. My role is not just dba like, I have a variety of hats all of which have something that is more important than anything else to deal with

thanks

Steve no function beer well without
Go to Top of Page

MuadDBA

628 Posts

Posted - 2004-06-23 : 09:30:30
if you know something, it's your responsibility to make everyone who might be impacted aware of this dangerous situation (within reason, following the chain of command type of thing). If all else fails, tell the users.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-23 : 13:27:10
And with that said...make sure your resume is up to date...



Brett

8-)

EDIT: Oh, and I suggest you drink heavily

Go to Top of Page

MuadDBA

628 Posts

Posted - 2004-06-23 : 14:32:33
Oh, and document, with e-mails, with memos, whatever...so you have proof you have brought this to someone's attention.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-06-23 : 16:54:51
quote:
Originally posted by elwoos

but I am interested in what people think about who is responsible here ...

Who is the youngest? Yeah, that'll do!

Kristen
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -