dvang
I neglected to backup the transaction log as part of the process of backing up the database. Now i only have the backup file for the database and no transaction log backup. When i try to do a restore on the database, i get the error on a "tail log missing" message (which i'm assuming is that it's looking for the t-log backup ).
Is it possible to restore or even restore to a new database I'm only looking to retreive data from 2 tables within the backup file.
Thanks!
SQL Server 2005 on Windows 2003 Server x64.
Re: Is it possible to restore from a database backup without a transaction log backup?
Ekrem Önsoy
You don't have to have a Transaction Log Backup to restore a database if it's the only aim to restore a database.
How do you try to restore it exactly Can you show us the RESTORE DATABASE command that you use to restore your DB
P.S.
I doubt this could be because of the Recovery State that you use while restoring it... If you do not want to keep your database in a Restoring... state, then you ought to use "RESTORE WITH RECOVERY".
You don't have to have a Transaction Log Backup to restore a database if it's the only aim to restore a database.
How do you try to restore it exactly Can you show us the RESTORE DATABASE command that you use to restore your DB
P.S.
I doubt this could be because of the Recovery State that you use while restoring it... If you do not want to keep your database in a Restoring... state, then you ought to use "RESTORE WITH RECOVERY".
Re: Is it possible to restore from a database backup without a transaction log backup?
dvang
I was actually attempting to do it via the SSMS by right-clicking the database, then clicking on restore, then pointing to the .bak file, then execute. I get an error when i do it this way. Any insights
I was actually attempting to do it via the SSMS by right-clicking the database, then clicking on restore, then pointing to the .bak file, then execute. I get an error when i do it this way. Any insights
Re: Is it possible to restore from a database backup without a transaction log backup?
Deepak Rangarajan
You dont need Tran log backup to restore the db if you have full backup you can restore the db, the only difference is that using tran log backup provides point in time recovery and hence the data loss is minimised if you have tran log backup.
Perform as below,
1. Restore database Yourdbname With Recovery (Execute this command)
2. Now try to restore your full backup.
Regards
Deepak
Re: Is it possible to restore from a database backup without a transaction log backup?
Ekrem Önsoy
If it's a FULL backup, then go to Restore Database... (You can see if it's a FULL or not from the Type column in "Select the backup sets in restore" list)
Ensure that in the Restore Database window, in the Options pane Recovery State "RESTORE WITH RECOVERY" is selected
dvang wrote:
I was actually attempting to do it via the SSMS by right-clicking the database, then clicking on restore, then pointing to the .bak file, then execute. I get an error when i do it this way. Any insights
If it's a FULL backup, then go to Restore Database... (You can see if it's a FULL or not from the Type column in "Select the backup sets in restore" list)
Ensure that in the Restore Database window, in the Options pane Recovery State "RESTORE WITH RECOVERY" is selected
Re: Is it possible to restore from a database backup without a transaction log backup?
dvang
This is what i get when i do it via smss, it's looking for the tail log or something
Restore failed for Server 'DK\VANG'.
System.Data.SqlClient.SqlError: The tail of the log for the database "DK01" 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. (Microsoft.SqlServer.Smo)
I forgot to mention that the database that i'm trying to restore back to has been changed (tables have been altered). the backup file was a backup of the database when it hadn't been altered yet. Could this be an issue I'm in a sense trying to restore an old backup to a newer updated database of the same. Even if this will not work, what about just restoring it to a whole new database (create a database from a restore backup ). I'll read up some more.
Any knowledge insights is greatly appreciated!
dvang wrote:
I was actually attempting to do it via the SSMS by right-clicking the database, then clicking on restore, then pointing to the .bak file, then execute. I get an error when i do it this way. Any insights
If it's a FULL backup, then go to Restore Database... (You can see if it's a FULL or not from the Type column in "Select the backup sets in restore" list)
Ensure that in the Restore Database window, in the Options pane Recovery State "RESTORE WITH RECOVERY" is selected
This is what i get when i do it via smss, it's looking for the tail log or something
Restore failed for Server 'DK\VANG'.
System.Data.SqlClient.SqlError: The tail of the log for the database "DK01" 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. (Microsoft.SqlServer.Smo)
I forgot to mention that the database that i'm trying to restore back to has been changed (tables have been altered). the backup file was a backup of the database when it hadn't been altered yet. Could this be an issue I'm in a sense trying to restore an old backup to a newer updated database of the same. Even if this will not work, what about just restoring it to a whole new database (create a database from a restore backup ). I'll read up some more.
Any knowledge insights is greatly appreciated!
Re: Is it possible to restore from a database backup without a transaction log backup?
Deepak Rangarajan
Did you try restoring the db using With Replace option i.e you need to chose 'Force restore over existing database' option in SSMS i believe.
Regards
Deepak
Re: Is it possible to restore from a database backup without a transaction log backup?
dvang
where is that "force restore" option there are a few options at the top in the options tab, i'll test each out...
Did you try restoring the db using With Replace option i.e you need to chose 'Force restore over existing database' option in SSMS i believe.
Regards
Deepak
where is that "force restore" option there are a few options at the top in the options tab, i'll test each out...
Re: Is it possible to restore from a database backup without a transaction log backup?
Ekrem Önsoy
I suggest you to take a look at the following document from BOL:
http://msdn2.microsoft.com/en-us/library/ms186858.aspx
You better use "RESTORE DATABASE" TSQL command instead of GUI.
Also, when you restore on an up-to-date database, you'll lose all your current data. So, if you don't need that data, then delete it all and restore a clean instance of that database. However, you better take a backup of your current database.
I suggest you to take a look at the following document from BOL:
http://msdn2.microsoft.com/en-us/library/ms186858.aspx
You better use "RESTORE DATABASE" TSQL command instead of GUI.
Also, when you restore on an up-to-date database, you'll lose all your current data. So, if you don't need that data, then delete it all and restore a clean instance of that database. However, you better take a backup of your current database.
Re: Is it possible to restore from a database backup without a transaction log backup?
Deepak Rangarajan
Sorry ! You need to chose the option "Overwrite existing database" Pls refer the below link,
http://msdn2.microsoft.com/en-us/library/ms177429.aspx
Regards
Deepak
Sorry ! You need to chose the option "Overwrite existing database" Pls refer the below link,
http://msdn2.microsoft.com/en-us/library/ms177429.aspx
Regards
Deepak
Re: Is it possible to restore from a database backup without a transaction log backup?
dvang
Hey, thanks for the BOL link, i've already begun the process of learning how to do database maintenance/duties via command line vs. GUI. The GUI makes it more convenient, but I should really learn the command line methods as well so I don't become too dependent on the GUI interfaces.
Again, thanks for letting me know this. I was only after 2 tables (which were rather large) on the backup file. The up-to-date database will stand where it is. I guess I'm glad the restore didn't go through as it would have wiped out the data in those 2 tables in the up-to-date database. Honestly, I would have preferred to create a new database from the backup anyways.
I figured it was that option. I've yet to test it out (but i'm sure this is what i'm looking for) and i'll restore to a new database rather than against my current up-to-date database which has been altered. If the restore to new database is successful, i'll just attach the newly created database and copy the tables over from db to db and do my manipulation from there. I'll probably do it today and then get back to everyone here so that if anyone else runs into my scenario, it will help them too. I also did some more reading on those options at:
http://msdn2.microsoft.com/en-us/library/ms188223.aspx
I suggest you to take a look at the following document from BOL:
http://msdn2.microsoft.com/en-us/library/ms186858.aspx
You better use "RESTORE DATABASE" TSQL command instead of GUI.
Hey, thanks for the BOL link, i've already begun the process of learning how to do database maintenance/duties via command line vs. GUI. The GUI makes it more convenient, but I should really learn the command line methods as well so I don't become too dependent on the GUI interfaces.
Also, when you restore on an up-to-date database, you'll lose all your current data. So, if you don't need that data, then delete it all and restore a clean instance of that database. However, you better take a backup of your current database.
Again, thanks for letting me know this. I was only after 2 tables (which were rather large) on the backup file. The up-to-date database will stand where it is. I guess I'm glad the restore didn't go through as it would have wiped out the data in those 2 tables in the up-to-date database. Honestly, I would have preferred to create a new database from the backup anyways.
Sorry ! You need to chose the option "Overwrite existing database" Pls refer the below link,
http://msdn2.microsoft.com/en-us/library/ms177429.aspx
Regards
Deepak
I figured it was that option. I've yet to test it out (but i'm sure this is what i'm looking for) and i'll restore to a new database rather than against my current up-to-date database which has been altered. If the restore to new database is successful, i'll just attach the newly created database and copy the tables over from db to db and do my manipulation from there. I'll probably do it today and then get back to everyone here so that if anyone else runs into my scenario, it will help them too. I also did some more reading on those options at:
http://msdn2.microsoft.com/en-us/library/ms188223.aspx
Re: Is it possible to restore from a database backup without a transaction log backup?
Ekrem Önsoy
After restoring your database from backup, restore it using another name and use Import by right clicking on your target database and click Tasks\Import Data. Follow the wizard and you'll see options to Copy only selected tables and their columns.
This Import thing will meet your needs I believe.
Re: Is it possible to restore from a database backup without a transaction log backup?
Kevin Farlee
If I'm understanding you correctly, what you really want to do is to restore a copy of the database from backup to a new location without disturbing the current database. That way you can extract the data you need from the back-up database.
If this is the case, DO NOT use the option to overwrite the existing database! That will wipe out the current data!
If you care about the current data, then use the "WITH MOVE" option to redirect the backup to new data files and not overwrite the existing ones.
RESTORE DATABASE <new-name> FROM
DISK='q:\some-backup-file.bak'
WITH MOVE 'DataFile1' TO z:\new_location\mydb.mdf',
MOVE 'DataFile2' TO 'z:\new_location\mydb2.ndf',
MOVE 'MyLog' TO 'y:\new_location\mydb.ldf'
WITH RECOVERY
Re: Is it possible to restore from a database backup without a transaction log backup?
dvang
Hey guys, I just wanted to extend a big THANK YOU to you all who have chimed in and helped me with my question, as well as sharing some knowledge on the subject. I don't know what I was doing wrong before, but I just decided to restore to a whole new database (which was the more prudent method and safer too in regards to data integrity).
Used the SSMS interface and restored backup to a new database and got a "device not ready" error. After reviewing all the settings and options of the restore, it seemed the cause of the error was the fact that the file location of the MDF and LDF of the new database was in a drive that didn't exist (how it got to be that way is beyond me). I changed the location of the files in the OPTIONS settings and the backup restored beautifully.
I'm still fairly new to SQL so I do greatly appreciate everyone's help. This is a great place for knowledge.
Thanks to all who has pointed me to command-line restores. I'm am DEFINITELY going to learn how to do restores via RESTORE transact-sql.
x 100!
Hey guys, I just wanted to extend a big THANK YOU to you all who have chimed in and helped me with my question, as well as sharing some knowledge on the subject. I don't know what I was doing wrong before, but I just decided to restore to a whole new database (which was the more prudent method and safer too in regards to data integrity).
Used the SSMS interface and restored backup to a new database and got a "device not ready" error. After reviewing all the settings and options of the restore, it seemed the cause of the error was the fact that the file location of the MDF and LDF of the new database was in a drive that didn't exist (how it got to be that way is beyond me). I changed the location of the files in the OPTIONS settings and the backup restored beautifully.
I'm still fairly new to SQL so I do greatly appreciate everyone's help. This is a great place for knowledge.
Thanks to all who has pointed me to command-line restores. I'm am DEFINITELY going to learn how to do restores via RESTORE transact-sql.