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
 Site Related Forums
 Article Discussion
 Article: Database Backup Script

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-10-27 : 21:18:35
This procedure will backup every database on the server including any new ones. It will also delete any old backups after a defined retention period. It differs from production versions in that it will automatically back up any new databases.

Article Link.

1fred
Posting Yak Master

158 Posts

Posted - 2002-10-28 : 10:24:09
Its a really nice sript, but it is so easy with maintenance plan...

Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-28 : 10:25:51
How does the maintenance plan work? What makes it easy?

Jay White
{0}
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-28 : 10:45:14
Try just creating a new maintenance plan and see.

I don't like maint.exe - and you will notice the number of questions about it failing. OK most of the problems are probably because people don't know what they are asking it to do - but if they didn't use it they would learn a lot more about maintenance.

This also enables you to exclude databases from the backup easily or change the frequency of individual databases - don't know whether you can do that with maint. And it it keeps everything withing the server rather than loading a tool to run it.

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

Edited by - nr on 10/28/2002 10:47:11
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-28 : 11:05:36
quote:
Try just creating a new maintenance plan and see.

See what? Some cryptic calls to some extended stored procedures? I know how to set it up, my question is how does it work? How could a dba, who is responsible for backups, rely on a magical wizardry when she can't answer "How does it work?"

The Maintenance Plan wizard is useful if you don't know how to do a backup/restore.

Jay White
{0}
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-28 : 11:08:03
Yep - I'd go with that - that's why I've never used it.

>> The Maintenance Plan wizard is useful if you don't know how to do a backup/restore.

Not really - it just stops you from learning.

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

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-28 : 11:08:30
Yep - I'd go with that - that's why I've never used it.

>> The Maintenance Plan wizard is useful if you don't know how to do a backup/restore.

Not really - it just stops you from learning.

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

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-28 : 11:12:24
quote:

>> The Maintenance Plan wizard is useful if you don't know how to do a backup/restore.

Not really - it just stops you from learning.

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



Just like cursors ... come on nr, I'm desperately trying to be witty here ...

Jay White
{0}
Go to Top of Page

1fred
Posting Yak Master

158 Posts

Posted - 2002-10-28 : 13:58:11
Ok, I can see that my point of view is not shared , I agree that creating a maintenance plan is not the best way to learn who your SQL server really works. But it's a good start, the plan you created is automatically scheduled,it checks integrety, create backup, optimize indexes, remove old backup and you can even get a report on the job . When I start working with Sql server I was happy to have that! I had never worked with it and I have the DBA job, before I worked on Oracle

Go to Top of Page

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-10-29 : 17:46:35
Wow, I was working on something simular (just no where near as brilliant). Very very nice nr

-----------------------
SQL isn't just a hobby, It's an addiction
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-10-30 : 12:28:27
quote:

Just like cursors ... come on nr, I'm desperately trying to be witty here ...



Don't let him get to you Jay. It's that warm, flat beer he drinks...

Go to Top of Page

jimmers
Starting Member

12 Posts

Posted - 2002-11-06 : 07:59:06
Instead of discussing why Maintenance Plan is bad thing for DBA, it is better to fix
some holes in spBackupAllDatabases. Specifically, it doesn't validate @Path parameter
so anybody who can call it can pass arbitrary commands to the OS shell. Article
doesn't say anything about setting permissions on SP nor does supplied T-SQL.

Bye


Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-06 : 08:03:08
quote:
Instead of discussing why Maintenance Plan is bad thing for DBA, it is better to fix some holes in spBackupAllDatabases. Specifically, it doesn't validate @Path parameter so anybody who can call it can pass arbitrary commands to the OS shell. Article doesn't say anything about setting permissions on SP nor does supplied T-SQL.
Instead of criticizing the procedure that was provided, why not contribute an improved version that fixes the holes you describe?

Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-06 : 08:55:23
quote:

Article
doesn't say anything about setting permissions on SP nor does supplied T-SQL.



SP is in Admin database and permissions are controlled by access to that database.

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

jimmers
Starting Member

12 Posts

Posted - 2002-11-06 : 09:24:52
2robvolk:

Its not my job to fix other people bugs.

2nr:

"SP is in Admin database" doesn't mean that it is protected.

Bye


Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-06 : 09:44:57
SP is in Admin database and permissions are controlled by access to that database.

i.e. only users that should have access to admin procedures have any access to the database. That would usually mean the DBAs only. If the system uses sa then that can be used otherwise the admin role users.
Basically anyone who has access to this database shold also have access to everything else on the server.



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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-06 : 09:48:43
quote:
2robvolk:

Its not my job to fix other people bugs.
You don't have much of a career in database administration or development then, cause that's about 90% of the job.

If you have something positive to contribute, please feel free to post it here. But if you're looking simply to criticize others who make a positive contribution, find another site to do it. We all help each other on SQL Team.

Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-06 : 09:53:55
quote:
Its not my job to fix other people bugs.

You're right...putting thought into improving a solution on a free public forum (who's purpose is community education and the betterment of the general dba population) is really a pain in the ass. Helping people just isn't worth it and really is a clear sign of weekness weakness...

Jay White
{0}

Edited by - Page47 on 11/06/2002 10:08:25
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-06 : 09:56:03
quote:
Helping people just isn't worth it and really is a clear sign of weekness...
Is that related to monthness? I think I might be coming down with a case of it and can't find any symptoms on WebMD...

Go to Top of Page

jimmers
Starting Member

12 Posts

Posted - 2002-11-06 : 10:06:48

2robvolk:

I'm not sure what you mean here, but it is very *unpolitely* to reply in such manner to my bug report. It is a common practice for software industry to confirm found bugs, fix 'em and disclose details. Regarding this issue (xp_cmdshell), anyone concerned about security may look into SQL Server 2000 internals and find a fix (fn_escapecmdshellsymbols, fn_escapecmdshellsymbolsremovequotes).

Moreover, I'd like to imform everyone on this site that few times I've reported those people
(graz, robvolk) bugs on their own site (multiple SQL Injection bugs) and they even don't say
me "thanks". Also those “gurus” have deleted my messages where I’ve shown how easy it is to break encryption used by SQL Server 7. I think that this forum is completely controlled by “moderators” and reflects only their own opinion but I’ll post further my findings and thoughts here because I’d like to share information.

Bye
Martin Rakhmanoff




Edited by - jimmers on 11/06/2002 10:09:53
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-06 : 10:22:54
quote:
it doesn't validate @Path parameter


create table #fileexists
(
fileexists int,
fileisdir int,
pardirexist int
)

insert into #fileexists
exec master.dbo.xp_fileexist @path

if not exists(select 1 from #fileexists where fileisdir = 1)
begin
--handle invalide @path error
return
end

 


Jay White
{0}
Go to Top of Page
    Next Page

- Advertisement -