Multipe text file import to single table

All,
I need to import the contents of 1000+ text files into a single table
in SQL Server 7.0. The files are plain ascii, fields seperated by
"/".
Does anyone have ideas of how this can be achieved?
Regards,
A.Multiple BULK INSERT commands?
"Adrian Smith" <adriancsi@.aol.com> wrote in message
news:eb79b8a3.0311181012.76211750@.posting.google.com...
> All,
> I need to import the contents of 1000+ text files into a single table
> in SQL Server 7.0. The files are plain ascii, fields seperated by
> "/".
> Does anyone have ideas of how this can be achieved?
> Regards,
> A.|||Do they all have exactly the same format?
If so it may be easier to concatenate them into one single file... From a
DOS prompt you could use:
TYPE *.TXT > BIGFILE.TXT
(Assuming they all have .TXT extension and that they're the only files in
the folder with .TXT extension, of course)
From there you could then use either BULK COPY or BCP.
If for some reason you don't want to or can't concatenate them, you could
write a batch file that would call BCP, replacing the filename parameter in
the batch file with %1, then call the batch file for each file using (again
from a DOS prompt):
FOR %i IN (DIR *.TXT) DO INSERT.BAT %i
Or you could write the entire thing in a SQL script using a cursor and a
call to xp_cmdshell to get a list of the files... IMO the DOS methods are a
lot easier.
"Adrian Smith" <adriancsi@.aol.com> wrote in message
news:eb79b8a3.0311181012.76211750@.posting.google.com...
> All,
> I need to import the contents of 1000+ text files into a single table
> in SQL Server 7.0. The files are plain ascii, fields seperated by
> "/".
> Does anyone have ideas of how this can be achieved?
> Regards,
> A.

Multipe receipients for Operators in SQL 2005?

Using database mail. It works using a single email address for an operator.
Adding a second email address after a semicolon still sends to the first
email address but not the second. Separating them with a comma breaks the
whole thing, no email is sent.
Does this mean I have to create a dl in Exchange, if I need to notify more
than one person for a given event?
GerhardHi Gerhard,
That's usually the easiest way. It also allows who is on the list to be
managed externally easily.
HTH,
Greg
"Gerhard" <gerhardpremovethis@.inch.com> wrote in message
news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> Using database mail. It works using a single email address for an
> operator.
> Adding a second email address after a semicolon still sends to the first
> email address but not the second. Separating them with a comma breaks the
> whole thing, no email is sent.
> Does this mean I have to create a dl in Exchange, if I need to notify more
> than one person for a given event?
> Gerhard
>|||Thanks Greg,
So are you saying it cannot be managed from the Operator page?
I would have preferred to handle it in SQL Server rather than Exchange,
Gerhard
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:e6n1IJWJGHA.2696@.TK2MSFTNGP14.phx.gbl...
> Hi Gerhard,
> That's usually the easiest way. It also allows who is on the list to be
> managed externally easily.
> HTH,
> Greg
> "Gerhard" <gerhardpremovethis@.inch.com> wrote in message
> news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
more[vbcol=seagreen]
>|||Greg,
Forget that question. It works with semicolons. I re-created everything
today and did some more testing. SQL 2005 has been difficult for us.
One thing I've noticed is that things often don't work on the first try, and
then when you blow them away and re-create them they start working.
I have a SRX with Microsoft right now, not on this, on something much more
serious where even they can't get it to work. It is just not working at
all. I guess I keep forgetting that it is an initial release and that it
behaves as such,
Gerhard
"Gerhard Paulman" <gerhardpremovethis@.inch.com> wrote in message
news:uKWQvLaJGHA.3224@.TK2MSFTNGP09.phx.gbl...
> Thanks Greg,
> So are you saying it cannot be managed from the Operator page?
> I would have preferred to handle it in SQL Server rather than Exchange,
> Gerhard
> "Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
> news:e6n1IJWJGHA.2696@.TK2MSFTNGP14.phx.gbl...
first[vbcol=seagreen]
> the
> more
>

