backup db. No loop nor cursor.

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)

backup db. No loop nor cursor.

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-12-10 : 15:10:44

How do you backup db without using cursor or loops?
My code:

declare @db table (dbName sysname)
declare @sql varchar(8000)
declare @bk varchar(8000)

insert @db
select top 2 name
from master..sysdatabases

select @sql= name from master..sysdatabases

select @bk = 'backup database ' + @sql +
' to disk = ''C:\' + @sql + '.bak'''
exec (@bk)

http://www.sqlserverstudy.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 15:17:56

Any two databases goes?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 15:21:39

[code]declare @sql varchar(8000)

set @sql = ''

select top 2 @sql = @sql + ';backup database ' + QUOTENAME(name) + ' to disk = ''C:\' + name + '.bak'
from master..sysdatabases
order by name

exec (@sql)[/code]

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-10 : 15:24:26

Why don't you want to use cursors or loops? There is no performance impact with these for backups.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-12-10 : 15:30:14

quote:
Originally posted by tkizer

Why don't you want to use cursors or loops? There is no performance impact with these for backups.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Really, I didn't know. What does cursor or loops impact?

http://www.sqlserverstudy.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 15:33:53

What Tara talks about, is that the backup itself takes so much longer time than a loop or cursor take, in comparison.

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-10 : 15:35:26

It impacts applications as typically you can handle it via set-based methods. Most DBA procedures must loop.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-10 : 16:28:07

funketekun -- never avoid or do something because that's what people "say" or that's what you hear. Avoid something because you understand *why* to avoid it, and use techniques because you understand *why* you should use them. Blindly following advise from anyone without understanding it is never a good idea.

If you know why cursors or loops should be avoided in SQL, then you'll know that there is no reason at all to avoid using them in scenarios like this one. If you don't know why cursors or loops should be avoided, and WHEN and WHERE they should be avoided, then it is time to ask someone and/or educate yourself by researching this on your own and really thinking about it.

- Jeff
http://weblogs.sqlteam.com/JeffS

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-12-10 : 17:14:33

I undestand why. cause cursors fetch thru each row and we need every row on sysdatabases.

so if we don't need everyrow, we wont need to use cursors. In this case we do.

http://www.sqlserverstudy.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 17:18:52

You still don't get it?

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-10 : 17:27:03

quote:
Originally posted by funketekun

I undestand why. cause cursors fetch thru each row and we need every row on sysdatabases.

so if we don't need everyrow, we wont need to use cursors. In this case we do.

You don't need every row from sysdatabases. What happens if you just want the user databases? You loop through sysdatabases to get those.

Check out isp_Backup (I'm the author) for how we backup our databases:
http://weblogs.sqlteam.com/tarad/archive/2007/02/26/60120.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-12-10 : 21:07:44

quote:
Originally posted by Peso

You still don't get it?

E 12°55'05.25"
N 56°04'39.16"

But I need all the databases.

http://www.sqlserverstudy.com

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-10 : 21:56:45

here's how I do my backups - it's very fast.

bcp master.dbo.sysdatabases out backup.txt -SMYSERVER -T -c

elsasoft.org

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-10 : 22:22:11

How many dbs on the server? Get db name to backup in cursor is different from fetching million rows to update.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-11 : 01:02:12

quote:
Originally posted by funketekun

quote:
Originally posted by Peso

You still don't get it?

But I need all the databases.

So use my script.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-12-11 : 08:08:11

I have implemented the batch-mode for backup (cursor and loop and multi-file) and when one database encounters a problem or takes a lot more time, the succeeding databases "suffer".

So now I prefer to separate the backups via job steps, this way I can do a lot more control per database that has been backed up without encapsulating them all in one batch.

Just one backup procedure which is called from the job step supplied with the backup directory and database name.

The only caveat is that you have to remember to add job step(s) for new database(s), but that can be handled by defining your own process for setting up new databases.

I'm not a guru, but this works for me...

--------------------
keeping it simple...

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-12-11 : 08:54:08

quote:
Originally posted by tkizer

quote:
Originally posted by funketekun

quote:
Originally posted by Peso

You still don't get it?

But I need all the databases.

So use my script.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Your script is too complex. I want to write my own.

http://www.sqlserverstudy.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 09:07:31

Good luck.

E 12°55'05.25"
N 56°04'39.16"

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-12-11 : 09:19:53

quote:
Originally posted by tkizer

quote:
Originally posted by funketekun

I undestand why. cause cursors fetch thru each row and we need every row on sysdatabases.

so if we don't need everyrow, we wont need to use cursors. In this case we do.

You don't need every row from sysdatabases. What happens if you just want the user databases? You loop through sysdatabases to get those.

Check out isp_Backup (I'm the author) for how we backup our databases:
http://weblogs.sqlteam.com/tarad/archive/2007/02/26/60120.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

why you won't backup the sys db?

http://www.sqlserverstudy.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-11 : 13:38:35

quote:
Originally posted by funketekun

quote:
Originally posted by tkizer

quote:
Originally posted by funketekun

I undestand why. cause cursors fetch thru each row and we need every row on sysdatabases.

so if we don't need everyrow, we wont need to use cursors. In this case we do.

You don't need every row from sysdatabases. What happens if you just want the user databases? You loop through sysdatabases to get those.

Check out isp_Backup (I'm the author) for how we backup our databases:
http://weblogs.sqlteam.com/tarad/archive/2007/02/26/60120.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

why you won't backup the sys db?

My code handles system databases. You can specify All, System, User, or a single database to be backed up. The terms should be self-explanatory.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources