database problem: backups

Showing posts with label backups. Show all posts

Wednesday, March 28, 2012

Restoring Transaction Logs Into Backups of MDF and LDF

Hello,
Here's a brief outline of my scenario:
I am trying to recover from a database corruption problem. I have a
backup of the db's physical MDF and LDF files from before the
corruption, but no actual backup (ie bak file). I also have daily
transaction log backups right up to today, and my live db is still
operation despite the corruption, so I can take another transaction
log backup when I do the repair.
The problem I have is I don't know how to, or if it's possible to
attach the backup MDF & LDF files and then somehow apply my
transaction log backups to build a new up to date database.
Thanks,
AlexYou would need Attach with NORECOVERY, which currently is not a possibility.
There is a connect item you can vote in for a future feature request.
https://connect.microsoft.com/SQLSe...=2706
51
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>|||Alex,
If you don't mind my asking, what kind of corruption did you get and how did
it happen. We're just moving to SQLServer to avoid corruptions and I'd hate
to find that we did not leave the problem behind.
Regards
Brian Morris
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>

Restoring Transaction Logs Into Backups of MDF and LDF

Hello,
Here's a brief outline of my scenario:
I am trying to recover from a database corruption problem. I have a
backup of the db's physical MDF and LDF files from before the
corruption, but no actual backup (ie bak file). I also have daily
transaction log backups right up to today, and my live db is still
operation despite the corruption, so I can take another transaction
log backup when I do the repair.
The problem I have is I don't know how to, or if it's possible to
attach the backup MDF & LDF files and then somehow apply my
transaction log backups to build a new up to date database.
Thanks,
AlexYou would need Attach with NORECOVERY, which currently is not a possibility.
There is a connect item you can vote in for a future feature request.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=270651
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>|||Alex,
If you don't mind my asking, what kind of corruption did you get and how did
it happen. We're just moving to SQLServer to avoid corruptions and I'd hate
to find that we did not leave the problem behind.
Regards
Brian Morris
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>

Monday, March 26, 2012

Restoring to a different directory structure

We are trying to import our backups made on one SQL Server 2000
machine to another SQL Server 2000 machine. The problem is the
directory structures are different. After the import is done, I get
errors trying to start SQL Server. I have to move the master database
around. This makes me wonder what other things are in the wrong place?
What other problems will we have? etc.
I don't understand what the problem is exactly, shouldn't SQL Server
import the database and put its files wherever the current 'data'
directory is?
The source server was originally running 7.0, which we upgraded in
place to 2000. All the sql server directories, like data, repldata,
ftdata, logs, etc are in our d:\database directory.
On the destination server, it is a fresh install of SQL Server 2000. I
tried to specify "d:\database" as our data directory when installing,
but it insists on creating an MSSQL directory, and then putting all of
its directories within that directory (argghhh!!).
Any ideas?
Thanks,
ThomasThomas,
you should use the MOVE option of the Restore command. Check for syntax in
Books OnLine.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0311100913.21169934@.posting.google.com...
> We are trying to import our backups made on one SQL Server 2000
> machine to another SQL Server 2000 machine. The problem is the
> directory structures are different. After the import is done, I get
> errors trying to start SQL Server. I have to move the master database
> around. This makes me wonder what other things are in the wrong place?
> What other problems will we have? etc.
> I don't understand what the problem is exactly, shouldn't SQL Server
> import the database and put its files wherever the current 'data'
> directory is?
> The source server was originally running 7.0, which we upgraded in
> place to 2000. All the sql server directories, like data, repldata,
> ftdata, logs, etc are in our d:\database directory.
> On the destination server, it is a fresh install of SQL Server 2000. I
> tried to specify "d:\database" as our data directory when installing,
> but it insists on creating an MSSQL directory, and then putting all of
> its directories within that directory (argghhh!!).
> Any ideas?
> Thanks,
> Thomassql

Friday, March 23, 2012

Restoring SQL server from log files

