472,805 Members | 837 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,805 software developers and data experts.

Restore database with no log

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
>>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

Aug 14 '07 #1
3 22890
I should add that both production and development environments are SQL
Server 2005

Aug 14 '07 #2
Bill E. (bi********@netscape.net) writes:
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
...
>>>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.
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, es****@sommarskog.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
Aug 14 '07 #3
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

Aug 17 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Tim Morrison | last post by:
MSDE2000 I have an application in which I am running a TSQL command of BACKUP DATABASE and RESTORE DATABASE for the backup and restore commands for my application. For testing purposes, i did the...
0
by: xo55ox | last post by:
Hi, I have been trying to set up an automated restore process from prod to backup server. First, I schedule the full database backup nightly, transfer the backup file and restore it to the...
3
by: Jon Jacobs | last post by:
I attempt to back up a database on one server and restore it on my local machine. This is what the query text looks like: restore database model from Disk='c:\JQJ\mydump\model.bak' with...
4
by: Hardy | last post by:
hi gurus, now I have to backup and restore a 8 T size db2 database. from two s85 to two 670. the partitions,tablespaces of the db should be redesigned then I plan to use redirected restore. but...
9
by: GL | last post by:
I am running DB2 8.1.1 on AIX 5.1 Having a problem with a redirected restore. Once into the restore continue phase, I immediately get the following SQL2059W A device full warning was...
2
by: anna_cheng11 | last post by:
We do not have a DBA available, and this is a development environment, hence I was asked to do the work. I am not a DBA. I need some help to clarify my understanding of DB2 recovery and I am...
11
by: Chris | last post by:
I have searched this group for answers and tried the responses. I am trying to Use an full online backup from our production server and apply it to our test server. The Tablespaces in the...
0
by: Takpol | last post by:
Hello, I have several archived filegroups that have data in them partitioned based on the date. These filegroups have been removed from database after archival. For example two months ago....
5
by: HSP | last post by:
hi. i need to restore an old database. The db was backed up using a DLT drive, using 2 volumes. The content for the tapes was copied to file onto Solaris machine using rsh and dd (for backup...
5
by: chow.justy | last post by:
Dear all, I'm a new beginner of DB2. I face 2 question during restore the data. I have 2 DB2 servers on my company. Server A is running on V7.2 and Server B is running on v8.2 (Enterprise...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.