April 21, 2008 at 11:30 am
Hi,
I have Full,Diff and Log files created daily.
Somebody automated this process before me in this company.
I am now supposed to look at performance issues.
So I built a Testing box (Virtual 2003 Server / SQL Server 2005).
On this new Virtual server I need to keep the data
in sync with the production.
So I was planning to automate RESTORE Full/Diff/Log file every night.
I don't have too much experience with automating Backup/Restore processes.
I'm not really sure I'm doing it the right way.
I mean If I do
RESTORE DATABASE USFPIMS
FROM DISK = '\\yyz-fs1\backup\usf-sys\USFPIMS\FullUSFPIMS20080418230819.BAK'
WITH NORECOVERY,
REPLACE,
MOVE 'USFPIMS_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\USFPIMS_Data.mdf',
MOVE 'USFPIMS_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\USFPIMS_Log.mdf'
RESTORE DATABASE USFPIMS
FROM DISK = '\\yyz-fs1\backup\usf-sys\USFPIMS\DiffUSFPIMS20080420230004.BAK'
WITH NORECOVERY,
REPLACE,
MOVE 'USFPIMS_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\USFPIMS_Data.mdf',
MOVE 'USFPIMS_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\USFPIMS_Log.mdf'
RESTORE LOG USFPIMS
FROM DISK = '\\yyz-fs1\backup\usf-sys\USFPIMS\LogUSFPIMS20080421090008.TRN'
WITH RECOVERY,
REPLACE,
MOVE 'USFPIMS_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\USFPIMS_Data.mdf',
MOVE 'USFPIMS_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\USFPIMS_Log.mdf'
I need to replace the existing MDF,LDF files from the previous night but
Second Restore. It will not override whatever first created?
Do I have to specify "REPLACE" in the second and third RESTORE statement
Will my script do what I need to do?
Thanks,
Robert
April 22, 2008 at 2:24 am
I quite don't get what you're talking about second restore ...
replace should only be necessary in the first statement
and you'll probably want to link the users of the restored database to logins of the instance with sp_change_users_login @Action='AUTO_FIX', @UserNamePattern = ' '
for info see the reference:
April 22, 2008 at 6:55 am
Hi Michael,
It's a DEV box. Only a couple of people will use it including me.
I don't need to copy all the users.
Michael,
So you're saying I don't need
REPLACE when run the second RESTORE statement (Diff)?
I probably miss something.
So I want to understand what exactly happens
when you run these:
RESTORE FROM Full_041520082300.BAK NORECOVERY....
RESTORE FROM Diff_042120082200.BAK NORECOVERY....
RESTORE FROM Log_042220080800.TRN RECOVERY ....
With the first command it's clear.
It restores the data to Apr-15-2008 state.
Then what happens when the second RESTORE is executed?
I thought if you don't specify REPLACE it'll give you an error because
MDF file already exists?
Am I wrong?
Thanks,
Robert
April 22, 2008 at 12:15 pm
Hi,
If you do RESTORE LOG @DBname
is it cumulative? I mean if I have
Log_USFPIMS_20080422090002.TRN
Log_USFPIMS_20080422110002.TRN
Log_USFPIMS_20080422130002.TRN
Can I just apply the latest Log_USFPIMS_20080422130002.TRN
and all the data since last Diff was applied would be there?
Or I have to do them all, one by one?
Thanks,
Rob
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply