-
Jun 24th, 2005, 06:09 AM
#1
Thread Starter
Hyperactive Member
SQL Server Backup Question.
Hey Chaps,
Anyone got any suggestions:
I need to backup and restore a database to a new server. I don't want the log (which is over 1 gig in size). I back up and restore fine it takes a long time though. I'm wondering what I can do T-SQL wise or thru the manager to backup the DB without a log (will that work on restore ????)
Cheers
Chubby..
-
Jun 24th, 2005, 08:26 AM
#2
Re: SQL Server Backup Question.
You really should truncate the log - so it's shrunken down to a good size - before the backup...
I've seen so many postings on MS Tech Net about this - and I still use a crazy system of my own to make a log shrink.
My logs go from huge to 1024KB with this technique.
In query analyzer do:
[edit] DO NOT FOLLOW the steps in this POST - go to POST # 21 for proper instructions!!!!!!!
[edit] - SEE POST #21 below - this TRUNCATE ONLY is dangerous!!!
Code:
BACKUP LOG FUNDS WITH TRUNCATE_ONLY
[edit] - SEE POST #21 below - this TRUNCATE ONLY is dangerous!!!
In the example above the DB name is FUNDS.
This does not do a backup - it simply cleans up and makes ready the log for truncate.
Then - this is the crazy part (and maybe someone knows a better technique - vbDBA??)
I go into ENTERPRISE MANAGER - right click on the DATABASE
ALL TASKS>SHRINK DATABASE>
Do not PRESS OK on this SCREEN - it will shrink the MDB
Click the [FILES...] button
Change the drop-down box to the LOG file.
Click the radio-button that says "Truncate free space from the end of the file"
Click OK - the log file is now 1024KB
Make sure to click CANCEL to get off the screen - as you are right back to the SHRINK DATABASE window!
Seems crazy - but it takes a multi-gig log and makes it 1024KB.
Now you can do you BACKUP with the standard backup/restore commands - like...
Code:
--BACKUP DATABASE Funds
-- TO DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_NB_May21.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\acs desktop\Funds_May28.bak'
RESTORE DATABASE Funds
FROM DISK = 'c:\acs desktop\Funds_May28.bak'
WITH MOVE 'Funds_data' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Funds.mdf',
MOVE 'Funds_log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Funds.ldf'
GO
Last edited by szlamany; Oct 26th, 2007 at 06:48 AM.
-
Jun 24th, 2005, 08:43 AM
#3
Re: SQL Server Backup Question.
That is crazy..... but it works right? Hmmmm..... I think we have a couple of clients for which this could be handy.
Tg
(I'd rate, but I musta have done that for you recently as it won't let me at the moment - ggrrr!)
-
Jun 24th, 2005, 08:57 AM
#4
Thread Starter
Hyperactive Member
Re: SQL Server Backup Question.
Originally Posted by szlamany
You really should truncate the log - so it's shrunken down to a good size - before the backup...
I've seen so many postings on MS Tech Net about this - and I still use a crazy system of my own to make a log shrink.
My logs go from huge to 1024KB with this technique.
In query analyzer do:
Code:
BACKUP LOG FUNDS WITH TRUNCATE_ONLY
In the example above the DB name is FUNDS.
This does not do a backup - it simply cleans up and makes ready the log for truncate.
Then - this is the crazy part (and maybe someone knows a better technique - vbDBA??)
I go into ENTERPRISE MANAGER - right click on the DATABASE
ALL TASKS>SHRINK DATABASE>
Do not PRESS OK on this SCREEN - it will shrink the MDB
Click the [FILES...] button
Change the drop-down box to the LOG file.
Click the radio-button that says "Truncate free space from the end of the file"
Click OK - the log file is now 1024KB
Make sure to click CANCEL to get off the screen - as you are right back to the SHRINK DATABASE window!
Seems crazy - but it takes a multi-gig log and makes it 1024KB.
Now you can do you BACKUP with the standard backup/restore commands - like...
Whoah !
That seems like it would work but I'm sh-scared of crashing a live client database ......
There is an option called NO LOG or something I read somewhere too....
Will more than likely use your example tho as I've no real time to kick this about...
Thanks!
Chubby..
-
Jun 24th, 2005, 09:15 AM
#5
Re: SQL Server Backup Question.
TG and Chubby - it does work - I've done it on production machines with live data...
I'm sure the ENTERPRISE MANAGER steps could be turned into a simple QA T-SQL command - I simply don't know what it is.
All the MS Tech Net articles I read say that the nightly maintenance plan is supposed to keep the logs shrunk - but I don't see that happen often at my customer sites!
-
Jun 24th, 2005, 09:25 AM
#6
Re: SQL Server Backup Question.
That is pretty "baked", but if it works (and I'm going to try it), it is worth the effort.
I'm "bookmarking" this thread!
-
Jun 24th, 2005, 09:26 AM
#7
Re: SQL Server Backup Question.
Originally Posted by techgnome
I'd rate, but I musta have done that for you recently as it won't let me at the moment - ggrrr!)
Same thing happened to me. Double ggrrr
-
Jun 24th, 2005, 09:36 AM
#8
Fanatic Member
Re: SQL Server Backup Question.
I found this script from one of the SQL Server sites that I frequent, can't remember which one though. The comments says that it's for 7.0, but I've tweaked it to work on SQL 2000. I use this quite regularly and have never had a bit of problem with it. That being said, use at your own risk.
You can either shrink the log to a desired size (I use 2MB as my default) or you can shrink it by a certain percentage.
Code:
--CREATE proc sp_force_shrink_log
--------------------------------------------------------------------------------
-- Purpose: Shrink transaction log of the current database in SQL Server 7.0.
-- Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000
-- zanevsky@azdatabases.com
--------------------------------------------------------------------------------
-- These were the input parameters
Declare @target_percent tinyint
, @target_size_MB int
, @max_iterations int
, @backup_log_opt nvarchar(1000)
--as
set nocount on
--initialize the input parameters
Set @target_percent = 0
Set @target_size_MB = 2
Set @max_iterations = 1000
Set @backup_log_opt = 'with truncate_only'
declare @db sysname
, @last_row int
, @log_size decimal(15,2)
, @unused1 decimal(15,2)
, @unused decimal(15,2)
, @shrinkable decimal(15,2)
, @iteration int
, @file_max int
, @file int
, @fileid varchar(5)
select @db = db_name()
, @iteration = 0
--drop table #loginfo
--drop table #logfiles
create table #loginfo (
[id] int identity
, FileId int
, FileSize numeric(22,0)
, StartOffset numeric(22,0)
, FSeqNo int
, Status int
, Parity smallint
, CreateLSN float
)
create unique clustered index loginfo_FSeqNo on #loginfo ( FSeqNo, StartOffset )
create table #logfiles ( [id] int identity(1,1), fileid varchar(5) not null )
insert #logfiles ( fileid )
select convert( varchar, fileid )
from sysfiles
where status & 0x40 = 0x40
select @file_max = @@rowcount
if object_id( 'table_to_force_shrink_log' ) is null
exec( 'create table table_to_force_shrink_log ( x nchar(3000) not null )' )
insert #loginfo ( FileId
, FileSize
, StartOffset
, FSeqNo
, Status
, Parity
, CreateLSN )
exec ( 'dbcc loginfo' )
select @last_row = @@rowcount
select @log_size = sum( FileSize ) / 1048576.00
, @unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00
, @shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo
select @unused1 = @unused -- save for later
select 'iteration' = @iteration
, 'log size, MB' = @log_size
, 'unused log, MB' = @unused
, 'shrinkable log, MB' = @shrinkable
, 'shrinkable %' = convert( decimal(6,2), @shrinkable * 100 / @log_size )
while @shrinkable * 100 / @log_size > @target_percent
and @shrinkable > @target_size_MB
and @iteration < @max_iterations
begin
select @iteration = @iteration + 1 -- this is just a precaution
exec( 'insert table_to_force_shrink_log select name from sysobjects
delete table_to_force_shrink_log')
select @file = 0
while @file < @file_max
begin
select @file = @file + 1
select @fileid = fileid from #logfiles where id = @file
exec( 'dbcc shrinkfile( ' + @fileid + ' )' )
end
exec( 'backup log [' + @db + '] ' + @backup_log_opt )
truncate table #loginfo
insert #loginfo ( FileId
, FileSize
, StartOffset
, FSeqNo
, Status
, Parity
, CreateLSN )
exec ( 'dbcc loginfo' )
select @last_row = @@rowcount
select @log_size = sum( FileSize ) / 1048576.00
, @unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00
, @shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo
select 'iteration' = @iteration
, 'log size, MB' = @log_size
, 'unused log, MB' = @unused
, 'shrinkable log, MB' = @shrinkable
, 'shrinkable %' = convert( decimal(6,2), @shrinkable * 100 / @log_size )
end
if @unused1 < @unused
select 'After ' + convert( varchar, @iteration ) + ' iterations the unused portion of the log has grown from ' +
convert( varchar, @unused1 ) + ' MB to ' + convert( varchar, @unused ) + ' MB.'
union all
select 'Since the remaining unused portion is larger than 10 MB,' where @unused > 10
union all
select 'you may try running this procedure again with a higher number of iterations.' where @unused > 10
union all
select 'Sometimes the log would not shrink to a size smaller than several Megabytes.' where @unused <= 10
else
select 'It took ' + convert( varchar, @iteration ) + ' iterations to shrink the unused portion of the log from ' +
convert( varchar, @unused1 ) + ' MB to ' + convert( varchar, @unused ) + ' MB'
exec( 'drop table table_to_force_shrink_log' )
GO
Chris
Master Of My Domain
Got A Question? Look Here First
-
Jun 24th, 2005, 09:40 AM
#9
Re: SQL Server Backup Question.
vb_dba - I'm sad - I figured you would come along and give us a simple one line T-SQL to take the place of the mess of steps I'm doing in EM...
Why is it so hard to shrink a LOG FILE?
-
Jun 24th, 2005, 09:44 AM
#10
Fanatic Member
Re: SQL Server Backup Question.
Well....you could create a stored procedure out of that, then you'd have your one-line TSQL statement.
It's an issue that everyone who uses SQL Server has to deal with and unfortunately, MS doesn't provide an easy solution to deal with it. Maybe Yukon or SQL 2005 or whatever they are calling it now, will provide an easier method to shrink the log.
Chris
Master Of My Domain
Got A Question? Look Here First
-
Jun 24th, 2005, 09:49 AM
#11
Re: SQL Server Backup Question.
Originally Posted by vb_dba
Well....you could create a stored procedure out of that, then you'd have your one-line TSQL statement.
Very funny...
Oh well - I guess I'll stick with my crazy EM multi-step nightmare - you can use your 45 page SPROC with iterations ( )- and we will all just wait till MS gives us a better answer!
The really bad thing about all this is that I've got customers that end up with 7 gig log files - start running out of room - and they don't have the comfort level or experience to want to do either technique!
-
Jun 25th, 2005, 07:59 AM
#12
Re: SQL Server Backup Question.
Originally Posted by Chubby
Hey Chaps,
Anyone got any suggestions:
I need to backup and restore a database to a new server. I don't want the log (which is over 1 gig in size). I back up and restore fine it takes a long time though. I'm wondering what I can do T-SQL wise or thru the manager to backup the DB without a log (will that work on restore ????)
Cheers
Chubby..
When you do a full database backup (BACKUP DATABASE) it doesn't backup the transaction log.
-
Jun 25th, 2005, 08:00 AM
#13
Re: SQL Server Backup Question.
Originally Posted by szlamany
vb_dba - I'm sad - I figured you would come along and give us a simple one line T-SQL to take the place of the mess of steps I'm doing in EM...
Why is it so hard to shrink a LOG FILE?
Is it? Have you tried DBCC SHRINKFILE after you have truncated the log? I think that is what EM executes when you choose to truncate the log file.
But why do you really want to shrink the log file anyway, unless disk space is low or the file has grown extremely large due to many transactions since last backup.
Last edited by kaffenils; Jun 25th, 2005 at 08:06 AM.
-
Jun 25th, 2005, 08:06 AM
#14
Re: SQL Server Backup Question.
Originally Posted by kaffenils
Is it? Have you tried DBCC SHRINKFILE after you hav trucated the log?
Feel free to give me the exact syntax that would be used after the BACKUP LOG {dbname} WITH TRUNCATE_ONLY that would physically change the LOG size to 1024KB.
I will test it - I've got several servers running SQL right now - and get back to you with my results.
-
Jun 25th, 2005, 08:10 AM
#15
Re: SQL Server Backup Question.
Originally Posted by szlamany
Feel free to give me the exact syntax that would be used after the BACKUP LOG {dbname} WITH TRUNCATE_ONLY that would physically change the LOG size to 1024KB.
I will test it - I've got several servers running SQL right now - and get back to you with my results.
According to BOL the syntax would be
Code:
DBCC SHRINKFILE (MyDB_Log,1)
But I'm not sure if it will shrink the file if the log file is in use.
-
Jun 25th, 2005, 08:37 AM
#16
Re: SQL Server Backup Question.
Perfect - this appears to work.
Code:
backup log acctfiles with truncate_only
go
DBCC SHRINKFILE (acctfiles_Log,1)
go
Shrunk the LOG to 1024KB.
And I was in query analyzer connected to that DATABASE - so my guess is that it will work with the DB in use.
You obviously need to know the name of the "log" for the database - this can be found several ways - they are not always DBNAME_LOG.
Rep points for KAFFENILS!!
[edit] Apparently I have given to KAFFENILS recently - I cannot rep again - argh!
-
Jun 25th, 2005, 08:42 AM
#17
Re: SQL Server Backup Question.
Originally Posted by kaffenils
But why do you really want to shrink the log file anyway, unless disk space is low or the file has grown extremely large due to many transactions since last backup.
Most of the time it's because I am ripping a copy of a production DB and putting it on my laptop or our test server at the shop.
Also, I'm not always so sure about the "shrinking" due to backup/maintenance plans. I just looked at a log at one of my customer sites - they do a backup twice a day (so no users in DB since yesterday - at least one backup done since then) - the log is 1.5 gig. That's an absurd size for a log.
-
Jun 25th, 2005, 08:46 AM
#18
Re: SQL Server Backup Question.
Originally Posted by szlamany
Perfect - this appears to work.
[edit] Apparently I have given to KAFFENILS recently - I cannot rep again - argh!
Well, rules are not always fair
But who cares. The main goal is to help each other, right!
-
Jun 25th, 2005, 08:51 AM
#19
Re: SQL Server Backup Question.
Originally Posted by szlamany
Also, I'm not always so sure about the "shrinking" due to backup/maintenance plans. I just looked at a log at one of my customer sites - they do a backup twice a day (so no users in DB since yesterday - at least one backup done since then) - the log is 1.5 gig. That's an absurd size for a log.
The log file for our document management system has stabilized at about 3,5GB event though I do hourly transaction log backups and these files are only about 100K. I have no idea either why the log file grows to this absurd size.
-
Dec 19th, 2005, 12:44 AM
#20
Re: SQL Server Backup Question.
-
Jul 11th, 2006, 05:58 PM
#21
Re: SQL Server Backup Question.
After working some threads today and doing some research I found that...
TRUNCATE_ONLY is dangerous - it leaves the database in a non-recoverable state - a full backup is needed as soon as possible to protect the DB.
It seems a proper method to TRUNCATE a log file is to:
Code:
BACKUP LOG ACCTFILES TO DISK='C:\somefilename'
GO
DBCC SHRINKFILE (ACCTFILES_LOG,1)
GO
This successfully truncated the logfile to 1 MB on this test file.
edit: see this link http://www.vbforums.com/showpost.php...70&postcount=8
Last edited by szlamany; Oct 19th, 2007 at 07:10 PM.
Reason: typo - don't use " quotes around the disk path/filename
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|