I am using SQL serve. We have a probelm after our log backup have been damaged.
Problem is we have only log files for the daily backups. ie., after the
entire datbase files were backed up few days ago, it has been a daily
routine of
- backup log file with NT backup
- backup log with SQL 'BACKUP log" command
The SQL log backup is toast now.
The dates on the log files are different on the first and last day.
Replacing the log with the last days log files doesn't seem to work
spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
invalid..
spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
(database ID 7) could not recover. Contact Technical Support...
Is there a way we can just restore the daily log file copies from the first
day and roll forward the database ? Is there a 'RESTORE log.. WITH
NORECOVERY" option without using an archive? Or can I do roll forward by
restarting the server each time replacing the log file?
Any help is appreciated.
-JBThe only way to do any type if "incremental" restore is to restore from a database backup, then
restore an unbroken chain of log backups. I didn't quite follow your scenario, but that is what you
need to do.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bertiew" <bertiew@.discussions.microsoft.com> wrote in message
news:3E26B852-C0D5-4424-A423-D1A2AC411873@.microsoft.com...
>I am using SQL serve. We have a probelm after our log backup have been damaged.
> Problem is we have only log files for the daily backups. ie., after the
> entire datbase files were backed up few days ago, it has been a daily
> routine of
> - backup log file with NT backup
> - backup log with SQL 'BACKUP log" command
> The SQL log backup is toast now.
> The dates on the log files are different on the first and last day.
> Replacing the log with the last days log files doesn't seem to work
> spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> invalid..
> spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> (database ID 7) could not recover. Contact Technical Support...
> Is there a way we can just restore the daily log file copies from the first
> day and roll forward the database ? Is there a 'RESTORE log.. WITH
> NORECOVERY" option without using an archive? Or can I do roll forward by
> restarting the server each time replacing the log file?
> Any help is appreciated.
> -JB
>|||Hi
I am not clear on what you mean by
- backup log file with NT backup !
Is this the backup file created by
- backup log with SQL 'BACKUP log" command ?
When restoring the log you can use the NORECOVERY option when you have a
subsequent log file to restore.
Check out the topic "How to restore to the point of failure" and possibly
"How to restore to a point in time" and "Reducing Recovery Time" in Books
online.
John
"bertiew" wrote:
> I am using SQL serve. We have a probelm after our log backup have been damaged.
> Problem is we have only log files for the daily backups. ie., after the
> entire datbase files were backed up few days ago, it has been a daily
> routine of
> - backup log file with NT backup
> - backup log with SQL 'BACKUP log" command
> The SQL log backup is toast now.
> The dates on the log files are different on the first and last day.
> Replacing the log with the last days log files doesn't seem to work
> spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> invalid..
> spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> (database ID 7) could not recover. Contact Technical Support...
> Is there a way we can just restore the daily log file copies from the first
> day and roll forward the database ? Is there a 'RESTORE log.. WITH
> NORECOVERY" option without using an archive? Or can I do roll forward by
> restarting the server each time replacing the log file?
> Any help is appreciated.
> -JB
>|||By NT backup, i mean the file backups created by the Windows backup utility.
Not the one created by the SQL backup command. In short they are copies of
the ldf files.
Can these be restored? With the NO RECOVERY option?
Thanks,
-JB
"John Bell" wrote:
> Hi
> I am not clear on what you mean by
> - backup log file with NT backup !
> Is this the backup file created by
> - backup log with SQL 'BACKUP log" command ?
> When restoring the log you can use the NORECOVERY option when you have a
> subsequent log file to restore.
> Check out the topic "How to restore to the point of failure" and possibly
> "How to restore to a point in time" and "Reducing Recovery Time" in Books
> online.
> John
>
>
> "bertiew" wrote:
> > I am using SQL serve. We have a probelm after our log backup have been damaged.
> >
> > Problem is we have only log files for the daily backups. ie., after the
> > entire datbase files were backed up few days ago, it has been a daily
> > routine of
> > - backup log file with NT backup
> > - backup log with SQL 'BACKUP log" command
> >
> > The SQL log backup is toast now.
> >
> > The dates on the log files are different on the first and last day.
> > Replacing the log with the last days log files doesn't seem to work
> >
> > spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> > invalid..
> > spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> > (database ID 7) could not recover. Contact Technical Support...
> >
> > Is there a way we can just restore the daily log file copies from the first
> > day and roll forward the database ? Is there a 'RESTORE log.. WITH
> > NORECOVERY" option without using an archive? Or can I do roll forward by
> > restarting the server each time replacing the log file?
> >
> > Any help is appreciated.
> >
> > -JB
> >|||No,. See my other post. Any type of incremental restore requires you to have performed backups from
SQL Ser4ver and such restore starts with RESTORE DATABASE and then a number of RESTORE LOG. All from
SQL Server backup files, not raw database files.
Seems you have a good opportunity to revise your backup strategy.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bertiew" <bertiew@.discussions.microsoft.com> wrote in message
news:C74AD621-98A4-4DC3-A0D1-7D4F4188BAF5@.microsoft.com...
> By NT backup, i mean the file backups created by the Windows backup utility.
> Not the one created by the SQL backup command. In short they are copies of
> the ldf files.
> Can these be restored? With the NO RECOVERY option?
> Thanks,
> -JB
> "John Bell" wrote:
>> Hi
>> I am not clear on what you mean by
>> - backup log file with NT backup !
>> Is this the backup file created by
>> - backup log with SQL 'BACKUP log" command ?
>> When restoring the log you can use the NORECOVERY option when you have a
>> subsequent log file to restore.
>> Check out the topic "How to restore to the point of failure" and possibly
>> "How to restore to a point in time" and "Reducing Recovery Time" in Books
>> online.
>> John
>>
>>
>> "bertiew" wrote:
>> > I am using SQL serve. We have a probelm after our log backup have been damaged.
>> >
>> > Problem is we have only log files for the daily backups. ie., after the
>> > entire datbase files were backed up few days ago, it has been a daily
>> > routine of
>> > - backup log file with NT backup
>> > - backup log with SQL 'BACKUP log" command
>> >
>> > The SQL log backup is toast now.
>> >
>> > The dates on the log files are different on the first and last day.
>> > Replacing the log with the last days log files doesn't seem to work
>> >
>> > spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
>> > invalid..
>> > spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
>> > (database ID 7) could not recover. Contact Technical Support...
>> >
>> > Is there a way we can just restore the daily log file copies from the first
>> > day and roll forward the database ? Is there a 'RESTORE log.. WITH
>> > NORECOVERY" option without using an archive? Or can I do roll forward by
>> > restarting the server each time replacing the log file?
>> >
>> > Any help is appreciated.
>> >
>> > -JB
>> >|||Hi
Unless you have stopped SQL Server or detached the database before doing
your NT Backup, then it is unlikely that any mdf or ldf files on your NT
backup are any use. If you have both a data (mdf) and log (ldf) files it will
be possible to restore them info a different location and re-attach them
using sp_attach_db as a different database.
John
"bertiew" wrote:
> By NT backup, i mean the file backups created by the Windows backup utility.
> Not the one created by the SQL backup command. In short they are copies of
> the ldf files.
> Can these be restored? With the NO RECOVERY option?
> Thanks,
> -JB
> "John Bell" wrote:
> > Hi
> >
> > I am not clear on what you mean by
> > - backup log file with NT backup !
> > Is this the backup file created by
> > - backup log with SQL 'BACKUP log" command ?
> >
> > When restoring the log you can use the NORECOVERY option when you have a
> > subsequent log file to restore.
> >
> > Check out the topic "How to restore to the point of failure" and possibly
> > "How to restore to a point in time" and "Reducing Recovery Time" in Books
> > online.
> >
> > John
> >
> >
> >
> >
> >
> > "bertiew" wrote:
> >
> > > I am using SQL serve. We have a probelm after our log backup have been damaged.
> > >
> > > Problem is we have only log files for the daily backups. ie., after the
> > > entire datbase files were backed up few days ago, it has been a daily
> > > routine of
> > > - backup log file with NT backup
> > > - backup log with SQL 'BACKUP log" command
> > >
> > > The SQL log backup is toast now.
> > >
> > > The dates on the log files are different on the first and last day.
> > > Replacing the log with the last days log files doesn't seem to work
> > >
> > > spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> > > invalid..
> > > spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> > > (database ID 7) could not recover. Contact Technical Support...
> > >
> > > Is there a way we can just restore the daily log file copies from the first
> > > day and roll forward the database ? Is there a 'RESTORE log.. WITH
> > > NORECOVERY" option without using an archive? Or can I do roll forward by
> > > restarting the server each time replacing the log file?
> > >
> > > Any help is appreciated.
> > >
> > > -JB
> > >|||Here's what I have observed
backups are
(1)data files, log files
(2)logfiles
(3)logfiles
(4)logfiles
(5)logfiles
(6)logfiles
I can make sqlsvr recover
with data files (1) and any of logfiles from (1), (2), or (3)
and the data is recovered in the state it was at the time of backup of the
logfile
But if I use logfiles from (4), I get the LSN error.
I am presuming the log file got reused as the log wrapped around. So can I
make sqlsvr do something like this?
a) Start sqlsvr with data files from (1) and log files from (3) <== This works
b) After recovery is done, shut down sqlsvr
c) Replace log with log files from (4), and recover again <== does'nt work.
Can I work around this?
Thanks,
-JB
"John Bell" wrote:
> Hi
> Unless you have stopped SQL Server or detached the database before doing
> your NT Backup, then it is unlikely that any mdf or ldf files on your NT
> backup are any use. If you have both a data (mdf) and log (ldf) files it will
> be possible to restore them info a different location and re-attach them
> using sp_attach_db as a different database.
> John
> "bertiew" wrote:
> > By NT backup, i mean the file backups created by the Windows backup utility.
> > Not the one created by the SQL backup command. In short they are copies of
> > the ldf files.
> >
> > Can these be restored? With the NO RECOVERY option?
> >
> > Thanks,
> > -JB
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > I am not clear on what you mean by
> > > - backup log file with NT backup !
> > > Is this the backup file created by
> > > - backup log with SQL 'BACKUP log" command ?
> > >
> > > When restoring the log you can use the NORECOVERY option when you have a
> > > subsequent log file to restore.
> > >
> > > Check out the topic "How to restore to the point of failure" and possibly
> > > "How to restore to a point in time" and "Reducing Recovery Time" in Books
> > > online.
> > >
> > > John
> > >
> > >
> > >
> > >
> > >
> > > "bertiew" wrote:
> > >
> > > > I am using SQL serve. We have a probelm after our log backup have been damaged.
> > > >
> > > > Problem is we have only log files for the daily backups. ie., after the
> > > > entire datbase files were backed up few days ago, it has been a daily
> > > > routine of
> > > > - backup log file with NT backup
> > > > - backup log with SQL 'BACKUP log" command
> > > >
> > > > The SQL log backup is toast now.
> > > >
> > > > The dates on the log files are different on the first and last day.
> > > > Replacing the log with the last days log files doesn't seem to work
> > > >
> > > > spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> > > > invalid..
> > > > spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> > > > (database ID 7) could not recover. Contact Technical Support...
> > > >
> > > > Is there a way we can just restore the daily log file copies from the first
> > > > day and roll forward the database ? Is there a 'RESTORE log.. WITH
> > > > NORECOVERY" option without using an archive? Or can I do roll forward by
> > > > restarting the server each time replacing the log file?
> > > >
> > > > Any help is appreciated.
> > > >
> > > > -JB
> > > >|||Hi
If these are NT backups then the best you can do is restore the files from
(1) and use sp_attach_db. If (1) is a Full SQL Server Backup and 2-6 are SQL
Log backups then follow the process already described by Tibor and also in
the topic "Reducing Recovery Time" in books online.
John
"bertiew" wrote:
> Here's what I have observed
> backups are
> (1)data files, log files
> (2)logfiles
> (3)logfiles
> (4)logfiles
> (5)logfiles
> (6)logfiles
> I can make sqlsvr recover
> with data files (1) and any of logfiles from (1), (2), or (3)
> and the data is recovered in the state it was at the time of backup of the
> logfile
> But if I use logfiles from (4), I get the LSN error.
> I am presuming the log file got reused as the log wrapped around. So can I
> make sqlsvr do something like this?
> a) Start sqlsvr with data files from (1) and log files from (3) <== This works
> b) After recovery is done, shut down sqlsvr
> c) Replace log with log files from (4), and recover again <== does'nt work.
> Can I work around this?
> Thanks,
> -JB
> "John Bell" wrote:
> > Hi
> >
> > Unless you have stopped SQL Server or detached the database before doing
> > your NT Backup, then it is unlikely that any mdf or ldf files on your NT
> > backup are any use. If you have both a data (mdf) and log (ldf) files it will
> > be possible to restore them info a different location and re-attach them
> > using sp_attach_db as a different database.
> >
> > John
> >
> > "bertiew" wrote:
> >
> > > By NT backup, i mean the file backups created by the Windows backup utility.
> > > Not the one created by the SQL backup command. In short they are copies of
> > > the ldf files.
> > >
> > > Can these be restored? With the NO RECOVERY option?
> > >
> > > Thanks,
> > > -JB
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > I am not clear on what you mean by
> > > > - backup log file with NT backup !
> > > > Is this the backup file created by
> > > > - backup log with SQL 'BACKUP log" command ?
> > > >
> > > > When restoring the log you can use the NORECOVERY option when you have a
> > > > subsequent log file to restore.
> > > >
> > > > Check out the topic "How to restore to the point of failure" and possibly
> > > > "How to restore to a point in time" and "Reducing Recovery Time" in Books
> > > > online.
> > > >
> > > > John
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "bertiew" wrote:
> > > >
> > > > > I am using SQL serve. We have a probelm after our log backup have been damaged.
> > > > >
> > > > > Problem is we have only log files for the daily backups. ie., after the
> > > > > entire datbase files were backed up few days ago, it has been a daily
> > > > > routine of
> > > > > - backup log file with NT backup
> > > > > - backup log with SQL 'BACKUP log" command
> > > > >
> > > > > The SQL log backup is toast now.
> > > > >
> > > > > The dates on the log files are different on the first and last day.
> > > > > Replacing the log with the last days log files doesn't seem to work
> > > > >
> > > > > spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> > > > > invalid..
> > > > > spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> > > > > (database ID 7) could not recover. Contact Technical Support...
> > > > >
> > > > > Is there a way we can just restore the daily log file copies from the first
> > > > > day and roll forward the database ? Is there a 'RESTORE log.. WITH
> > > > > NORECOVERY" option without using an archive? Or can I do roll forward by
> > > > > restarting the server each time replacing the log file?
> > > > >
> > > > > Any help is appreciated.
> > > > >
> > > > > -JB
> > > > >|||bertiew wrote:
> Here's what I have observed
> backups are
> (1)data files, log files
> (2)logfiles
> (3)logfiles
> (4)logfiles
> (5)logfiles
> (6)logfiles
> I can make sqlsvr recover
> with data files (1) and any of logfiles from (1), (2), or (3)
> and the data is recovered in the state it was at the time of backup of the
> logfile
> But if I use logfiles from (4), I get the LSN error.
> I am presuming the log file got reused as the log wrapped around. So can I
> make sqlsvr do something like this?
> a) Start sqlsvr with data files from (1) and log files from (3) <== This works
> b) After recovery is done, shut down sqlsvr
> c) Replace log with log files from (4), and recover again <== does'nt work.
> Can I work around this?
> Thanks,
> -JB
> "John Bell" wrote:
>> Hi
>> Unless you have stopped SQL Server or detached the database before doing
>> your NT Backup, then it is unlikely that any mdf or ldf files on your NT
>> backup are any use. If you have both a data (mdf) and log (ldf) files it will
>> be possible to restore them info a different location and re-attach them
>> using sp_attach_db as a different database.
>> John
>> "bertiew" wrote:
>> By NT backup, i mean the file backups created by the Windows backup utility.
>> Not the one created by the SQL backup command. In short they are copies of
>> the ldf files.
>> Can these be restored? With the NO RECOVERY option?
>> Thanks,
>> -JB
>> "John Bell" wrote:
>> Hi
>> I am not clear on what you mean by
>> - backup log file with NT backup !
>> Is this the backup file created by
>> - backup log with SQL 'BACKUP log" command ?
>> When restoring the log you can use the NORECOVERY option when you have a
>> subsequent log file to restore.
>> Check out the topic "How to restore to the point of failure" and possibly
>> "How to restore to a point in time" and "Reducing Recovery Time" in Books
>> online.
>> John
>>
>>
>> "bertiew" wrote:
>> I am using SQL serve. We have a probelm after our log backup have been damaged.
>> Problem is we have only log files for the daily backups. ie., after the
>> entire datbase files were backed up few days ago, it has been a daily
>> routine of
>> - backup log file with NT backup
>> - backup log with SQL 'BACKUP log" command
>> The SQL log backup is toast now.
>> The dates on the log files are different on the first and last day.
>> Replacing the log with the last days log files doesn't seem to work
>> spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
>> invalid..
>> spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
>> (database ID 7) could not recover. Contact Technical Support...
>> Is there a way we can just restore the daily log file copies from the first
>> day and roll forward the database ? Is there a 'RESTORE log.. WITH
>> NORECOVERY" option without using an archive? Or can I do roll forward by
>> restarting the server each time replacing the log file?
>> Any help is appreciated.
>> -JB
Like the other ones, I'm a bit confused about what it is you've done.
An NT Backup will not backup neither a database file nor a logfile
unless you've stopped the SQL server service, or the files has been
detached. Also when you have NT Backups of logfiles, you can not restore
these using SQL server Restore command.
If I follow your above mentioned backup steps, I'd assume that you can
restore the datafile from (1), restore the logfile from (6) and then try
to run sp_attach_db and then hope it works. Here I assume that it's NT
Backup files you've got and that you haven't mingled around with SQL
server backup command in between.
As already mentioned, you should read up on BACKUP/RESTORE in Books On
Line, because is seem like you are missing some basic understanding of
how it works.
Regards
Steen|||Actually you can backup the files while they are in use, either using VSS or
by enabling SQL writer service (SQL 2005).
I did read up about the SQL backup and restore features. I understand that
my question is a bit unorthodox.
I can restore data 1 and log 3. My take is that in log backup 4 ,the log
files get reused. (as SQL server log circles back to the beginning of the
file). If I were to
draw a picture of the backup of the log files, I would presume it were
something like this (at least based on the SQL 2005 documentation of the log
architecture)
Log file backup 1 ===Log file backup 2 =========Log file backup 3 =============Log file backup 4 ++++++========== <-- ++ part of log file reused.
Log file backup 5 ++++++++++++++==
My question is this: Can I make SQL server somehow recover with log file
backup 3 (be done with the part of the log that got reused in log file backup
4) and then make it continue recovery after I switch log files? (the 'O'ther
database company does this - so maybe MS SQL too?)
Thanks,
-JB
"Steen Persson (DK)" wrote:
> bertiew wrote:
> > Here's what I have observed
> >
> > backups are
> > (1)data files, log files
> > (2)logfiles
> > (3)logfiles
> > (4)logfiles
> > (5)logfiles
> > (6)logfiles
> >
> > I can make sqlsvr recover
> > with data files (1) and any of logfiles from (1), (2), or (3)
> > and the data is recovered in the state it was at the time of backup of the
> > logfile
> >
> > But if I use logfiles from (4), I get the LSN error.
> >
> > I am presuming the log file got reused as the log wrapped around. So can I
> > make sqlsvr do something like this?
> > a) Start sqlsvr with data files from (1) and log files from (3) <== This works
> > b) After recovery is done, shut down sqlsvr
> > c) Replace log with log files from (4), and recover again <== does'nt work.
> >
> > Can I work around this?
> >
> > Thanks,
> > -JB
> >
> > "John Bell" wrote:
> >
> >> Hi
> >>
> >> Unless you have stopped SQL Server or detached the database before doing
> >> your NT Backup, then it is unlikely that any mdf or ldf files on your NT
> >> backup are any use. If you have both a data (mdf) and log (ldf) files it will
> >> be possible to restore them info a different location and re-attach them
> >> using sp_attach_db as a different database.
> >>
> >> John
> >>
> >> "bertiew" wrote:
> >>
> >> By NT backup, i mean the file backups created by the Windows backup utility.
> >> Not the one created by the SQL backup command. In short they are copies of
> >> the ldf files.
> >>
> >> Can these be restored? With the NO RECOVERY option?
> >>
> >> Thanks,
> >> -JB
> >>
> >> "John Bell" wrote:
> >>
> >> Hi
> >>
> >> I am not clear on what you mean by
> >> - backup log file with NT backup !
> >> Is this the backup file created by
> >> - backup log with SQL 'BACKUP log" command ?
> >>
> >> When restoring the log you can use the NORECOVERY option when you have a
> >> subsequent log file to restore.
> >>
> >> Check out the topic "How to restore to the point of failure" and possibly
> >> "How to restore to a point in time" and "Reducing Recovery Time" in Books
> >> online.
> >>
> >> John
> >>
> >>
> >>
> >>
> >>
> >> "bertiew" wrote:
> >>
> >> I am using SQL serve. We have a probelm after our log backup have been damaged.
> >>
> >> Problem is we have only log files for the daily backups. ie., after the
> >> entire datbase files were backed up few days ago, it has been a daily
> >> routine of
> >> - backup log file with NT backup
> >> - backup log with SQL 'BACKUP log" command
> >>
> >> The SQL log backup is toast now.
> >>
> >> The dates on the log files are different on the first and last day.
> >> Replacing the log with the last days log files doesn't seem to work
> >>
> >> spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> >> invalid..
> >> spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> >> (database ID 7) could not recover. Contact Technical Support...
> >>
> >> Is there a way we can just restore the daily log file copies from the first
> >> day and roll forward the database ? Is there a 'RESTORE log.. WITH
> >> NORECOVERY" option without using an archive? Or can I do roll forward by
> >> restarting the server each time replacing the log file?
> >>
> >> Any help is appreciated.
> >>
> >> -JB
> >>
> Like the other ones, I'm a bit confused about what it is you've done.
> An NT Backup will not backup neither a database file nor a logfile
> unless you've stopped the SQL server service, or the files has been
> detached. Also when you have NT Backups of logfiles, you can not restore
> these using SQL server Restore command.
> If I follow your above mentioned backup steps, I'd assume that you can
> restore the datafile from (1), restore the logfile from (6) and then try
> to run sp_attach_db and then hope it works. Here I assume that it's NT
> Backup files you've got and that you haven't mingled around with SQL
> server backup command in between.
> As already mentioned, you should read up on BACKUP/RESTORE in Books On
> Line, because is seem like you are missing some basic understanding of
> how it works.
> Regards
> Steen
>|||Hi
You did not mention that you were using SQL 2005, SQL Writer or Volume
Shadow Copy Service in your original email, and it is still not clear if you
are actually using them.
To perform point in time recovery you will need to use Full Recovery model,
and the log file will not be overwritten unless it has been backed up i.e.
your situation will not occur. Once backed up SQL Server may re-use the
truncated inactive part of the log.
John
"bertiew" wrote:
> Actually you can backup the files while they are in use, either using VSS or
> by enabling SQL writer service (SQL 2005).
> I did read up about the SQL backup and restore features. I understand that
> my question is a bit unorthodox.
> I can restore data 1 and log 3. My take is that in log backup 4 ,the log
> files get reused. (as SQL server log circles back to the beginning of the
> file). If I were to
> draw a picture of the backup of the log files, I would presume it were
> something like this (at least based on the SQL 2005 documentation of the log
> architecture)
> Log file backup 1 ===> Log file backup 2 =========> Log file backup 3 =============> Log file backup 4 ++++++========== <-- ++ part of log file reused.
> Log file backup 5 ++++++++++++++==> My question is this: Can I make SQL server somehow recover with log file
> backup 3 (be done with the part of the log that got reused in log file backup
> 4) and then make it continue recovery after I switch log files? (the 'O'ther
> database company does this - so maybe MS SQL too?)
> Thanks,
> -JB
> "Steen Persson (DK)" wrote:
> > bertiew wrote:
> > > Here's what I have observed
> > >
> > > backups are
> > > (1)data files, log files
> > > (2)logfiles
> > > (3)logfiles
> > > (4)logfiles
> > > (5)logfiles
> > > (6)logfiles
> > >
> > > I can make sqlsvr recover
> > > with data files (1) and any of logfiles from (1), (2), or (3)
> > > and the data is recovered in the state it was at the time of backup of the
> > > logfile
> > >
> > > But if I use logfiles from (4), I get the LSN error.
> > >
> > > I am presuming the log file got reused as the log wrapped around. So can I
> > > make sqlsvr do something like this?
> > > a) Start sqlsvr with data files from (1) and log files from (3) <== This works
> > > b) After recovery is done, shut down sqlsvr
> > > c) Replace log with log files from (4), and recover again <== does'nt work.
> > >
> > > Can I work around this?
> > >
> > > Thanks,
> > > -JB
> > >
> > > "John Bell" wrote:
> > >
> > >> Hi
> > >>
> > >> Unless you have stopped SQL Server or detached the database before doing
> > >> your NT Backup, then it is unlikely that any mdf or ldf files on your NT
> > >> backup are any use. If you have both a data (mdf) and log (ldf) files it will
> > >> be possible to restore them info a different location and re-attach them
> > >> using sp_attach_db as a different database.
> > >>
> > >> John
> > >>
> > >> "bertiew" wrote:
> > >>
> > >> By NT backup, i mean the file backups created by the Windows backup utility.
> > >> Not the one created by the SQL backup command. In short they are copies of
> > >> the ldf files.
> > >>
> > >> Can these be restored? With the NO RECOVERY option?
> > >>
> > >> Thanks,
> > >> -JB
> > >>
> > >> "John Bell" wrote:
> > >>
> > >> Hi
> > >>
> > >> I am not clear on what you mean by
> > >> - backup log file with NT backup !
> > >> Is this the backup file created by
> > >> - backup log with SQL 'BACKUP log" command ?
> > >>
> > >> When restoring the log you can use the NORECOVERY option when you have a
> > >> subsequent log file to restore.
> > >>
> > >> Check out the topic "How to restore to the point of failure" and possibly
> > >> "How to restore to a point in time" and "Reducing Recovery Time" in Books
> > >> online.
> > >>
> > >> John
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> "bertiew" wrote:
> > >>
> > >> I am using SQL serve. We have a probelm after our log backup have been damaged.
> > >>
> > >> Problem is we have only log files for the daily backups. ie., after the
> > >> entire datbase files were backed up few days ago, it has been a daily
> > >> routine of
> > >> - backup log file with NT backup
> > >> - backup log with SQL 'BACKUP log" command
> > >>
> > >> The SQL log backup is toast now.
> > >>
> > >> The dates on the log files are different on the first and last day.
> > >> Replacing the log with the last days log files doesn't seem to work
> > >>
> > >> spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> > >> invalid..
> > >> spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> > >> (database ID 7) could not recover. Contact Technical Support...
> > >>
> > >> Is there a way we can just restore the daily log file copies from the first
> > >> day and roll forward the database ? Is there a 'RESTORE log.. WITH
> > >> NORECOVERY" option without using an archive? Or can I do roll forward by
> > >> restarting the server each time replacing the log file?
> > >>
> > >> Any help is appreciated.
> > >>
> > >> -JB
> > >>
> >
> > Like the other ones, I'm a bit confused about what it is you've done.
> > An NT Backup will not backup neither a database file nor a logfile
> > unless you've stopped the SQL server service, or the files has been
> > detached. Also when you have NT Backups of logfiles, you can not restore
> > these using SQL server Restore command.
> >
> > If I follow your above mentioned backup steps, I'd assume that you can
> > restore the datafile from (1), restore the logfile from (6) and then try
> > to run sp_attach_db and then hope it works. Here I assume that it's NT
> > Backup files you've got and that you haven't mingled around with SQL
> > server backup command in between.
> >
> > As already mentioned, you should read up on BACKUP/RESTORE in Books On
> > Line, because is seem like you are missing some basic understanding of
> > how it works.
> >
> > Regards
> > Steen
> >|||bertiew wrote:
> Actually you can backup the files while they are in use, either using VSS or
> by enabling SQL writer service (SQL 2005).
> I did read up about the SQL backup and restore features. I understand that
> my question is a bit unorthodox.
> I can restore data 1 and log 3. My take is that in log backup 4 ,the log
> files get reused. (as SQL server log circles back to the beginning of the
> file). If I were to
> draw a picture of the backup of the log files, I would presume it were
> something like this (at least based on the SQL 2005 documentation of the log
> architecture)
> Log file backup 1 ===> Log file backup 2 =========> Log file backup 3 =============> Log file backup 4 ++++++========== <-- ++ part of log file reused.
> Log file backup 5 ++++++++++++++==> My question is this: Can I make SQL server somehow recover with log file
> backup 3 (be done with the part of the log that got reused in log file backup
> 4) and then make it continue recovery after I switch log files? (the 'O'ther
> database company does this - so maybe MS SQL too?)
> Thanks,
> -JB
>
I still think that you are mixing things up a little bit. An NT Backup
(or similar file backup) is not the same as a BACKUP LOG command in the
way it treats the file. I don't know the SQL writer option in detail,
but to me it looks like it's only enabling you to make a regular file
copy of your logfile. It doesn't truncate the log or anything else like
a regular BACKUP LOG command does. This means that if you use this to
backup your logfile, it will just take a backup of whatever is in your
logfile at that time, and the logfile will still contain "old" data
because it's not being truncated.
I've haven't tried the SQL writer service my self, but I still think
that the best way to set up a backup routine is to do a database backup
e.g. once a day, and then run log backups a number of times during the
day. These backup files can then be backed/copied to somewhere else
since they are now regular files. By doing this, you can always restore
your database backup and then logfiles up to the point in time you need.
It's my feeling that the setup you have now, is a mix of different ways
of backing up your data and that's why it's causing you troubles.
Regards
Steen

