SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Database Backup Script
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 10/27/2002 :  21:18:35  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

Canada
158 Posts

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

Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 10/28/2002 :  10:25:51  Show Profile  Reply with Quote
How does the maintenance plan work? What makes it easy?

Jay White
{0}
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 10/28/2002 :  10:45:14  Show Profile  Visit nr's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 10/28/2002 :  11:05:36  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 10/28/2002 :  11:08:03  Show Profile  Visit nr's Homepage  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 10/28/2002 :  11:08:30  Show Profile  Visit nr's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 10/28/2002 :  11:12:24  Show Profile  Reply with Quote
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

Canada
158 Posts

Posted - 10/28/2002 :  13:58:11  Show Profile  Reply with Quote
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

Canada
539 Posts

Posted - 10/29/2002 :  17:46:35  Show Profile  Reply with Quote
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

USA
3246 Posts

Posted - 10/30/2002 :  12:28:27  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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 - 11/06/2002 :  07:59:06  Show Profile  Reply with 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.

Bye


Go to Top of Page

robvolk
Most Valuable Yak

USA
15671 Posts

Posted - 11/06/2002 :  08:03:08  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 11/06/2002 :  08:55:23  Show Profile  Visit nr's Homepage  Reply with Quote
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 - 11/06/2002 :  09:24:52  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 11/06/2002 :  09:44:57  Show Profile  Visit nr's Homepage  Reply with Quote
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

USA
15671 Posts

Posted - 11/06/2002 :  09:48:43  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/06/2002 :  09:53:55  Show Profile  Reply with Quote
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

USA
15671 Posts

Posted - 11/06/2002 :  09:56:03  Show Profile  Visit robvolk's Homepage  Reply with Quote
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 - 11/06/2002 :  10:06:48  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/06/2002 :  10:22:54  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000