Multipe receipients for Operators in SQL 2005?

Using database mail. It works using a single email address for an operator.
Adding a second email address after a semicolon still sends to the first
email address but not the second. Separating them with a comma breaks the
whole thing, no email is sent.
Does this mean I have to create a dl in Exchange, if I need to notify more
than one person for a given event?
Gerhard
Hi Gerhard,
That's usually the easiest way. It also allows who is on the list to be
managed externally easily.
HTH,
Greg
"Gerhard" <gerhardpremovethis@.inch.com> wrote in message
news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> Using database mail. It works using a single email address for an
> operator.
> Adding a second email address after a semicolon still sends to the first
> email address but not the second. Separating them with a comma breaks the
> whole thing, no email is sent.
> Does this mean I have to create a dl in Exchange, if I need to notify more
> than one person for a given event?
> Gerhard
>
|||Thanks Greg,
So are you saying it cannot be managed from the Operator page?
I would have preferred to handle it in SQL Server rather than Exchange,
Gerhard
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:e6n1IJWJGHA.2696@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hi Gerhard,
> That's usually the easiest way. It also allows who is on the list to be
> managed externally easily.
> HTH,
> Greg
> "Gerhard" <gerhardpremovethis@.inch.com> wrote in message
> news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
more
>
|||Greg,
Forget that question. It works with semicolons. I re-created everything
today and did some more testing. SQL 2005 has been difficult for us.
One thing I've noticed is that things often don't work on the first try, and
then when you blow them away and re-create them they start working.
I have a SRX with Microsoft right now, not on this, on something much more
serious where even they can't get it to work. It is just not working at
all. I guess I keep forgetting that it is an initial release and that it
behaves as such,
Gerhard
"Gerhard Paulman" <gerhardpremovethis@.inch.com> wrote in message
news:uKWQvLaJGHA.3224@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Greg,
> So are you saying it cannot be managed from the Operator page?
> I would have preferred to handle it in SQL Server rather than Exchange,
> Gerhard
> "Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
> news:e6n1IJWJGHA.2696@.TK2MSFTNGP14.phx.gbl...
first
> the
> more
>

Multipe receipients for Operators in SQL 2005?