Tuesday, March 20, 2012

Restoring multiple TRN files to another computer

I have a backup process that does full backups
periodically with transaction logs backed up every 5
minutes. In the event that I need to restore the backup
and apply a large number of TRN files on another computer
(for example if the server dies), is there an easy way?
If I do the restore on the original server, the interface
is easy because it knows about all the recent backups,
but when I do the restore to another server, the backups
are not listed in the interface so it seems I have to
choose "restore from device" and then select the backup
file and each TRN file individually which is very tedious.
Rick Harrison.In EM 2000, you can generate backup history from the contents of the backup
device. I've never used it (I prefer to do RESTORE though TSQL command), but
my guess that the feature was put there for this type of situations.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"rick@.knowware.com" <anonymous@.discussions.microsoft.com> wrote in message
news:0bd901c3bb52$2cbc0da0$a501280a@.phx.gbl...
> I have a backup process that does full backups
> periodically with transaction logs backed up every 5
> minutes. In the event that I need to restore the backup
> and apply a large number of TRN files on another computer
> (for example if the server dies), is there an easy way?
> If I do the restore on the original server, the interface
> is easy because it knows about all the recent backups,
> but when I do the restore to another server, the backups
> are not listed in the interface so it seems I have to
> choose "restore from device" and then select the backup
> file and each TRN file individually which is very tedious.
> Rick Harrison.|||We recently recovered from a total disk array failure to
another SQL server. We were fortunate enough to have all
the .BAK & .TRN files on tape. But without a little
program I wrote to automate the restore, it would have
taken an unreasonable amount of time to apply each TLog
using the GUI.
If you're interested, I can send you some VB code. It
uses the SQL DMO objects to restore DB's & files. I still
need to finalize some things, because I had to step
through the code & make minor adjustments to restore
everything. But I'm supposed to finalize the tool soon.
If you have VB knowledge, or someone at your company does,
you could take what I have now & make alterations to suit
your needs. Just let me know,
Gene Daigle
>--Original Message--
>I have a backup process that does full backups
>periodically with transaction logs backed up every 5
>minutes. In the event that I need to restore the backup
>and apply a large number of TRN files on another computer
>(for example if the server dies), is there an easy way?
>If I do the restore on the original server, the interface
>is easy because it knows about all the recent backups,
>but when I do the restore to another server, the backups
>are not listed in the interface so it seems I have to
>choose "restore from device" and then select the backup
>file and each TRN file individually which is very tedious.
> Rick Harrison.
>.
>

