Restore Question

Hi,

I am using SQL 2008

I have two databases, one on my dev server and one on my production server.  Both are the same databases but they have different .mdf and .ldf sizes.

I'm have backup (.bak) of the production database and I want to overwrite the one in dev but the production one has a huge transaction log and there isn't space on the dev server to store.

How can I overwrite the dev database with the production database backup file WITHOUT truncating the log in production.

Thanks,

-ws
LVL 1
Westside2004Asked:
Who is Participating?
 
SQLTriVeggCommented:
When restoring a database with logs in SQL Server, you MUST start with the full backup, then apply EACH of the transactional logs in the correct sequence, beginning with the first log backup, ending with the last log backup.

This the same as saying that if the destination database has already been restored from a full backup, you only can apply logs in the order of which they were made, in the same order.

Back to your question, if you want to restore things to where they were at 1pm, and you did a tran log backup at 1pm, you MUST restore that - the 5pm tran backup cannot be used to to restore to the 1pm state.

Another way to decrease the size of your log backups - perform the log backup more often.  

Also keep in mind there are 2 things that are being discussed.  Things get confusing unless you understand both.   :)
1.  Transaction log backups.  The more activity (inserts, updates, deletes) between these backups, the greater the file size of the backups.
2.  Transaction Log, also simply called the "log" file.  Every database has a minimum of two files, one is the database file, the other is the log file.  The log file can get big if you run a long transaction.  For example, a DELETE or INSERT query involving millions of rows.  Prior to the query, the log file may be small.  It the max size of the log file is set to auto-grow, then after the transaction it will have gotten a lot bigger.  (If it wasn't set to auto-grow, then the query would have failed).  But after the next checkpoint, which occurs frequently and automatically, the info in the log file is written to the database file.  The size of the log file is now large.  This is ALLOCATED space for the future.  It is not really a problem if your database log file is large (allocated) - unless you have space issues on your disk, in which case you can use DBCC ShrinkFile.  But having a large file will not affect the size of subsequent log backups, EXCEPT for the first time.  

Hope this helps.
0
 
zadeveloperCommented:
TRY

RESTORE DATABASE mydb
FROM DISK='c:\path\mydb.bak'
WITH REPLACE,
MOVE '',
MOVE '',
NORECOVERY

Open in new window

0
 
Westside2004Author Commented:
I get an error:

Incorrect syntax near ','.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
zadeveloperCommented:
which one?
0
 
dbidbaCommented:
Not sure how to restore with restoring the tranlog, but you will want to look into why the tranlog is so large. So just checking, please confirm that your production database is either in simple recovery mode, or you are running frequent transaction log backups.


URL: Database Journal: SQL Server 2008 Recovery Models and Backups
http://www.databasejournal.com/features/mssql/article.php/3782401/SQL-Server-2008-Recovery-Models-and-Backups.htm

"This recorded history doesnt remain in the transition log forever though. If it did, the size of the log file over time would become huge and unmanageable. Instead, the log file is periodically cleared, or Truncated. The amount of time the log file is allowed to grow before being truncated is determined by the database Recovery Model."

0
 
Anthony PerkinsCommented:
>>How can I overwrite the dev database with the production database backup file WITHOUT truncating the log in production.<<
Restoring the production backup on your development database is not going to truncate the log in production.  How can it?  Once the backup is taken they are unrelated?

0
 
Westside2004Author Commented:
I ended up backing up the transaction log and then running dbcc shrinkfile(logfilename, <size>)

I did this twice, and then I was able to shrink it down.  

1. Is this approach ok?

2. Is it fair to say that if one gets in this situation where they have large transaction log files to effectively back them up you need disk space to do so?

Thanks,

-ws
0
 
zadeveloperCommented:
You may want to restrict the log file size growth. At the moment you would have it set to autogrow. Check your database properties.


0
 
dbidbaCommented:
>2. Is it fair to say that if one gets in this situation where they have large transaction log files to effectively back them up you need disk space to do so?

It is a question of your recovery goal. If you require point-in-time recovery of the database, you must do frequent transaction log backups,

If you are fine with only being able to restore to the time when the last full or differential backup completed, just set the database to simple recovery model and SQL Server will remove completed transactions from the tranlog for you.

0
 
Westside2004Author Commented:
If I did a tran log backup at 1:00 pm and another at 5:00 pm, and I wanted to restore the database to the state of how things were at 1:00 p.m. do I have to restore the log files in the order from most recent backwards?

Thanks for your assistance here.....

-ws
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.