| Author |
Topic |
|
deepayamini
Starting Member
11 Posts |
 Posted - 2010-01-19 : 13:54:24
|
| Hi,I am totally new to SQL server backup and restore. I was able to successfully set up a back up job on our SQL server 2005 production server. The maintenance plan does a full backup every week, diff backup every day and a log backup every hour. The backup files are then backed up to the tape at the end of the day. I restored the contents of the tape and I am trying to restore the database on one of the user's computer (since I did not want to test it on the actual prod server). For some reason, I am not able to do this restore. I keep getting an error stating "the media files were wrongly formed....". Please help me out. Thanks all for your time.With smiles,Deepa. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
deepayamini
Starting Member
11 Posts |
Posted - 2010-01-19 : 15:58:47
|
| Thanks for your reply.Well actually I did the restore through the UI (SQL server management studio) and not with a query or script. The exact error message wasTITLE: Microsoft SQL Server Management Studio------------------------------An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)------------------------------ADDITIONAL INFORMATION:The volume on device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\VDEP_Full\VDEP\VDEP_backup_201001151700.bak' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set.RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3259)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=3259&LinkId=20476------------------------------BUTTONS:OK------------------------------Hope this helps to understand my situation. Thanks again for your time.With smiles,Deepa. |
 |
|
|
shan
Yak Posting Veteran
84 Posts |
Posted - 2010-01-19 : 16:37:48
|
| Looks like you are trying to restore to a dirve which does not existDo the following stepsRESTORE HEADERONLY from disk='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\VDEP_Full\VDEP\VDEP_backup_201001151700.bak' From this you can find the Databasename, note this down (lets assume its called VDEP)RESTORE FILELISTONLY from disk='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\VDEP_Full\VDEP\VDEP_backup_201001151700.bak' You can find the MDF and LDF filenames (logical and physical name) Note down the logical file names(VDEL_Data and VDEP_Log).Then you got to decide were you are planning to place the MDF and LDF (assume C:\DATA for data and C:\LOG)RESTORE DATABASE VDEP from disk ='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\VDEP_Full\VDEP\VDEP_backup_201001151700.bak' with move 'VDEP_Data' to 'C:\Data\VDEP_Data.mdf',with move 'VDEP_Log' to 'C:\Log\VDEP_Data.Ldf'-Shan |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
deepayamini
Starting Member
11 Posts |
Posted - 2010-01-19 : 17:42:23
|
| Hi all,I tried the SQL script and I got the following error:Msg 3159, Level 16, State 1, Line 1The tail of the log for the database "VDEP" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.I actually did a full backup, diff backup and a log backup. When I try it from the UI, it does not even allow me to access options as the restore location is not specified. In turn when I try to add my backup files to the restore location list and hit OK, I get the error I first stated. So Scipr or through the UI, I am not able to understand what mistake I made. Please help me out in sorting this thing. For information, the backup files are from a different SQL server and the restoration is on a different server.With smiles,Deepa. |
|
|
|
deepayamini
Starting Member
11 Posts |
Posted - 2010-01-19 : 17:53:58
|
| Hi,I corrected the script to include WITH REPLACE and the restore worked fine. But the data is not current. So I need to restore the latest log file backed up too (probably I guess the diff backup file should also be restored), so how do I do that?Thanks for your replies.With smiles,Deepa. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
deepayamini
Starting Member
11 Posts |
Posted - 2010-01-20 : 01:18:27
|
| Hi,Thanks so much. Well the full, diff and log backup worked. But I could only restore the log backup that was close to the time of the Diff backup file. I tried restoring the latest (like a day old from which the Diff backup file differs) and I got the following error message saying that there were no transactions to rollback. Should I restore all log files from the last diff backup to the current time in sequential order to restore the latest data or should i do something else?With smiles,Deepa. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-20 : 03:20:02
|
| You need to restore every TLog backup in order, starting with the first one after the DIFF backup.Use NORECOVERY on all of them, except the last one.NORECOVERY allows you to keep restoring more files, but does not set the database to make it usable - so you won't be able to access the database until you are done recovering.What I normally do is to use NORECOVERY for ALL the files I want to restore and then when I am SURE that I have finished I juse use this command (i.e. without specifying a backup file at all):RESTORE DATABASE MyDatabaseName WITH RECOVERY |
|
|
|
shan
Yak Posting Veteran
84 Posts |
Posted - 2010-01-20 : 09:39:50
|
| Standby mode have the advantage of database being available when it not restoring the logs.-Shan |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-20 : 12:48:11
|
| I don't think STANDBY mode helps during a normal restore, only useful for standby-servers that are doing log-shipping - unless someone can enlighten me otherwise? |
|
|
|
deepayamini
Starting Member
11 Posts |
Posted - 2010-01-20 : 14:48:07
|
| Hi,Hurray....It worked. I am so happy and thank you so much. But also curious as to why cannot I do the same restoration using the GUI. Any thoughts...With smiles,Deepa. |
|
|
|
shan
Yak Posting Veteran
84 Posts |
Posted - 2010-01-20 : 15:16:02
|
| Difference as i understand between standby and norecovery mode isStandby Mode - Your secondary database will be available to the users for reading. They can perform select queries till there is any restore of logs not happening on the secondary. The moment a restore begins, all user connections will be dropped.Norecovery Mode - your secondary database will not be available to users for reading. the database will be in restoring mode which means it is expecting more log files to be restored, which is obviously the case in log shipping.-Shan |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-01-20 : 19:42:53
|
quote:
Originally posted by Kristen
I don't think STANDBY mode helps during a normal restore, only useful for standby-servers that are doing log-shipping - unless someone can enlighten me otherwise?
Outside of the log shipping reason for read-only queries, it's so that you can check the data in between the restores to determine where the data went bad such as a bad delete.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 02:12:47
|
| Yes that's true, although I've never found it useful in practice - unless there is some sort of rollback I could use?Example:If I think that the issue occurred between 9am and 5pm I will restore to 12noon. If the problem is present I will then restore to 10:30 ... and so on, using a binary chop.Using standby I would have to restore in, say, 5 minute increments checking to see if the problem was there, so I didn't miss it. If the problem was at 4:55pm I'd have a lot of restores ... and I would still be a few minutes beyond the problem, so would have to re-restore to the 5 minute earlier point if I wanted to find exactly when the problem was. In that final stage StANDBY would be useful.Could I use STANDBY restore to get to 12 noon, and then rollback to 10:30 in some way? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 02:35:41
|
| "But also curious as to why cannot I do the same restoration using the GUI."You should be able to. On the OPTIONS tab, in SSMS, is a radio button for RECOVERY / NO RECOVERY / STANDBY. |
|
|
|
deepayamini
Starting Member
11 Posts |
Posted - 2010-01-21 : 13:51:49
|
| I tried with the GUI, but it allows me to restore only the Full backup file. When I try to add the path for diff backup file and log backup files, it gives me the error that the files dont belong to the same media set and I am not able to continue after that. But with the same set of files, I am able to restore using the GUI on the same server from which the backup was taken. I get the error only when I try to do the restoration on a different server. Probably I am missing somethnig, but I could not figure out what it is.Thanks for all of youre replies and thoughts.With smiles,Deepa. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 13:55:53
|
Personally I think it is easier with the raw SQL commands, but over time I have developed scripts that work well for me, so its probably just familiarity  |
|
|
|
|
SQL server 2005 Full restore