Using database mail. It works using a single email address for an operator.
Adding a second email address after a semicolon still sends to the first
email address but not the second. Separating them with a comma breaks the
whole thing, no email is sent.
Does this mean I have to create a dl in Exchange, if I need to notify more
than one person for a given event?
GerhardHi Gerhard,
That's usually the easiest way. It also allows who is on the list to be
managed externally easily.
HTH,
Greg
"Gerhard" <gerhardpremovethis@.inch.com> wrote in message
news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> Using database mail. It works using a single email address for an
> operator.
> Adding a second email address after a semicolon still sends to the first
> email address but not the second. Separating them with a comma breaks the
> whole thing, no email is sent.
> Does this mean I have to create a dl in Exchange, if I need to notify more
> than one person for a given event?
> Gerhard
>|||Thanks Greg,
So are you saying it cannot be managed from the Operator page?
I would have preferred to handle it in SQL Server rather than Exchange,
Gerhard
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:e6n1IJWJGHA.2696@.TK2MSFTNGP14.phx.gbl...
> Hi Gerhard,
> That's usually the easiest way. It also allows who is on the list to be
> managed externally easily.
> HTH,
> Greg
> "Gerhard" <gerhardpremovethis@.inch.com> wrote in message
> news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> > Using database mail. It works using a single email address for an
> > operator.
> > Adding a second email address after a semicolon still sends to the first
> > email address but not the second. Separating them with a comma breaks
the
> > whole thing, no email is sent.
> > Does this mean I have to create a dl in Exchange, if I need to notify
more
> > than one person for a given event?
> > Gerhard
> >
> >
>|||Greg,
Forget that question. It works with semicolons. I re-created everything
today and did some more testing. SQL 2005 has been difficult for us.
One thing I've noticed is that things often don't work on the first try, and
then when you blow them away and re-create them they start working.
I have a SRX with Microsoft right now, not on this, on something much more
serious where even they can't get it to work. It is just not working at
all. I guess I keep forgetting that it is an initial release and that it
behaves as such,
Gerhard
"Gerhard Paulman" <gerhardpremovethis@.inch.com> wrote in message
news:uKWQvLaJGHA.3224@.TK2MSFTNGP09.phx.gbl...
> Thanks Greg,
> So are you saying it cannot be managed from the Operator page?
> I would have preferred to handle it in SQL Server rather than Exchange,
> Gerhard
> "Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
> news:e6n1IJWJGHA.2696@.TK2MSFTNGP14.phx.gbl...
> > Hi Gerhard,
> >
> > That's usually the easiest way. It also allows who is on the list to be
> > managed externally easily.
> >
> > HTH,
> >
> > Greg
> >
> > "Gerhard" <gerhardpremovethis@.inch.com> wrote in message
> > news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> > > Using database mail. It works using a single email address for an
> > > operator.
> > > Adding a second email address after a semicolon still sends to the
first
> > > email address but not the second. Separating them with a comma breaks
> the
> > > whole thing, no email is sent.
> > > Does this mean I have to create a dl in Exchange, if I need to notify
> more
> > > than one person for a given event?
> > > Gerhard
> > >
> > >
> >
> >
>

Multipe Inserts Causes Error - Please Help

I am currently using ADO to create a recordset, once I have selected my data (1 column called customer - datatype int - about 50,000 of them), I then post a transaction to each cutomer using a stored procedure. I am using a disconnect recordset for the initial ADO recordset and after each run of the store procedure I set everything to closed then nothing. I have plenty of resources available but I still get the following error.

17066 :
SQL Server Assertion: File: <S:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.

Can anyone help, sorry if this is not infomation I can post more if needed only to be honest I don't what to extra to post.

As a matter of interest I have also had this error when importing 1.7 millon records with DTS!!Have since found out this was due to a hardware issue, it seems that it relates to CPU clocking speeds and or Video cards.

Have replaced the cpu and motherboard in my case and the problem has not returned, and I have run extensive test and re-tests

Multipe DBs on 1 Server backup schedule

is it best to kick off the backups at the same time, i.e. 1am, or to space
them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
Any links or articles welcome.
Thanks,
MPM
As always it depends. But in general if you can issue the backups at
different times you will most likely get better performance for each and
less impact on the general users. This is due to the large amounts of I/O
(and to some extent CPU) that backups can induce. If you run multiple
backups at the same time you run the risk of maximizing your I/O bandwidth
and even causing network bottlenecks if done remotely. If you are going to
backup multiple dbs one right after the other it is best to do them in a
loop vs. setting specific times. That is because you never know if the
first is finished before the second one starts and so on. If you kick them
off after the previous completes you never have to worry about adjusting the
times as DB sizes change. Here is a sample of that:
-- Backup all user Databases --
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
'DD_Pubs_Full')
EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
'C:\Data\Backups\DD_Pubs_Full.bak'
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
'DD_Northwind_Full')
EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
'C:\Data\Backups\DD_Northwind_Full.bak'
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT Catalog_Name
FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'DD_' + @.DBName + '_Full'
SET @.Name = @.DBName + N' Full Backup'
SET @.Descript = @.DBName + N' Full Backup - ' +
CONVERT(NVARCHAR(16),GETDATE(),112)
BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name,
DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
Andrew J. Kelly SQL MVP
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
> is it best to kick off the backups at the same time, i.e. 1am, or to space
> them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
> Any links or articles welcome.
> Thanks,
> MPM
|||Andrew,
Thanks a lot for that excellent reply.
MPM
"Andrew J. Kelly" wrote:

