Restore database with no log
Bill E.
my development machine. I don't want to restore the transaction logs
(there are two) because they are far too large and are unnecessary for
what I'm doing. I would be happy to simply create one new log in my
development environment. Therefore, I backed up the database using
the No_Log option as in
backup database CPTS to disk = 'd:\backups\cpts_backup_billeh.bak'
with no_log
I took the backup file and I'm able to restore it on my development
machine with
RESTORE DATABASE [CPTS] FILE = N'CPTS_Data' FROM DISK = N'D:\archives
\Citrix\CPTS\SupportingMaterials\cpts_backup_billeh.bak' WITH MOVE
'CPTS_Data' TO 'd:\databases\CPTS.mdf', MOVE 'CPTS_Log' TO 'd:
\databases\CPTS_log.ldf', MOVE 'CPTS_1_Log' TO 'd:\databases
\CPTS_1_log.ldf'
Note that I cannot additionally specify FILE = N'CPTS_Log' or FILE =
N'CPTS_1_Log' because I get an error.
The restore created the CPTS_Data file only and returned the message
>>Processed 415664 pages for database 'CPTS', file 'CPTS_Data' on file 1.
The database cannot be recovered because the log was not restored.<<
What additional step should I take to create a log file? I thought
that No_Log was supposed to give me a truncated log file but it didn't
seem to do so.
Is there another way around this problem? I tried taking the the
resulting mdf file and attaching it using sp_attach_single_file_db but
this didn't work either.
Thanks,
Bill E.
Hollywood, FL
Bill E.
Server 2005
Erland Sommarskog
> I'm trying to restore a database backed up a production machine onto
> my development machine. I don't want to restore the transaction logs
> (there are two) because they are far too large and are unnecessary for
> what I'm doing. I would be happy to simply create one new log in my
> development environment. Therefore, I backed up the database using
> the No_Log option as in
> The database cannot be recovered because the log was not restored.<<
>
> What additional step should I take to create a log file? I thought
> that No_Log was supposed to give me a truncated log file but it didn't
> seem to do so.
In the context of a BACKUP DATABASE statement, specifies that a backup
will not contain any log. This equates to how file backups were created
before SQL Server 2005. A database backup created with NO_LOG equates
to a full set of file backups that contains no log records.
Under the full recovery model, NO_LOG is useful if you need to back up
data quickly, and you have a complete sequence of log backups of that
data.
The last paragraph implies that you are expected to apply the transaction
logs from elsewhere.
There is a reason why this does not work as you had expected: you
see, that log serves a purpose. A database backup is taken online, which
means that pages can be updated while it's working. Therefore the
backup must include log records, so that transactions that were committed
when the backup was running can be rolled forward. And more importantly,
transactions that had not yet been committed when the backup completed,
must be rolled back. Thus a database restored from a backup with no log
content is in a inconsistent state, and thus you are not permitted in.
I would suggest that you do a regular backup/restore, and then shrink
the log files once you have restored the database and set the recovery
mode to simple. A tip is to add WITH COPY_ONLY to the BACKUP command.
That prevents the backup from being recorded as a "real" backup.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Bill E.
Thanks for the response. I was fully expecting to see a truncated log
file in my backup, but nothing was there. Perhaps
I misread the BOL entry in the SQL 2005 documentation. I read it
again, but I'm not sure that it's
very clear.
As it turns out, I was unable to restore the full backup because my
drive was formatted using FAT
and it appears that files over 4 GB are not supported with FAT, even
though my total available disk space
is far beyond 4 GB.
I eventually had to do exactly what you suggested -- find a machine
that would allow me to restore
the full backup, shrink the logs and then create another backup that
could be restored on my machine.
This was certainly a pain but hopefully I won't have to do it again.
Also, the log files were so big
because nobody was doing transaction log backups so the log had been
growing for 1 1/2 years.
We made a backup and then did a shrink and hopefully the logs will get
back to reasonable size.
Bill
Erland Sommarskog
> As it turns out, I was unable to restore the full backup because my
> drive was formatted using FAT and it appears that files over 4 GB are
> not supported with FAT, even though my total available disk space is far
> beyond 4 GB.
>
> I eventually had to do exactly what you suggested -- find a machine that
> would allow me to restore the full backup, shrink the logs and then
> create another backup that could be restored on my machine. This was
> certainly a pain but hopefully I won't have to do it again.
I don't want to be nosey, but I can't escape the comment that the
real problem is the FAT partition. I didn't think anyone still was
using that.
I would certainly look into a way to transfer that FAT partition to
NTFS.
Bill E.
that way, but it was. It's a Windows x64 PC. I don't think that I'm
going to go through the trouble of reformatting the drives. This is
the first time that the issue has come arisen.
Bill