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

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_bille h.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


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

I should add that both production and development environments are SQL
Server 2005

Bill E. (billmiami2 (AT) netscape (DOT) net) writes:
Books Online says about NO_LOG:

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, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland,

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

Bill E. (billmiami2 (AT) netscape (DOT) net) writes:
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.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

You're not being nosey. I don't know why this little PC was formatted
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