> As always it depends. But in general if you can issue the backups at
> different times you will most likely get better performance for each and
> less impact on the general users. This is due to the large amounts of I/O
> (and to some extent CPU) that backups can induce. If you run multiple
> backups at the same time you run the risk of maximizing your I/O bandwidth
> and even causing network bottlenecks if done remotely. If you are going to
> backup multiple dbs one right after the other it is best to do them in a
> loop vs. setting specific times. That is because you never know if the
> first is finished before the second one starts and so on. If you kick them
> off after the previous completes you never have to worry about adjusting the
> times as DB sizes change. Here is a sample of that:
>
> -- Backup all user Databases --
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
> 'DD_Pubs_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
> 'C:\Data\Backups\DD_Pubs_Full.bak'
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
> 'DD_Northwind_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
> 'C:\Data\Backups\DD_Northwind_Full.bak'
>
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
> DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
> DECLARE cur_DBs CURSOR STATIC LOCAL
> FOR SELECT Catalog_Name
> FROM Information_Schema.Schemata
> WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.Device = N'DD_' + @.DBName + '_Full'
> SET @.Name = @.DBName + N' Full Backup'
> SET @.Descript = @.DBName + N' Full Backup - ' +
> CONVERT(NVARCHAR(16),GETDATE(),112)
> BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name,
> DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
> RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
>
> --
> Andrew J. Kelly SQL MVP
>
> "MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
> news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
>
>

Multipe DBs on 1 Server backup schedule

is it best to kick off the backups at the same time, i.e. 1am, or to space
them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
Any links or articles welcome.
Thanks,
MPMAs always it depends. But in general if you can issue the backups at
different times you will most likely get better performance for each and
less impact on the general users. This is due to the large amounts of I/O
(and to some extent CPU) that backups can induce. If you run multiple
backups at the same time you run the risk of maximizing your I/O bandwidth
and even causing network bottlenecks if done remotely. If you are going to
backup multiple dbs one right after the other it is best to do them in a
loop vs. setting specific times. That is because you never know if the
first is finished before the second one starts and so on. If you kick them
off after the previous completes you never have to worry about adjusting the
times as DB sizes change. Here is a sample of that:
-- Backup all user Databases --
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] ='DD_Pubs_Full')
EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
'C:\Data\Backups\DD_Pubs_Full.bak'
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] ='DD_Northwind_Full')
EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
'C:\Data\Backups\DD_Northwind_Full.bak'
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT Catalog_Name
FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'DD_' + @.DBName + '_Full'
SET @.Name = @.DBName + N' Full Backup'
SET @.Descript = @.DBName + N' Full Backup - ' +
CONVERT(NVARCHAR(16),GETDATE(),112)
BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name,
DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
Andrew J. Kelly SQL MVP
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
> is it best to kick off the backups at the same time, i.e. 1am, or to space
> them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
> Any links or articles welcome.
> Thanks,
> MPM|||Andrew,
Thanks a lot for that excellent reply.
MPM
"Andrew J. Kelly" wrote:
> As always it depends. But in general if you can issue the backups at
> different times you will most likely get better performance for each and
> less impact on the general users. This is due to the large amounts of I/O
> (and to some extent CPU) that backups can induce. If you run multiple
> backups at the same time you run the risk of maximizing your I/O bandwidth
> and even causing network bottlenecks if done remotely. If you are going to
> backup multiple dbs one right after the other it is best to do them in a
> loop vs. setting specific times. That is because you never know if the
> first is finished before the second one starts and so on. If you kick them
> off after the previous completes you never have to worry about adjusting the
> times as DB sizes change. Here is a sample of that:
>
> -- Backup all user Databases --
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] => 'DD_Pubs_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
> 'C:\Data\Backups\DD_Pubs_Full.bak'
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] => 'DD_Northwind_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
> 'C:\Data\Backups\DD_Northwind_Full.bak'
>
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
> DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
> DECLARE cur_DBs CURSOR STATIC LOCAL
> FOR SELECT Catalog_Name
> FROM Information_Schema.Schemata
> WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.Device = N'DD_' + @.DBName + '_Full'
> SET @.Name = @.DBName + N' Full Backup'
> SET @.Descript = @.DBName + N' Full Backup - ' +
> CONVERT(NVARCHAR(16),GETDATE(),112)
> BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name,
> DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
> RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
>
> --
> Andrew J. Kelly SQL MVP
>
> "MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
> news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
> > is it best to kick off the backups at the same time, i.e. 1am, or to space
> > them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
> >
> > Any links or articles welcome.
> >
> > Thanks,
> > MPM
>
>