Restoring multiple transaction logs on another server

Dear knowledgeable people,
Is there a simple way to restore the latest full backup, plus all the transaction
log backups on to another server. I know you can on the same server, 'cause
all the backup history is there in MSDB. But how many people back msdb up
every 15 minutes?
Consider the case:
Full Backup of Database @. 0300 each day
T.log backups every 15 minutes
Crash at 19:00 takes out entire server, but not the backup files 'cause
you cunningly configured File Replication Service to copy them elsewhere.
When restoring, do you really have to type 60+ "restore log ... with no recovery"
commands (without typos) to get the database back?
i.e Should I polish up the following script ( error handling / differential backups ...)
and/or write my own .exe to intelligently parse a backup directory, or am I
totally missing the point? ( Me programmer not normally sysadmin ).
Regards
AJ
CREATE procedure spAJGRestoreAsFarAsYouCan
(
@.Path varchar(200) = 'c:\mssql\backup\',
@.DBName varchar(30) = 'ALC',
@.DoItOrJustPrint char(1) = 'Y'
)
as
set nocount on
-- Get list of files in directory
declare @.SqlCmd varchar(300)
set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
create table #DirList
(
RowID int identity,
FileOnly varchar(400)
)
insert #DirList ( FileOnly )
exec master..xp_cmdshell @.SqlCmd
-- Last full backup ( .bak )
declare @.FullBackup varchar(300)
declare @.CurrentID int
Select top 1
@.CurrentID = RowID,
@.FullBackup = @.Path + FileOnly
from #DirList
where patindex( '%.bak%', FileOnly) > 0
order by RowID desc
-- if @.FullBackup is NULL return -1
Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' + @.FullBackup + ''' with norecovery'
print @.SQlCmd
if @.DoItOrJustPrint = 'Y'
exec(@.SQLCmd)
Declare @.TransLog varchar(300)
Set @.TransLog = 'dummy'
While @.TransLog is Not NULL
Begin
Set @.TransLog = NULL
Select top 1
@.TransLog = @.Path + FileOnly,
@.CurrentID = RowID
from #DirList
where patindex( '%.trn%', FileOnly) > 0
and RowID > @.CurrentID
order by RowID
if @.TransLog is not NULL
Begin
Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' + @.TransLog + ''' with norecovery'
print @.SQlCmd
if @.DoItOrJustPrint = 'Y'
exec(@.SQLCmd)
End
End
Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
print @.SQlCmd
if @.DoItOrJustPrint = 'Y'
exec(@.SQLCmd)
drop table #DirList
return (0)I'd just polish the script. I'd like to point out the following, though:
* You may want to change dir /t:c to dir /t:w
* If your directory may have files other than the database/log backup files,
you may want to check
each file with RESTORE HEADERONLY to make sure that it's a backup file
* Instead of relying on the file date/time to determine the order of the
restore, you can also use
RESTORE HEADERONLY to make sure that the order is correct.
* Using RESTORE HEADERONLY, you can even determine whether you have gaps in
your log
backups by inspecting the LSN's (log sequence numbers)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Andrew John" <aj@.DELETEmistrose.com> wrote in message
news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> Dear knowledgeable people,
> Is there a simple way to restore the latest full backup, plus all the
transaction
> log backups on to another server. I know you can on the same server,
'cause
> all the backup history is there in MSDB. But how many people back msdb up
> every 15 minutes?
> Consider the case:
> Full Backup of Database @. 0300 each day
> T.log backups every 15 minutes
> Crash at 19:00 takes out entire server, but not the backup files 'cause
> you cunningly configured File Replication Service to copy them elsewhere.
> When restoring, do you really have to type 60+ "restore log ... with no
recovery"
> commands (without typos) to get the database back?
> i.e Should I polish up the following script ( error handling /
differential backups ...)
> and/or write my own .exe to intelligently parse a backup directory, or am
I
> totally missing the point? ( Me programmer not normally sysadmin ).
> Regards
> AJ
> CREATE procedure spAJGRestoreAsFarAsYouCan
> (
> @.Path varchar(200) = 'c:\mssql\backup\',
> @.DBName varchar(30) = 'ALC',
> @.DoItOrJustPrint char(1) = 'Y'
> )
> as
> set nocount on
> -- Get list of files in directory
> declare @.SqlCmd varchar(300)
> set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> create table #DirList
> (
> RowID int identity,
> FileOnly varchar(400)
> )
> insert #DirList ( FileOnly )
> exec master..xp_cmdshell @.SqlCmd
> -- Last full backup ( .bak )
> declare @.FullBackup varchar(300)
> declare @.CurrentID int
> Select top 1
> @.CurrentID = RowID,
> @.FullBackup = @.Path + FileOnly
> from #DirList
> where patindex( '%.bak%', FileOnly) > 0
> order by RowID desc
> -- if @.FullBackup is NULL return -1
> Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' +
@.FullBackup + ''' with norecovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> Declare @.TransLog varchar(300)
> Set @.TransLog = 'dummy'
> While @.TransLog is Not NULL
> Begin
> Set @.TransLog = NULL
> Select top 1
> @.TransLog = @.Path + FileOnly,
> @.CurrentID = RowID
> from #DirList
> where patindex( '%.trn%', FileOnly) > 0
> and RowID > @.CurrentID
> order by RowID
> if @.TransLog is not NULL
> Begin
> Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' +
@.TransLog + ''' with norecovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> End
> End
>
> Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> drop table #DirList
> return (0)
>|||Andrew
Yeh, you need to be prepared for such situations.
I would write stored procedure that accept tree parameters
(dbname,from_hour,to_hour)
You need to keep transaction backups with appropriatte file name like
my_log1200 ,next my_log1215.
"Andrew John" <aj@.DELETEmistrose.com> wrote in message
news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> Dear knowledgeable people,
> Is there a simple way to restore the latest full backup, plus all the
transaction
> log backups on to another server. I know you can on the same server,
'cause
> all the backup history is there in MSDB. But how many people back msdb up
> every 15 minutes?
> Consider the case:
> Full Backup of Database @. 0300 each day
> T.log backups every 15 minutes
> Crash at 19:00 takes out entire server, but not the backup files 'cause
> you cunningly configured File Replication Service to copy them elsewhere.
> When restoring, do you really have to type 60+ "restore log ... with no
recovery"
> commands (without typos) to get the database back?
> i.e Should I polish up the following script ( error handling /
differential backups ...)
> and/or write my own .exe to intelligently parse a backup directory, or am
I
> totally missing the point? ( Me programmer not normally sysadmin ).
> Regards
> AJ
> CREATE procedure spAJGRestoreAsFarAsYouCan
> (
> @.Path varchar(200) = 'c:\mssql\backup\',
> @.DBName varchar(30) = 'ALC',
> @.DoItOrJustPrint char(1) = 'Y'
> )
> as
> set nocount on
> -- Get list of files in directory
> declare @.SqlCmd varchar(300)
> set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> create table #DirList
> (
> RowID int identity,
> FileOnly varchar(400)
> )
> insert #DirList ( FileOnly )
> exec master..xp_cmdshell @.SqlCmd
> -- Last full backup ( .bak )
> declare @.FullBackup varchar(300)
> declare @.CurrentID int
> Select top 1
> @.CurrentID = RowID,
> @.FullBackup = @.Path + FileOnly
> from #DirList
> where patindex( '%.bak%', FileOnly) > 0
> order by RowID desc
> -- if @.FullBackup is NULL return -1
> Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' +
@.FullBackup + ''' with norecovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> Declare @.TransLog varchar(300)
> Set @.TransLog = 'dummy'
> While @.TransLog is Not NULL
> Begin
> Set @.TransLog = NULL
> Select top 1
> @.TransLog = @.Path + FileOnly,
> @.CurrentID = RowID
> from #DirList
> where patindex( '%.trn%', FileOnly) > 0
> and RowID > @.CurrentID
> order by RowID
> if @.TransLog is not NULL
> Begin
> Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' +
@.TransLog + ''' with norecovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> End
> End
>
> Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> drop table #DirList
> return (0)
>|||Thank you all for your comments.
I'm glad to see I wasn't completely off track.
I am a bit surprised with t:c vs t:w. Wouldn't that
cause issues if the full backup took more than 15minutes ?
( It wont in my case - but I did think of that ).
What I am trying to write is something that can be run
in the wee hours of the morning, by some overstressed
service technician, without a microscopic examination of
file names and a touch typing course.
Pretty please Mr Microsoft - It wouldn't be too hard to put
some MSDB data into the T.Log backups would it?
Thanks Again
AJ
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message news:uygo6aAgDHA.1648@.TK2MSFTNGP09.phx.gbl...
> I'd just polish the script. I'd like to point out the following, though:
> * You may want to change dir /t:c to dir /t:w
> * If your directory may have files other than the database/log backup files,
> you may want to check
> each file with RESTORE HEADERONLY to make sure that it's a backup file
> * Instead of relying on the file date/time to determine the order of the
> restore, you can also use
> RESTORE HEADERONLY to make sure that the order is correct.
> * Using RESTORE HEADERONLY, you can even determine whether you have gaps in
> your log
> backups by inspecting the LSN's (log sequence numbers)
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Andrew John" <aj@.DELETEmistrose.com> wrote in message
> news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> > Dear knowledgeable people,
> >
> > Is there a simple way to restore the latest full backup, plus all the
> transaction
> > log backups on to another server. I know you can on the same server,
> 'cause
> > all the backup history is there in MSDB. But how many people back msdb up
> > every 15 minutes?
> >
> > Consider the case:
> >
> > Full Backup of Database @. 0300 each day
> > T.log backups every 15 minutes
> > Crash at 19:00 takes out entire server, but not the backup files 'cause
> > you cunningly configured File Replication Service to copy them elsewhere.
> >
> > When restoring, do you really have to type 60+ "restore log ... with no
> recovery"
> > commands (without typos) to get the database back?
> >
> > i.e Should I polish up the following script ( error handling /
> differential backups ...)
> > and/or write my own .exe to intelligently parse a backup directory, or am
> I
> > totally missing the point? ( Me programmer not normally sysadmin ).
> >
> > Regards
> > AJ
> >
> > CREATE procedure spAJGRestoreAsFarAsYouCan
> > (
> > @.Path varchar(200) = 'c:\mssql\backup\',
> > @.DBName varchar(30) = 'ALC',
> > @.DoItOrJustPrint char(1) = 'Y'
> > )
> > as
> >
> > set nocount on
> > -- Get list of files in directory
> > declare @.SqlCmd varchar(300)
> > set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> >
> > create table #DirList
> > (
> > RowID int identity,
> > FileOnly varchar(400)
> > )
> >
> > insert #DirList ( FileOnly )
> > exec master..xp_cmdshell @.SqlCmd
> >
> > -- Last full backup ( .bak )
> > declare @.FullBackup varchar(300)
> > declare @.CurrentID int
> >
> > Select top 1
> > @.CurrentID = RowID,
> > @.FullBackup = @.Path + FileOnly
> > from #DirList
> > where patindex( '%.bak%', FileOnly) > 0
> > order by RowID desc
> >
> > -- if @.FullBackup is NULL return -1
> >
> > Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' +
> @.FullBackup + ''' with norecovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> >
> > Declare @.TransLog varchar(300)
> > Set @.TransLog = 'dummy'
> > While @.TransLog is Not NULL
> > Begin
> >
> > Set @.TransLog = NULL
> > Select top 1
> > @.TransLog = @.Path + FileOnly,
> > @.CurrentID = RowID
> > from #DirList
> > where patindex( '%.trn%', FileOnly) > 0
> > and RowID > @.CurrentID
> > order by RowID
> >
> > if @.TransLog is not NULL
> > Begin
> > Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' +
> @.TransLog + ''' with norecovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> > End
> > End
> >
> >
> > Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> >
> > drop table #DirList
> >
> > return (0)
> >
> >
>|||> I always like using backup / restore for version control as it
> controls dev data versions along with schema objects etc.
IMHO, DDL (and selected DML) belongs in a version control system. This
facilitates new application installs as well as upgrades. Test data can
also be stored but its not as important to keep that under version
control.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:eWKx7SAgDHA.1748@.TK2MSFTNGP10.phx.gbl...
> Hi Andrew.
> I regularly need to do this on software dev projects for testing,
release
> etc.. I always like using backup / restore for version control as it
> controls dev data versions along with schema objects etc. So, it
sounds like
> my needs are similar to yours. (c:
> My approach is to use similar scripts / app logic, but there's a fair
bit of
> complexity as there's a full app with additional features not relevant
to
> your question, so I can't easily post scripts as such.
> The feature that I use though is to restore file headers, extract LSNs
&
> apply in order, which works whether the files are .bak, .diff or .trn.
I've
> found that use of .diffs mitigate your "60+ retore log statements"
problem
> significantly. Perhaps you could adjust your script to include .diff,
> restore headers & retore log / diff / db in LSN order?
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Andrew John" <aj@.DELETEmistrose.com> wrote in message
> news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> > Dear knowledgeable people,
> >
> > Is there a simple way to restore the latest full backup, plus all
the
> transaction
> > log backups on to another server. I know you can on the same
server,
> 'cause
> > all the backup history is there in MSDB. But how many people back
msdb up
> > every 15 minutes?
> >
> > Consider the case:
> >
> > Full Backup of Database @. 0300 each day
> > T.log backups every 15 minutes
> > Crash at 19:00 takes out entire server, but not the backup files
'cause
> > you cunningly configured File Replication Service to copy them
elsewhere.
> >
> > When restoring, do you really have to type 60+ "restore log ...
with no
> recovery"
> > commands (without typos) to get the database back?
> >
> > i.e Should I polish up the following script ( error handling /
> differential backups ...)
> > and/or write my own .exe to intelligently parse a backup directory,
or am
> I
> > totally missing the point? ( Me programmer not normally sysadmin ).
> >
> > Regards
> > AJ
> >
> > CREATE procedure spAJGRestoreAsFarAsYouCan
> > (
> > @.Path varchar(200) = 'c:\mssql\backup\',
> > @.DBName varchar(30) = 'ALC',
> > @.DoItOrJustPrint char(1) = 'Y'
> > )
> > as
> >
> > set nocount on
> > -- Get list of files in directory
> > declare @.SqlCmd varchar(300)
> > set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> >
> > create table #DirList
> > (
> > RowID int identity,
> > FileOnly varchar(400)
> > )
> >
> > insert #DirList ( FileOnly )
> > exec master..xp_cmdshell @.SqlCmd
> >
> > -- Last full backup ( .bak )
> > declare @.FullBackup varchar(300)
> > declare @.CurrentID int
> >
> > Select top 1
> > @.CurrentID = RowID,
> > @.FullBackup = @.Path + FileOnly
> > from #DirList
> > where patindex( '%.bak%', FileOnly) > 0
> > order by RowID desc
> >
> > -- if @.FullBackup is NULL return -1
> >
> > Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' +
> @.FullBackup + ''' with norecovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> >
> > Declare @.TransLog varchar(300)
> > Set @.TransLog = 'dummy'
> > While @.TransLog is Not NULL
> > Begin
> >
> > Set @.TransLog = NULL
> > Select top 1
> > @.TransLog = @.Path + FileOnly,
> > @.CurrentID = RowID
> > from #DirList
> > where patindex( '%.trn%', FileOnly) > 0
> > and RowID > @.CurrentID
> > order by RowID
> >
> > if @.TransLog is not NULL
> > Begin
> > Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' +
> @.TransLog + ''' with norecovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> > End
> > End
> >
> >
> > Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> >
> > drop table #DirList
> >
> > return (0)
> >
> >
>|||> I also advocate using VSS, this is really just supplemental dev team
> procedure.
I see what you mean now. Backup / restore certainly has its place for
taking snapshots to aid iterative testing. I'm not a big fan of VSS but
there are other better, albeit much more expensive, source control
products out there.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:e3v3LvEgDHA.4024@.TK2MSFTNGP11.phx.gbl...
> Experience has made me (slightly) weary of VSS which is enough to
warrant
> extra protection! So, on my projects, I have been supplementing
version
> control of database objects by implementing fully logged backups (with
> transaction log marks in sql2k or timestamps in earlier versions).
With this
> approach you get bullet-proof backup & recoverability of all db source
/
> objects and data thrown in as well. This approach works on any
platform.
> Assuming the project doesn't have massive test / dev data
requirements, it's
> easy to store these on tape or ide media or just mix into a business's
> normal backup procedures.
> Being able to easily restore dev / test environments to marks or
specific
> points in time is a powerful technique for regression testing. As a
side
> benefit you also get early indications of capacity requirements for
backup /
> restore which a data tier architect will find useful when documenting
> capacity expectations.
> I also advocate using VSS, this is really just supplemental dev team
> procedure.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ez8u7kEgDHA.3896@.tk2msftngp13.phx.gbl...
> > > I always like using backup / restore for version control as it
> > > controls dev data versions along with schema objects etc.
> >
> > IMHO, DDL (and selected DML) belongs in a version control system.
This
> > facilitates new application installs as well as upgrades. Test data
can
> > also be stored but its not as important to keep that under version
> > control.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > news:eWKx7SAgDHA.1748@.TK2MSFTNGP10.phx.gbl...
> > > Hi Andrew.
> > >
> > > I regularly need to do this on software dev projects for testing,
> > release
> > > etc.. I always like using backup / restore for version control as
it
> > > controls dev data versions along with schema objects etc. So, it
> > sounds like
> > > my needs are similar to yours. (c:
> > >
> > > My approach is to use similar scripts / app logic, but there's a
fair
> > bit of
> > > complexity as there's a full app with additional features not
relevant
> > to
> > > your question, so I can't easily post scripts as such.
> > >
> > > The feature that I use though is to restore file headers, extract
LSNs
> > &
> > > apply in order, which works whether the files are .bak, .diff or
.trn.
> > I've
> > > found that use of .diffs mitigate your "60+ retore log statements"
> > problem
> > > significantly. Perhaps you could adjust your script to include
.diff,
> > > restore headers & retore log / diff / db in LSN order?
> > >
> > > HTH
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "Andrew John" <aj@.DELETEmistrose.com> wrote in message
> > > news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > > Dear knowledgeable people,
> > > >
> > > > Is there a simple way to restore the latest full backup, plus
all
> > the
> > > transaction
> > > > log backups on to another server. I know you can on the same
> > server,
> > > 'cause
> > > > all the backup history is there in MSDB. But how many people
back
> > msdb up
> > > > every 15 minutes?
> > > >
> > > > Consider the case:
> > > >
> > > > Full Backup of Database @. 0300 each day
> > > > T.log backups every 15 minutes
> > > > Crash at 19:00 takes out entire server, but not the backup files
> > 'cause
> > > > you cunningly configured File Replication Service to copy them
> > elsewhere.
> > > >
> > > > When restoring, do you really have to type 60+ "restore log ...
> > with no
> > > recovery"
> > > > commands (without typos) to get the database back?
> > > >
> > > > i.e Should I polish up the following script ( error handling /
> > > differential backups ...)
> > > > and/or write my own .exe to intelligently parse a backup
directory,
> > or am
> > > I
> > > > totally missing the point? ( Me programmer not normally
sysadmin ).
> > > >
> > > > Regards
> > > > AJ
> > > >
> > > > CREATE procedure spAJGRestoreAsFarAsYouCan
> > > > (
> > > > @.Path varchar(200) = 'c:\mssql\backup\',
> > > > @.DBName varchar(30) = 'ALC',
> > > > @.DoItOrJustPrint char(1) = 'Y'
> > > > )
> > > > as
> > > >
> > > > set nocount on
> > > > -- Get list of files in directory
> > > > declare @.SqlCmd varchar(300)
> > > > set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> > > >
> > > > create table #DirList
> > > > (
> > > > RowID int identity,
> > > > FileOnly varchar(400)
> > > > )
> > > >
> > > > insert #DirList ( FileOnly )
> > > > exec master..xp_cmdshell @.SqlCmd
> > > >
> > > > -- Last full backup ( .bak )
> > > > declare @.FullBackup varchar(300)
> > > > declare @.CurrentID int
> > > >
> > > > Select top 1
> > > > @.CurrentID = RowID,
> > > > @.FullBackup = @.Path + FileOnly
> > > > from #DirList
> > > > where patindex( '%.bak%', FileOnly) > 0
> > > > order by RowID desc
> > > >
> > > > -- if @.FullBackup is NULL return -1
> > > >
> > > > Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk =''' +
> > > @.FullBackup + ''' with norecovery'
> > > > print @.SQlCmd
> > > > if @.DoItOrJustPrint = 'Y'
> > > > exec(@.SQLCmd)
> > > >
> > > > Declare @.TransLog varchar(300)
> > > > Set @.TransLog = 'dummy'
> > > > While @.TransLog is Not NULL
> > > > Begin
> > > >
> > > > Set @.TransLog = NULL
> > > > Select top 1
> > > > @.TransLog = @.Path + FileOnly,
> > > > @.CurrentID = RowID
> > > > from #DirList
> > > > where patindex( '%.trn%', FileOnly) > 0
> > > > and RowID > @.CurrentID
> > > > order by RowID
> > > >
> > > > if @.TransLog is not NULL
> > > > Begin
> > > > Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = '''
+
> > > @.TransLog + ''' with norecovery'
> > > > print @.SQlCmd
> > > > if @.DoItOrJustPrint = 'Y'
> > > > exec(@.SQLCmd)
> > > > End
> > > > End
> > > >
> > > >
> > > > Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> > > > print @.SQlCmd
> > > > if @.DoItOrJustPrint = 'Y'
> > > > exec(@.SQLCmd)
> > > >
> > > > drop table #DirList
> > > >
> > > > return (0)
> > > >
> > > >
> > >
> > >
> >
> >
>

Wednesday, March 28, 2012

Restoring Transaction Logs Into Backups of MDF and LDF

Restoring Transaction Logs Into Backups of MDF and LDF

Monday, March 26, 2012

Restoring to a different directory structure

Friday, March 23, 2012

Restoring SQL server from log files

Restoring SQL server from log files

Restoring SQL server from log files

Tuesday, March 20, 2012

Restoring multiple TRN files to another computer

Restoring multiple transaction logs on another server

database problem

Blog Archive

About Me