October 31, 2007 at 1:04 pm
ok
October 31, 2007 at 1:17 pm
Steps:
1) Read books/magazines/articles on SQL Server
2) Take the test based on your own knowledge rather than asking other people to answer the questions for you.
October 31, 2007 at 1:49 pm
0) keep your head cool. Know what you do !
1) disable automatic backup jobs !
2) make a new incremental log backup !
3) if you have the place or another server, restore the db to a "staging" db. pull over the data of the truncate table to the original table and off you go.
4) check if it is ok
5) re-enable the automatic backup jobs.
...
100) flame the one who did it 😉
Maybe this script may get you started.
It only generates a restore script, it will not execute the restore itself!
(it is for sql2000, but it will get you started - the 'RESTORE HEADERONLY ' table needs to be adjusted for sql2005)
declare @Logbackup_Filename varchar(500)
declare @Restore_New_DBname varchar(128)
select @Logbackup_Filename = 'c:\dtestLog.bak'
, @Restore_New_DBname = 'DTEST1'
declare @RestoreDb_stmt varchar(5000)
select @RestoreDb_stmt = 'restore database ' + @Restore_New_DBname + '
FROM DISK = ''c:\dtestfull.bak''
WITH MOVE N''DTEST'' TO N''U:\MSSQL$ALBE0DB78\Data\' + @Restore_New_DBname + '.mdf''
, MOVE N''DTEST_log'' TO N''S:\MSSQL$ALBE0DB78\Log\' + @Restore_New_DBname + '_log.LDF''
, replace
, NoRecovery '
print @RestoreDb_stmt + char(13) + 'GO'
-- exec (@RestoreDb_stmt)
if (select isnull( object_id(N'tempdb.[dbo].[#Tmp_BackupHeaders]'), 0)) <> 0
begin
DROP table #Tmp_BackupHeaders
end
-- versie SQL2K sp3a
create table #Tmp_BackupHeaders (
BackupName nvarchar(128)
,BackupDescription nvarchar(255)
,BackupType smallint
,ExpirationDate datetime
,Compressed tinyint
,Position smallint
,DeviceType tinyint
,UserName nvarchar(128)
,ServerName nvarchar(128)
,DatabaseName nvarchar(128)
,DatabaseVersion int
,DatabaseCreationDate datetime
,BackupSize numeric(20,0)
,FirstLSN numeric(25,0)
,LastLSN numeric(25,0)
,CheckpointLSN numeric(25,0)
,DatabaseBackupLSN numeric(25,0)
,BackupStartDate datetime
,BackupFinishDate datetime
,SortOrder smallint
,CodePage smallint
,UnicodeLocaleId int
, UnicodeComparisonStyle int
, CompatibilityLevel tinyint
, SoftwareVendorId int
,SoftwareVersionMajor int
,SoftwareVersionMinor int
,SoftwareVersionBuild int
,MachineName nvarchar(128)
,Flags int
,BindingID uniqueidentifier
,RecoveryForkID uniqueidentifier
,Collation nvarchar(128)
)
declare @sql varchar(5000)
set @sql = 'RESTORE HEADERONLY FROM DISK = ''' + @Logbackup_Filename + ''' '
insert into #Tmp_BackupHeaders
exec (@SQL)
declare csrRestoreLog cursor for
select 'print ''-- db [' + convert(varchar(128),DatabaseName) + '] -- ' + convert(varchar(15), Position)
+ ' -- LSN ' + convert(varchar(128), FirstLSN ) + ' ' + convert(varchar(128), LastLSN )
+ ' -- BackupDate ' + convert(varchar(25), BackupStartDate, 121) + ' ' + convert(varchar(25), BackupFinishDate, 121)
+ ' ''' + char(13)
+ 'Restore Log ' + @Restore_New_DBname + '
FROM DISK = ''' + @Logbackup_Filename + '''
WITH FILE = ' + convert(varchar(15),Position) + char(13)
+ case Position when M.MAX_Position then ' , Recovery '
else ' , NoRecovery '
end
-- + char(13) + 'GO'
from #Tmp_BackupHeaders
, (select max(Position) as MAX_Position from #Tmp_BackupHeaders ) M
order by Position
declare @RestoreLog_stmt varchar(5000)
open csrRestoreLog
FETCH NEXT FROM csrRestoreLog
INTO @RestoreLog_stmt
WHILE @@FETCH_STATUS = 0
BEGIN
print @RestoreLog_stmt + char(10) + 'GO'
-- exec (@RestoreLog_stmt)
-- Volgende rij inlezen
FETCH NEXT FROM csrRestoreLog
INTO @RestoreLog_stmt
END
-- Cursor afsluiten
CLOSE csrRestoreLog
DEALLOCATE csrRestoreLog
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply