It recently occurred to me that I should see if the Server backs up it's
logins somewhere/somehow. I make backups of my databases but not the logins
(I'm working on learing that export/import now). But, does SQL Server back u
p
its logins on its own, as part of its own maintenance?
--
Michael Hocksteinsystem databases
"michael" <howlinghound@.nospam.nospam> wrote in message
news:33514BD0-10D3-495E-AD7F-961BE209C322@.microsoft.com...
> It recently occurred to me that I should see if the Server backs up it's
> logins somewhere/somehow. I make backups of my databases but not the
> logins
> (I'm working on learing that export/import now). But, does SQL Server back
> up
> its logins on its own, as part of its own maintenance?
> --
> Michael Hockstein|||Logins are stored in master database, so you should back up the master
database to get a logins backup
"michael" <howlinghound@.nospam.nospam> escribi en el mensaje
news:33514BD0-10D3-495E-AD7F-961BE209C322@.microsoft.com...
> It recently occurred to me that I should see if the Server backs up it's
> logins somewhere/somehow. I make backups of my databases but not the
> logins
> (I'm working on learing that export/import now). But, does SQL Server back
> up
> its logins on its own, as part of its own maintenance?
> --
> Michael Hockstein|||Thanks.
Michael Hockstein
"David J. Cartwright" wrote:
> system databases
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:33514BD0-10D3-495E-AD7F-961BE209C322@.microsoft.com...
>
>|||It looks like the data I want to back up is specifically in dbo.sysxlogins.
I'll have to backup the master database.
Thanks
--
Michael Hockstein
"Antonio Soto" wrote:
> Logins are stored in master database, so you should back up the master
> database to get a logins backup
>
> "michael" <howlinghound@.nospam.nospam> escribió en el mensaje
> news:33514BD0-10D3-495E-AD7F-961BE209C322@.microsoft.com...
>
>
Showing posts with label backup. Show all posts
Showing posts with label backup. Show all posts
Tuesday, March 27, 2012
Friday, March 9, 2012
Does fragmentation changes after backup/restore operation?
Hello,
If i backup a database and then restore it, would physical structure remain the same? specially fragmentation.
I'm concerned about output of DBCC SHOWCONTIG.
Senario: I want to check if client database needs defragmentation, so he's sending db backup file. But is it possible that when i restore it fragmentation info has got lost?
Thank you.
Table/index fragmentation should be unaffected by BACKUP/RESTORE.
|||
And i hope it's same for tranferring .mdf file to be attached later?
?
|||Yes, it is the same for detach/attach.
Does field exist in backup?
I run scripts against my db,
1) Rename table
2) Create new table (original name)
3) Create Indexes
4) Insert into new, Select from backup
My problem is that one table may have more fields than on another db, but I want to run the same script to update the tables.
What I'd like it to do, is in the insert select stuff, I want to put logic to select field from backup if it exists and insert in new.
If it doesnt exist in backup then insert null into new table, instead of having the line blow up cause the field doesnt exist in backup.
Suggestions would be appreciated. Thanks!, MitchIf it's not in the list it will automatically put in nulls...as long as it is nullable...
and you could go crazy...but it might just easier to code the dang thing...
USE Northwind
GO
sp_help Orders
GO
-- The lazy man's way to create a table
SELECT * INTO NewOrders FROM Orders WHERE 1=0
GO
ALTER TABLE NewOrders DROP Column RequiredDate
GO
DECLARE @.x varchar(8000)
SELECT @.x = 'INSERT INTO NewOrders ('
SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1
SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION
SELECT @.x = @.x + ') SELECT '
SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1
SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION
SELECT @.x = @.x + ' FROM Orders'
SELECT @.x
SET IDENTITY_INSERT NewOrders ON
EXEC(@.x)
SET IDENTITY_INSERT NewOrders OFF
GO
SELECT * FROM NewOrders
GO
DROP TABLE NewOrders
GO|||If you could send me a link or something, that would help me understand the logic below that would be awesome. I sort of follow the code below, but I'd like to see step by step what does what.
Thanks for your reply.
Mitch
Originally posted by Brett Kaiser
If it's not in the list it will automatically put in nulls...as long as it is nullable...
and you could go crazy...but it might just easier to code the dang thing...
USE Northwind
GO
sp_help Orders
GO
-- The lazy man's way to create a table
SELECT * INTO NewOrders FROM Orders WHERE 1=0
GO
ALTER TABLE NewOrders DROP Column RequiredDate
GO
DECLARE @.x varchar(8000)
SELECT @.x = 'INSERT INTO NewOrders ('
SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1
SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION
SELECT @.x = @.x + ') SELECT '
SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1
SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION
SELECT @.x = @.x + ' FROM Orders'
SELECT @.x
SET IDENTITY_INSERT NewOrders ON
EXEC(@.x)
SET IDENTITY_INSERT NewOrders OFF
GO
SELECT * FROM NewOrders
GO
DROP TABLE NewOrders
GO|||Mitch,
Just cut and paste the code into a query analyzer window...
Just execute...I already tested it and it runs like a champ...
1) Rename table
2) Create new table (original name)
3) Create Indexes
4) Insert into new, Select from backup
My problem is that one table may have more fields than on another db, but I want to run the same script to update the tables.
What I'd like it to do, is in the insert select stuff, I want to put logic to select field from backup if it exists and insert in new.
If it doesnt exist in backup then insert null into new table, instead of having the line blow up cause the field doesnt exist in backup.
Suggestions would be appreciated. Thanks!, MitchIf it's not in the list it will automatically put in nulls...as long as it is nullable...
and you could go crazy...but it might just easier to code the dang thing...
USE Northwind
GO
sp_help Orders
GO
-- The lazy man's way to create a table
SELECT * INTO NewOrders FROM Orders WHERE 1=0
GO
ALTER TABLE NewOrders DROP Column RequiredDate
GO
DECLARE @.x varchar(8000)
SELECT @.x = 'INSERT INTO NewOrders ('
SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1
SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION
SELECT @.x = @.x + ') SELECT '
SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1
SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION
SELECT @.x = @.x + ' FROM Orders'
SELECT @.x
SET IDENTITY_INSERT NewOrders ON
EXEC(@.x)
SET IDENTITY_INSERT NewOrders OFF
GO
SELECT * FROM NewOrders
GO
DROP TABLE NewOrders
GO|||If you could send me a link or something, that would help me understand the logic below that would be awesome. I sort of follow the code below, but I'd like to see step by step what does what.
Thanks for your reply.
Mitch
Originally posted by Brett Kaiser
If it's not in the list it will automatically put in nulls...as long as it is nullable...
and you could go crazy...but it might just easier to code the dang thing...
USE Northwind
GO
sp_help Orders
GO
-- The lazy man's way to create a table
SELECT * INTO NewOrders FROM Orders WHERE 1=0
GO
ALTER TABLE NewOrders DROP Column RequiredDate
GO
DECLARE @.x varchar(8000)
SELECT @.x = 'INSERT INTO NewOrders ('
SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1
SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION
SELECT @.x = @.x + ') SELECT '
SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1
SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION
SELECT @.x = @.x + ' FROM Orders'
SELECT @.x
SET IDENTITY_INSERT NewOrders ON
EXEC(@.x)
SET IDENTITY_INSERT NewOrders OFF
GO
SELECT * FROM NewOrders
GO
DROP TABLE NewOrders
GO|||Mitch,
Just cut and paste the code into a query analyzer window...
Just execute...I already tested it and it runs like a champ...
does database backup truncate tlog ?
I am running sql server 2000 sp3a on win2k. does complete database backup do
tlog backup and truncate it also ?Jay
No,it does not
This is a great thing in SQL Server that you can have a full backup that
was done let me say at 1 May and at 1 June and doing t-log file backup. In
case of disaster you restore a full backup (at 1 May ) and apply all t-log
file backups till a point of distaster .
If sql server was trunucated a log file during the backup you was not be
able to restore all log files due to breaking chaines of transactions
"Jay" <Jay@.discussions.microsoft.com> wrote in message
news:A4A8062F-6DA1-4AA8-B33B-63F91E3E9737@.microsoft.com...
>I am running sql server 2000 sp3a on win2k. does complete database backup
>do
> tlog backup and truncate it also ?
>|||does complete database backup do
> tlog backup and truncate it also ?
No when your db is in bulk_logged or full recovery model. In order to
truncate the log, you should backup the transaction log, using "backup log".
The db should be set to use bulk_logged or full recovery model in order to
backup the transaction log.
If your database's recovery model is simple, you can not backup the
transaction log. SQL Server will truncate the transaction log after a
checkpoint.
AMB
"Jay" wrote:
> I am running sql server 2000 sp3a on win2k. does complete database backup do
> tlog backup and truncate it also ?
>|||Correction,
> No when your db is in bulk_logged or full recovery model.
No, a full backup of the db does not truncate the log, no matter what
recovery model is it using.
AMB
"Alejandro Mesa" wrote:
> does complete database backup do
> > tlog backup and truncate it also ?
> No when your db is in bulk_logged or full recovery model. In order to
> truncate the log, you should backup the transaction log, using "backup log".
> The db should be set to use bulk_logged or full recovery model in order to
> backup the transaction log.
> If your database's recovery model is simple, you can not backup the
> transaction log. SQL Server will truncate the transaction log after a
> checkpoint.
>
> AMB
> "Jay" wrote:
> > I am running sql server 2000 sp3a on win2k. does complete database backup do
> > tlog backup and truncate it also ?
> >
tlog backup and truncate it also ?Jay
No,it does not
This is a great thing in SQL Server that you can have a full backup that
was done let me say at 1 May and at 1 June and doing t-log file backup. In
case of disaster you restore a full backup (at 1 May ) and apply all t-log
file backups till a point of distaster .
If sql server was trunucated a log file during the backup you was not be
able to restore all log files due to breaking chaines of transactions
"Jay" <Jay@.discussions.microsoft.com> wrote in message
news:A4A8062F-6DA1-4AA8-B33B-63F91E3E9737@.microsoft.com...
>I am running sql server 2000 sp3a on win2k. does complete database backup
>do
> tlog backup and truncate it also ?
>|||does complete database backup do
> tlog backup and truncate it also ?
No when your db is in bulk_logged or full recovery model. In order to
truncate the log, you should backup the transaction log, using "backup log".
The db should be set to use bulk_logged or full recovery model in order to
backup the transaction log.
If your database's recovery model is simple, you can not backup the
transaction log. SQL Server will truncate the transaction log after a
checkpoint.
AMB
"Jay" wrote:
> I am running sql server 2000 sp3a on win2k. does complete database backup do
> tlog backup and truncate it also ?
>|||Correction,
> No when your db is in bulk_logged or full recovery model.
No, a full backup of the db does not truncate the log, no matter what
recovery model is it using.
AMB
"Alejandro Mesa" wrote:
> does complete database backup do
> > tlog backup and truncate it also ?
> No when your db is in bulk_logged or full recovery model. In order to
> truncate the log, you should backup the transaction log, using "backup log".
> The db should be set to use bulk_logged or full recovery model in order to
> backup the transaction log.
> If your database's recovery model is simple, you can not backup the
> transaction log. SQL Server will truncate the transaction log after a
> checkpoint.
>
> AMB
> "Jay" wrote:
> > I am running sql server 2000 sp3a on win2k. does complete database backup do
> > tlog backup and truncate it also ?
> >
does database backup truncate tlog ?
I am running sql server 2000 sp3a on win2k. does complete database backup do
tlog backup and truncate it also ?
Jay
No,it does not
This is a great thing in SQL Server that you can have a full backup that
was done let me say at 1 May and at 1 June and doing t-log file backup. In
case of disaster you restore a full backup (at 1 May ) and apply all t-log
file backups till a point of distaster .
If sql server was trunucated a log file during the backup you was not be
able to restore all log files due to breaking chaines of transactions
"Jay" <Jay@.discussions.microsoft.com> wrote in message
news:A4A8062F-6DA1-4AA8-B33B-63F91E3E9737@.microsoft.com...
>I am running sql server 2000 sp3a on win2k. does complete database backup
>do
> tlog backup and truncate it also ?
>
|||does complete database backup do
> tlog backup and truncate it also ?
No when your db is in bulk_logged or full recovery model. In order to
truncate the log, you should backup the transaction log, using "backup log".
The db should be set to use bulk_logged or full recovery model in order to
backup the transaction log.
If your database's recovery model is simple, you can not backup the
transaction log. SQL Server will truncate the transaction log after a
checkpoint.
AMB
"Jay" wrote:
> I am running sql server 2000 sp3a on win2k. does complete database backup do
> tlog backup and truncate it also ?
>
|||Correction,
> No when your db is in bulk_logged or full recovery model.
No, a full backup of the db does not truncate the log, no matter what
recovery model is it using.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> does complete database backup do
> No when your db is in bulk_logged or full recovery model. In order to
> truncate the log, you should backup the transaction log, using "backup log".
> The db should be set to use bulk_logged or full recovery model in order to
> backup the transaction log.
> If your database's recovery model is simple, you can not backup the
> transaction log. SQL Server will truncate the transaction log after a
> checkpoint.
>
> AMB
> "Jay" wrote:
tlog backup and truncate it also ?
Jay
No,it does not
This is a great thing in SQL Server that you can have a full backup that
was done let me say at 1 May and at 1 June and doing t-log file backup. In
case of disaster you restore a full backup (at 1 May ) and apply all t-log
file backups till a point of distaster .
If sql server was trunucated a log file during the backup you was not be
able to restore all log files due to breaking chaines of transactions
"Jay" <Jay@.discussions.microsoft.com> wrote in message
news:A4A8062F-6DA1-4AA8-B33B-63F91E3E9737@.microsoft.com...
>I am running sql server 2000 sp3a on win2k. does complete database backup
>do
> tlog backup and truncate it also ?
>
|||does complete database backup do
> tlog backup and truncate it also ?
No when your db is in bulk_logged or full recovery model. In order to
truncate the log, you should backup the transaction log, using "backup log".
The db should be set to use bulk_logged or full recovery model in order to
backup the transaction log.
If your database's recovery model is simple, you can not backup the
transaction log. SQL Server will truncate the transaction log after a
checkpoint.
AMB
"Jay" wrote:
> I am running sql server 2000 sp3a on win2k. does complete database backup do
> tlog backup and truncate it also ?
>
|||Correction,
> No when your db is in bulk_logged or full recovery model.
No, a full backup of the db does not truncate the log, no matter what
recovery model is it using.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> does complete database backup do
> No when your db is in bulk_logged or full recovery model. In order to
> truncate the log, you should backup the transaction log, using "backup log".
> The db should be set to use bulk_logged or full recovery model in order to
> backup the transaction log.
> If your database's recovery model is simple, you can not backup the
> transaction log. SQL Server will truncate the transaction log after a
> checkpoint.
>
> AMB
> "Jay" wrote:
does database backup truncate tlog ?
I am running sql server 2000 sp3a on win2k. does complete database backup do
tlog backup and truncate it also ?Jay
No,it does not
This is a great thing in SQL Server that you can have a full backup that
was done let me say at 1 May and at 1 June and doing t-log file backup. In
case of disaster you restore a full backup (at 1 May ) and apply all t-log
file backups till a point of distaster .
If sql server was trunucated a log file during the backup you was not be
able to restore all log files due to breaking chaines of transactions
"Jay" <Jay@.discussions.microsoft.com> wrote in message
news:A4A8062F-6DA1-4AA8-B33B-63F91E3E9737@.microsoft.com...
>I am running sql server 2000 sp3a on win2k. does complete database backup
>do
> tlog backup and truncate it also ?
>|||does complete database backup do
> tlog backup and truncate it also ?
No when your db is in bulk_logged or full recovery model. In order to
truncate the log, you should backup the transaction log, using "backup log".
The db should be set to use bulk_logged or full recovery model in order to
backup the transaction log.
If your database's recovery model is simple, you can not backup the
transaction log. SQL Server will truncate the transaction log after a
checkpoint.
AMB
"Jay" wrote:
> I am running sql server 2000 sp3a on win2k. does complete database backup
do
> tlog backup and truncate it also ?
>|||Correction,
> No when your db is in bulk_logged or full recovery model.
No, a full backup of the db does not truncate the log, no matter what
recovery model is it using.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> does complete database backup do
> No when your db is in bulk_logged or full recovery model. In order to
> truncate the log, you should backup the transaction log, using "backup log
".
> The db should be set to use bulk_logged or full recovery model in order to
> backup the transaction log.
> If your database's recovery model is simple, you can not backup the
> transaction log. SQL Server will truncate the transaction log after a
> checkpoint.
>
> AMB
> "Jay" wrote:
>
tlog backup and truncate it also ?Jay
No,it does not
This is a great thing in SQL Server that you can have a full backup that
was done let me say at 1 May and at 1 June and doing t-log file backup. In
case of disaster you restore a full backup (at 1 May ) and apply all t-log
file backups till a point of distaster .
If sql server was trunucated a log file during the backup you was not be
able to restore all log files due to breaking chaines of transactions
"Jay" <Jay@.discussions.microsoft.com> wrote in message
news:A4A8062F-6DA1-4AA8-B33B-63F91E3E9737@.microsoft.com...
>I am running sql server 2000 sp3a on win2k. does complete database backup
>do
> tlog backup and truncate it also ?
>|||does complete database backup do
> tlog backup and truncate it also ?
No when your db is in bulk_logged or full recovery model. In order to
truncate the log, you should backup the transaction log, using "backup log".
The db should be set to use bulk_logged or full recovery model in order to
backup the transaction log.
If your database's recovery model is simple, you can not backup the
transaction log. SQL Server will truncate the transaction log after a
checkpoint.
AMB
"Jay" wrote:
> I am running sql server 2000 sp3a on win2k. does complete database backup
do
> tlog backup and truncate it also ?
>|||Correction,
> No when your db is in bulk_logged or full recovery model.
No, a full backup of the db does not truncate the log, no matter what
recovery model is it using.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> does complete database backup do
> No when your db is in bulk_logged or full recovery model. In order to
> truncate the log, you should backup the transaction log, using "backup log
".
> The db should be set to use bulk_logged or full recovery model in order to
> backup the transaction log.
> If your database's recovery model is simple, you can not backup the
> transaction log. SQL Server will truncate the transaction log after a
> checkpoint.
>
> AMB
> "Jay" wrote:
>
Wednesday, March 7, 2012
Does Backup then Restore optimize the data?
I was wondering if backing up a database, whacking the database, then
restoring from the backup optimize the database? (like it would if i
backed up a drive to tape, formatted the drive, then restored from the
tape).
And what maintenance can i perform on the database to make things
faster?
Any help would be greatly appreciated.
RobThis is a multi-part message in MIME format.
--=_NextPart_000_0326_01C37933.941430B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
It would not optimize the database internally. However, reformatting =your disk would have the effect of defragging it. What's your specific =problem?
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"RKay" <robkayinto@.yahoo.com> wrote in message =news:2adcc671.0309120934.21827c1a@.posting.google.com...
I was wondering if backing up a database, whacking the database, then
restoring from the backup optimize the database? (like it would if i
backed up a drive to tape, formatted the drive, then restored from the
tape).
And what maintenance can i perform on the database to make things
faster?
Any help would be greatly appreciated.
Rob
--=_NextPart_000_0326_01C37933.941430B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
It would not optimize the database internally. However, reformatting your disk would have the effect =of defragging it. What's your specific problem?
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"RKay" wrote =in message news:2adcc6=71.0309120934.21827c1a@.posting.google.com...I was wondering if backing up a database, whacking the database, =thenrestoring from the backup optimize the database? (like it would if ibacked up =a drive to tape, formatted the drive, then restored from =thetape).And what maintenance can i perform on the database to make =thingsfaster?Any help would be greatly appreciated.Rob
--=_NextPart_000_0326_01C37933.941430B0--|||I want to optimize the layout of the data and indexes in the database
to speed up the queries.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:<OJbgHUVeDHA.560@.tk2msftngp13.phx.gbl>...
> It would not optimize the database internally. However, reformatting
> your disk would have the effect of defragging it. What's your specific
> problem?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "RKay" <robkayinto@.yahoo.com> wrote in message
> news:2adcc671.0309120934.21827c1a@.posting.google.com...
> I was wondering if backing up a database, whacking the database, then
> restoring from the backup optimize the database? (like it would if i
> backed up a drive to tape, formatted the drive, then restored from the
> tape).
> And what maintenance can i perform on the database to make things
> faster?
> Any help would be greatly appreciated.
> Rob
> --|||Rob,
Optimise data means?Usually you tune your queries by providing indexes on
the relevant columns and update the statistics etc in order to have a faster
response time for your queries.Is that what you are looking for? Regarding
data, you can check if the data is fragmented by using DBCC SHOWCONTIG.Also,
in oder to make sure that your database is consistent, perform DBCC CHECKDB
on it at offpeak times.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"RKay" <robkayinto@.yahoo.com> wrote in message
news:2adcc671.0309121404.62467127@.posting.google.com...
> I want to optimize the layout of the data and indexes in the database
> to speed up the queries.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:<OJbgHUVeDHA.560@.tk2msftngp13.phx.gbl>...
> > It would not optimize the database internally. However, reformatting
> > your disk would have the effect of defragging it. What's your specific
> > problem?
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com/sql
> >
> >
> > "RKay" <robkayinto@.yahoo.com> wrote in message
> > news:2adcc671.0309120934.21827c1a@.posting.google.com...
> > I was wondering if backing up a database, whacking the database, then
> > restoring from the backup optimize the database? (like it would if i
> > backed up a drive to tape, formatted the drive, then restored from the
> > tape).
> >
> > And what maintenance can i perform on the database to make things
> > faster?
> >
> > Any help would be greatly appreciated.
> >
> > Rob
> > --|||This is a multi-part message in MIME format.
--=_NextPart_000_0088_01C37964.0C1ACF80
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Consider running a test script against the Index Tuning Wizard. =Physical design is based on how you use your data.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"RKay" <robkayinto@.yahoo.com> wrote in message =news:2adcc671.0309121404.62467127@.posting.google.com...
I want to optimize the layout of the data and indexes in the database
to speed up the queries.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:<OJbgHUVeDHA.560@.tk2msftngp13.phx.gbl>...
> It would not optimize the database internally. However, reformatting > your disk would have the effect of defragging it. What's your =specific > problem?
> > -- > Tom
> > ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> > > "RKay" <robkayinto@.yahoo.com> wrote in message > news:2adcc671.0309120934.21827c1a@.posting.google.com...
> I was wondering if backing up a database, whacking the database, then
> restoring from the backup optimize the database? (like it would if i
> backed up a drive to tape, formatted the drive, then restored from the
> tape).
> > And what maintenance can i perform on the database to make things
> faster?
> > Any help would be greatly appreciated.
> > Rob
> --
--=_NextPart_000_0088_01C37964.0C1ACF80
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Consider running a test script against =the Index Tuning Wizard. Physical design is based on how you use your data.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"RKay" wrote =in message news:2adcc6=71.0309121404.62467127@.posting.google.com...I want to optimize the layout of the data and indexes in the =databaseto speed up the queries."Tom Moreau" = wrote in message ...> It would not optimize the database internally. However, =reformatting > your disk would have the effect of defragging it. What's =your specific > problem?> > -- > Tom> => ---> =Thomas A. Moreau, BSc, PhD, MCSE, MCDBA> SQL Server MVP> =Columnist, SQL Server Professional> Toronto, ON Canada>http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql> > > "RKay" wrote =in message > news:2adcc6=71.0309120934.21827c1a@.posting.google.com...> I was wondering if backing up a database, whacking the database, =then> restoring from the backup optimize the database? (like it would if =i> backed up a drive to tape, formatted the drive, then restored from =the> tape).> > And what maintenance can i perform on the =database to make things> faster?> > Any help would be greatly appreciated.> > Rob> --
--=_NextPart_000_0088_01C37964.0C1ACF80--|||"Bob Simms" <bob_simms@.hotmail.com> wrote in message news:<8Xr8b.1050$QO6.707@.news-binary.blueyonder.co.uk>...
> "RKay" <robkayinto@.yahoo.com> wrote in message
> news:2adcc671.0309121404.62467127@.posting.google.com...
> > I want to optimize the layout of the data and indexes in the database
> > to speed up the queries.
> >
> Backup does a page-level backup. It neither knows nor cares whether the
> pages it is backing up contain tables, indexes or anything else. The
> restore simply dumps the files back in the internal format it got them in.
> If you have the hardware, you might consider putting the tables and their
> corresponding indexes on different drives, thus reducing IO contention when
> updating indexed columns.
> Bob
>
I think what Rob is looking for is physical optimization of the data.
How do you get the physical storage optimized so that all the table
data & indexes are stored unfragmented and in order thereby improving
performance.
My experience in the past (with Oracle) is that if you did a export of
the data base, dropped the database, then imported the whole database
into a "fresh" db it runs faster. I've experienced greatly improved
speeds with Oracle, but I'm unfamiliar with the internals of SQL
Server, to know if this will truly improve performance with SQLServer.
Mike|||Make sure you have a clustered index on the table and do a DBCC DBREINDEX.
--
Andrew J. Kelly
SQL Server MVP
"RKay" <robkayinto@.yahoo.com> wrote in message
news:2adcc671.0309121404.62467127@.posting.google.com...
> I want to optimize the layout of the data and indexes in the database
> to speed up the queries.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:<OJbgHUVeDHA.560@.tk2msftngp13.phx.gbl>...
> > It would not optimize the database internally. However, reformatting
> > your disk would have the effect of defragging it. What's your specific
> > problem?
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com/sql
> >
> >
> > "RKay" <robkayinto@.yahoo.com> wrote in message
> > news:2adcc671.0309120934.21827c1a@.posting.google.com...
> > I was wondering if backing up a database, whacking the database, then
> > restoring from the backup optimize the database? (like it would if i
> > backed up a drive to tape, formatted the drive, then restored from the
> > tape).
> >
> > And what maintenance can i perform on the database to make things
> > faster?
> >
> > Any help would be greatly appreciated.
> >
> > Rob
> > --|||Hello
> Make sure you have a clustered index on the table and do a DBCC DBREINDEX.
It is not always possible to stop production system and rebuild indexes.
I think that physical location of data in DB significantly affects
performance.
Neither DBCC DBREINDEX nor INDEXDEFRAG doesn't remove physical
fragmentation. Using SQLFE tool you can make sure that objects in
database
are heavily fragmented even after "defragmentation".
I don't know if exist command to swap two pages or extents? This command
will be enough to write defragmentation program.
Serge Shakhov|||> Neither DBCC DBREINDEX nor INDEXDEFRAG doesn't remove physical
> fragmentation
That is just plain not true. Both will indeed remove physical fragmentation
although INDEXDEFRAG will only work at the Leaf level of the index.
DBREINDEX will actually build a whole new table so if you have enough free
contiguous space in the db it will completely rebuild the indexes.
> I don't know if exist command to swap two pages or extents? This
command
> will be enough to write defragmentation program.
That is almost exactly how INDEXDEFRAG works. It's actually a little better
than just swapping but it does indeed work similar to that.
I suggest you get a hold of "Inside SQL Server 2000" and read up on these
two commands. BooksOnLine also does a pretty good job explaining. This
too may be of interest:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp
Andrew J. Kelly
SQL Server MVP
"Serge Shakhov" <ACETYLENE@.mail.ru> wrote in message
news:mht3kb.lv1.ln@.proxyserver.ctd.mmk.chel.su...
> Hello
> > Make sure you have a clustered index on the table and do a DBCC
DBREINDEX.
> It is not always possible to stop production system and rebuild
indexes.
> I think that physical location of data in DB significantly affects
> performance.
> Neither DBCC DBREINDEX nor INDEXDEFRAG doesn't remove physical
> fragmentation. Using SQLFE tool you can make sure that objects in
> database
> are heavily fragmented even after "defragmentation".
> I don't know if exist command to swap two pages or extents? This
command
> will be enough to write defragmentation program.
>
> Serge Shakhov
>|||Hello
> > Neither DBCC DBREINDEX nor INDEXDEFRAG doesn't remove physical
> > fragmentation
> That is just plain not true. Both will indeed remove physical
fragmentation
Unfortunately this is true. Let me show you simple example:
dbcc showcontig ('contracts', 1)
DBCC SHOWCONTIG scanning 'CONTRACTS' table...
Table: 'CONTRACTS' (1973634124); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 113
- Extents Scanned.......................: 16
- Extent Switches.......................: 24
- Avg. Pages per Extent..................: 7.1
- Scan Density [Best Count:Actual Count]......: 60.00% [15:25]
- Logical Scan Fragmentation ..............: 4.42%
- Extent Scan Fragmentation ...............: 37.50%
- Avg. Bytes Free per Page................: 121.5
- Avg. Page Density (full)................: 98.50%
So we can see that table (clustered index) is fragmented.
dbcc indexdefrag ('gruz','contracts',1)
dbcc showcontig ('contracts', 1)
DBCC SHOWCONTIG scanning 'CONTRACTS' table...
Table: 'CONTRACTS' (1973634124); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 112
- Extents Scanned.......................: 16
- Extent Switches.......................: 24
- Avg. Pages per Extent..................: 7.0
- Scan Density [Best Count:Actual Count]......: 56.00% [14:25]
- Logical Scan Fragmentation ..............: 4.46%
- Extent Scan Fragmentation ...............: 37.50%
- Avg. Bytes Free per Page................: 50.3
- Avg. Page Density (full)................: 99.38%
The table is still fragmented.
DBCC DBREINDEX ('cargo.dbo.contracts','PK_contracts')
dbcc showcontig ('contracts', 1)
DBCC SHOWCONTIG scanning 'CONTRACTS' table...
Table: 'CONTRACTS' (1973634124); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 112
- Extents Scanned.......................: 15
- Extent Switches.......................: 14
- Avg. Pages per Extent..................: 7.5
- Scan Density [Best Count:Actual Count]......: 93.33% [14:15]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 20.00%
- Avg. Bytes Free per Page................: 50.3
- Avg. Page Density (full)................: 99.38%
Now level of fragmentation significantly decreased
but table is still FRAGMENTED! Not entire table located in contiguous
space. For small tables final Scan Density will be 100% but the more
table size the worse results we can get.
> although INDEXDEFRAG will only work at the Leaf level of the index.
> DBREINDEX will actually build a whole new table so if you have enough free
> contiguous space in the db it will completely rebuild the indexes.
Yes, it will. But there is no guarantee that rebuilded index will be
placed
in contiguous space wholly. This is what I'm talking about.
By the way I simply can't use DBREINDEX because my system works
in 24x7 mode. That is why I'm trying to find the way to write my own
defragmentation procedure.
Serge Shakhov|||Serge,
Do you have more than 1 file in your filegroup by any chance? If so the
Extent Fragmentation value that is reported will generally be high due to
the fact that Indexdefrag only works on a file at a time and does not swap
pages between files. This is well documented in BooksOnLine and elsewhere.
As for the DBREINDEX you only have 15 extents and the switches were only 14.
That's not too bad even though the stats might look high but that is due to
the small number of extents and possibly due to the first extent being a
mixed one. This is quite normal. Even on larger tables you can never
guarantee you will get 100% logical and physical defragmentation due to the
factors mentioned already. And you don't need it either. Did you read the
link I posted? If not then I really suggest you do as it will explain a lot
and calm down your fears of needing to be 100% defragged.
--
Andrew J. Kelly
SQL Server MVP
"Serge Shakhov" <ACETYLENE@.mail.ru> wrote in message
news:28j8kb.r1k.ln@.proxyserver.ctd.mmk.chel.su...
> Hello
> > > Neither DBCC DBREINDEX nor INDEXDEFRAG doesn't remove physical
> > > fragmentation
> >
> > That is just plain not true. Both will indeed remove physical
> fragmentation
> Unfortunately this is true. Let me show you simple example:
> dbcc showcontig ('contracts', 1)
> DBCC SHOWCONTIG scanning 'CONTRACTS' table...
> Table: 'CONTRACTS' (1973634124); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 113
> - Extents Scanned.......................: 16
> - Extent Switches.......................: 24
> - Avg. Pages per Extent..................: 7.1
> - Scan Density [Best Count:Actual Count]......: 60.00% [15:25]
> - Logical Scan Fragmentation ..............: 4.42%
> - Extent Scan Fragmentation ...............: 37.50%
> - Avg. Bytes Free per Page................: 121.5
> - Avg. Page Density (full)................: 98.50%
> So we can see that table (clustered index) is fragmented.
> dbcc indexdefrag ('gruz','contracts',1)
> dbcc showcontig ('contracts', 1)
> DBCC SHOWCONTIG scanning 'CONTRACTS' table...
> Table: 'CONTRACTS' (1973634124); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 112
> - Extents Scanned.......................: 16
> - Extent Switches.......................: 24
> - Avg. Pages per Extent..................: 7.0
> - Scan Density [Best Count:Actual Count]......: 56.00% [14:25]
> - Logical Scan Fragmentation ..............: 4.46%
> - Extent Scan Fragmentation ...............: 37.50%
> - Avg. Bytes Free per Page................: 50.3
> - Avg. Page Density (full)................: 99.38%
> The table is still fragmented.
> DBCC DBREINDEX ('cargo.dbo.contracts','PK_contracts')
> dbcc showcontig ('contracts', 1)
> DBCC SHOWCONTIG scanning 'CONTRACTS' table...
> Table: 'CONTRACTS' (1973634124); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 112
> - Extents Scanned.......................: 15
> - Extent Switches.......................: 14
> - Avg. Pages per Extent..................: 7.5
> - Scan Density [Best Count:Actual Count]......: 93.33% [14:15]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 20.00%
> - Avg. Bytes Free per Page................: 50.3
> - Avg. Page Density (full)................: 99.38%
> Now level of fragmentation significantly decreased
> but table is still FRAGMENTED! Not entire table located in contiguous
> space. For small tables final Scan Density will be 100% but the more
> table size the worse results we can get.
>
> > although INDEXDEFRAG will only work at the Leaf level of the index.
> > DBREINDEX will actually build a whole new table so if you have enough
free
> > contiguous space in the db it will completely rebuild the indexes.
> Yes, it will. But there is no guarantee that rebuilded index will be
> placed
> in contiguous space wholly. This is what I'm talking about.
> By the way I simply can't use DBREINDEX because my system works
> in 24x7 mode. That is why I'm trying to find the way to write my own
> defragmentation procedure.
>
> Serge Shakhov
>
>|||Andrew
> Do you have more than 1 file in your filegroup by any chance? If so the
> Extent Fragmentation value that is reported will generally be high due to
> the fact that Indexdefrag only works on a file at a time and does not swap
> pages between files.
Yes, there are two files in each filegroup and I wasn't surprised
by results of INDEXDEFRAG.
> As for the DBREINDEX you only have 15 extents and the switches were only
14.
> That's not too bad even though the stats might look high but that is due
to
> the small number of extents and possibly due to the first extent being a
> mixed one. This is quite normal.
I know. This is just small sample table. I can not start DBREINDEX on
large tables in production database.
> Even on larger tables you can never
> guarantee you will get 100% logical and physical defragmentation due to
the
> factors mentioned already. And you don't need it either.
Of course I'll be satisfied with 90-95% Scan Density.
The problem is that I can't achieve this result with INDEXDEFRAG
and can't stop database to rebuild indexes.
> Did you read the link I posted?
Not yet, I've just printed it... 17 pages and english is not my native
language...
Of course, I'll read it.
Serge Shakhov
restoring from the backup optimize the database? (like it would if i
backed up a drive to tape, formatted the drive, then restored from the
tape).
And what maintenance can i perform on the database to make things
faster?
Any help would be greatly appreciated.
RobThis is a multi-part message in MIME format.
--=_NextPart_000_0326_01C37933.941430B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
It would not optimize the database internally. However, reformatting =your disk would have the effect of defragging it. What's your specific =problem?
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"RKay" <robkayinto@.yahoo.com> wrote in message =news:2adcc671.0309120934.21827c1a@.posting.google.com...
I was wondering if backing up a database, whacking the database, then
restoring from the backup optimize the database? (like it would if i
backed up a drive to tape, formatted the drive, then restored from the
tape).
And what maintenance can i perform on the database to make things
faster?
Any help would be greatly appreciated.
Rob
--=_NextPart_000_0326_01C37933.941430B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
It would not optimize the database internally. However, reformatting your disk would have the effect =of defragging it. What's your specific problem?
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"RKay"
--=_NextPart_000_0326_01C37933.941430B0--|||I want to optimize the layout of the data and indexes in the database
to speed up the queries.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:<OJbgHUVeDHA.560@.tk2msftngp13.phx.gbl>...
> It would not optimize the database internally. However, reformatting
> your disk would have the effect of defragging it. What's your specific
> problem?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "RKay" <robkayinto@.yahoo.com> wrote in message
> news:2adcc671.0309120934.21827c1a@.posting.google.com...
> I was wondering if backing up a database, whacking the database, then
> restoring from the backup optimize the database? (like it would if i
> backed up a drive to tape, formatted the drive, then restored from the
> tape).
> And what maintenance can i perform on the database to make things
> faster?
> Any help would be greatly appreciated.
> Rob
> --|||Rob,
Optimise data means?Usually you tune your queries by providing indexes on
the relevant columns and update the statistics etc in order to have a faster
response time for your queries.Is that what you are looking for? Regarding
data, you can check if the data is fragmented by using DBCC SHOWCONTIG.Also,
in oder to make sure that your database is consistent, perform DBCC CHECKDB
on it at offpeak times.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"RKay" <robkayinto@.yahoo.com> wrote in message
news:2adcc671.0309121404.62467127@.posting.google.com...
> I want to optimize the layout of the data and indexes in the database
> to speed up the queries.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:<OJbgHUVeDHA.560@.tk2msftngp13.phx.gbl>...
> > It would not optimize the database internally. However, reformatting
> > your disk would have the effect of defragging it. What's your specific
> > problem?
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com/sql
> >
> >
> > "RKay" <robkayinto@.yahoo.com> wrote in message
> > news:2adcc671.0309120934.21827c1a@.posting.google.com...
> > I was wondering if backing up a database, whacking the database, then
> > restoring from the backup optimize the database? (like it would if i
> > backed up a drive to tape, formatted the drive, then restored from the
> > tape).
> >
> > And what maintenance can i perform on the database to make things
> > faster?
> >
> > Any help would be greatly appreciated.
> >
> > Rob
> > --|||This is a multi-part message in MIME format.
--=_NextPart_000_0088_01C37964.0C1ACF80
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Consider running a test script against the Index Tuning Wizard. =Physical design is based on how you use your data.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"RKay" <robkayinto@.yahoo.com> wrote in message =news:2adcc671.0309121404.62467127@.posting.google.com...
I want to optimize the layout of the data and indexes in the database
to speed up the queries.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:<OJbgHUVeDHA.560@.tk2msftngp13.phx.gbl>...
> It would not optimize the database internally. However, reformatting > your disk would have the effect of defragging it. What's your =specific > problem?
> > -- > Tom
> > ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> > > "RKay" <robkayinto@.yahoo.com> wrote in message > news:2adcc671.0309120934.21827c1a@.posting.google.com...
> I was wondering if backing up a database, whacking the database, then
> restoring from the backup optimize the database? (like it would if i
> backed up a drive to tape, formatted the drive, then restored from the
> tape).
> > And what maintenance can i perform on the database to make things
> faster?
> > Any help would be greatly appreciated.
> > Rob
> --
--=_NextPart_000_0088_01C37964.0C1ACF80
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Consider running a test script against =the Index Tuning Wizard. Physical design is based on how you use your data.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"RKay"
--=_NextPart_000_0088_01C37964.0C1ACF80--|||"Bob Simms" <bob_simms@.hotmail.com> wrote in message news:<8Xr8b.1050$QO6.707@.news-binary.blueyonder.co.uk>...
> "RKay" <robkayinto@.yahoo.com> wrote in message
> news:2adcc671.0309121404.62467127@.posting.google.com...
> > I want to optimize the layout of the data and indexes in the database
> > to speed up the queries.
> >
> Backup does a page-level backup. It neither knows nor cares whether the
> pages it is backing up contain tables, indexes or anything else. The
> restore simply dumps the files back in the internal format it got them in.
> If you have the hardware, you might consider putting the tables and their
> corresponding indexes on different drives, thus reducing IO contention when
> updating indexed columns.
> Bob
>
I think what Rob is looking for is physical optimization of the data.
How do you get the physical storage optimized so that all the table
data & indexes are stored unfragmented and in order thereby improving
performance.
My experience in the past (with Oracle) is that if you did a export of
the data base, dropped the database, then imported the whole database
into a "fresh" db it runs faster. I've experienced greatly improved
speeds with Oracle, but I'm unfamiliar with the internals of SQL
Server, to know if this will truly improve performance with SQLServer.
Mike|||Make sure you have a clustered index on the table and do a DBCC DBREINDEX.
--
Andrew J. Kelly
SQL Server MVP
"RKay" <robkayinto@.yahoo.com> wrote in message
news:2adcc671.0309121404.62467127@.posting.google.com...
> I want to optimize the layout of the data and indexes in the database
> to speed up the queries.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:<OJbgHUVeDHA.560@.tk2msftngp13.phx.gbl>...
> > It would not optimize the database internally. However, reformatting
> > your disk would have the effect of defragging it. What's your specific
> > problem?
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com/sql
> >
> >
> > "RKay" <robkayinto@.yahoo.com> wrote in message
> > news:2adcc671.0309120934.21827c1a@.posting.google.com...
> > I was wondering if backing up a database, whacking the database, then
> > restoring from the backup optimize the database? (like it would if i
> > backed up a drive to tape, formatted the drive, then restored from the
> > tape).
> >
> > And what maintenance can i perform on the database to make things
> > faster?
> >
> > Any help would be greatly appreciated.
> >
> > Rob
> > --|||Hello
> Make sure you have a clustered index on the table and do a DBCC DBREINDEX.
It is not always possible to stop production system and rebuild indexes.
I think that physical location of data in DB significantly affects
performance.
Neither DBCC DBREINDEX nor INDEXDEFRAG doesn't remove physical
fragmentation. Using SQLFE tool you can make sure that objects in
database
are heavily fragmented even after "defragmentation".
I don't know if exist command to swap two pages or extents? This command
will be enough to write defragmentation program.
Serge Shakhov|||> Neither DBCC DBREINDEX nor INDEXDEFRAG doesn't remove physical
> fragmentation
That is just plain not true. Both will indeed remove physical fragmentation
although INDEXDEFRAG will only work at the Leaf level of the index.
DBREINDEX will actually build a whole new table so if you have enough free
contiguous space in the db it will completely rebuild the indexes.
> I don't know if exist command to swap two pages or extents? This
command
> will be enough to write defragmentation program.
That is almost exactly how INDEXDEFRAG works. It's actually a little better
than just swapping but it does indeed work similar to that.
I suggest you get a hold of "Inside SQL Server 2000" and read up on these
two commands. BooksOnLine also does a pretty good job explaining. This
too may be of interest:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp
Andrew J. Kelly
SQL Server MVP
"Serge Shakhov" <ACETYLENE@.mail.ru> wrote in message
news:mht3kb.lv1.ln@.proxyserver.ctd.mmk.chel.su...
> Hello
> > Make sure you have a clustered index on the table and do a DBCC
DBREINDEX.
> It is not always possible to stop production system and rebuild
indexes.
> I think that physical location of data in DB significantly affects
> performance.
> Neither DBCC DBREINDEX nor INDEXDEFRAG doesn't remove physical
> fragmentation. Using SQLFE tool you can make sure that objects in
> database
> are heavily fragmented even after "defragmentation".
> I don't know if exist command to swap two pages or extents? This
command
> will be enough to write defragmentation program.
>
> Serge Shakhov
>|||Hello
> > Neither DBCC DBREINDEX nor INDEXDEFRAG doesn't remove physical
> > fragmentation
> That is just plain not true. Both will indeed remove physical
fragmentation
Unfortunately this is true. Let me show you simple example:
dbcc showcontig ('contracts', 1)
DBCC SHOWCONTIG scanning 'CONTRACTS' table...
Table: 'CONTRACTS' (1973634124); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 113
- Extents Scanned.......................: 16
- Extent Switches.......................: 24
- Avg. Pages per Extent..................: 7.1
- Scan Density [Best Count:Actual Count]......: 60.00% [15:25]
- Logical Scan Fragmentation ..............: 4.42%
- Extent Scan Fragmentation ...............: 37.50%
- Avg. Bytes Free per Page................: 121.5
- Avg. Page Density (full)................: 98.50%
So we can see that table (clustered index) is fragmented.
dbcc indexdefrag ('gruz','contracts',1)
dbcc showcontig ('contracts', 1)
DBCC SHOWCONTIG scanning 'CONTRACTS' table...
Table: 'CONTRACTS' (1973634124); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 112
- Extents Scanned.......................: 16
- Extent Switches.......................: 24
- Avg. Pages per Extent..................: 7.0
- Scan Density [Best Count:Actual Count]......: 56.00% [14:25]
- Logical Scan Fragmentation ..............: 4.46%
- Extent Scan Fragmentation ...............: 37.50%
- Avg. Bytes Free per Page................: 50.3
- Avg. Page Density (full)................: 99.38%
The table is still fragmented.
DBCC DBREINDEX ('cargo.dbo.contracts','PK_contracts')
dbcc showcontig ('contracts', 1)
DBCC SHOWCONTIG scanning 'CONTRACTS' table...
Table: 'CONTRACTS' (1973634124); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 112
- Extents Scanned.......................: 15
- Extent Switches.......................: 14
- Avg. Pages per Extent..................: 7.5
- Scan Density [Best Count:Actual Count]......: 93.33% [14:15]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 20.00%
- Avg. Bytes Free per Page................: 50.3
- Avg. Page Density (full)................: 99.38%
Now level of fragmentation significantly decreased
but table is still FRAGMENTED! Not entire table located in contiguous
space. For small tables final Scan Density will be 100% but the more
table size the worse results we can get.
> although INDEXDEFRAG will only work at the Leaf level of the index.
> DBREINDEX will actually build a whole new table so if you have enough free
> contiguous space in the db it will completely rebuild the indexes.
Yes, it will. But there is no guarantee that rebuilded index will be
placed
in contiguous space wholly. This is what I'm talking about.
By the way I simply can't use DBREINDEX because my system works
in 24x7 mode. That is why I'm trying to find the way to write my own
defragmentation procedure.
Serge Shakhov|||Serge,
Do you have more than 1 file in your filegroup by any chance? If so the
Extent Fragmentation value that is reported will generally be high due to
the fact that Indexdefrag only works on a file at a time and does not swap
pages between files. This is well documented in BooksOnLine and elsewhere.
As for the DBREINDEX you only have 15 extents and the switches were only 14.
That's not too bad even though the stats might look high but that is due to
the small number of extents and possibly due to the first extent being a
mixed one. This is quite normal. Even on larger tables you can never
guarantee you will get 100% logical and physical defragmentation due to the
factors mentioned already. And you don't need it either. Did you read the
link I posted? If not then I really suggest you do as it will explain a lot
and calm down your fears of needing to be 100% defragged.
--
Andrew J. Kelly
SQL Server MVP
"Serge Shakhov" <ACETYLENE@.mail.ru> wrote in message
news:28j8kb.r1k.ln@.proxyserver.ctd.mmk.chel.su...
> Hello
> > > Neither DBCC DBREINDEX nor INDEXDEFRAG doesn't remove physical
> > > fragmentation
> >
> > That is just plain not true. Both will indeed remove physical
> fragmentation
> Unfortunately this is true. Let me show you simple example:
> dbcc showcontig ('contracts', 1)
> DBCC SHOWCONTIG scanning 'CONTRACTS' table...
> Table: 'CONTRACTS' (1973634124); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 113
> - Extents Scanned.......................: 16
> - Extent Switches.......................: 24
> - Avg. Pages per Extent..................: 7.1
> - Scan Density [Best Count:Actual Count]......: 60.00% [15:25]
> - Logical Scan Fragmentation ..............: 4.42%
> - Extent Scan Fragmentation ...............: 37.50%
> - Avg. Bytes Free per Page................: 121.5
> - Avg. Page Density (full)................: 98.50%
> So we can see that table (clustered index) is fragmented.
> dbcc indexdefrag ('gruz','contracts',1)
> dbcc showcontig ('contracts', 1)
> DBCC SHOWCONTIG scanning 'CONTRACTS' table...
> Table: 'CONTRACTS' (1973634124); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 112
> - Extents Scanned.......................: 16
> - Extent Switches.......................: 24
> - Avg. Pages per Extent..................: 7.0
> - Scan Density [Best Count:Actual Count]......: 56.00% [14:25]
> - Logical Scan Fragmentation ..............: 4.46%
> - Extent Scan Fragmentation ...............: 37.50%
> - Avg. Bytes Free per Page................: 50.3
> - Avg. Page Density (full)................: 99.38%
> The table is still fragmented.
> DBCC DBREINDEX ('cargo.dbo.contracts','PK_contracts')
> dbcc showcontig ('contracts', 1)
> DBCC SHOWCONTIG scanning 'CONTRACTS' table...
> Table: 'CONTRACTS' (1973634124); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 112
> - Extents Scanned.......................: 15
> - Extent Switches.......................: 14
> - Avg. Pages per Extent..................: 7.5
> - Scan Density [Best Count:Actual Count]......: 93.33% [14:15]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 20.00%
> - Avg. Bytes Free per Page................: 50.3
> - Avg. Page Density (full)................: 99.38%
> Now level of fragmentation significantly decreased
> but table is still FRAGMENTED! Not entire table located in contiguous
> space. For small tables final Scan Density will be 100% but the more
> table size the worse results we can get.
>
> > although INDEXDEFRAG will only work at the Leaf level of the index.
> > DBREINDEX will actually build a whole new table so if you have enough
free
> > contiguous space in the db it will completely rebuild the indexes.
> Yes, it will. But there is no guarantee that rebuilded index will be
> placed
> in contiguous space wholly. This is what I'm talking about.
> By the way I simply can't use DBREINDEX because my system works
> in 24x7 mode. That is why I'm trying to find the way to write my own
> defragmentation procedure.
>
> Serge Shakhov
>
>|||Andrew
> Do you have more than 1 file in your filegroup by any chance? If so the
> Extent Fragmentation value that is reported will generally be high due to
> the fact that Indexdefrag only works on a file at a time and does not swap
> pages between files.
Yes, there are two files in each filegroup and I wasn't surprised
by results of INDEXDEFRAG.
> As for the DBREINDEX you only have 15 extents and the switches were only
14.
> That's not too bad even though the stats might look high but that is due
to
> the small number of extents and possibly due to the first extent being a
> mixed one. This is quite normal.
I know. This is just small sample table. I can not start DBREINDEX on
large tables in production database.
> Even on larger tables you can never
> guarantee you will get 100% logical and physical defragmentation due to
the
> factors mentioned already. And you don't need it either.
Of course I'll be satisfied with 90-95% Scan Density.
The problem is that I can't achieve this result with INDEXDEFRAG
and can't stop database to rebuild indexes.
> Did you read the link I posted?
Not yet, I've just printed it... 17 pages and english is not my native
language...
Of course, I'll read it.
Serge Shakhov
does backup database need to specify file name?
hi, we are using SQL Server 2000 SP4, i am very new. Since i normally use
DB2, when i backup a database, i only need to designate the backup folder and
it will automatically generate the image file by using the timestamp as the
file name. But on SQL server, only specify the backup folder gives me error :
ODBC SQLState: 42000, cannot open backup device (which is the directory
name), device error, can some one help me on this?
thanks
Yes, you need to specify a file name. It is easy to generate using some TSQL code, though. For
example:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs' + CONVERT(char(8), CURRENT_TIMESTAMP, 112) + '.bak'
BACKUP DATABASE pubs
TO DISK = @.f
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:43F3E954-4DA6-4D3F-8F76-88DF689BBADD@.microsoft.com...
> hi, we are using SQL Server 2000 SP4, i am very new. Since i normally use
> DB2, when i backup a database, i only need to designate the backup folder and
> it will automatically generate the image file by using the timestamp as the
> file name. But on SQL server, only specify the backup folder gives me error :
> ODBC SQLState: 42000, cannot open backup device (which is the directory
> name), device error, can some one help me on this?
> thanks
|||but the backup image has not timestamp on it, only year, month, day, so if i
have multiple copies on the same day, what is the best way to distinct them?
thank you
|||I only posted an example. Read in Books Online about the CONVERT function and use a suitable
formatting code (3:rd parameter).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:9F07F525-8E2B-4984-BB97-D9810B6F730F@.microsoft.com...
> but the backup image has not timestamp on it, only year, month, day, so if i
> have multiple copies on the same day, what is the best way to distinct them?
> thank you
|||yes, i look at the third parameter, i try to use the one with time stamp
(e.g. 120), but they all contain space in the filename , which gives me error
,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
device off-line. See the SQL Server error log for more details. how do you do
normally?
thanks
|||The character ":" is not allowed in a file name. That is the reason for your error message. Use
another style, or use REPLACE to change those characters to something else:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs ' + REPLACE(CONVERT(char(19), CURRENT_TIMESTAMP, 120) + '.bak', ':', '.')
select @.f
BACKUP DATABASE pubs
TO DISK = @.f
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:74896E89-E698-446E-9D34-65380179FDB3@.microsoft.com...
> yes, i look at the third parameter, i try to use the one with time stamp
> (e.g. 120), but they all contain space in the filename , which gives me error
> ,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
> device off-line. See the SQL Server error log for more details. how do you do
> normally?
> thanks
DB2, when i backup a database, i only need to designate the backup folder and
it will automatically generate the image file by using the timestamp as the
file name. But on SQL server, only specify the backup folder gives me error :
ODBC SQLState: 42000, cannot open backup device (which is the directory
name), device error, can some one help me on this?
thanks
Yes, you need to specify a file name. It is easy to generate using some TSQL code, though. For
example:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs' + CONVERT(char(8), CURRENT_TIMESTAMP, 112) + '.bak'
BACKUP DATABASE pubs
TO DISK = @.f
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:43F3E954-4DA6-4D3F-8F76-88DF689BBADD@.microsoft.com...
> hi, we are using SQL Server 2000 SP4, i am very new. Since i normally use
> DB2, when i backup a database, i only need to designate the backup folder and
> it will automatically generate the image file by using the timestamp as the
> file name. But on SQL server, only specify the backup folder gives me error :
> ODBC SQLState: 42000, cannot open backup device (which is the directory
> name), device error, can some one help me on this?
> thanks
|||but the backup image has not timestamp on it, only year, month, day, so if i
have multiple copies on the same day, what is the best way to distinct them?
thank you
|||I only posted an example. Read in Books Online about the CONVERT function and use a suitable
formatting code (3:rd parameter).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:9F07F525-8E2B-4984-BB97-D9810B6F730F@.microsoft.com...
> but the backup image has not timestamp on it, only year, month, day, so if i
> have multiple copies on the same day, what is the best way to distinct them?
> thank you
|||yes, i look at the third parameter, i try to use the one with time stamp
(e.g. 120), but they all contain space in the filename , which gives me error
,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
device off-line. See the SQL Server error log for more details. how do you do
normally?
thanks
|||The character ":" is not allowed in a file name. That is the reason for your error message. Use
another style, or use REPLACE to change those characters to something else:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs ' + REPLACE(CONVERT(char(19), CURRENT_TIMESTAMP, 120) + '.bak', ':', '.')
select @.f
BACKUP DATABASE pubs
TO DISK = @.f
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:74896E89-E698-446E-9D34-65380179FDB3@.microsoft.com...
> yes, i look at the third parameter, i try to use the one with time stamp
> (e.g. 120), but they all contain space in the filename , which gives me error
> ,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
> device off-line. See the SQL Server error log for more details. how do you do
> normally?
> thanks
does backup database need to specify file name?
hi, we are using SQL Server 2000 SP4, i am very new. Since i normally use
DB2, when i backup a database, i only need to designate the backup folder an
d
it will automatically generate the image file by using the timestamp as the
file name. But on SQL server, only specify the backup folder gives me error
:
ODBC SQLState: 42000, cannot open backup device (which is the directory
name), device error, can some one help me on this?
thanksYes, you need to specify a file name. It is easy to generate using some TSQL
code, though. For
example:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs' + CONVERT(char(8), CURRENT_TIMESTAMP, 112) + '.bak'
BACKUP DATABASE pubs
TO DISK = @.f
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:43F3E954-4DA6-4D3F-8F76-88DF689BBADD@.microsoft.com...
> hi, we are using SQL Server 2000 SP4, i am very new. Since i normally use
> DB2, when i backup a database, i only need to designate the backup folder
and
> it will automatically generate the image file by using the timestamp as th
e
> file name. But on SQL server, only specify the backup folder gives me erro
r :
> ODBC SQLState: 42000, cannot open backup device (which is the directory
> name), device error, can some one help me on this?
> thanks|||but the backup image has not timestamp on it, only year, month, day, so if i
have multiple copies on the same day, what is the best way to distinct them?
thank you|||I only posted an example. Read in Books Online about the CONVERT function an
d use a suitable
formatting code (3:rd parameter).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:9F07F525-8E2B-4984-BB97-D9810B6F730F@.microsoft.com...
> but the backup image has not timestamp on it, only year, month, day, so if
i
> have multiple copies on the same day, what is the best way to distinct the
m?
> thank you|||yes, i look at the third parameter, i try to use the one with time stamp
(e.g. 120), but they all contain space in the filename , which gives me erro
r
,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
device off-line. See the SQL Server error log for more details. how do you d
o
normally?
thanks|||The character ":" is not allowed in a file name. That is the reason for your
error message. Use
another style, or use REPLACE to change those characters to something else:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs ' + REPLACE(CONVERT(char(19), CURRENT_TIMESTAMP, 120)
+ '.bak', ':', '.')
select @.f
BACKUP DATABASE pubs
TO DISK = @.f
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:74896E89-E698-446E-9D34-65380179FDB3@.microsoft.com...
> yes, i look at the third parameter, i try to use the one with time stamp
> (e.g. 120), but they all contain space in the filename , which gives me er
ror
> ,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
> device off-line. See the SQL Server error log for more details. how do you
do
> normally?
> thanks
DB2, when i backup a database, i only need to designate the backup folder an
d
it will automatically generate the image file by using the timestamp as the
file name. But on SQL server, only specify the backup folder gives me error
:
ODBC SQLState: 42000, cannot open backup device (which is the directory
name), device error, can some one help me on this?
thanksYes, you need to specify a file name. It is easy to generate using some TSQL
code, though. For
example:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs' + CONVERT(char(8), CURRENT_TIMESTAMP, 112) + '.bak'
BACKUP DATABASE pubs
TO DISK = @.f
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:43F3E954-4DA6-4D3F-8F76-88DF689BBADD@.microsoft.com...
> hi, we are using SQL Server 2000 SP4, i am very new. Since i normally use
> DB2, when i backup a database, i only need to designate the backup folder
and
> it will automatically generate the image file by using the timestamp as th
e
> file name. But on SQL server, only specify the backup folder gives me erro
r :
> ODBC SQLState: 42000, cannot open backup device (which is the directory
> name), device error, can some one help me on this?
> thanks|||but the backup image has not timestamp on it, only year, month, day, so if i
have multiple copies on the same day, what is the best way to distinct them?
thank you|||I only posted an example. Read in Books Online about the CONVERT function an
d use a suitable
formatting code (3:rd parameter).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:9F07F525-8E2B-4984-BB97-D9810B6F730F@.microsoft.com...
> but the backup image has not timestamp on it, only year, month, day, so if
i
> have multiple copies on the same day, what is the best way to distinct the
m?
> thank you|||yes, i look at the third parameter, i try to use the one with time stamp
(e.g. 120), but they all contain space in the filename , which gives me erro
r
,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
device off-line. See the SQL Server error log for more details. how do you d
o
normally?
thanks|||The character ":" is not allowed in a file name. That is the reason for your
error message. Use
another style, or use REPLACE to change those characters to something else:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs ' + REPLACE(CONVERT(char(19), CURRENT_TIMESTAMP, 120)
+ '.bak', ':', '.')
select @.f
BACKUP DATABASE pubs
TO DISK = @.f
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:74896E89-E698-446E-9D34-65380179FDB3@.microsoft.com...
> yes, i look at the third parameter, i try to use the one with time stamp
> (e.g. 120), but they all contain space in the filename , which gives me er
ror
> ,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
> device off-line. See the SQL Server error log for more details. how do you
do
> normally?
> thanks
does backup database need to specify file name?
hi, we are using SQL Server 2000 SP4, i am very new. Since i normally use
DB2, when i backup a database, i only need to designate the backup folder and
it will automatically generate the image file by using the timestamp as the
file name. But on SQL server, only specify the backup folder gives me error :
ODBC SQLState: 42000, cannot open backup device (which is the directory
name), device error, can some one help me on this?
thanksYes, you need to specify a file name. It is easy to generate using some TSQL code, though. For
example:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs' + CONVERT(char(8), CURRENT_TIMESTAMP, 112) + '.bak'
BACKUP DATABASE pubs
TO DISK = @.f
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:43F3E954-4DA6-4D3F-8F76-88DF689BBADD@.microsoft.com...
> hi, we are using SQL Server 2000 SP4, i am very new. Since i normally use
> DB2, when i backup a database, i only need to designate the backup folder and
> it will automatically generate the image file by using the timestamp as the
> file name. But on SQL server, only specify the backup folder gives me error :
> ODBC SQLState: 42000, cannot open backup device (which is the directory
> name), device error, can some one help me on this?
> thanks|||but the backup image has not timestamp on it, only year, month, day, so if i
have multiple copies on the same day, what is the best way to distinct them?
thank you|||I only posted an example. Read in Books Online about the CONVERT function and use a suitable
formatting code (3:rd parameter).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:9F07F525-8E2B-4984-BB97-D9810B6F730F@.microsoft.com...
> but the backup image has not timestamp on it, only year, month, day, so if i
> have multiple copies on the same day, what is the best way to distinct them?
> thank you|||yes, i look at the third parameter, i try to use the one with time stamp
(e.g. 120), but they all contain space in the filename , which gives me error
,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
device off-line. See the SQL Server error log for more details. how do you do
normally?
thanks|||The character ":" is not allowed in a file name. That is the reason for your error message. Use
another style, or use REPLACE to change those characters to something else:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs ' + REPLACE(CONVERT(char(19), CURRENT_TIMESTAMP, 120) + '.bak', ':', '.')
select @.f
BACKUP DATABASE pubs
TO DISK = @.f
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:74896E89-E698-446E-9D34-65380179FDB3@.microsoft.com...
> yes, i look at the third parameter, i try to use the one with time stamp
> (e.g. 120), but they all contain space in the filename , which gives me error
> ,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
> device off-line. See the SQL Server error log for more details. how do you do
> normally?
> thanks
DB2, when i backup a database, i only need to designate the backup folder and
it will automatically generate the image file by using the timestamp as the
file name. But on SQL server, only specify the backup folder gives me error :
ODBC SQLState: 42000, cannot open backup device (which is the directory
name), device error, can some one help me on this?
thanksYes, you need to specify a file name. It is easy to generate using some TSQL code, though. For
example:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs' + CONVERT(char(8), CURRENT_TIMESTAMP, 112) + '.bak'
BACKUP DATABASE pubs
TO DISK = @.f
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:43F3E954-4DA6-4D3F-8F76-88DF689BBADD@.microsoft.com...
> hi, we are using SQL Server 2000 SP4, i am very new. Since i normally use
> DB2, when i backup a database, i only need to designate the backup folder and
> it will automatically generate the image file by using the timestamp as the
> file name. But on SQL server, only specify the backup folder gives me error :
> ODBC SQLState: 42000, cannot open backup device (which is the directory
> name), device error, can some one help me on this?
> thanks|||but the backup image has not timestamp on it, only year, month, day, so if i
have multiple copies on the same day, what is the best way to distinct them?
thank you|||I only posted an example. Read in Books Online about the CONVERT function and use a suitable
formatting code (3:rd parameter).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:9F07F525-8E2B-4984-BB97-D9810B6F730F@.microsoft.com...
> but the backup image has not timestamp on it, only year, month, day, so if i
> have multiple copies on the same day, what is the best way to distinct them?
> thank you|||yes, i look at the third parameter, i try to use the one with time stamp
(e.g. 120), but they all contain space in the filename , which gives me error
,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
device off-line. See the SQL Server error log for more details. how do you do
normally?
thanks|||The character ":" is not allowed in a file name. That is the reason for your error message. Use
another style, or use REPLACE to change those characters to something else:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs ' + REPLACE(CONVERT(char(19), CURRENT_TIMESTAMP, 120) + '.bak', ':', '.')
select @.f
BACKUP DATABASE pubs
TO DISK = @.f
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:74896E89-E698-446E-9D34-65380179FDB3@.microsoft.com...
> yes, i look at the third parameter, i try to use the one with time stamp
> (e.g. 120), but they all contain space in the filename , which gives me error
> ,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
> device off-line. See the SQL Server error log for more details. how do you do
> normally?
> thanks
Friday, February 24, 2012
Does a Full Backup include transaction logs
In MS SQL 2005 when you do a Full Backup does it also backup and
truncate the transaction logs or do I need to back the transaction
logs up separately?
Thanks.
Brian
Full backups do not mark any log segments as inactive, Therefore no segments
will be truncated following a full backup. Only log backup mark segments as
inactive. Full backups do not interrupt the log backup chain.
This is so if you have a bad full backup, you can go to an earlier full
backup and restore logs through the time of the bad backup and up to
current.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1184253274.265128.108590@.w3g2000hsg.googlegro ups.com...
> In MS SQL 2005 when you do a Full Backup does it also backup and
> truncate the transaction logs or do I need to back the transaction
> logs up separately?
> Thanks.
> Brian
>
|||Brian D (bdaltilio@.yahoo.com) writes:
> In MS SQL 2005 when you do a Full Backup does it also backup and
> truncate the transaction logs or do I need to back the transaction
> logs up separately?
You need to backup the transaction log separately. The rationale is that
the last recent backup may have gone lost, or be broken. If the log
backups are OK (and you saved them), you can recover from an older
full backup + the translog backups.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Erland,
We do a full backup daily at 6pm. If we do a trans log backup 4 times
a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
immediately before or after the full backup or can we just skip the
6pm one all together? If I skip the 6pm trans log backup should I
truncate the log immediately after the full backup?
Brian
|||Brian,
If you want to save a little bit of backup space, run the 6pm log backup
just before the full backup. If you run it after, you will have backed up
the 12pm-6pm log activities twice.
Do NOT truncate the log ever, if you want to be able to restore the full
backup and then apply the logs to the full backup. If you were to backup
the database, then truncate the 6pm logs, the 12am, 6am, 12pm log backups
are all useless, since there is an unbridgeable transaction log gap between
the full backup and the first log.
RLF
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1184261915.544240.106090@.d55g2000hsg.googlegr oups.com...
> Erland,
> We do a full backup daily at 6pm. If we do a trans log backup 4 times
> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
> immediately before or after the full backup or can we just skip the
> 6pm one all together? If I skip the 6pm trans log backup should I
> truncate the log immediately after the full backup?
> Brian
>
|||Brian D (bdaltilio@.yahoo.com) writes:
> We do a full backup daily at 6pm. If we do a trans log backup 4 times
> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
> immediately before or after the full backup or can we just skip the
> 6pm one all together? If I skip the 6pm trans log backup should I
> truncate the log immediately after the full backup?
First ask yourself: if the database goes belly-up, how much can we afford
to lose? In the case, the database file goes bad, there is a possibility
to take a final backup of the log. But if the log file disappears, this
means that you could lose up almost six hours of work. Can you afford
that?
No, that is it not a leading question. There are businesses where even
the loss of five minutes of data is a disaster. And there are businesses
where a full backup once a night without log backup is perfectly sufficient.
I just want you to evaluate where you fit in. Taking log backups as rarely
as you do, appears a bit unusual, so it might be that your business is
content with restoring the backup from last night. In which case, dealing
with the log is just extra overhead for you. For the rest of the post I will
nevertheless assume that this six-hour window is right for you.
I can't see that it matter whether you back up the log before or after
the full backup, but you should back up the log sometime there. In a
way log backups and full backups are independent of each other.
Russell mentioned that you should never truncate the transaction log.
I like to point out another thing. Where do you write the transaction log
dumps? Do you append them to the same device? Do you ever use WITH INIT?
Here is something to be careful with so that you don't lose part of a
log chain.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I agree completely with Erland. In my experience, I have never been
rewarded or punished because I did or did not have a backup. I was always
judged on whether I had a working recovery plan. Start from the recovery
side and build a complete plan, including a backup plan, that meets the
business needs. Finally, test your recoery plan to see that it actually
works and that it can be done in the agreed-upon time.
If you don't test, you have a recovery hope, not a recovery plan.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns996C38071EF5Yazorman@.127.0.0.1...
> Brian D (bdaltilio@.yahoo.com) writes:
> First ask yourself: if the database goes belly-up, how much can we afford
> to lose? In the case, the database file goes bad, there is a possibility
> to take a final backup of the log. But if the log file disappears, this
> means that you could lose up almost six hours of work. Can you afford
> that?
> No, that is it not a leading question. There are businesses where even
> the loss of five minutes of data is a disaster. And there are businesses
> where a full backup once a night without log backup is perfectly
> sufficient.
> I just want you to evaluate where you fit in. Taking log backups as rarely
> as you do, appears a bit unusual, so it might be that your business is
> content with restoring the backup from last night. In which case, dealing
> with the log is just extra overhead for you. For the rest of the post I
> will
> nevertheless assume that this six-hour window is right for you.
> I can't see that it matter whether you back up the log before or after
> the full backup, but you should back up the log sometime there. In a
> way log backups and full backups are independent of each other.
> Russell mentioned that you should never truncate the transaction log.
> I like to point out another thing. Where do you write the transaction log
> dumps? Do you append them to the same device? Do you ever use WITH INIT?
> Here is something to be careful with so that you don't lose part of a
> log chain.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
truncate the transaction logs or do I need to back the transaction
logs up separately?
Thanks.
Brian
Full backups do not mark any log segments as inactive, Therefore no segments
will be truncated following a full backup. Only log backup mark segments as
inactive. Full backups do not interrupt the log backup chain.
This is so if you have a bad full backup, you can go to an earlier full
backup and restore logs through the time of the bad backup and up to
current.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1184253274.265128.108590@.w3g2000hsg.googlegro ups.com...
> In MS SQL 2005 when you do a Full Backup does it also backup and
> truncate the transaction logs or do I need to back the transaction
> logs up separately?
> Thanks.
> Brian
>
|||Brian D (bdaltilio@.yahoo.com) writes:
> In MS SQL 2005 when you do a Full Backup does it also backup and
> truncate the transaction logs or do I need to back the transaction
> logs up separately?
You need to backup the transaction log separately. The rationale is that
the last recent backup may have gone lost, or be broken. If the log
backups are OK (and you saved them), you can recover from an older
full backup + the translog backups.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Erland,
We do a full backup daily at 6pm. If we do a trans log backup 4 times
a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
immediately before or after the full backup or can we just skip the
6pm one all together? If I skip the 6pm trans log backup should I
truncate the log immediately after the full backup?
Brian
|||Brian,
If you want to save a little bit of backup space, run the 6pm log backup
just before the full backup. If you run it after, you will have backed up
the 12pm-6pm log activities twice.
Do NOT truncate the log ever, if you want to be able to restore the full
backup and then apply the logs to the full backup. If you were to backup
the database, then truncate the 6pm logs, the 12am, 6am, 12pm log backups
are all useless, since there is an unbridgeable transaction log gap between
the full backup and the first log.
RLF
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1184261915.544240.106090@.d55g2000hsg.googlegr oups.com...
> Erland,
> We do a full backup daily at 6pm. If we do a trans log backup 4 times
> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
> immediately before or after the full backup or can we just skip the
> 6pm one all together? If I skip the 6pm trans log backup should I
> truncate the log immediately after the full backup?
> Brian
>
|||Brian D (bdaltilio@.yahoo.com) writes:
> We do a full backup daily at 6pm. If we do a trans log backup 4 times
> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
> immediately before or after the full backup or can we just skip the
> 6pm one all together? If I skip the 6pm trans log backup should I
> truncate the log immediately after the full backup?
First ask yourself: if the database goes belly-up, how much can we afford
to lose? In the case, the database file goes bad, there is a possibility
to take a final backup of the log. But if the log file disappears, this
means that you could lose up almost six hours of work. Can you afford
that?
No, that is it not a leading question. There are businesses where even
the loss of five minutes of data is a disaster. And there are businesses
where a full backup once a night without log backup is perfectly sufficient.
I just want you to evaluate where you fit in. Taking log backups as rarely
as you do, appears a bit unusual, so it might be that your business is
content with restoring the backup from last night. In which case, dealing
with the log is just extra overhead for you. For the rest of the post I will
nevertheless assume that this six-hour window is right for you.
I can't see that it matter whether you back up the log before or after
the full backup, but you should back up the log sometime there. In a
way log backups and full backups are independent of each other.
Russell mentioned that you should never truncate the transaction log.
I like to point out another thing. Where do you write the transaction log
dumps? Do you append them to the same device? Do you ever use WITH INIT?
Here is something to be careful with so that you don't lose part of a
log chain.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I agree completely with Erland. In my experience, I have never been
rewarded or punished because I did or did not have a backup. I was always
judged on whether I had a working recovery plan. Start from the recovery
side and build a complete plan, including a backup plan, that meets the
business needs. Finally, test your recoery plan to see that it actually
works and that it can be done in the agreed-upon time.
If you don't test, you have a recovery hope, not a recovery plan.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns996C38071EF5Yazorman@.127.0.0.1...
> Brian D (bdaltilio@.yahoo.com) writes:
> First ask yourself: if the database goes belly-up, how much can we afford
> to lose? In the case, the database file goes bad, there is a possibility
> to take a final backup of the log. But if the log file disappears, this
> means that you could lose up almost six hours of work. Can you afford
> that?
> No, that is it not a leading question. There are businesses where even
> the loss of five minutes of data is a disaster. And there are businesses
> where a full backup once a night without log backup is perfectly
> sufficient.
> I just want you to evaluate where you fit in. Taking log backups as rarely
> as you do, appears a bit unusual, so it might be that your business is
> content with restoring the backup from last night. In which case, dealing
> with the log is just extra overhead for you. For the rest of the post I
> will
> nevertheless assume that this six-hour window is right for you.
> I can't see that it matter whether you back up the log before or after
> the full backup, but you should back up the log sometime there. In a
> way log backups and full backups are independent of each other.
> Russell mentioned that you should never truncate the transaction log.
> I like to point out another thing. Where do you write the transaction log
> dumps? Do you append them to the same device? Do you ever use WITH INIT?
> Here is something to be careful with so that you don't lose part of a
> log chain.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Does a Full Backup include transaction logs
In MS SQL 2005 when you do a Full Backup does it also backup and
truncate the transaction logs or do I need to back the transaction
logs up separately?
You need to backup the transaction log separately. The rationale is that
the last recent backup may have gone lost, or be broken. If the log
backups are OK (and you saved them), you can recover from an older
full backup + the translog backups.
First ask yourself: if the database goes belly-up, how much can we afford
to lose? In the case, the database file goes bad, there is a possibility
to take a final backup of the log. But if the log file disappears, this
means that you could lose up almost six hours of work. Can you afford
that?
truncate the transaction logs or do I need to back the transaction
logs up separately?
Thanks.
BrianBrian D (bdaltilio@.yahoo.com) writes:
Quote:
Originally Posted by
In MS SQL 2005 when you do a Full Backup does it also backup and
truncate the transaction logs or do I need to back the transaction
logs up separately?
You need to backup the transaction log separately. The rationale is that
the last recent backup may have gone lost, or be broken. If the log
backups are OK (and you saved them), you can recover from an older
full backup + the translog backups.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
We do a full backup daily at 6pm. If we do a trans log backup 4 times
a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
immediately before or after the full backup or can we just skip the
6pm one all together? If I skip the 6pm trans log backup should I
truncate the log immediately after the full backup?
Brian|||Brian,
If you want to save a little bit of backup space, run the 6pm log backup
just before the full backup. If you run it after, you will have backed up
the 12pm-6pm log activities twice.
Do NOT truncate the log ever, if you want to be able to restore the full
backup and then apply the logs to the full backup. If you were to backup
the database, then truncate the 6pm logs, the 12am, 6am, 12pm log backups
are all useless, since there is an unbridgeable transaction log gap between
the full backup and the first log.
RLF
"Brian D" <bdaltilio@.yahoo.comwrote in message
news:1184261915.544240.106090@.d55g2000hsg.googlegr oups.com...
Quote:
Originally Posted by
Erland,
>
We do a full backup daily at 6pm. If we do a trans log backup 4 times
a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
immediately before or after the full backup or can we just skip the
6pm one all together? If I skip the 6pm trans log backup should I
truncate the log immediately after the full backup?
>
Brian
>
Quote:
Originally Posted by
We do a full backup daily at 6pm. If we do a trans log backup 4 times
a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
immediately before or after the full backup or can we just skip the
6pm one all together? If I skip the 6pm trans log backup should I
truncate the log immediately after the full backup?
First ask yourself: if the database goes belly-up, how much can we afford
to lose? In the case, the database file goes bad, there is a possibility
to take a final backup of the log. But if the log file disappears, this
means that you could lose up almost six hours of work. Can you afford
that?
No, that is it not a leading question. There are businesses where even
the loss of five minutes of data is a disaster. And there are businesses
where a full backup once a night without log backup is perfectly sufficient.
I just want you to evaluate where you fit in. Taking log backups as rarely
as you do, appears a bit unusual, so it might be that your business is
content with restoring the backup from last night. In which case, dealing
with the log is just extra overhead for you. For the rest of the post I will
nevertheless assume that this six-hour window is right for you.
I can't see that it matter whether you back up the log before or after
the full backup, but you should back up the log sometime there. In a
way log backups and full backups are independent of each other.
Russell mentioned that you should never truncate the transaction log.
I like to point out another thing. Where do you write the transaction log
dumps? Do you append them to the same device? Do you ever use WITH INIT?
Here is something to be careful with so that you don't lose part of a
log chain.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Does a Full Backup include transaction logs
In MS SQL 2005 when you do a Full Backup does it also backup and
truncate the transaction logs or do I need to back the transaction
logs up separately?
Thanks.
BrianFull backups do not mark any log segments as inactive, Therefore no segments
will be truncated following a full backup. Only log backup mark segments as
inactive. Full backups do not interrupt the log backup chain.
This is so if you have a bad full backup, you can go to an earlier full
backup and restore logs through the time of the bad backup and up to
current.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1184253274.265128.108590@.w3g2000hsg.googlegroups.com...
> In MS SQL 2005 when you do a Full Backup does it also backup and
> truncate the transaction logs or do I need to back the transaction
> logs up separately?
> Thanks.
> Brian
>|||Brian D (bdaltilio@.yahoo.com) writes:
> In MS SQL 2005 when you do a Full Backup does it also backup and
> truncate the transaction logs or do I need to back the transaction
> logs up separately?
You need to backup the transaction log separately. The rationale is that
the last recent backup may have gone lost, or be broken. If the log
backups are OK (and you saved them), you can recover from an older
full backup + the translog backups.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Erland,
We do a full backup daily at 6pm. If we do a trans log backup 4 times
a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
immediately before or after the full backup or can we just skip the
6pm one all together? If I skip the 6pm trans log backup should I
truncate the log immediately after the full backup?
Brian|||Brian,
If you want to save a little bit of backup space, run the 6pm log backup
just before the full backup. If you run it after, you will have backed up
the 12pm-6pm log activities twice.
Do NOT truncate the log ever, if you want to be able to restore the full
backup and then apply the logs to the full backup. If you were to backup
the database, then truncate the 6pm logs, the 12am, 6am, 12pm log backups
are all useless, since there is an unbridgeable transaction log gap between
the full backup and the first log.
RLF
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1184261915.544240.106090@.d55g2000hsg.googlegroups.com...
> Erland,
> We do a full backup daily at 6pm. If we do a trans log backup 4 times
> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
> immediately before or after the full backup or can we just skip the
> 6pm one all together? If I skip the 6pm trans log backup should I
> truncate the log immediately after the full backup?
> Brian
>|||Brian D (bdaltilio@.yahoo.com) writes:
> We do a full backup daily at 6pm. If we do a trans log backup 4 times
> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
> immediately before or after the full backup or can we just skip the
> 6pm one all together? If I skip the 6pm trans log backup should I
> truncate the log immediately after the full backup?
First ask yourself: if the database goes belly-up, how much can we afford
to lose? In the case, the database file goes bad, there is a possibility
to take a final backup of the log. But if the log file disappears, this
means that you could lose up almost six hours of work. Can you afford
that?
No, that is it not a leading question. There are businesses where even
the loss of five minutes of data is a disaster. And there are businesses
where a full backup once a night without log backup is perfectly sufficient.
I just want you to evaluate where you fit in. Taking log backups as rarely
as you do, appears a bit unusual, so it might be that your business is
content with restoring the backup from last night. In which case, dealing
with the log is just extra overhead for you. For the rest of the post I will
nevertheless assume that this six-hour window is right for you.
I can't see that it matter whether you back up the log before or after
the full backup, but you should back up the log sometime there. In a
way log backups and full backups are independent of each other.
Russell mentioned that you should never truncate the transaction log.
I like to point out another thing. Where do you write the transaction log
dumps? Do you append them to the same device? Do you ever use WITH INIT?
Here is something to be careful with so that you don't lose part of a
log chain.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||I agree completely with Erland. In my experience, I have never been
rewarded or punished because I did or did not have a backup. I was always
judged on whether I had a working recovery plan. Start from the recovery
side and build a complete plan, including a backup plan, that meets the
business needs. Finally, test your recoery plan to see that it actually
works and that it can be done in the agreed-upon time.
If you don't test, you have a recovery hope, not a recovery plan.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns996C38071EF5Yazorman@.127.0.0.1...
> Brian D (bdaltilio@.yahoo.com) writes:
>> We do a full backup daily at 6pm. If we do a trans log backup 4 times
>> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
>> immediately before or after the full backup or can we just skip the
>> 6pm one all together? If I skip the 6pm trans log backup should I
>> truncate the log immediately after the full backup?
> First ask yourself: if the database goes belly-up, how much can we afford
> to lose? In the case, the database file goes bad, there is a possibility
> to take a final backup of the log. But if the log file disappears, this
> means that you could lose up almost six hours of work. Can you afford
> that?
> No, that is it not a leading question. There are businesses where even
> the loss of five minutes of data is a disaster. And there are businesses
> where a full backup once a night without log backup is perfectly
> sufficient.
> I just want you to evaluate where you fit in. Taking log backups as rarely
> as you do, appears a bit unusual, so it might be that your business is
> content with restoring the backup from last night. In which case, dealing
> with the log is just extra overhead for you. For the rest of the post I
> will
> nevertheless assume that this six-hour window is right for you.
> I can't see that it matter whether you back up the log before or after
> the full backup, but you should back up the log sometime there. In a
> way log backups and full backups are independent of each other.
> Russell mentioned that you should never truncate the transaction log.
> I like to point out another thing. Where do you write the transaction log
> dumps? Do you append them to the same device? Do you ever use WITH INIT?
> Here is something to be careful with so that you don't lose part of a
> log chain.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
truncate the transaction logs or do I need to back the transaction
logs up separately?
Thanks.
BrianFull backups do not mark any log segments as inactive, Therefore no segments
will be truncated following a full backup. Only log backup mark segments as
inactive. Full backups do not interrupt the log backup chain.
This is so if you have a bad full backup, you can go to an earlier full
backup and restore logs through the time of the bad backup and up to
current.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1184253274.265128.108590@.w3g2000hsg.googlegroups.com...
> In MS SQL 2005 when you do a Full Backup does it also backup and
> truncate the transaction logs or do I need to back the transaction
> logs up separately?
> Thanks.
> Brian
>|||Brian D (bdaltilio@.yahoo.com) writes:
> In MS SQL 2005 when you do a Full Backup does it also backup and
> truncate the transaction logs or do I need to back the transaction
> logs up separately?
You need to backup the transaction log separately. The rationale is that
the last recent backup may have gone lost, or be broken. If the log
backups are OK (and you saved them), you can recover from an older
full backup + the translog backups.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Erland,
We do a full backup daily at 6pm. If we do a trans log backup 4 times
a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
immediately before or after the full backup or can we just skip the
6pm one all together? If I skip the 6pm trans log backup should I
truncate the log immediately after the full backup?
Brian|||Brian,
If you want to save a little bit of backup space, run the 6pm log backup
just before the full backup. If you run it after, you will have backed up
the 12pm-6pm log activities twice.
Do NOT truncate the log ever, if you want to be able to restore the full
backup and then apply the logs to the full backup. If you were to backup
the database, then truncate the 6pm logs, the 12am, 6am, 12pm log backups
are all useless, since there is an unbridgeable transaction log gap between
the full backup and the first log.
RLF
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1184261915.544240.106090@.d55g2000hsg.googlegroups.com...
> Erland,
> We do a full backup daily at 6pm. If we do a trans log backup 4 times
> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
> immediately before or after the full backup or can we just skip the
> 6pm one all together? If I skip the 6pm trans log backup should I
> truncate the log immediately after the full backup?
> Brian
>|||Brian D (bdaltilio@.yahoo.com) writes:
> We do a full backup daily at 6pm. If we do a trans log backup 4 times
> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
> immediately before or after the full backup or can we just skip the
> 6pm one all together? If I skip the 6pm trans log backup should I
> truncate the log immediately after the full backup?
First ask yourself: if the database goes belly-up, how much can we afford
to lose? In the case, the database file goes bad, there is a possibility
to take a final backup of the log. But if the log file disappears, this
means that you could lose up almost six hours of work. Can you afford
that?
No, that is it not a leading question. There are businesses where even
the loss of five minutes of data is a disaster. And there are businesses
where a full backup once a night without log backup is perfectly sufficient.
I just want you to evaluate where you fit in. Taking log backups as rarely
as you do, appears a bit unusual, so it might be that your business is
content with restoring the backup from last night. In which case, dealing
with the log is just extra overhead for you. For the rest of the post I will
nevertheless assume that this six-hour window is right for you.
I can't see that it matter whether you back up the log before or after
the full backup, but you should back up the log sometime there. In a
way log backups and full backups are independent of each other.
Russell mentioned that you should never truncate the transaction log.
I like to point out another thing. Where do you write the transaction log
dumps? Do you append them to the same device? Do you ever use WITH INIT?
Here is something to be careful with so that you don't lose part of a
log chain.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||I agree completely with Erland. In my experience, I have never been
rewarded or punished because I did or did not have a backup. I was always
judged on whether I had a working recovery plan. Start from the recovery
side and build a complete plan, including a backup plan, that meets the
business needs. Finally, test your recoery plan to see that it actually
works and that it can be done in the agreed-upon time.
If you don't test, you have a recovery hope, not a recovery plan.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns996C38071EF5Yazorman@.127.0.0.1...
> Brian D (bdaltilio@.yahoo.com) writes:
>> We do a full backup daily at 6pm. If we do a trans log backup 4 times
>> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
>> immediately before or after the full backup or can we just skip the
>> 6pm one all together? If I skip the 6pm trans log backup should I
>> truncate the log immediately after the full backup?
> First ask yourself: if the database goes belly-up, how much can we afford
> to lose? In the case, the database file goes bad, there is a possibility
> to take a final backup of the log. But if the log file disappears, this
> means that you could lose up almost six hours of work. Can you afford
> that?
> No, that is it not a leading question. There are businesses where even
> the loss of five minutes of data is a disaster. And there are businesses
> where a full backup once a night without log backup is perfectly
> sufficient.
> I just want you to evaluate where you fit in. Taking log backups as rarely
> as you do, appears a bit unusual, so it might be that your business is
> content with restoring the backup from last night. In which case, dealing
> with the log is just extra overhead for you. For the rest of the post I
> will
> nevertheless assume that this six-hour window is right for you.
> I can't see that it matter whether you back up the log before or after
> the full backup, but you should back up the log sometime there. In a
> way log backups and full backups are independent of each other.
> Russell mentioned that you should never truncate the transaction log.
> I like to point out another thing. Where do you write the transaction log
> dumps? Do you append them to the same device? Do you ever use WITH INIT?
> Here is something to be careful with so that you don't lose part of a
> log chain.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Does a Full Backup include transaction logs
In MS SQL 2005 when you do a Full Backup does it also backup and
truncate the transaction logs or do I need to back the transaction
logs up separately?
Thanks.
BrianFull backups do not mark any log segments as inactive, Therefore no segments
will be truncated following a full backup. Only log backup mark segments as
inactive. Full backups do not interrupt the log backup chain.
This is so if you have a bad full backup, you can go to an earlier full
backup and restore logs through the time of the bad backup and up to
current.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1184253274.265128.108590@.w3g2000hsg.googlegroups.com...
> In MS SQL 2005 when you do a Full Backup does it also backup and
> truncate the transaction logs or do I need to back the transaction
> logs up separately?
> Thanks.
> Brian
>|||Brian D (bdaltilio@.yahoo.com) writes:
> In MS SQL 2005 when you do a Full Backup does it also backup and
> truncate the transaction logs or do I need to back the transaction
> logs up separately?
You need to backup the transaction log separately. The rationale is that
the last recent backup may have gone lost, or be broken. If the log
backups are OK (and you saved them), you can recover from an older
full backup + the translog backups.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
We do a full backup daily at 6pm. If we do a trans log backup 4 times
a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
immediately before or after the full backup or can we just skip the
6pm one all together? If I skip the 6pm trans log backup should I
truncate the log immediately after the full backup?
Brian|||Brian,
If you want to save a little bit of backup space, run the 6pm log backup
just before the full backup. If you run it after, you will have backed up
the 12pm-6pm log activities twice.
Do NOT truncate the log ever, if you want to be able to restore the full
backup and then apply the logs to the full backup. If you were to backup
the database, then truncate the 6pm logs, the 12am, 6am, 12pm log backups
are all useless, since there is an unbridgeable transaction log gap between
the full backup and the first log.
RLF
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1184261915.544240.106090@.d55g2000hsg.googlegroups.com...
> Erland,
> We do a full backup daily at 6pm. If we do a trans log backup 4 times
> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
> immediately before or after the full backup or can we just skip the
> 6pm one all together? If I skip the 6pm trans log backup should I
> truncate the log immediately after the full backup?
> Brian
>|||Brian D (bdaltilio@.yahoo.com) writes:
> We do a full backup daily at 6pm. If we do a trans log backup 4 times
> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
> immediately before or after the full backup or can we just skip the
> 6pm one all together? If I skip the 6pm trans log backup should I
> truncate the log immediately after the full backup?
First ask yourself: if the database goes belly-up, how much can we afford
to lose? In the case, the database file goes bad, there is a possibility
to take a final backup of the log. But if the log file disappears, this
means that you could lose up almost six hours of work. Can you afford
that?
No, that is it not a leading question. There are businesses where even
the loss of five minutes of data is a disaster. And there are businesses
where a full backup once a night without log backup is perfectly sufficient.
I just want you to evaluate where you fit in. Taking log backups as rarely
as you do, appears a bit unusual, so it might be that your business is
content with restoring the backup from last night. In which case, dealing
with the log is just extra overhead for you. For the rest of the post I will
nevertheless assume that this six-hour window is right for you.
I can't see that it matter whether you back up the log before or after
the full backup, but you should back up the log sometime there. In a
way log backups and full backups are independent of each other.
Russell mentioned that you should never truncate the transaction log.
I like to point out another thing. Where do you write the transaction log
dumps? Do you append them to the same device? Do you ever use WITH INIT?
Here is something to be careful with so that you don't lose part of a
log chain.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I agree completely with Erland. In my experience, I have never been
rewarded or punished because I did or did not have a backup. I was always
judged on whether I had a working recovery plan. Start from the recovery
side and build a complete plan, including a backup plan, that meets the
business needs. Finally, test your recoery plan to see that it actually
works and that it can be done in the agreed-upon time.
If you don't test, you have a recovery hope, not a recovery plan.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns996C38071EF5Yazorman@.127.0.0.1...
> Brian D (bdaltilio@.yahoo.com) writes:
> First ask yourself: if the database goes belly-up, how much can we afford
> to lose? In the case, the database file goes bad, there is a possibility
> to take a final backup of the log. But if the log file disappears, this
> means that you could lose up almost six hours of work. Can you afford
> that?
> No, that is it not a leading question. There are businesses where even
> the loss of five minutes of data is a disaster. And there are businesses
> where a full backup once a night without log backup is perfectly
> sufficient.
> I just want you to evaluate where you fit in. Taking log backups as rarely
> as you do, appears a bit unusual, so it might be that your business is
> content with restoring the backup from last night. In which case, dealing
> with the log is just extra overhead for you. For the rest of the post I
> will
> nevertheless assume that this six-hour window is right for you.
> I can't see that it matter whether you back up the log before or after
> the full backup, but you should back up the log sometime there. In a
> way log backups and full backups are independent of each other.
> Russell mentioned that you should never truncate the transaction log.
> I like to point out another thing. Where do you write the transaction log
> dumps? Do you append them to the same device? Do you ever use WITH INIT?
> Here is something to be careful with so that you don't lose part of a
> log chain.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
truncate the transaction logs or do I need to back the transaction
logs up separately?
Thanks.
BrianFull backups do not mark any log segments as inactive, Therefore no segments
will be truncated following a full backup. Only log backup mark segments as
inactive. Full backups do not interrupt the log backup chain.
This is so if you have a bad full backup, you can go to an earlier full
backup and restore logs through the time of the bad backup and up to
current.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1184253274.265128.108590@.w3g2000hsg.googlegroups.com...
> In MS SQL 2005 when you do a Full Backup does it also backup and
> truncate the transaction logs or do I need to back the transaction
> logs up separately?
> Thanks.
> Brian
>|||Brian D (bdaltilio@.yahoo.com) writes:
> In MS SQL 2005 when you do a Full Backup does it also backup and
> truncate the transaction logs or do I need to back the transaction
> logs up separately?
You need to backup the transaction log separately. The rationale is that
the last recent backup may have gone lost, or be broken. If the log
backups are OK (and you saved them), you can recover from an older
full backup + the translog backups.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
We do a full backup daily at 6pm. If we do a trans log backup 4 times
a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
immediately before or after the full backup or can we just skip the
6pm one all together? If I skip the 6pm trans log backup should I
truncate the log immediately after the full backup?
Brian|||Brian,
If you want to save a little bit of backup space, run the 6pm log backup
just before the full backup. If you run it after, you will have backed up
the 12pm-6pm log activities twice.
Do NOT truncate the log ever, if you want to be able to restore the full
backup and then apply the logs to the full backup. If you were to backup
the database, then truncate the 6pm logs, the 12am, 6am, 12pm log backups
are all useless, since there is an unbridgeable transaction log gap between
the full backup and the first log.
RLF
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1184261915.544240.106090@.d55g2000hsg.googlegroups.com...
> Erland,
> We do a full backup daily at 6pm. If we do a trans log backup 4 times
> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
> immediately before or after the full backup or can we just skip the
> 6pm one all together? If I skip the 6pm trans log backup should I
> truncate the log immediately after the full backup?
> Brian
>|||Brian D (bdaltilio@.yahoo.com) writes:
> We do a full backup daily at 6pm. If we do a trans log backup 4 times
> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
> immediately before or after the full backup or can we just skip the
> 6pm one all together? If I skip the 6pm trans log backup should I
> truncate the log immediately after the full backup?
First ask yourself: if the database goes belly-up, how much can we afford
to lose? In the case, the database file goes bad, there is a possibility
to take a final backup of the log. But if the log file disappears, this
means that you could lose up almost six hours of work. Can you afford
that?
No, that is it not a leading question. There are businesses where even
the loss of five minutes of data is a disaster. And there are businesses
where a full backup once a night without log backup is perfectly sufficient.
I just want you to evaluate where you fit in. Taking log backups as rarely
as you do, appears a bit unusual, so it might be that your business is
content with restoring the backup from last night. In which case, dealing
with the log is just extra overhead for you. For the rest of the post I will
nevertheless assume that this six-hour window is right for you.
I can't see that it matter whether you back up the log before or after
the full backup, but you should back up the log sometime there. In a
way log backups and full backups are independent of each other.
Russell mentioned that you should never truncate the transaction log.
I like to point out another thing. Where do you write the transaction log
dumps? Do you append them to the same device? Do you ever use WITH INIT?
Here is something to be careful with so that you don't lose part of a
log chain.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I agree completely with Erland. In my experience, I have never been
rewarded or punished because I did or did not have a backup. I was always
judged on whether I had a working recovery plan. Start from the recovery
side and build a complete plan, including a backup plan, that meets the
business needs. Finally, test your recoery plan to see that it actually
works and that it can be done in the agreed-upon time.
If you don't test, you have a recovery hope, not a recovery plan.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns996C38071EF5Yazorman@.127.0.0.1...
> Brian D (bdaltilio@.yahoo.com) writes:
> First ask yourself: if the database goes belly-up, how much can we afford
> to lose? In the case, the database file goes bad, there is a possibility
> to take a final backup of the log. But if the log file disappears, this
> means that you could lose up almost six hours of work. Can you afford
> that?
> No, that is it not a leading question. There are businesses where even
> the loss of five minutes of data is a disaster. And there are businesses
> where a full backup once a night without log backup is perfectly
> sufficient.
> I just want you to evaluate where you fit in. Taking log backups as rarely
> as you do, appears a bit unusual, so it might be that your business is
> content with restoring the backup from last night. In which case, dealing
> with the log is just extra overhead for you. For the rest of the post I
> will
> nevertheless assume that this six-hour window is right for you.
> I can't see that it matter whether you back up the log before or after
> the full backup, but you should back up the log sometime there. In a
> way log backups and full backups are independent of each other.
> Russell mentioned that you should never truncate the transaction log.
> I like to point out another thing. Where do you write the transaction log
> dumps? Do you append them to the same device? Do you ever use WITH INIT?
> Here is something to be careful with so that you don't lose part of a
> log chain.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
Does a Full Backup include data changes made during the backup?
If my backup starts at 8PM and take 1 hour to complete, will the changes made to the database during that hour be captured in the full backup?
Stated another way, will my backup be a snapshot of:
a) 8PM when the backup started
b) 8PM with some of the changes made between the hour
c) 9PM when the backup finished?
Anybody know the exact way SQL Server handles that logic?
Thanks,
MarcFrom BOL (full backups [SQL Server]):
"A full backup (formerly known as a database backup) backs up the entire database, including part of the transaction log (so that the full backup can be recovered). Full backups represent the database at the time the backup completed. The transaction log included in the full backup allows it to be used to recover the database to the point in time at which the backup was completed. Creating a full backup is a single operation, usually scheduled to occur at regular intervals. "|||I remember reading in SAMS SQL 2000 DBA Guide, that the backup processes uses logic to make certain that the backup represents the state of the database when the backup is completed.
Regards,
hmscott
Stated another way, will my backup be a snapshot of:
a) 8PM when the backup started
b) 8PM with some of the changes made between the hour
c) 9PM when the backup finished?
Anybody know the exact way SQL Server handles that logic?
Thanks,
MarcFrom BOL (full backups [SQL Server]):
"A full backup (formerly known as a database backup) backs up the entire database, including part of the transaction log (so that the full backup can be recovered). Full backups represent the database at the time the backup completed. The transaction log included in the full backup allows it to be used to recover the database to the point in time at which the backup was completed. Creating a full backup is a single operation, usually scheduled to occur at regular intervals. "|||I remember reading in SAMS SQL 2000 DBA Guide, that the backup processes uses logic to make certain that the backup represents the state of the database when the backup is completed.
Regards,
hmscott
Subscribe to:
Posts (Atom)