February 24, 2014 at 3:10 pm
One of our users said that our application has lost her data. It was from last week Thursday, 2/20/2014. I've looked at the database, but cannot find any evidence that it was ever saved. The best thing I can think of to do is restore the database from the last full backup. Our servers, and databases, were taken over last year by our primary IT department. I can get to them, administer SQL Server, etc., but they say when and how other backups are done. They've set up the backups to occur once a day, at 1 AM, so the best time I can restore from is 2/21/2014 at 1 AM. I've performed restores 2 or 3 times, so it isn't something I do often. I got into SSMS, and followed the steps on BOL from the article titled Restore a Database Backup (SQL Server Management Studio). Then, before running it I generated the SQL script. I just want to make certain I've done it all correct before I run the script. The database in question is named ASI. I'm trying to restore it to another, new database I'll name ASIBak. Here's the SQL script:
USE [master]
BACKUP LOG [ASI] TO DISK = N'D:\Backups\ASI_LogBackup_2014-02-24_14-49-46.bak' WITH NOFORMAT, NOINIT, NAME = N'ASI_LogBackup_2014-02-24_14-49-46', NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5
RESTORE DATABASE [ASIBak] FROM DISK = N'D:\Backups\ASI\ASI_backup_2014_02_20_010714_7778760.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [ASIBak] FROM DISK = N'D:\Backups\ASI_LogBackup_2014-02-24_14-49-46.bak' WITH NOUNLOAD, STATS = 5, STOPAT = N'2014-02-21T01:05:35'
GO
Please tell me, have I don't it correctly?
Kindest Regards, Rod Connect with me on LinkedIn.
February 25, 2014 at 4:55 am
You have to restore with RECOVERY at the end of the log process too.
Looking at that, are they running a single log backup? Normally you'll see lots of log backups all day long, so that you would have to run a series of log restores to get to the final STOPAT point and then run the restore with RECOVERY.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 25, 2014 at 5:01 am
What recovery model is the database in?
Where are all the other log restores? You need to restore all the log backups in sequence from the time of the full backup to the time you want to restore to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2014 at 8:15 am
That's kind of nuts. I wouldn't even bother with log backups there. Just have the daily one since it's extremely likely you'll only ever be able to go to the last full backup. Set the database to Simple Recovery and be done. Or, bump the frequency on the log backups up to every 1/2 hour so that you only lose up to 1/2 hour of data. Or some other value other than daily.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 25, 2014 at 8:42 am
Grant Fritchey (2/25/2014)
That's kind of nuts. I wouldn't even bother with log backups there. Just have the daily one since it's extremely likely you'll only ever be able to go to the last full backup. Set the database to Simple Recovery and be done. Or, bump the frequency on the log backups up to every 1/2 hour so that you only lose up to 1/2 hour of data. Or some other value other than daily.
Grant, when I read what you've said, what you've said does make sense. I've got to change it, or get IT to change it. We could use backups of the logs at least once every half hour during business hours. Well, maybe even starting at 7 AM and going until 11 PM, when external customers are likely to still be using our website to enter data. I'll have to get IT's approval on this.
But going back to my original question, is the SQL script that SSMS generated sufficient to recover the ASI database, into a new database named ASIBak?
Kindest Regards, Rod Connect with me on LinkedIn.
February 25, 2014 at 9:09 am
That's not sufficient. You need to restore the intermediate log backups too before you could restore that last log backup ..
--
SQLBuddy.
February 25, 2014 at 9:15 am
Rod at work (2/24/2014)
...
USE [master]
BACKUP LOG [ASI] TO DISK = N'D:\Backups\ASI_LogBackup_2014-02-24_14-49-46.bak' WITH NOFORMAT, NOINIT, NAME = N'ASI_LogBackup_2014-02-24_14-49-46', NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5
RESTORE DATABASE [ASIBak] FROM DISK = N'D:\Backups\ASI\ASI_backup_2014_02_20_010714_7778760.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [ASIBak] FROM DISK = N'D:\Backups\ASI_LogBackup_2014-02-24_14-49-46.bak' WITH NOUNLOAD, STATS = 5, STOPAT = N'2014-02-21T01:05:35'
GO
Please tell me, have I don't it correctly?
Hi Rod at Work,
One thing you're going to need to do, if you are restoring this new ASIBak database on the same sSQL Instance as the ASI database, is to rename the physical file names, as if you restore as-is it will want to restore the new database, but with the original mdf and ldf physical file names...
Use script below, changing text in '<...>' to your values as required, to get it to restore...
The logical names can be the same, but the physical file names must be different and not clash with any other DB physical file names...
------------------------------------------------------------------------------------
-- Use this to find out what the logical filenames are...
------------------------------------------------------------------------------------
RESTORE FILELISTONLY FROM DISK = N'D:\Backups\ASI\ASI_backup_2014_02_20_010714_7778760.bak';
------------------------------------------------------------------------------------
-- Enter the info from above, plus any other...
------------------------------------------------------------------------------------
RESTORE DATABASE ASIBak
FROM DISK = N'D:\Backups\ASI\ASI_backup_2014_02_20_010714_7778760.bak'
WITH
MOVE N'<logical_data_name>' TO N'<path>\ASIBak_data.mdf',
MOVE N'<logical_tlog_name>' TO N'<path>\ASIBak_log.ldf',
STATS = 1;
I hope this is useful.
Rgds
humbleDBA
*Edit: I've removed the RESTRICTED_USER bit to save confusion. Also, this does not include restoring TLogs.
February 25, 2014 at 9:41 am
If you need to restore TLog backups as well, here's the scripts you can use...
------------------------------------------------------------------------------------
-- Use one of these to find out whats in the backup and what the
-- logical filenames are...
------------------------------------------------------------------------------------
RESTORE FILELISTONLY FROM DISK = N'D:\Backups\ASI\ASI_backup_2014_02_20_010714_7778760.bak';
------------------------------------------------------------------------------------
-- Get current DB File info...
------------------------------------------------------------------------------------
EXEC ASI.dbo.sp_helpfile
------------------------------------------------------------------------------------
-- Enter the info from above, plus any other...
------------------------------------------------------------------------------------
RESTORE DATABASE [ASIBak]
FROM DISK = N'D:\Backups\ASI\ASI_backup_2014_02_20_010714_7778760.bak'
WITH
MOVE N'<logical_data_name>' TO N'<path>\ASIBak_data.mdf',
MOVE N'<logical_tlog_name>' TO N'<path>\ASIBak_log.ldf',
NORECOVERY,
STATS = 1;
------------------------------------------------------------------------------------
-- Copy/Paste this script for 'each' TLog backup you need to restore...
------------------------------------------------------------------------------------
RESTORE LOG [ASIBak]
FROM DISK = N'D:\Backups\ASI_LogBackup_<dates_here>.bak'
WITH
MOVE N'<logical_data_name>' TO N'<path>\ASIBak_data.mdf',
MOVE N'<logical_tlog_name>' TO N'<path>\ASIBak_log.ldf',
NORECOVERY,
STATS = 1;
------------------------------------------------------------------------------------
-- When all done with FULL backups and TLOG Backups, bring DB online...
------------------------------------------------------------------------------------
RESTORE DATABASE [ASIBak] WITH RECOVERY;
I've confirmed that they work - based on simple restore of one of my DBs. If you have more than the two physical database files, ie, the mdf and ldf files, then simply add extra ...WITH MOVE... sections as in...
...
WITH
MOVE N'<logical_data_name>' TO N'<path>\ASIBak_data.mdf',
MOVE N'<logical_data_name>' TO N'<path>\ASIBak_data_02.ndf', -- ***Added extra physical file here ***
MOVE N'<logical_tlog_name>' TO N'<path>\ASIBak_log.ldf',
NORECOVERY,
STATS = 1;
I hope this is useful.
Rgds
humbleDBA
February 25, 2014 at 9:50 am
Rod at work (2/25/2014)
Grant Fritchey (2/25/2014)
That's kind of nuts. I wouldn't even bother with log backups there. Just have the daily one since it's extremely likely you'll only ever be able to go to the last full backup. Set the database to Simple Recovery and be done. Or, bump the frequency on the log backups up to every 1/2 hour so that you only lose up to 1/2 hour of data. Or some other value other than daily.Grant, when I read what you've said, what you've said does make sense. I've got to change it, or get IT to change it. We could use backups of the logs at least once every half hour during business hours. Well, maybe even starting at 7 AM and going until 11 PM, when external customers are likely to still be using our website to enter data. I'll have to get IT's approval on this.
But going back to my original question, is the SQL script that SSMS generated sufficient to recover the ASI database, into a new database named ASIBak?
Like I said in my first post, it looks OK, but you need to add WITH RECOVERY to the log restore too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 25, 2014 at 9:57 am
Grant Fritchey (2/25/2014)
Like I said in my first post, it looks OK, but you need to add WITH RECOVERY to the log restore too.
It won't work if its being restored on the same SQL Instance, as the physical file names will clash with the current ASI database physical file names; the physical file names will need to be changed with the ...WITH MOVE... part in the RESTORE... statement. 🙂
February 25, 2014 at 11:18 am
humbleDBA (2/25/2014)
Grant Fritchey (2/25/2014)
Like I said in my first post, it looks OK, but you need to add WITH RECOVERY to the log restore too.
It won't work if its being restored on the same SQL Instance, as the physical file names will clash with the current ASI database physical file names; the physical file names will need to be changed with the ...WITH MOVE... part in the RESTORE... statement. 🙂
Maybe I misunderstood. I thought he was overwriting the existing db.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 25, 2014 at 12:32 pm
Grant Fritchey (2/25/2014)
humbleDBA (2/25/2014)
Grant Fritchey (2/25/2014)
Like I said in my first post, it looks OK, but you need to add WITH RECOVERY to the log restore too.
It won't work if its being restored on the same SQL Instance, as the physical file names will clash with the current ASI database physical file names; the physical file names will need to be changed with the ...WITH MOVE... part in the RESTORE... statement. 🙂
Maybe I misunderstood. I thought he was overwriting the existing db.
No I wasn't wanting to overwrite the existing database, but install a backup in a side-by-side fashion.
Kindest Regards, Rod Connect with me on LinkedIn.
February 25, 2014 at 12:37 pm
Rod at work (2/25/2014)
Grant Fritchey (2/25/2014)
humbleDBA (2/25/2014)
Grant Fritchey (2/25/2014)
Like I said in my first post, it looks OK, but you need to add WITH RECOVERY to the log restore too.
It won't work if its being restored on the same SQL Instance, as the physical file names will clash with the current ASI database physical file names; the physical file names will need to be changed with the ...WITH MOVE... part in the RESTORE... statement. 🙂
Maybe I misunderstood. I thought he was overwriting the existing db.
No I wasn't wanting to overwrite the existing database, but install a backup in a side-by-side fashion.
Oops. Then humbleDBA is right. You need to have WITH MOVE to move the data files as part of the RESTORE.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply