Hi all,
Our client wants some guidance on how to handle backup files created by
SQL Server 2005 for our product.
I was thinking that we would create
1) a nightly backup to say e:\backup
2) create a transaction log backup every hour to e:\backup
Now is it best to create a new file each time - or is it best to
overwrite the original file?
Reason I ask is the client will need to them back these files up to an
external tape drive.
It will probably be easier for them if they have the same 20+ files
each time (ie. one full backup, and hourly transaction files)
If the filenames incremented, then they would need to delete older
files that have been backed up (which can invite user error)
What are peoples opinions on this? Is there a "good way" to do this'
Cheers
DavidHi
Well, in our shop we do a full backup every night to c:\ and log backup
every 15 minutes to d:\
A log file may contain many files so you'll need to specify i like
drop database test
go
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'd:\db.bak' WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak'WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
...............
.............
<davconts@.gmail.com> wrote in message
news:1151992078.629506.142340@.p79g2000cwp.googlegroups.com...
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>|||This is a multi-part message in MIME format.
--070701000301070106090101
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
davconts@.gmail.com wrote:
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>
Hi David
I don't think there's a single "good way" to do it. It pretty much
depends on personal preferences and I don't think one mtethod is better
than others.
Bottomline is that no matter how you decide to do it, it will require
some degree of knowledge on how it works in order to maintain it and
restore a backup in case of a failure.
I prefer to only have one backup in each file and then name the files
with a name that are composed by date and a forth running number( for
the log files). This is because I like to know that each file only
contains one backup so when I need to restore I have to find the full
backup file and then the corresponding logfile 1, 2, 3... up to the
point in time I need. If I had more backups in the same file, I'd first
have to find out which file number I had to restore for the fulle backup
and then I'd also have to find out from which filenumber the logfile
backup would start. Another thing that I don't like about having many
backups in one file, is when the backups starts to get too big. In that
case you suddenly get some quite big backup files that can be a pain to
"administer". The backup of one of our databases are currently around 85
GB and if you have a number of these in the same file, it will easily
get quite "un-handy".
I think the biggest advantage of having several files in one backup
file, is that it's easier to let SQL server delete old backups in the
file. If you have seperate files with naming by date, you'll have to do
some scripting to delete the files.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--070701000301070106090101
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:davconts@.gmail.com">davconts@.gmail.com</a> wrote:
<blockquote
cite="mid1151992078.629506.142340@.p79g2000cwp.googlegroups.com"
type="cite">
<pre wrap="">Hi all,
Our client wants some guidance on how to handle backup files created by
SQL Server 2005 for our product.
I was thinking that we would create
1) a nightly backup to say e:\backup
2) create a transaction log backup every hour to e:\backup
Now is it best to create a new file each time - or is it best to
overwrite the original file?
Reason I ask is the client will need to them back these files up to an
external tape drive.
It will probably be easier for them if they have the same 20+ files
each time (ie. one full backup, and hourly transaction files)
If the filenames incremented, then they would need to delete older
files that have been backed up (which can invite user error)
What are peoples opinions on this? Is there a "good way" to do this'
Cheers
David
</pre>
</blockquote>
<font size="-1"><font face="Arial">Hi David<br>
<br>
I don't think there's a single "good way" to do it. It pretty much
depends on personal preferences and I don't think one mtethod is better
than others.<br>
Bottomline is that no matter how you decide to do it, it will require
some degree of knowledge on how it works in order to maintain it and
restore a backup in case of a failure.<br>
I prefer to only have one backup in each file and then name the files
with a name that are composed by date and a forth running number( for
the log files). This is because I like to know that each file only
contains one backup so when I need to restore I have to find the full
backup file and then the corresponding logfile 1, 2, 3... up to the
point in time I need. If I had more backups in the same file, I'd first
have to find out which file number I had to restore for the fulle
backup and then I'd also have to find out from which filenumber the
logfile backup would start. Another thing that I don't like about
having many backups in one file, is when the backups starts to get too
big. In that case you suddenly get some quite big backup files that can
be a pain to "administer". The backup of one of our databases are
currently around 85 GB and if you have a number of these in the same
file, it will easily get quite "un-handy".<br>
I think the biggest advantage of having several files in one backup
file, is that it's easier to let SQL server delete old backups in the
file. If you have seperate files with naming by date, you'll have to do
some scripting to delete the files.<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
<br>
</font></font>
</body>
</html>
--070701000301070106090101--|||In addition to the other posts:
I agree that there is no "best way" for this. Understand the backup command and the options (mainly
INIT and NOINIT). Then you can consider:
Maintenance plans (to do backups). It creates a new file for each time a backup is taken and also
remove files older than X days.
Do above with your own TSQL code. This opens the ability for further customization.
Have several backups on same file. For instance, have a Monday backup file which you backup to on
Mondays and overwrite each time. This will give you 7 days worth of backup generations. Or some
variation of this. One advantage is reduced number of files. Just make sure that the people doing
restore know about RESTORE HEADERONLY and FILELISTONY and the FILE option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<davconts@.gmail.com> wrote in message news:1151992078.629506.142340@.p79g2000cwp.googlegroups.com...
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>
Showing posts with label backup. Show all posts
Showing posts with label backup. Show all posts
Tuesday, March 27, 2012
Advice on how to handle backup files
Hi all,
Our client wants some guidance on how to handle backup files created by
SQL Server 2005 for our product.
I was thinking that we would create
1) a nightly backup to say e:\backup
2) create a transaction log backup every hour to e:\backup
Now is it best to create a new file each time - or is it best to
overwrite the original file?
Reason I ask is the client will need to them back these files up to an
external tape drive.
It will probably be easier for them if they have the same 20+ files
each time (ie. one full backup, and hourly transaction files)
If the filenames incremented, then they would need to delete older
files that have been backed up (which can invite user error)
What are peoples opinions on this? Is there a "good way" to do this'
Cheers
DavidHi
Well, in our shop we do a full backup every night to c:\ and log backup
every 15 minutes to d:\
A log file may contain many files so you'll need to specify i like
drop database test
go
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'd:\db.bak' WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak'WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
...............
.............
<davconts@.gmail.com> wrote in message
news:1151992078.629506.142340@.p79g2000cwp.googlegroups.com...
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>|||davconts@.gmail.com wrote:
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>
Hi David
I don't think there's a single "good way" to do it. It pretty much
depends on personal preferences and I don't think one mtethod is better
than others.
Bottomline is that no matter how you decide to do it, it will require
some degree of knowledge on how it works in order to maintain it and
restore a backup in case of a failure.
I prefer to only have one backup in each file and then name the files
with a name that are composed by date and a forth running number( for
the log files). This is because I like to know that each file only
contains one backup so when I need to restore I have to find the full
backup file and then the corresponding logfile 1, 2, 3... up to the
point in time I need. If I had more backups in the same file, I'd first
have to find out which file number I had to restore for the fulle backup
and then I'd also have to find out from which filenumber the logfile
backup would start. Another thing that I don't like about having many
backups in one file, is when the backups starts to get too big. In that
case you suddenly get some quite big backup files that can be a pain to
"administer". The backup of one of our databases are currently around 85
GB and if you have a number of these in the same file, it will easily
get quite "un-handy".
I think the biggest advantage of having several files in one backup
file, is that it's easier to let SQL server delete old backups in the
file. If you have seperate files with naming by date, you'll have to do
some scripting to delete the files.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||In addition to the other posts:
I agree that there is no "best way" for this. Understand the backup command
and the options (mainly
INIT and NOINIT). Then you can consider:
Maintenance plans (to do backups). It creates a new file for each time a bac
kup is taken and also
remove files older than X days.
Do above with your own TSQL code. This opens the ability for further customi
zation.
Have several backups on same file. For instance, have a Monday backup file w
hich you backup to on
Mondays and overwrite each time. This will give you 7 days worth of backup g
enerations. Or some
variation of this. One advantage is reduced number of files. Just make sure
that the people doing
restore know about RESTORE HEADERONLY and FILELISTONY and the FILE option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<davconts@.gmail.com> wrote in message news:1151992078.629506.142340@.p79g2000cwp.googlegroups
.com...
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>
Our client wants some guidance on how to handle backup files created by
SQL Server 2005 for our product.
I was thinking that we would create
1) a nightly backup to say e:\backup
2) create a transaction log backup every hour to e:\backup
Now is it best to create a new file each time - or is it best to
overwrite the original file?
Reason I ask is the client will need to them back these files up to an
external tape drive.
It will probably be easier for them if they have the same 20+ files
each time (ie. one full backup, and hourly transaction files)
If the filenames incremented, then they would need to delete older
files that have been backed up (which can invite user error)
What are peoples opinions on this? Is there a "good way" to do this'
Cheers
DavidHi
Well, in our shop we do a full backup every night to c:\ and log backup
every 15 minutes to d:\
A log file may contain many files so you'll need to specify i like
drop database test
go
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'd:\db.bak' WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak'WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
...............
.............
<davconts@.gmail.com> wrote in message
news:1151992078.629506.142340@.p79g2000cwp.googlegroups.com...
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>|||davconts@.gmail.com wrote:
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>
Hi David
I don't think there's a single "good way" to do it. It pretty much
depends on personal preferences and I don't think one mtethod is better
than others.
Bottomline is that no matter how you decide to do it, it will require
some degree of knowledge on how it works in order to maintain it and
restore a backup in case of a failure.
I prefer to only have one backup in each file and then name the files
with a name that are composed by date and a forth running number( for
the log files). This is because I like to know that each file only
contains one backup so when I need to restore I have to find the full
backup file and then the corresponding logfile 1, 2, 3... up to the
point in time I need. If I had more backups in the same file, I'd first
have to find out which file number I had to restore for the fulle backup
and then I'd also have to find out from which filenumber the logfile
backup would start. Another thing that I don't like about having many
backups in one file, is when the backups starts to get too big. In that
case you suddenly get some quite big backup files that can be a pain to
"administer". The backup of one of our databases are currently around 85
GB and if you have a number of these in the same file, it will easily
get quite "un-handy".
I think the biggest advantage of having several files in one backup
file, is that it's easier to let SQL server delete old backups in the
file. If you have seperate files with naming by date, you'll have to do
some scripting to delete the files.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||In addition to the other posts:
I agree that there is no "best way" for this. Understand the backup command
and the options (mainly
INIT and NOINIT). Then you can consider:
Maintenance plans (to do backups). It creates a new file for each time a bac
kup is taken and also
remove files older than X days.
Do above with your own TSQL code. This opens the ability for further customi
zation.
Have several backups on same file. For instance, have a Monday backup file w
hich you backup to on
Mondays and overwrite each time. This will give you 7 days worth of backup g
enerations. Or some
variation of this. One advantage is reduced number of files. Just make sure
that the people doing
restore know about RESTORE HEADERONLY and FILELISTONY and the FILE option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<davconts@.gmail.com> wrote in message news:1151992078.629506.142340@.p79g2000cwp.googlegroups
.com...
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>
advice on creating a backup plan
Could someone give me some feedback on the backup plan that I have created
for my companies SQL Server. First a little background.
We have 1 win2k advance server running both IIS and our SQL Server. The
database held on the server is the backend to our e-commerce site. The db
needs to be up 24-7 and typically sees the most usage from 9am - 11pm EST.
Atleast half of the tables in the db are modified through the day. There are
no large bulk insertions or deletions done through the day. The MDF and LDF
files are on different hd's.
I would like to create a plan so that in case of massive hardware failur we
could have a complete up-to-date copy of the db back up in less than 4
hours. I would like to backup up my sql databases and the Win2k system
state. The database recovery method is set to FULL.
The backup cycle starts at 6:00 am EST with a complete backup of the
database. Every 2 hours after the complete backup the transaction log is
backed up. Every 6 hours after the complete backup a Differential DB backup
is made.
At 5:00 am the next day those files are copied to another drive and the
cycle starts again with the complete db backup file overwriting itself.
This way I will always have a complete backup of the following day. I also
copy the backed up backups offline to a different server for archiving.
The system db's are backedup every week. Integrity checks are done every
week as well.
Is there anything else I should do like re create indexes as part of my
plan? How often should I back up the system state? Rigth now I back it up
once a week.
Any comments would be appreciated as preventing the loss of data is of the
utmost importance. Thanks in advance.The big thing I see regards WHERE the backups are kept...If you are leaving
them on the SQL box all day, change the plan... backup things to a different
box, and get the backups off of the SQL Server...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"news.microsoft.com" <cramsundar@.mail.com> wrote in message
news:OBt4tPpjDHA.2244@.TK2MSFTNGP12.phx.gbl...
> Could someone give me some feedback on the backup plan that I have created
> for my companies SQL Server. First a little background.
> We have 1 win2k advance server running both IIS and our SQL Server. The
> database held on the server is the backend to our e-commerce site. The db
> needs to be up 24-7 and typically sees the most usage from 9am - 11pm EST.
> Atleast half of the tables in the db are modified through the day. There
are
> no large bulk insertions or deletions done through the day. The MDF and
LDF
> files are on different hd's.
> I would like to create a plan so that in case of massive hardware failur
we
> could have a complete up-to-date copy of the db back up in less than 4
> hours. I would like to backup up my sql databases and the Win2k system
> state. The database recovery method is set to FULL.
> The backup cycle starts at 6:00 am EST with a complete backup of the
> database. Every 2 hours after the complete backup the transaction log is
> backed up. Every 6 hours after the complete backup a Differential DB
backup
> is made.
> At 5:00 am the next day those files are copied to another drive and the
> cycle starts again with the complete db backup file overwriting itself.
> This way I will always have a complete backup of the following day. I
also
> copy the backed up backups offline to a different server for archiving.
> The system db's are backedup every week. Integrity checks are done every
> week as well.
> Is there anything else I should do like re create indexes as part of my
> plan? How often should I back up the system state? Rigth now I back it
up
> once a week.
> Any comments would be appreciated as preventing the loss of data is of the
> utmost importance. Thanks in advance.
>|||We do not have another box to back up the data too. We have a very small
operation. The backups reside on two different hds and will be copied off
the server.
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:Oh4WL2pjDHA.1284@.TK2MSFTNGP09.phx.gbl...
> The big thing I see regards WHERE the backups are kept...If you are
leaving
> them on the SQL box all day, change the plan... backup things to a
different
> box, and get the backups off of the SQL Server...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "news.microsoft.com" <cramsundar@.mail.com> wrote in message
> news:OBt4tPpjDHA.2244@.TK2MSFTNGP12.phx.gbl...
> > Could someone give me some feedback on the backup plan that I have
created
> > for my companies SQL Server. First a little background.
> >
> > We have 1 win2k advance server running both IIS and our SQL Server. The
> > database held on the server is the backend to our e-commerce site. The
db
> > needs to be up 24-7 and typically sees the most usage from 9am - 11pm
EST.
> > Atleast half of the tables in the db are modified through the day. There
> are
> > no large bulk insertions or deletions done through the day. The MDF and
> LDF
> > files are on different hd's.
> >
> > I would like to create a plan so that in case of massive hardware failur
> we
> > could have a complete up-to-date copy of the db back up in less than 4
> > hours. I would like to backup up my sql databases and the Win2k system
> > state. The database recovery method is set to FULL.
> >
> > The backup cycle starts at 6:00 am EST with a complete backup of the
> > database. Every 2 hours after the complete backup the transaction log is
> > backed up. Every 6 hours after the complete backup a Differential DB
> backup
> > is made.
> >
> > At 5:00 am the next day those files are copied to another drive and the
> > cycle starts again with the complete db backup file overwriting itself.
> > This way I will always have a complete backup of the following day. I
> also
> > copy the backed up backups offline to a different server for archiving.
> >
> > The system db's are backedup every week. Integrity checks are done
every
> > week as well.
> >
> > Is there anything else I should do like re create indexes as part of my
> > plan? How often should I back up the system state? Rigth now I back it
> up
> > once a week.
> >
> > Any comments would be appreciated as preventing the loss of data is of
the
> > utmost importance. Thanks in advance.
> >
> >
>sql
for my companies SQL Server. First a little background.
We have 1 win2k advance server running both IIS and our SQL Server. The
database held on the server is the backend to our e-commerce site. The db
needs to be up 24-7 and typically sees the most usage from 9am - 11pm EST.
Atleast half of the tables in the db are modified through the day. There are
no large bulk insertions or deletions done through the day. The MDF and LDF
files are on different hd's.
I would like to create a plan so that in case of massive hardware failur we
could have a complete up-to-date copy of the db back up in less than 4
hours. I would like to backup up my sql databases and the Win2k system
state. The database recovery method is set to FULL.
The backup cycle starts at 6:00 am EST with a complete backup of the
database. Every 2 hours after the complete backup the transaction log is
backed up. Every 6 hours after the complete backup a Differential DB backup
is made.
At 5:00 am the next day those files are copied to another drive and the
cycle starts again with the complete db backup file overwriting itself.
This way I will always have a complete backup of the following day. I also
copy the backed up backups offline to a different server for archiving.
The system db's are backedup every week. Integrity checks are done every
week as well.
Is there anything else I should do like re create indexes as part of my
plan? How often should I back up the system state? Rigth now I back it up
once a week.
Any comments would be appreciated as preventing the loss of data is of the
utmost importance. Thanks in advance.The big thing I see regards WHERE the backups are kept...If you are leaving
them on the SQL box all day, change the plan... backup things to a different
box, and get the backups off of the SQL Server...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"news.microsoft.com" <cramsundar@.mail.com> wrote in message
news:OBt4tPpjDHA.2244@.TK2MSFTNGP12.phx.gbl...
> Could someone give me some feedback on the backup plan that I have created
> for my companies SQL Server. First a little background.
> We have 1 win2k advance server running both IIS and our SQL Server. The
> database held on the server is the backend to our e-commerce site. The db
> needs to be up 24-7 and typically sees the most usage from 9am - 11pm EST.
> Atleast half of the tables in the db are modified through the day. There
are
> no large bulk insertions or deletions done through the day. The MDF and
LDF
> files are on different hd's.
> I would like to create a plan so that in case of massive hardware failur
we
> could have a complete up-to-date copy of the db back up in less than 4
> hours. I would like to backup up my sql databases and the Win2k system
> state. The database recovery method is set to FULL.
> The backup cycle starts at 6:00 am EST with a complete backup of the
> database. Every 2 hours after the complete backup the transaction log is
> backed up. Every 6 hours after the complete backup a Differential DB
backup
> is made.
> At 5:00 am the next day those files are copied to another drive and the
> cycle starts again with the complete db backup file overwriting itself.
> This way I will always have a complete backup of the following day. I
also
> copy the backed up backups offline to a different server for archiving.
> The system db's are backedup every week. Integrity checks are done every
> week as well.
> Is there anything else I should do like re create indexes as part of my
> plan? How often should I back up the system state? Rigth now I back it
up
> once a week.
> Any comments would be appreciated as preventing the loss of data is of the
> utmost importance. Thanks in advance.
>|||We do not have another box to back up the data too. We have a very small
operation. The backups reside on two different hds and will be copied off
the server.
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:Oh4WL2pjDHA.1284@.TK2MSFTNGP09.phx.gbl...
> The big thing I see regards WHERE the backups are kept...If you are
leaving
> them on the SQL box all day, change the plan... backup things to a
different
> box, and get the backups off of the SQL Server...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "news.microsoft.com" <cramsundar@.mail.com> wrote in message
> news:OBt4tPpjDHA.2244@.TK2MSFTNGP12.phx.gbl...
> > Could someone give me some feedback on the backup plan that I have
created
> > for my companies SQL Server. First a little background.
> >
> > We have 1 win2k advance server running both IIS and our SQL Server. The
> > database held on the server is the backend to our e-commerce site. The
db
> > needs to be up 24-7 and typically sees the most usage from 9am - 11pm
EST.
> > Atleast half of the tables in the db are modified through the day. There
> are
> > no large bulk insertions or deletions done through the day. The MDF and
> LDF
> > files are on different hd's.
> >
> > I would like to create a plan so that in case of massive hardware failur
> we
> > could have a complete up-to-date copy of the db back up in less than 4
> > hours. I would like to backup up my sql databases and the Win2k system
> > state. The database recovery method is set to FULL.
> >
> > The backup cycle starts at 6:00 am EST with a complete backup of the
> > database. Every 2 hours after the complete backup the transaction log is
> > backed up. Every 6 hours after the complete backup a Differential DB
> backup
> > is made.
> >
> > At 5:00 am the next day those files are copied to another drive and the
> > cycle starts again with the complete db backup file overwriting itself.
> > This way I will always have a complete backup of the following day. I
> also
> > copy the backed up backups offline to a different server for archiving.
> >
> > The system db's are backedup every week. Integrity checks are done
every
> > week as well.
> >
> > Is there anything else I should do like re create indexes as part of my
> > plan? How often should I back up the system state? Rigth now I back it
> up
> > once a week.
> >
> > Any comments would be appreciated as preventing the loss of data is of
the
> > utmost importance. Thanks in advance.
> >
> >
>sql
Monday, March 19, 2012
Advantage comparison on Attach & Restore database
If I were to copy a database from one machine to another machine, I can
use:
- Backup & restore
- Detach & attach
Not to mention on those daily backup or availability of the database.
Just consider copy one database to another machine.
Does anyone know what's the advantage of each method mention above?
I used to Detach & Attach cause it's fast. Backup & restore database
would takes long time.
Other than that, anyone know is there any side effect or drawback of
the methods mentiond above?
Peter CCHHi,
Detach and attach must be faster but it require downtime; where as Backup
and restore do not need any downtime.
Thanks
Hari
SQL Server MVP
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...
> If I were to copy a database from one machine to another machine, I can
> use:
> - Backup & restore
> - Detach & attach
> Not to mention on those daily backup or availability of the database.
> Just consider copy one database to another machine.
> Does anyone know what's the advantage of each method mention above?
> I used to Detach & Attach cause it's fast. Backup & restore database
> would takes long time.
> Other than that, anyone know is there any side effect or drawback of
> the methods mentiond above?
>
> Peter CCH
>|||Other than that, nothing else?
Then it would be fine for me cause it's a developer machine, not
production server.
We always need to copy database from machine to machine.
Thanks.
Hari Prasad wrote:
> Hi,
> Detach and attach must be faster but it require downtime; where as Backup
> and restore do not need any downtime.
> Thanks
> Hari
> SQL Server MVP
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...
> > If I were to copy a database from one machine to another machine, I can
> > use:
> > - Backup & restore
> > - Detach & attach
> >
> > Not to mention on those daily backup or availability of the database.
> > Just consider copy one database to another machine.
> >
> > Does anyone know what's the advantage of each method mention above?
> > I used to Detach & Attach cause it's fast. Backup & restore database
> > would takes long time.
> >
> > Other than that, anyone know is there any side effect or drawback of
> > the methods mentiond above?
> >
> >
> >
> > Peter CCH
> >|||Backup and Restore may also 'defrag' the tables and indexes.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...
> If I were to copy a database from one machine to another machine, I can
> use:
> - Backup & restore
> - Detach & attach
> Not to mention on those daily backup or availability of the database.
> Just consider copy one database to another machine.
> Does anyone know what's the advantage of each method mention above?
> I used to Detach & Attach cause it's fast. Backup & restore database
> would takes long time.
> Other than that, anyone know is there any side effect or drawback of
> the methods mentiond above?
>
> Peter CCH
>|||> Backup and Restore may also 'defrag' the tables and indexes.
No table/index defragmentation takes place during the restore; the restored
database is exactly like the original. However, a restore can effectively
defrag database files at the file system level when the files are recreated.
Such fragmentation usually occurs only when one relies on automatic growth.
With the exception of a version upgrade, few changes are made during the
restore. The only changes that come to mind are to the file paths and
database owner.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23gDCwVMyGHA.4336@.TK2MSFTNGP06.phx.gbl...
> Backup and Restore may also 'defrag' the tables and indexes.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...
>> If I were to copy a database from one machine to another machine, I can
>> use:
>> - Backup & restore
>> - Detach & attach
>> Not to mention on those daily backup or availability of the database.
>> Just consider copy one database to another machine.
>> Does anyone know what's the advantage of each method mention above?
>> I used to Detach & Attach cause it's fast. Backup & restore database
>> would takes long time.
>> Other than that, anyone know is there any side effect or drawback of
>> the methods mentiond above?
>>
>> Peter CCH
>|||Thanks Dan,
I meant to say 'File' defrag, and my evil twin wrote in the other thought...
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OhkIAJPyGHA.4912@.TK2MSFTNGP03.phx.gbl...
>> Backup and Restore may also 'defrag' the tables and indexes.
> No table/index defragmentation takes place during the restore; the
> restored database is exactly like the original. However, a restore can
> effectively defrag database files at the file system level when the files
> are recreated. Such fragmentation usually occurs only when one relies on
> automatic growth.
> With the exception of a version upgrade, few changes are made during the
> restore. The only changes that come to mind are to the file paths and
> database owner.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23gDCwVMyGHA.4336@.TK2MSFTNGP06.phx.gbl...
>> Backup and Restore may also 'defrag' the tables and indexes.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
>> news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...
>> If I were to copy a database from one machine to another machine, I can
>> use:
>> - Backup & restore
>> - Detach & attach
>> Not to mention on those daily backup or availability of the database.
>> Just consider copy one database to another machine.
>> Does anyone know what's the advantage of each method mention above?
>> I used to Detach & Attach cause it's fast. Backup & restore database
>> would takes long time.
>> Other than that, anyone know is there any side effect or drawback of
>> the methods mentiond above?
>>
>> Peter CCH
>>
>
use:
- Backup & restore
- Detach & attach
Not to mention on those daily backup or availability of the database.
Just consider copy one database to another machine.
Does anyone know what's the advantage of each method mention above?
I used to Detach & Attach cause it's fast. Backup & restore database
would takes long time.
Other than that, anyone know is there any side effect or drawback of
the methods mentiond above?
Peter CCHHi,
Detach and attach must be faster but it require downtime; where as Backup
and restore do not need any downtime.
Thanks
Hari
SQL Server MVP
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...
> If I were to copy a database from one machine to another machine, I can
> use:
> - Backup & restore
> - Detach & attach
> Not to mention on those daily backup or availability of the database.
> Just consider copy one database to another machine.
> Does anyone know what's the advantage of each method mention above?
> I used to Detach & Attach cause it's fast. Backup & restore database
> would takes long time.
> Other than that, anyone know is there any side effect or drawback of
> the methods mentiond above?
>
> Peter CCH
>|||Other than that, nothing else?
Then it would be fine for me cause it's a developer machine, not
production server.
We always need to copy database from machine to machine.
Thanks.
Hari Prasad wrote:
> Hi,
> Detach and attach must be faster but it require downtime; where as Backup
> and restore do not need any downtime.
> Thanks
> Hari
> SQL Server MVP
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...
> > If I were to copy a database from one machine to another machine, I can
> > use:
> > - Backup & restore
> > - Detach & attach
> >
> > Not to mention on those daily backup or availability of the database.
> > Just consider copy one database to another machine.
> >
> > Does anyone know what's the advantage of each method mention above?
> > I used to Detach & Attach cause it's fast. Backup & restore database
> > would takes long time.
> >
> > Other than that, anyone know is there any side effect or drawback of
> > the methods mentiond above?
> >
> >
> >
> > Peter CCH
> >|||Backup and Restore may also 'defrag' the tables and indexes.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...
> If I were to copy a database from one machine to another machine, I can
> use:
> - Backup & restore
> - Detach & attach
> Not to mention on those daily backup or availability of the database.
> Just consider copy one database to another machine.
> Does anyone know what's the advantage of each method mention above?
> I used to Detach & Attach cause it's fast. Backup & restore database
> would takes long time.
> Other than that, anyone know is there any side effect or drawback of
> the methods mentiond above?
>
> Peter CCH
>|||> Backup and Restore may also 'defrag' the tables and indexes.
No table/index defragmentation takes place during the restore; the restored
database is exactly like the original. However, a restore can effectively
defrag database files at the file system level when the files are recreated.
Such fragmentation usually occurs only when one relies on automatic growth.
With the exception of a version upgrade, few changes are made during the
restore. The only changes that come to mind are to the file paths and
database owner.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23gDCwVMyGHA.4336@.TK2MSFTNGP06.phx.gbl...
> Backup and Restore may also 'defrag' the tables and indexes.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...
>> If I were to copy a database from one machine to another machine, I can
>> use:
>> - Backup & restore
>> - Detach & attach
>> Not to mention on those daily backup or availability of the database.
>> Just consider copy one database to another machine.
>> Does anyone know what's the advantage of each method mention above?
>> I used to Detach & Attach cause it's fast. Backup & restore database
>> would takes long time.
>> Other than that, anyone know is there any side effect or drawback of
>> the methods mentiond above?
>>
>> Peter CCH
>|||Thanks Dan,
I meant to say 'File' defrag, and my evil twin wrote in the other thought...
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OhkIAJPyGHA.4912@.TK2MSFTNGP03.phx.gbl...
>> Backup and Restore may also 'defrag' the tables and indexes.
> No table/index defragmentation takes place during the restore; the
> restored database is exactly like the original. However, a restore can
> effectively defrag database files at the file system level when the files
> are recreated. Such fragmentation usually occurs only when one relies on
> automatic growth.
> With the exception of a version upgrade, few changes are made during the
> restore. The only changes that come to mind are to the file paths and
> database owner.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23gDCwVMyGHA.4336@.TK2MSFTNGP06.phx.gbl...
>> Backup and Restore may also 'defrag' the tables and indexes.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
>> news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...
>> If I were to copy a database from one machine to another machine, I can
>> use:
>> - Backup & restore
>> - Detach & attach
>> Not to mention on those daily backup or availability of the database.
>> Just consider copy one database to another machine.
>> Does anyone know what's the advantage of each method mention above?
>> I used to Detach & Attach cause it's fast. Backup & restore database
>> would takes long time.
>> Other than that, anyone know is there any side effect or drawback of
>> the methods mentiond above?
>>
>> Peter CCH
>>
>
Advantage comparison on Attach & Restore database
If I were to copy a database from one machine to another machine, I can
use:
- Backup & restore
- Detach & attach
Not to mention on those daily backup or availability of the database.
Just consider copy one database to another machine.
Does anyone know what's the advantage of each method mention above?
I used to Detach & Attach cause it's fast. Backup & restore database
would takes long time.
Other than that, anyone know is there any side effect or drawback of
the methods mentiond above?
Peter CCHHi,
Detach and attach must be faster but it require downtime; where as Backup
and restore do not need any downtime.
Thanks
Hari
SQL Server MVP
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...
> If I were to copy a database from one machine to another machine, I can
> use:
> - Backup & restore
> - Detach & attach
> Not to mention on those daily backup or availability of the database.
> Just consider copy one database to another machine.
> Does anyone know what's the advantage of each method mention above?
> I used to Detach & Attach cause it's fast. Backup & restore database
> would takes long time.
> Other than that, anyone know is there any side effect or drawback of
> the methods mentiond above?
>
> Peter CCH
>|||Other than that, nothing else?
Then it would be fine for me cause it's a developer machine, not
production server.
We always need to copy database from machine to machine.
Thanks.
Hari Prasad wrote:[vbcol=seagreen]
> Hi,
> Detach and attach must be faster but it require downtime; where as Backup
> and restore do not need any downtime.
> Thanks
> Hari
> SQL Server MVP
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...|||Backup and Restore may also 'defrag' the tables and indexes.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...
> If I were to copy a database from one machine to another machine, I can
> use:
> - Backup & restore
> - Detach & attach
> Not to mention on those daily backup or availability of the database.
> Just consider copy one database to another machine.
> Does anyone know what's the advantage of each method mention above?
> I used to Detach & Attach cause it's fast. Backup & restore database
> would takes long time.
> Other than that, anyone know is there any side effect or drawback of
> the methods mentiond above?
>
> Peter CCH
>|||> Backup and Restore may also 'defrag' the tables and indexes.
No table/index defragmentation takes place during the restore; the restored
database is exactly like the original. However, a restore can effectively
defrag database files at the file system level when the files are recreated.
Such fragmentation usually occurs only when one relies on automatic growth.
With the exception of a version upgrade, few changes are made during the
restore. The only changes that come to mind are to the file paths and
database owner.
Hope this helps.
Dan Guzman
SQL Server MVP
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23gDCwVMyGHA.4336@.TK2MSFTNGP06.phx.gbl...
> Backup and Restore may also 'defrag' the tables and indexes.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...
>|||Thanks Dan,
I meant to say 'File' defrag, and my evil twin wrote in the other thought...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OhkIAJPyGHA.4912@.TK2MSFTNGP03.phx.gbl...
> No table/index defragmentation takes place during the restore; the
> restored database is exactly like the original. However, a restore can
> effectively defrag database files at the file system level when the files
> are recreated. Such fragmentation usually occurs only when one relies on
> automatic growth.
> With the exception of a version upgrade, few changes are made during the
> restore. The only changes that come to mind are to the file paths and
> database owner.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23gDCwVMyGHA.4336@.TK2MSFTNGP06.phx.gbl...
>
use:
- Backup & restore
- Detach & attach
Not to mention on those daily backup or availability of the database.
Just consider copy one database to another machine.
Does anyone know what's the advantage of each method mention above?
I used to Detach & Attach cause it's fast. Backup & restore database
would takes long time.
Other than that, anyone know is there any side effect or drawback of
the methods mentiond above?
Peter CCHHi,
Detach and attach must be faster but it require downtime; where as Backup
and restore do not need any downtime.
Thanks
Hari
SQL Server MVP
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...
> If I were to copy a database from one machine to another machine, I can
> use:
> - Backup & restore
> - Detach & attach
> Not to mention on those daily backup or availability of the database.
> Just consider copy one database to another machine.
> Does anyone know what's the advantage of each method mention above?
> I used to Detach & Attach cause it's fast. Backup & restore database
> would takes long time.
> Other than that, anyone know is there any side effect or drawback of
> the methods mentiond above?
>
> Peter CCH
>|||Other than that, nothing else?
Then it would be fine for me cause it's a developer machine, not
production server.
We always need to copy database from machine to machine.
Thanks.
Hari Prasad wrote:[vbcol=seagreen]
> Hi,
> Detach and attach must be faster but it require downtime; where as Backup
> and restore do not need any downtime.
> Thanks
> Hari
> SQL Server MVP
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...|||Backup and Restore may also 'defrag' the tables and indexes.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...
> If I were to copy a database from one machine to another machine, I can
> use:
> - Backup & restore
> - Detach & attach
> Not to mention on those daily backup or availability of the database.
> Just consider copy one database to another machine.
> Does anyone know what's the advantage of each method mention above?
> I used to Detach & Attach cause it's fast. Backup & restore database
> would takes long time.
> Other than that, anyone know is there any side effect or drawback of
> the methods mentiond above?
>
> Peter CCH
>|||> Backup and Restore may also 'defrag' the tables and indexes.
No table/index defragmentation takes place during the restore; the restored
database is exactly like the original. However, a restore can effectively
defrag database files at the file system level when the files are recreated.
Such fragmentation usually occurs only when one relies on automatic growth.
With the exception of a version upgrade, few changes are made during the
restore. The only changes that come to mind are to the file paths and
database owner.
Hope this helps.
Dan Guzman
SQL Server MVP
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23gDCwVMyGHA.4336@.TK2MSFTNGP06.phx.gbl...
> Backup and Restore may also 'defrag' the tables and indexes.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...
>|||Thanks Dan,
I meant to say 'File' defrag, and my evil twin wrote in the other thought...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OhkIAJPyGHA.4912@.TK2MSFTNGP03.phx.gbl...
> No table/index defragmentation takes place during the restore; the
> restored database is exactly like the original. However, a restore can
> effectively defrag database files at the file system level when the files
> are recreated. Such fragmentation usually occurs only when one relies on
> automatic growth.
> With the exception of a version upgrade, few changes are made during the
> restore. The only changes that come to mind are to the file paths and
> database owner.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23gDCwVMyGHA.4336@.TK2MSFTNGP06.phx.gbl...
>
Advanced T-SQL Question: Producing Backup Window Timeline Chart
I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
would accomplish this task in Oracle but I have no clue how to do it in
T-SQL. In short, I want to produce a comma delimited gantt type
timeline chart of my Backup Windows for import into Excel.
I'm not sure if this is the best place to get this information but I
found the msdb sysjobservers table which contains information about the
last run and duration of a job. It might be nice to get historical
information but I'm not sure where I'd find it. If there is a better
place to get this information then I'd like to know.
Here is the data I'm looking at...
use msdb
go
select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
s.last_run_duration
from sysjobs j, sysjobservers s
where j.job_id = s.job_id
go
name last_run_outcome last_run_date last_run_time
last_run_duration
-- -- -- --
--
DB BACKUP1 1 20060406 200000 1958
DB BACKUP2 1 20060401 10000 5943
DB BACKUP3 1 20060402 30000 31934
(3 row(s) affected)
If I understand this correctly, the data shows backup1 last ran at
20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
to kick out a comma delimited timeline showing the duration of the
backup. The first field is of course the name. The remaining 24
fields corrospond to the hours of the day 0 through 23.
So using the above data my output would look like this...
DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
BACKUP1 ran at 20:00 for less than an hour so there is one X in the
field corrosponding to the 20:00 hour showing the timeblock where the
backup ran.
BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
where the backup ran.
BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
fields corrosponding to the 3:00 through 11:00 hours showing the
timeblock where the backup ran.
I would greatly appreciate it if someone could help me with the T-SQL
to produce such a report.
Thanks in advance,
Roger WestbrookThis conversation is ongoing in the .programming group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Kael_Sidhe@.yahoo.com> wrote in message news:1144417913.196803.81210@.t31g2000cwb.googlegroup
s.com...
> I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
> would accomplish this task in Oracle but I have no clue how to do it in
> T-SQL. In short, I want to produce a comma delimited gantt type
> timeline chart of my Backup Windows for import into Excel.
> I'm not sure if this is the best place to get this information but I
> found the msdb sysjobservers table which contains information about the
> last run and duration of a job. It might be nice to get historical
> information but I'm not sure where I'd find it. If there is a better
> place to get this information then I'd like to know.
> Here is the data I'm looking at...
> use msdb
> go
> select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
> s.last_run_duration
> from sysjobs j, sysjobservers s
> where j.job_id = s.job_id
> go
> name last_run_outcome last_run_date last_run_time
> last_run_duration
> -- -- -- --
> --
> DB BACKUP1 1 20060406 200000 1958
> DB BACKUP2 1 20060401 10000 5943
> DB BACKUP3 1 20060402 30000 31934
> (3 row(s) affected)
> If I understand this correctly, the data shows backup1 last ran at
> 20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
> to kick out a comma delimited timeline showing the duration of the
> backup. The first field is of course the name. The remaining 24
> fields corrospond to the hours of the day 0 through 23.
> So using the above data my output would look like this...
> DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
> DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
> DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
> BACKUP1 ran at 20:00 for less than an hour so there is one X in the
> field corrosponding to the 20:00 hour showing the timeblock where the
> backup ran.
> BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
> fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
> where the backup ran.
> BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
> fields corrosponding to the 3:00 through 11:00 hours showing the
> timeblock where the backup ran.
>
> I would greatly appreciate it if someone could help me with the T-SQL
> to produce such a report.
> Thanks in advance,
> Roger Westbrook
>
would accomplish this task in Oracle but I have no clue how to do it in
T-SQL. In short, I want to produce a comma delimited gantt type
timeline chart of my Backup Windows for import into Excel.
I'm not sure if this is the best place to get this information but I
found the msdb sysjobservers table which contains information about the
last run and duration of a job. It might be nice to get historical
information but I'm not sure where I'd find it. If there is a better
place to get this information then I'd like to know.
Here is the data I'm looking at...
use msdb
go
select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
s.last_run_duration
from sysjobs j, sysjobservers s
where j.job_id = s.job_id
go
name last_run_outcome last_run_date last_run_time
last_run_duration
-- -- -- --
--
DB BACKUP1 1 20060406 200000 1958
DB BACKUP2 1 20060401 10000 5943
DB BACKUP3 1 20060402 30000 31934
(3 row(s) affected)
If I understand this correctly, the data shows backup1 last ran at
20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
to kick out a comma delimited timeline showing the duration of the
backup. The first field is of course the name. The remaining 24
fields corrospond to the hours of the day 0 through 23.
So using the above data my output would look like this...
DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
BACKUP1 ran at 20:00 for less than an hour so there is one X in the
field corrosponding to the 20:00 hour showing the timeblock where the
backup ran.
BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
where the backup ran.
BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
fields corrosponding to the 3:00 through 11:00 hours showing the
timeblock where the backup ran.
I would greatly appreciate it if someone could help me with the T-SQL
to produce such a report.
Thanks in advance,
Roger WestbrookThis conversation is ongoing in the .programming group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Kael_Sidhe@.yahoo.com> wrote in message news:1144417913.196803.81210@.t31g2000cwb.googlegroup
s.com...
> I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
> would accomplish this task in Oracle but I have no clue how to do it in
> T-SQL. In short, I want to produce a comma delimited gantt type
> timeline chart of my Backup Windows for import into Excel.
> I'm not sure if this is the best place to get this information but I
> found the msdb sysjobservers table which contains information about the
> last run and duration of a job. It might be nice to get historical
> information but I'm not sure where I'd find it. If there is a better
> place to get this information then I'd like to know.
> Here is the data I'm looking at...
> use msdb
> go
> select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
> s.last_run_duration
> from sysjobs j, sysjobservers s
> where j.job_id = s.job_id
> go
> name last_run_outcome last_run_date last_run_time
> last_run_duration
> -- -- -- --
> --
> DB BACKUP1 1 20060406 200000 1958
> DB BACKUP2 1 20060401 10000 5943
> DB BACKUP3 1 20060402 30000 31934
> (3 row(s) affected)
> If I understand this correctly, the data shows backup1 last ran at
> 20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
> to kick out a comma delimited timeline showing the duration of the
> backup. The first field is of course the name. The remaining 24
> fields corrospond to the hours of the day 0 through 23.
> So using the above data my output would look like this...
> DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
> DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
> DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
> BACKUP1 ran at 20:00 for less than an hour so there is one X in the
> field corrosponding to the 20:00 hour showing the timeblock where the
> backup ran.
> BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
> fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
> where the backup ran.
> BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
> fields corrosponding to the 3:00 through 11:00 hours showing the
> timeblock where the backup ran.
>
> I would greatly appreciate it if someone could help me with the T-SQL
> to produce such a report.
> Thanks in advance,
> Roger Westbrook
>
Advanced T-SQL Question: Producing Backup Window Timeline Chart
I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
would accomplish this task in Oracle but I have no clue how to do it in
T-SQL. In short, I want to produce a comma delimited gantt type
timeline chart of my Backup Windows for import into Excel.
I'm not sure if this is the best place to get this information but I
found the msdb sysjobservers table which contains information about the
last run and duration of a job. It might be nice to get historical
information but I'm not sure where I'd find it. If there is a better
place to get this information then I'd like to know.
Here is the data I'm looking at...
use msdb
go
select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
s.last_run_duration
from sysjobs j, sysjobservers s
where j.job_id = s.job_id
go
name last_run_outcome last_run_date last_run_time
last_run_duration
-- -- -- --
--
DB BACKUP1 1 20060406 200000 1958
DB BACKUP2 1 20060401 10000 5943
DB BACKUP3 1 20060402 30000 31934
(3 row(s) affected)
If I understand this correctly, the data shows backup1 last ran at
20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
to kick out a comma delimited timeline showing the duration of the
backup. The first field is of course the name. The remaining 24
fields corrospond to the hours of the day 0 through 23.
So using the above data my output would look like this...
DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
BACKUP1 ran at 20:00 for less than an hour so there is one X in the
field corrosponding to the 20:00 hour showing the timeblock where the
backup ran.
BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
where the backup ran.
BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
fields corrosponding to the 3:00 through 11:00 hours showing the
timeblock where the backup ran.
I would greatly appreciate it if someone could help me with the T-SQL
to produce such a report.
Thanks in advance,
Roger WestbrookThis conversation is ongoing in the .programming group.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Kael_Sidhe@.yahoo.com> wrote in message news:1144417913.196803.81210@.t31g2000cwb.googlegroups.com...
> I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
> would accomplish this task in Oracle but I have no clue how to do it in
> T-SQL. In short, I want to produce a comma delimited gantt type
> timeline chart of my Backup Windows for import into Excel.
> I'm not sure if this is the best place to get this information but I
> found the msdb sysjobservers table which contains information about the
> last run and duration of a job. It might be nice to get historical
> information but I'm not sure where I'd find it. If there is a better
> place to get this information then I'd like to know.
> Here is the data I'm looking at...
> use msdb
> go
> select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
> s.last_run_duration
> from sysjobs j, sysjobservers s
> where j.job_id = s.job_id
> go
> name last_run_outcome last_run_date last_run_time
> last_run_duration
> -- -- -- --
> --
> DB BACKUP1 1 20060406 200000 1958
> DB BACKUP2 1 20060401 10000 5943
> DB BACKUP3 1 20060402 30000 31934
> (3 row(s) affected)
> If I understand this correctly, the data shows backup1 last ran at
> 20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
> to kick out a comma delimited timeline showing the duration of the
> backup. The first field is of course the name. The remaining 24
> fields corrospond to the hours of the day 0 through 23.
> So using the above data my output would look like this...
> DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
> DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
> DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
> BACKUP1 ran at 20:00 for less than an hour so there is one X in the
> field corrosponding to the 20:00 hour showing the timeblock where the
> backup ran.
> BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
> fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
> where the backup ran.
> BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
> fields corrosponding to the 3:00 through 11:00 hours showing the
> timeblock where the backup ran.
>
> I would greatly appreciate it if someone could help me with the T-SQL
> to produce such a report.
> Thanks in advance,
> Roger Westbrook
>
would accomplish this task in Oracle but I have no clue how to do it in
T-SQL. In short, I want to produce a comma delimited gantt type
timeline chart of my Backup Windows for import into Excel.
I'm not sure if this is the best place to get this information but I
found the msdb sysjobservers table which contains information about the
last run and duration of a job. It might be nice to get historical
information but I'm not sure where I'd find it. If there is a better
place to get this information then I'd like to know.
Here is the data I'm looking at...
use msdb
go
select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
s.last_run_duration
from sysjobs j, sysjobservers s
where j.job_id = s.job_id
go
name last_run_outcome last_run_date last_run_time
last_run_duration
-- -- -- --
--
DB BACKUP1 1 20060406 200000 1958
DB BACKUP2 1 20060401 10000 5943
DB BACKUP3 1 20060402 30000 31934
(3 row(s) affected)
If I understand this correctly, the data shows backup1 last ran at
20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
to kick out a comma delimited timeline showing the duration of the
backup. The first field is of course the name. The remaining 24
fields corrospond to the hours of the day 0 through 23.
So using the above data my output would look like this...
DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
BACKUP1 ran at 20:00 for less than an hour so there is one X in the
field corrosponding to the 20:00 hour showing the timeblock where the
backup ran.
BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
where the backup ran.
BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
fields corrosponding to the 3:00 through 11:00 hours showing the
timeblock where the backup ran.
I would greatly appreciate it if someone could help me with the T-SQL
to produce such a report.
Thanks in advance,
Roger WestbrookThis conversation is ongoing in the .programming group.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Kael_Sidhe@.yahoo.com> wrote in message news:1144417913.196803.81210@.t31g2000cwb.googlegroups.com...
> I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
> would accomplish this task in Oracle but I have no clue how to do it in
> T-SQL. In short, I want to produce a comma delimited gantt type
> timeline chart of my Backup Windows for import into Excel.
> I'm not sure if this is the best place to get this information but I
> found the msdb sysjobservers table which contains information about the
> last run and duration of a job. It might be nice to get historical
> information but I'm not sure where I'd find it. If there is a better
> place to get this information then I'd like to know.
> Here is the data I'm looking at...
> use msdb
> go
> select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
> s.last_run_duration
> from sysjobs j, sysjobservers s
> where j.job_id = s.job_id
> go
> name last_run_outcome last_run_date last_run_time
> last_run_duration
> -- -- -- --
> --
> DB BACKUP1 1 20060406 200000 1958
> DB BACKUP2 1 20060401 10000 5943
> DB BACKUP3 1 20060402 30000 31934
> (3 row(s) affected)
> If I understand this correctly, the data shows backup1 last ran at
> 20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
> to kick out a comma delimited timeline showing the duration of the
> backup. The first field is of course the name. The remaining 24
> fields corrospond to the hours of the day 0 through 23.
> So using the above data my output would look like this...
> DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
> DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
> DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
> BACKUP1 ran at 20:00 for less than an hour so there is one X in the
> field corrosponding to the 20:00 hour showing the timeblock where the
> backup ran.
> BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
> fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
> where the backup ran.
> BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
> fields corrosponding to the 3:00 through 11:00 hours showing the
> timeblock where the backup ran.
>
> I would greatly appreciate it if someone could help me with the T-SQL
> to produce such a report.
> Thanks in advance,
> Roger Westbrook
>
Advanced T-SQL Question: Produce Backup Window Gantt Chart
I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
would accomplish this task in Oracle but I have no clue how to do it in
T-SQL. In short, I want to produce a comma delimited gantt type
timeline chart of my Backup Windows for import into Excel.
I'm not sure if this is the best place to get this information but I
found the msdb sysjobservers table which contains information about the
last run and duration of a job. It might be nice to get historical
information but I'm not sure where I'd find it. If there is a better
place to get this information then I'd like to know.
Here is the data I'm looking at...
use msdb
go
select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
s.last_run_duration
from sysjobs j, sysjobservers s
where j.job_id = s.job_id
go
name last_run_outcome last_run_date last_run_time
last_run_duration
-- -- -- --
--
DB BACKUP1 1 20060406 200000 1958
DB BACKUP2 1 20060401 10000 5943
DB BACKUP3 1 20060402 30000 31934
(3 row(s) affected)
If I understand this correctly, the data shows backup1 last ran at
20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
to kick out a comma delimited timeline showing the duration of the
backup. The first field is of course the name. The remaining 24
fields corrospond to the hours of the day 0 through 23.
So using the above data my output would look like this...
DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
BACKUP1 ran at 20:00 for less than an hour so there is one X in the
field corrosponding to the 20:00 hour showing the timeblock where the
backup ran.
BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
where the backup ran.
BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
fields corrosponding to the 3:00 through 11:00 hours showing the
timeblock where the backup ran.
I would greatly appreciate it if someone could help me with the T-SQL
to produce such a report.
Thanks in advance,
Roger WestbrookI'm not familiar with these tables but this should give you
the output you want
SELECT j.name +
CASE WHEN 0 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 1 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 2 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 3 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 4 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 5 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 6 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 7 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 8 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 9 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 10 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 11 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 12 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 13 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 14 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 15 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 16 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 17 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 18 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 19 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 20 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 21 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 22 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 23 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END
FROM sysjobs j
INNER JOIN (SELECT job_id,
DATEPART(hour,DATEADD(second,
last_run_time % 100 +
((last_run_time/100) % 100)*60 +
(last_run_time / 10000)*3600,
'19000101')) AS JobStart,
DATEPART(hour,DATEADD(second,
last_run_duration +
last_run_time % 100 +
((last_run_time/100) % 100)*60 +
(last_run_time / 10000)*3600,
'19000101')) AS JobEnd,
last_run_duration
FROM sysjobservers) s ON j.job_id = s.job_id|||> It might be nice to get historical
> information but I'm not sure where I'd find it.
You have 5 tables in msdb with historical information about backup. The name
of the tables all start
with backup. For instance, check out msdb.dbo.backupset.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Kael_Sidhe@.yahoo.com> wrote in message
news:1144417911.795292.287140@.z34g2000cwc.googlegroups.com...
> I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
> would accomplish this task in Oracle but I have no clue how to do it in
> T-SQL. In short, I want to produce a comma delimited gantt type
> timeline chart of my Backup Windows for import into Excel.
> I'm not sure if this is the best place to get this information but I
> found the msdb sysjobservers table which contains information about the
> last run and duration of a job. It might be nice to get historical
> information but I'm not sure where I'd find it. If there is a better
> place to get this information then I'd like to know.
> Here is the data I'm looking at...
> use msdb
> go
> select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
> s.last_run_duration
> from sysjobs j, sysjobservers s
> where j.job_id = s.job_id
> go
> name last_run_outcome last_run_date last_run_time
> last_run_duration
> -- -- -- --
> --
> DB BACKUP1 1 20060406 200000 1958
> DB BACKUP2 1 20060401 10000 5943
> DB BACKUP3 1 20060402 30000 31934
> (3 row(s) affected)
> If I understand this correctly, the data shows backup1 last ran at
> 20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
> to kick out a comma delimited timeline showing the duration of the
> backup. The first field is of course the name. The remaining 24
> fields corrospond to the hours of the day 0 through 23.
> So using the above data my output would look like this...
> DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
> DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
> DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
> BACKUP1 ran at 20:00 for less than an hour so there is one X in the
> field corrosponding to the 20:00 hour showing the timeblock where the
> backup ran.
> BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
> fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
> where the backup ran.
> BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
> fields corrosponding to the 3:00 through 11:00 hours showing the
> timeblock where the backup ran.
>
> I would greatly appreciate it if someone could help me with the T-SQL
> to produce such a report.
> Thanks in advance,
> Roger Westbrook
>|||Corrected (and simplified) version that handles jobs spanning midnight
SELECT j.name +
CASE WHEN 0 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 1 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 2 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 3 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 4 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 5 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 6 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 7 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 8 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 9 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 10 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 11 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 12 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 13 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 14 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 15 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 16 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 17 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 18 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 19 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 20 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 21 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 22 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 23 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END
FROM sysjobs j
INNER JOIN (SELECT job_id,
last_run_time / 10000 AS JobStart,
(last_run_duration +
last_run_time % 100 +
((last_run_time/100) % 100)*60 +
(last_run_time / 10000)*3600) / 3600 AS JobEnd
FROM sysjobservers) s ON j.job_id = s.job_id|||Roger,
yet another approach:
create function backup_mask(@.from_hour int, @.to_hour int)
returns varchar(26)
as
begin
declare @.idle_mask varchar(26)
declare @.busy_mask varchar(24)
set @.idle_mask = '('+replicate('_',24)+')'
set @.busy_mask = replicate('X',24)
return(
substring(@.idle_mask, 1, @.from_hour)+
substring(@.busy_mask, 1, @.to_hour - @.from_hour+1)+
substring(@.idle_mask, 2 + @.to_hour,25-@.to_hour))
end
go
-- unit test --
select dbo.backup_mask(1,5)
select dbo.backup_mask(3,15)
select dbo.backup_mask(1,24)
select dbo.backup_mask(19,24)
(XXXXX___________________)
(1 row(s) affected)
(__XXXXXXXXXXXXX_________)
(1 row(s) affected)
(XXXXXXXXXXXXXXXXXXXXXXXX)
(1 row(s) affected)
(__________________XXXXXX)
(1 row(s) affected)
go
drop function backup_mask
go|||And another, using Alexander's function as a start,
just because we have QUOTENAME and STUFF.
(Note that the behavior of QUOTENAME when the
second parameter is '(' is not documented.)
create function backup_mask(@.from_hour int, @.to_hour int)
returns varchar(26)
as
begin
declare @.idle_mask varchar(26)
declare @.busy_mask varchar(24)
set @.idle_mask = replicate('_',24)
set @.busy_mask = replicate('X',24)
declare @.duration int
set @.duration = @.to_hour - @.from_hour + 1
if @.duration <= 0
return quotename(@.idle_mask,'(')
return
quotename(stuff(@.idle_mask,@.from_hour,@.d
uration,left(@.busy_mask,@.duration)),
'(')
end
Alexander Kuznetsov wrote:
>Roger,
>yet another approach:
>create function backup_mask(@.from_hour int, @.to_hour int)
>returns varchar(26)
>as
>begin
>declare @.idle_mask varchar(26)
>declare @.busy_mask varchar(24)
>set @.idle_mask = '('+replicate('_',24)+')'
>set @.busy_mask = replicate('X',24)
>return(
>substring(@.idle_mask, 1, @.from_hour)+
>substring(@.busy_mask, 1, @.to_hour - @.from_hour+1)+
>substring(@.idle_mask, 2 + @.to_hour,25-@.to_hour))
>end
>go
>-- unit test --
>select dbo.backup_mask(1,5)
>select dbo.backup_mask(3,15)
>select dbo.backup_mask(1,24)
>select dbo.backup_mask(19,24)
>
>--
>(XXXXX___________________)
>(1 row(s) affected)
>
>--
>(__XXXXXXXXXXXXX_________)
>(1 row(s) affected)
>
>--
>(XXXXXXXXXXXXXXXXXXXXXXXX)
>(1 row(s) affected)
>
>--
>(__________________XXXXXX)
>(1 row(s) affected)
>
>go
>drop function backup_mask
>go
>
>|||Thanks folks, I may have some follow up questions but for now I'm going
to digest the information you've sent me and see what I can come up
with. Thanks again for the assist!
would accomplish this task in Oracle but I have no clue how to do it in
T-SQL. In short, I want to produce a comma delimited gantt type
timeline chart of my Backup Windows for import into Excel.
I'm not sure if this is the best place to get this information but I
found the msdb sysjobservers table which contains information about the
last run and duration of a job. It might be nice to get historical
information but I'm not sure where I'd find it. If there is a better
place to get this information then I'd like to know.
Here is the data I'm looking at...
use msdb
go
select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
s.last_run_duration
from sysjobs j, sysjobservers s
where j.job_id = s.job_id
go
name last_run_outcome last_run_date last_run_time
last_run_duration
-- -- -- --
--
DB BACKUP1 1 20060406 200000 1958
DB BACKUP2 1 20060401 10000 5943
DB BACKUP3 1 20060402 30000 31934
(3 row(s) affected)
If I understand this correctly, the data shows backup1 last ran at
20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
to kick out a comma delimited timeline showing the duration of the
backup. The first field is of course the name. The remaining 24
fields corrospond to the hours of the day 0 through 23.
So using the above data my output would look like this...
DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
BACKUP1 ran at 20:00 for less than an hour so there is one X in the
field corrosponding to the 20:00 hour showing the timeblock where the
backup ran.
BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
where the backup ran.
BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
fields corrosponding to the 3:00 through 11:00 hours showing the
timeblock where the backup ran.
I would greatly appreciate it if someone could help me with the T-SQL
to produce such a report.
Thanks in advance,
Roger WestbrookI'm not familiar with these tables but this should give you
the output you want
SELECT j.name +
CASE WHEN 0 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 1 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 2 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 3 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 4 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 5 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 6 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 7 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 8 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 9 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 10 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 11 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 12 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 13 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 14 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 15 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 16 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 17 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 18 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 19 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 20 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 21 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 22 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 23 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END
FROM sysjobs j
INNER JOIN (SELECT job_id,
DATEPART(hour,DATEADD(second,
last_run_time % 100 +
((last_run_time/100) % 100)*60 +
(last_run_time / 10000)*3600,
'19000101')) AS JobStart,
DATEPART(hour,DATEADD(second,
last_run_duration +
last_run_time % 100 +
((last_run_time/100) % 100)*60 +
(last_run_time / 10000)*3600,
'19000101')) AS JobEnd,
last_run_duration
FROM sysjobservers) s ON j.job_id = s.job_id|||> It might be nice to get historical
> information but I'm not sure where I'd find it.
You have 5 tables in msdb with historical information about backup. The name
of the tables all start
with backup. For instance, check out msdb.dbo.backupset.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Kael_Sidhe@.yahoo.com> wrote in message
news:1144417911.795292.287140@.z34g2000cwc.googlegroups.com...
> I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
> would accomplish this task in Oracle but I have no clue how to do it in
> T-SQL. In short, I want to produce a comma delimited gantt type
> timeline chart of my Backup Windows for import into Excel.
> I'm not sure if this is the best place to get this information but I
> found the msdb sysjobservers table which contains information about the
> last run and duration of a job. It might be nice to get historical
> information but I'm not sure where I'd find it. If there is a better
> place to get this information then I'd like to know.
> Here is the data I'm looking at...
> use msdb
> go
> select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
> s.last_run_duration
> from sysjobs j, sysjobservers s
> where j.job_id = s.job_id
> go
> name last_run_outcome last_run_date last_run_time
> last_run_duration
> -- -- -- --
> --
> DB BACKUP1 1 20060406 200000 1958
> DB BACKUP2 1 20060401 10000 5943
> DB BACKUP3 1 20060402 30000 31934
> (3 row(s) affected)
> If I understand this correctly, the data shows backup1 last ran at
> 20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
> to kick out a comma delimited timeline showing the duration of the
> backup. The first field is of course the name. The remaining 24
> fields corrospond to the hours of the day 0 through 23.
> So using the above data my output would look like this...
> DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
> DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
> DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
> BACKUP1 ran at 20:00 for less than an hour so there is one X in the
> field corrosponding to the 20:00 hour showing the timeblock where the
> backup ran.
> BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
> fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
> where the backup ran.
> BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
> fields corrosponding to the 3:00 through 11:00 hours showing the
> timeblock where the backup ran.
>
> I would greatly appreciate it if someone could help me with the T-SQL
> to produce such a report.
> Thanks in advance,
> Roger Westbrook
>|||Corrected (and simplified) version that handles jobs spanning midnight
SELECT j.name +
CASE WHEN 0 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 1 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 2 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 3 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 4 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 5 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 6 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 7 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 8 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 9 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 10 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 11 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 12 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 13 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 14 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 15 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 16 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 17 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 18 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 19 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 20 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 21 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 22 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 23 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END
FROM sysjobs j
INNER JOIN (SELECT job_id,
last_run_time / 10000 AS JobStart,
(last_run_duration +
last_run_time % 100 +
((last_run_time/100) % 100)*60 +
(last_run_time / 10000)*3600) / 3600 AS JobEnd
FROM sysjobservers) s ON j.job_id = s.job_id|||Roger,
yet another approach:
create function backup_mask(@.from_hour int, @.to_hour int)
returns varchar(26)
as
begin
declare @.idle_mask varchar(26)
declare @.busy_mask varchar(24)
set @.idle_mask = '('+replicate('_',24)+')'
set @.busy_mask = replicate('X',24)
return(
substring(@.idle_mask, 1, @.from_hour)+
substring(@.busy_mask, 1, @.to_hour - @.from_hour+1)+
substring(@.idle_mask, 2 + @.to_hour,25-@.to_hour))
end
go
-- unit test --
select dbo.backup_mask(1,5)
select dbo.backup_mask(3,15)
select dbo.backup_mask(1,24)
select dbo.backup_mask(19,24)
(XXXXX___________________)
(1 row(s) affected)
(__XXXXXXXXXXXXX_________)
(1 row(s) affected)
(XXXXXXXXXXXXXXXXXXXXXXXX)
(1 row(s) affected)
(__________________XXXXXX)
(1 row(s) affected)
go
drop function backup_mask
go|||And another, using Alexander's function as a start,
just because we have QUOTENAME and STUFF.
(Note that the behavior of QUOTENAME when the
second parameter is '(' is not documented.)
create function backup_mask(@.from_hour int, @.to_hour int)
returns varchar(26)
as
begin
declare @.idle_mask varchar(26)
declare @.busy_mask varchar(24)
set @.idle_mask = replicate('_',24)
set @.busy_mask = replicate('X',24)
declare @.duration int
set @.duration = @.to_hour - @.from_hour + 1
if @.duration <= 0
return quotename(@.idle_mask,'(')
return
quotename(stuff(@.idle_mask,@.from_hour,@.d
uration,left(@.busy_mask,@.duration)),
'(')
end
Alexander Kuznetsov wrote:
>Roger,
>yet another approach:
>create function backup_mask(@.from_hour int, @.to_hour int)
>returns varchar(26)
>as
>begin
>declare @.idle_mask varchar(26)
>declare @.busy_mask varchar(24)
>set @.idle_mask = '('+replicate('_',24)+')'
>set @.busy_mask = replicate('X',24)
>return(
>substring(@.idle_mask, 1, @.from_hour)+
>substring(@.busy_mask, 1, @.to_hour - @.from_hour+1)+
>substring(@.idle_mask, 2 + @.to_hour,25-@.to_hour))
>end
>go
>-- unit test --
>select dbo.backup_mask(1,5)
>select dbo.backup_mask(3,15)
>select dbo.backup_mask(1,24)
>select dbo.backup_mask(19,24)
>
>--
>(XXXXX___________________)
>(1 row(s) affected)
>
>--
>(__XXXXXXXXXXXXX_________)
>(1 row(s) affected)
>
>--
>(XXXXXXXXXXXXXXXXXXXXXXXX)
>(1 row(s) affected)
>
>--
>(__________________XXXXXX)
>(1 row(s) affected)
>
>go
>drop function backup_mask
>go
>
>|||Thanks folks, I may have some follow up questions but for now I'm going
to digest the information you've sent me and see what I can come up
with. Thanks again for the assist!
Sunday, February 12, 2012
Administrator Password changed causes backup failure
We changed our administrator password on all of our servers. We were backin
g
up our SQL databases to another server
(ex: \\anotherserver\dropbox\databackup) However, we can no longer backup
to any other location other than a local drive on our SQL 2000 server.
If we try to create a backup to another location, we get a message that
reads something like: SQL can not verify the location of the file. Do you
want to use this location anyway?
When we try to backup to the location, the we get the error message that
reads: file or folder not on-line.
If we change the network adminstrator password back to it's original, we can
once again backup.
Thank you,
Ron Dittmann
--
Ron DittmannHere is the error code I found in the SqlServerAgent log:
[298] SQLServer Error: 8198, Could not obtain information about Windows
NT
group/user 'domain\username'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
Any info on this issue would be helpful. We are using Service Pack 3 one
SQL 2000 on Windows 2000 Server with ALL service packs and Windows updates
installed. The password we changed was the Administrator password for the
network, not the "sa" password. We even tried to reenter the Server Agent
password with no luck.
Thank you
Ron Dittmann
"dpolara" wrote:
> We changed our administrator password on all of our servers. We were back
ing
> up our SQL databases to another server
> (ex: \\anotherserver\dropbox\databackup) However, we can no longer backup
> to any other location other than a local drive on our SQL 2000 server.
> If we try to create a backup to another location, we get a message that
> reads something like: SQL can not verify the location of the file. Do you
> want to use this location anyway?
> When we try to backup to the location, the we get the error message that
> reads: file or folder not on-line.
> If we change the network adminstrator password back to it's original, we c
an
> once again backup.
> Thank you,
> Ron Dittmann
> --
> Ron Dittmann|||Check the following troubleshooting article:
How to troubleshoot a SQL Server 8198 error
http://support.microsoft.com/?id=834124
-Sue
On Wed, 13 Oct 2004 20:39:07 -0700, "dpolara"
<dpolara@.discussions.microsoft.com> wrote:
>We changed our administrator password on all of our servers. We were backi
ng
>up our SQL databases to another server
>(ex: \\anotherserver\dropbox\databackup) However, we can no longer backup
>to any other location other than a local drive on our SQL 2000 server.
>If we try to create a backup to another location, we get a message that
>reads something like: SQL can not verify the location of the file. Do you
>want to use this location anyway?
>When we try to backup to the location, the we get the error message that
>reads: file or folder not on-line.
>If we change the network adminstrator password back to it's original, we ca
n
>once again backup.
>Thank you,
>Ron Dittmann
g
up our SQL databases to another server
(ex: \\anotherserver\dropbox\databackup) However, we can no longer backup
to any other location other than a local drive on our SQL 2000 server.
If we try to create a backup to another location, we get a message that
reads something like: SQL can not verify the location of the file. Do you
want to use this location anyway?
When we try to backup to the location, the we get the error message that
reads: file or folder not on-line.
If we change the network adminstrator password back to it's original, we can
once again backup.
Thank you,
Ron Dittmann
--
Ron DittmannHere is the error code I found in the SqlServerAgent log:
[298] SQLServer Error: 8198, Could not obtain information about Windows
NT
group/user 'domain\username'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
Any info on this issue would be helpful. We are using Service Pack 3 one
SQL 2000 on Windows 2000 Server with ALL service packs and Windows updates
installed. The password we changed was the Administrator password for the
network, not the "sa" password. We even tried to reenter the Server Agent
password with no luck.
Thank you
Ron Dittmann
"dpolara" wrote:
> We changed our administrator password on all of our servers. We were back
ing
> up our SQL databases to another server
> (ex: \\anotherserver\dropbox\databackup) However, we can no longer backup
> to any other location other than a local drive on our SQL 2000 server.
> If we try to create a backup to another location, we get a message that
> reads something like: SQL can not verify the location of the file. Do you
> want to use this location anyway?
> When we try to backup to the location, the we get the error message that
> reads: file or folder not on-line.
> If we change the network adminstrator password back to it's original, we c
an
> once again backup.
> Thank you,
> Ron Dittmann
> --
> Ron Dittmann|||Check the following troubleshooting article:
How to troubleshoot a SQL Server 8198 error
http://support.microsoft.com/?id=834124
-Sue
On Wed, 13 Oct 2004 20:39:07 -0700, "dpolara"
<dpolara@.discussions.microsoft.com> wrote:
>We changed our administrator password on all of our servers. We were backi
ng
>up our SQL databases to another server
>(ex: \\anotherserver\dropbox\databackup) However, we can no longer backup
>to any other location other than a local drive on our SQL 2000 server.
>If we try to create a backup to another location, we get a message that
>reads something like: SQL can not verify the location of the file. Do you
>want to use this location anyway?
>When we try to backup to the location, the we get the error message that
>reads: file or folder not on-line.
>If we change the network adminstrator password back to it's original, we ca
n
>once again backup.
>Thank you,
>Ron Dittmann
Thursday, February 9, 2012
Admin Performance Issues, Large amount of DBs
I have a large amout of dbs (150) on my SQL Server and when using the enterprise manager to do administrational tasks like Backup, Restore etc. it takes 1.5 hour to open the Database folder. Server is 2xP4, 3Gb RAM. Any ideas on how to manage this number of dbs on the same server and instance of SQL.
Cheers!Use scripts ?
-PatP|||If you can run utilities on server itself, register server as "(LOCAL)" which is local named pipes. Also make sure "Enable shared memory protocol" is checked in Client Network Utility on server.
Otherwise if you can not use server console to run utilities make sure you use TCP/IP protocol.
Hans.
Cheers!Use scripts ?
-PatP|||If you can run utilities on server itself, register server as "(LOCAL)" which is local named pipes. Also make sure "Enable shared memory protocol" is checked in Client Network Utility on server.
Otherwise if you can not use server console to run utilities make sure you use TCP/IP protocol.
Hans.
Subscribe to:
Posts (Atom)