Multipe DBs on 1 Server backup schedule

is it best to kick off the backups at the same time, i.e. 1am, or to space
them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
Any links or articles welcome.
Thanks,
MPMAs always it depends. But in general if you can issue the backups at
different times you will most likely get better performance for each and
less impact on the general users. This is due to the large amounts of I/O
(and to some extent CPU) that backups can induce. If you run multiple
backups at the same time you run the risk of maximizing your I/O bandwidth
and even causing network bottlenecks if done remotely. If you are going to
backup multiple dbs one right after the other it is best to do them in a
loop vs. setting specific times. That is because you never know if the
first is finished before the second one starts and so on. If you kick them
off after the previous completes you never have to worry about adjusting the
times as DB sizes change. Here is a sample of that:
-- Backup all user Databases --
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
'DD_Pubs_Full')
EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
'C:\Data\Backups\DD_Pubs_Full.bak'
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
'DD_Northwind_Full')
EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
'C:\Data\Backups\DD_Northwind_Full.bak'
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT Catalog_Name
FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'DD_' + @.DBName + '_Full'
SET @.Name = @.DBName + N' Full Backup'
SET @.Descript = @.DBName + N' Full Backup - ' +
CONVERT(NVARCHAR(16),GETDATE(),112)
BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name,
DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
Andrew J. Kelly SQL MVP
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
> is it best to kick off the backups at the same time, i.e. 1am, or to space
> them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
> Any links or articles welcome.
> Thanks,
> MPM|||Andrew,
Thanks a lot for that excellent reply.
MPM
"Andrew J. Kelly" wrote:

> As always it depends. But in general if you can issue the backups at
> different times you will most likely get better performance for each and
> less impact on the general users. This is due to the large amounts of I/O
> (and to some extent CPU) that backups can induce. If you run multiple
> backups at the same time you run the risk of maximizing your I/O bandwidth
> and even causing network bottlenecks if done remotely. If you are going t
o
> backup multiple dbs one right after the other it is best to do them in a
> loop vs. setting specific times. That is because you never know if the
> first is finished before the second one starts and so on. If you kick the
m
> off after the previous completes you never have to worry about adjusting t
he
> times as DB sizes change. Here is a sample of that:
>
> -- Backup all user Databases --
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
> 'DD_Pubs_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
> 'C:\Data\Backups\DD_Pubs_Full.bak'
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
> 'DD_Northwind_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
> 'C:\Data\Backups\DD_Northwind_Full.bak'
>
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
> DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
> DECLARE cur_DBs CURSOR STATIC LOCAL
> FOR SELECT Catalog_Name
> FROM Information_Schema.Schemata
> WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMP
DB')
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.Device = N'DD_' + @.DBName + '_Full'
> SET @.Name = @.DBName + N' Full Backup'
> SET @.Descript = @.DBName + N' Full Backup - ' +
> CONVERT(NVARCHAR(16),GETDATE(),112)
> BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name
,
> DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
> RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
>
> --
> Andrew J. Kelly SQL MVP
>
> "MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
> news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
>
>