How to restore a .bak from full-recovery mode without trans logs
in [Programming SQL]
|
Prev: white circle in Management Studio servers ?
Next: The TRUNCATE TABLE SQL construct or statement is not supported
From: Analyst-as-Infrastructure-Savior on 15 Jan 2008 11:31 Hi, I'm in a small company (no DBA). We have a SQL 2005 database that was created in full-recovery mode. Full backups were performed along with transaction logs. However, all the transactions were part of a testing and we now want to restore the DB up to the last .BAK file (wihout the transaction logs). Unfortunately, when I grab the .BAK file, it tells me that another media family (the trans logs) is needed, which I don't want to restore. Is there a way to just restore the already backed up .BAK file without the trans logs? (I know that for the future I will just use single recovery mode). Thanks,
From: Tom Moreau on 15 Jan 2008 11:44 Could you post the actual RESTORE command you are using and the exact error message? -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "Analyst-as-Infrastructure-Savior" <Analyst-as-Infrastructure-Savior(a)discussions.microsoft.com> wrote in message news:71F857DB-0891-46C2-A3B0-7F22DE2772E0(a)microsoft.com... Hi, I'm in a small company (no DBA). We have a SQL 2005 database that was created in full-recovery mode. Full backups were performed along with transaction logs. However, all the transactions were part of a testing and we now want to restore the DB up to the last .BAK file (wihout the transaction logs). Unfortunately, when I grab the .BAK file, it tells me that another media family (the trans logs) is needed, which I don't want to restore. Is there a way to just restore the already backed up .BAK file without the trans logs? (I know that for the future I will just use single recovery mode). Thanks,
From: Tibor Karaszi on 15 Jan 2008 12:15
In addition to Tom's reply: When you do a restore from a full backup, then you don't need any transaction log backups. Recovery model doesn't matter. It seems to me like you (possibly by mistake) striped your backup over several files (like RAID 0). You can check this using RESTORE LABELONLY. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Analyst-as-Infrastructure-Savior" <Analyst-as-Infrastructure-Savior(a)discussions.microsoft.com> wrote in message news:71F857DB-0891-46C2-A3B0-7F22DE2772E0(a)microsoft.com... > Hi, I'm in a small company (no DBA). We have a SQL 2005 database that was > created in full-recovery mode. Full backups were performed along with > transaction logs. > > However, all the transactions were part of a testing and we now want to > restore the DB up to the last .BAK file (wihout the transaction logs). > Unfortunately, when I grab the .BAK file, it tells me that another media > family (the trans logs) is needed, which I don't want to restore. > > Is there a way to just restore the already backed up .BAK file without the > trans logs? (I know that for the future I will just use single recovery > mode). > > Thanks, > > |