Multiple files/filegroups

Hi,
We have a database which has reached our disk capacity of 340GB and we
are looking at our options on how to resolve the disk space issue. The
problem is I'm not too familiar with setting up additional filegroups
and/or secondary files, or how they function.
The database consists of only one table which stores all our product
hi-res photography, each image being about 20MBs. The table is not
heavily accessed - either read or write - but it is our central storage
location for our studio photographs.
We could delete all images for discontinued product, but we would then
have to rely on a tape backup if we needed access to them in the
future.
Other options I think we have are to: -
1. Create a new database on a separate disk and have a scheduled
archive task to move any discontinued product images to a table in this
database. Place this DB on a separate disk. This means our 'live' db
would be much smaller and quicker to backup
2. Create a new filegroup for existing DB and create an archive table
on this group (file on different disk). Archive periodically and use a
view to join the two tables. Only perform regular database backups on
the 'live' filegroup
3. Create a secondary file on the same filegroup (primary) as the
'live' table and let the system start to fill this up with data as new
records are added. Downside of this is the backup size will not reduce.
On point 3. I'm not sure how this works - will the primary file start
to reduce in size, or will it remain at capacity and the secondary file
will start to increase in size? Is there something I can do to start
balancing the file sizes?
Also would be interested to know opinions on what the best approach
would be and if I have any misconceptions about the above options (or
if I've missed any)!
Thanks,
Andrew Duncan
Hi Tibor,
Thanks for the advice. Option 1 does seem the simplest, so I'll be
opting for that.
Rgds,
Andrew
Tibor Karaszi wrote:[vbcol=seagreen]
> Some thoughts:
>
> Sounds like a lot of manual work.
>
> This is probably what I would look for, but it require that your code adapts for this.
>
> You still need to adapt your code. But you now have a more complex backup strategy compared to 1. If
> you are on 2005, you can archive, set file group to read-only, backup the filegroup. If you now
> restore that filegroup backup, you don't need to restore all subsequent log backup. In 2000, you
> *do* need to restore all subsequent log backups, even if the file group was in read-only since the
> file group backup was performed.
>
> Space will be used from the file according to free space on each file. So you want to pre-allocate
> file size for the new file, and then shrink the old file to push data over to the new file (and then
> possibly increase the old file again so you have some free space).
> I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all of the data when you
> restore but backup/restore is a bit more complex. 2 doesn't really buy you anything if you are on
> 2000.
> If you go for 1, you can get data from different point in time when you restore (as you have two
> databases).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <andrew@.duncanhcmc.com> wrote in message
> news:1163749909.367437.223570@.e3g2000cwe.googlegro ups.com...
sql

Multiple files/filegroups

Hi,
We have a database which has reached our disk capacity of 340GB and we
are looking at our options on how to resolve the disk space issue. The
problem is I'm not too familiar with setting up additional filegroups
and/or secondary files, or how they function.
The database consists of only one table which stores all our product
hi-res photography, each image being about 20MBs. The table is not
heavily accessed - either read or write - but it is our central storage
location for our studio photographs.
We could delete all images for discontinued product, but we would then
have to rely on a tape backup if we needed access to them in the
future.
Other options I think we have are to: -
1. Create a new database on a separate disk and have a scheduled
archive task to move any discontinued product images to a table in this
database. Place this DB on a separate disk. This means our 'live' db
would be much smaller and quicker to backup
2. Create a new filegroup for existing DB and create an archive table
on this group (file on different disk). Archive periodically and use a
view to join the two tables. Only perform regular database backups on
the 'live' filegroup
3. Create a secondary file on the same filegroup (primary) as the
'live' table and let the system start to fill this up with data as new
records are added. Downside of this is the backup size will not reduce.
On point 3. I'm not sure how this works - will the primary file start
to reduce in size, or will it remain at capacity and the secondary file
will start to increase in size? Is there something I can do to start
balancing the file sizes?
Also would be interested to know opinions on what the best approach
would be and if I have any misconceptions about the above options (or
if I've missed any)!
Thanks,
Andrew DuncanSome thoughts:
> We could delete all images for discontinued product, but we would then
> have to rely on a tape backup if we needed access to them in the
> future.
Sounds like a lot of manual work.
> 1. Create a new database on a separate disk and have a scheduled
> archive task to move any discontinued product images to a table in this
> database. Place this DB on a separate disk. This means our 'live' db
> would be much smaller and quicker to backup
This is probably what I would look for, but it require that your code adapts for this.
> 2. Create a new filegroup for existing DB and create an archive table
> on this group (file on different disk). Archive periodically and use a
> view to join the two tables. Only perform regular database backups on
> the 'live' filegroup
You still need to adapt your code. But you now have a more complex backup strategy compared to 1. If
you are on 2005, you can archive, set file group to read-only, backup the filegroup. If you now
restore that filegroup backup, you don't need to restore all subsequent log backup. In 2000, you
*do* need to restore all subsequent log backups, even if the file group was in read-only since the
file group backup was performed.
> 3. Create a secondary file on the same filegroup (primary) as the
> 'live' table and let the system start to fill this up with data as new
> records are added. Downside of this is the backup size will not reduce.
> On point 3. I'm not sure how this works - will the primary file start
> to reduce in size, or will it remain at capacity and the secondary file
> will start to increase in size? Is there something I can do to start
> balancing the file sizes?
Space will be used from the file according to free space on each file. So you want to pre-allocate
file size for the new file, and then shrink the old file to push data over to the new file (and then
possibly increase the old file again so you have some free space).
I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all of the data when you
restore but backup/restore is a bit more complex. 2 doesn't really buy you anything if you are on
2000.
If you go for 1, you can get data from different point in time when you restore (as you have two
databases).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<andrew@.duncanhcmc.com> wrote in message
news:1163749909.367437.223570@.e3g2000cwe.googlegroups.com...
> Hi,
> We have a database which has reached our disk capacity of 340GB and we
> are looking at our options on how to resolve the disk space issue. The
> problem is I'm not too familiar with setting up additional filegroups
> and/or secondary files, or how they function.
> The database consists of only one table which stores all our product
> hi-res photography, each image being about 20MBs. The table is not
> heavily accessed - either read or write - but it is our central storage
> location for our studio photographs.
> We could delete all images for discontinued product, but we would then
> have to rely on a tape backup if we needed access to them in the
> future.
> Other options I think we have are to: -
> 1. Create a new database on a separate disk and have a scheduled
> archive task to move any discontinued product images to a table in this
> database. Place this DB on a separate disk. This means our 'live' db
> would be much smaller and quicker to backup
> 2. Create a new filegroup for existing DB and create an archive table
> on this group (file on different disk). Archive periodically and use a
> view to join the two tables. Only perform regular database backups on
> the 'live' filegroup
> 3. Create a secondary file on the same filegroup (primary) as the
> 'live' table and let the system start to fill this up with data as new
> records are added. Downside of this is the backup size will not reduce.
> On point 3. I'm not sure how this works - will the primary file start
> to reduce in size, or will it remain at capacity and the secondary file
> will start to increase in size? Is there something I can do to start
> balancing the file sizes?
> Also would be interested to know opinions on what the best approach
> would be and if I have any misconceptions about the above options (or
> if I've missed any)!
> Thanks,
> Andrew Duncan
>|||Hi Tibor,
Thanks for the advice. Option 1 does seem the simplest, so I'll be
opting for that.
Rgds,
Andrew
Tibor Karaszi wrote:
> Some thoughts:
> > We could delete all images for discontinued product, but we would then
> > have to rely on a tape backup if we needed access to them in the
> > future.
> Sounds like a lot of manual work.
>
> > 1. Create a new database on a separate disk and have a scheduled
> > archive task to move any discontinued product images to a table in this
> > database. Place this DB on a separate disk. This means our 'live' db
> > would be much smaller and quicker to backup
> This is probably what I would look for, but it require that your code adapts for this.
>
> > 2. Create a new filegroup for existing DB and create an archive table
> > on this group (file on different disk). Archive periodically and use a
> > view to join the two tables. Only perform regular database backups on
> > the 'live' filegroup
> You still need to adapt your code. But you now have a more complex backup strategy compared to 1. If
> you are on 2005, you can archive, set file group to read-only, backup the filegroup. If you now
> restore that filegroup backup, you don't need to restore all subsequent log backup. In 2000, you
> *do* need to restore all subsequent log backups, even if the file group was in read-only since the
> file group backup was performed.
>
> > 3. Create a secondary file on the same filegroup (primary) as the
> > 'live' table and let the system start to fill this up with data as new
> > records are added. Downside of this is the backup size will not reduce.
> > On point 3. I'm not sure how this works - will the primary file start
> > to reduce in size, or will it remain at capacity and the secondary file
> > will start to increase in size? Is there something I can do to start
> > balancing the file sizes?
> Space will be used from the file according to free space on each file. So you want to pre-allocate
> file size for the new file, and then shrink the old file to push data over to the new file (and then
> possibly increase the old file again so you have some free space).
> I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all of the data when you
> restore but backup/restore is a bit more complex. 2 doesn't really buy you anything if you are on
> 2000.
> If you go for 1, you can get data from different point in time when you restore (as you have two
> databases).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <andrew@.duncanhcmc.com> wrote in message
> news:1163749909.367437.223570@.e3g2000cwe.googlegroups.com...
> > Hi,
> >
> > We have a database which has reached our disk capacity of 340GB and we
> > are looking at our options on how to resolve the disk space issue. The
> > problem is I'm not too familiar with setting up additional filegroups
> > and/or secondary files, or how they function.
> >
> > The database consists of only one table which stores all our product
> > hi-res photography, each image being about 20MBs. The table is not
> > heavily accessed - either read or write - but it is our central storage
> > location for our studio photographs.
> >
> > We could delete all images for discontinued product, but we would then
> > have to rely on a tape backup if we needed access to them in the
> > future.
> >
> > Other options I think we have are to: -
> >
> > 1. Create a new database on a separate disk and have a scheduled
> > archive task to move any discontinued product images to a table in this
> > database. Place this DB on a separate disk. This means our 'live' db
> > would be much smaller and quicker to backup
> >
> > 2. Create a new filegroup for existing DB and create an archive table
> > on this group (file on different disk). Archive periodically and use a
> > view to join the two tables. Only perform regular database backups on
> > the 'live' filegroup
> >
> > 3. Create a secondary file on the same filegroup (primary) as the
> > 'live' table and let the system start to fill this up with data as new
> > records are added. Downside of this is the backup size will not reduce.
> >
> > On point 3. I'm not sure how this works - will the primary file start
> > to reduce in size, or will it remain at capacity and the secondary file
> > will start to increase in size? Is there something I can do to start
> > balancing the file sizes?
> >
> > Also would be interested to know opinions on what the best approach
> > would be and if I have any misconceptions about the above options (or
> > if I've missed any)!
> >
> > Thanks,
> >
> > Andrew Duncan
> >

Multiple files/filegroups

Hi,
We have a database which has reached our disk capacity of 340GB and we
are looking at our options on how to resolve the disk space issue. The
problem is I'm not too familiar with setting up additional filegroups
and/or secondary files, or how they function.
The database consists of only one table which stores all our product
hi-res photography, each image being about 20MBs. The table is not
heavily accessed - either read or write - but it is our central storage
location for our studio photographs.
We could delete all images for discontinued product, but we would then
have to rely on a tape backup if we needed access to them in the
future.
Other options I think we have are to: -
1. Create a new database on a separate disk and have a scheduled
archive task to move any discontinued product images to a table in this
database. Place this DB on a separate disk. This means our 'live' db
would be much smaller and quicker to backup
2. Create a new filegroup for existing DB and create an archive table
on this group (file on different disk). Archive periodically and use a
view to join the two tables. Only perform regular database backups on
the 'live' filegroup
3. Create a secondary file on the same filegroup (primary) as the
'live' table and let the system start to fill this up with data as new
records are added. Downside of this is the backup size will not reduce.
On point 3. I'm not sure how this works - will the primary file start
to reduce in size, or will it remain at capacity and the secondary file
will start to increase in size? Is there something I can do to start
balancing the file sizes?
Also would be interested to know opinions on what the best approach
would be and if I have any misconceptions about the above options (or
if I've missed any)!
Thanks,
Andrew DuncanSome thoughts:

> We could delete all images for discontinued product, but we would then
> have to rely on a tape backup if we needed access to them in the
> future.
Sounds like a lot of manual work.

> 1. Create a new database on a separate disk and have a scheduled
> archive task to move any discontinued product images to a table in this
> database. Place this DB on a separate disk. This means our 'live' db
> would be much smaller and quicker to backup
This is probably what I would look for, but it require that your code adapts
for this.

> 2. Create a new filegroup for existing DB and create an archive table
> on this group (file on different disk). Archive periodically and use a
> view to join the two tables. Only perform regular database backups on
> the 'live' filegroup
You still need to adapt your code. But you now have a more complex backup st
rategy compared to 1. If
you are on 2005, you can archive, set file group to read-only, backup the fi
legroup. If you now
restore that filegroup backup, you don't need to restore all subsequent log
backup. In 2000, you
*do* need to restore all subsequent log backups, even if the file group was
in read-only since the
file group backup was performed.

> 3. Create a secondary file on the same filegroup (primary) as the
> 'live' table and let the system start to fill this up with data as new
> records are added. Downside of this is the backup size will not reduce.
> On point 3. I'm not sure how this works - will the primary file start
> to reduce in size, or will it remain at capacity and the secondary file
> will start to increase in size? Is there something I can do to start
> balancing the file sizes?
Space will be used from the file according to free space on each file. So yo
u want to pre-allocate
file size for the new file, and then shrink the old file to push data over t
o the new file (and then
possibly increase the old file again so you have some free space).
I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all of
the data when you
restore but backup/restore is a bit more complex. 2 doesn't really buy you a
nything if you are on
2000.
If you go for 1, you can get data from different point in time when you rest
ore (as you have two
databases).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<andrew@.duncanhcmc.com> wrote in message
news:1163749909.367437.223570@.e3g2000cwe.googlegroups.com...
> Hi,
> We have a database which has reached our disk capacity of 340GB and we
> are looking at our options on how to resolve the disk space issue. The
> problem is I'm not too familiar with setting up additional filegroups
> and/or secondary files, or how they function.
> The database consists of only one table which stores all our product
> hi-res photography, each image being about 20MBs. The table is not
> heavily accessed - either read or write - but it is our central storage
> location for our studio photographs.
> We could delete all images for discontinued product, but we would then
> have to rely on a tape backup if we needed access to them in the
> future.
> Other options I think we have are to: -
> 1. Create a new database on a separate disk and have a scheduled
> archive task to move any discontinued product images to a table in this
> database. Place this DB on a separate disk. This means our 'live' db
> would be much smaller and quicker to backup
> 2. Create a new filegroup for existing DB and create an archive table
> on this group (file on different disk). Archive periodically and use a
> view to join the two tables. Only perform regular database backups on
> the 'live' filegroup
> 3. Create a secondary file on the same filegroup (primary) as the
> 'live' table and let the system start to fill this up with data as new
> records are added. Downside of this is the backup size will not reduce.
> On point 3. I'm not sure how this works - will the primary file start
> to reduce in size, or will it remain at capacity and the secondary file
> will start to increase in size? Is there something I can do to start
> balancing the file sizes?
> Also would be interested to know opinions on what the best approach
> would be and if I have any misconceptions about the above options (or
> if I've missed any)!
> Thanks,
> Andrew Duncan
>|||Hi Tibor,
Thanks for the advice. Option 1 does seem the simplest, so I'll be
opting for that.
Rgds,
Andrew
Tibor Karaszi wrote:[vbcol=seagreen]
> Some thoughts:
>
> Sounds like a lot of manual work.
>
> This is probably what I would look for, but it require that your code adap
ts for this.
>
> You still need to adapt your code. But you now have a more complex backup
strategy compared to 1. If
> you are on 2005, you can archive, set file group to read-only, backup the
filegroup. If you now
> restore that filegroup backup, you don't need to restore all subsequent lo
g backup. In 2000, you
> *do* need to restore all subsequent log backups, even if the file group wa
s in read-only since the
> file group backup was performed.
>
> Space will be used from the file according to free space on each file. So
you want to pre-allocate
> file size for the new file, and then shrink the old file to push data over
to the new file (and then
> possibly increase the old file again so you have some free space).
> I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all
of the data when you
> restore but backup/restore is a bit more complex. 2 doesn't really buy you
anything if you are on
> 2000.
> If you go for 1, you can get data from different point in time when you re
store (as you have two
> databases).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <andrew@.duncanhcmc.com> wrote in message
> news:1163749909.367437.223570@.e3g2000cwe.googlegroups.com...

multiple database files per database on a cluster

i have an issue where i am running out of drive space on a sql server cluste
r
one idea that was presente was to add another disk to the cluster then add
that disk to the list of database files for the db that is growing?
is this a good idea?Works for me. Make sure and put the new cluster disk in the SQL resource
group AND set the SQL Server to be dependent on the new disk resource.
Otherwise, you cannot write SQL data files to the new clustered disk. You
will have to take the SQL service offline briefly to set the dependencies.
Also, disable auto-grow for the data files on the original disk.
Geoff N. Hiten
Microsoft SQL Server MVP
"jason" <jason@.discussions.microsoft.com> wrote in message
news:39E62F12-F816-4A1D-A036-D59B41C07FB9@.microsoft.com...
>i have an issue where i am running out of drive space on a sql server
>cluster
> one idea that was presente was to add another disk to the cluster then add
> that disk to the list of database files for the db that is growing?
> is this a good idea?

multiple database files per database on a cluster

i have an issue where i am running out of drive space on a sql server cluster
one idea that was presente was to add another disk to the cluster then add
that disk to the list of database files for the db that is growing?
is this a good idea?
Works for me. Make sure and put the new cluster disk in the SQL resource
group AND set the SQL Server to be dependent on the new disk resource.
Otherwise, you cannot write SQL data files to the new clustered disk. You
will have to take the SQL service offline briefly to set the dependencies.
Also, disable auto-grow for the data files on the original disk.
Geoff N. Hiten
Microsoft SQL Server MVP
"jason" <jason@.discussions.microsoft.com> wrote in message
news:39E62F12-F816-4A1D-A036-D59B41C07FB9@.microsoft.com...
>i have an issue where i am running out of drive space on a sql server
>cluster
> one idea that was presente was to add another disk to the cluster then add
> that disk to the list of database files for the db that is growing?
> is this a good idea?

multiple database files per database on a cluster

i have an issue where i am running out of drive space on a sql server cluster
one idea that was presente was to add another disk to the cluster then add
that disk to the list of database files for the db that is growing?
is this a good idea?Works for me. Make sure and put the new cluster disk in the SQL resource
group AND set the SQL Server to be dependent on the new disk resource.
Otherwise, you cannot write SQL data files to the new clustered disk. You
will have to take the SQL service offline briefly to set the dependencies.
Also, disable auto-grow for the data files on the original disk.
Geoff N. Hiten
Microsoft SQL Server MVP
"jason" <jason@.discussions.microsoft.com> wrote in message
news:39E62F12-F816-4A1D-A036-D59B41C07FB9@.microsoft.com...
>i have an issue where i am running out of drive space on a sql server
>cluster
> one idea that was presente was to add another disk to the cluster then add
> that disk to the list of database files for the db that is growing?
> is this a good idea?

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