0

From our production server, I have downloaded a .bak file of SQL Server 2014 which I want to restore in my SQL Server 2016 Developer in my pc to test newer versions of the application with the most recent data.

I restore the DB from SSMS and while the progress bar gets at 100% in few seconds (the .bak file is about 145 MB), then the process remains stuck like that and nothing more happens, even if the restoring process seems to continue.

I have noticed that a very huge transaction log is created at once as soon as the restore process starts and it has a size of more than 300 GB.

How can I solve this? I have read maybe I should shrink the transaction log before create the .bak file but I am afraid of causing damages to the production DB. Which am I supposed to do in the correct way?

migrated from stackoverflow.com Aug 8 '17 at 11:37

This question came from our site for professional and enthusiast programmers.

  • Are you using the UI or T-SQL to restore the backup? – virusstorm Aug 8 '17 at 11:12
  • Thank you for you comment. Am I using SQL Server Management Studio. – Lory Lory Aug 8 '17 at 11:12
  • I think you should check your production database size: if full backup is just about 145Mb, even if it could be compressed, it means that the database has at most 1Gb of data. So maybe that db is in full recovery model and no one takes log backups (300Gb of log vs 1Gb of data -> smth goes wrong) – sepupic Aug 8 '17 at 11:13
  • Once you are sure that db is in full recovery model but there is no need to have it in full, you can put it in simple recovery model and shrink the log – sepupic Aug 8 '17 at 11:15
  • @LoryLory,Restore Without transaction log file.Only restore .mdf file only and .ldf file. – Mansoor Aug 8 '17 at 11:17
1

When you backup a database, it grabs all the data from your database and the part of transaction log that it needs to put the database in the consistent state after restore.

When you restore database from backup it creates exactly that database that was backuped, so if you now have a log file of 300Gb it means the original database on your production server has a log file of 300Gb.

Yes you can shrink your log file now, on your test server, but first you should put your database in SIMPLE RECOVERY MODEL. This will not affect your production database.

But if your backup size was 145Mb only, this means that your production database has a problem: someone put it into FULL RECOVERY MODEL and does not take log backups. It's clear from your database size: when log file is >300 times greater than data file, it's a problem with wrong recovery model

Here is the code to check your database size:

use yourDB;
select name, type_desc, cast(size * 8. / 1024 as decimal(20,2)) as Mb
from sys.database_files;

UPDATE:

...to set up a maitenance plan every night so that it does a full backup of the DB, re-organizes/updates indexes and, once a week, updates statistics.Up to now, a night backup has always been enough.

... Anyway, should I want to keep thos transactional logs and the full recovery model, what should I know to improve things? I mean, if I wanted to take log backups in order to reduce transactional logs, what shall I do? Am I supposed to change any settings in the maintenance plan

You don't need FULL recovery model. As for now, your backup plan suppose that it's acceptable to you to lose 1 day of work, and it's normal for a small noncritical database.

When you are in simple recovery model, minimally logged operations like index rebuild or select into will be minimally logged, that means they will be faster.

In case you need to restore your database it will be one simple restore from a full backup.

Your log management will be minimal: it will be automatically truncated on checkpoint.

When you are in full recovery model it means that every change to your data will be fully logged, and all that changes will be retained in your log file until you do a log backup. So what happened to you now is that your log file contains all the changes made to your database from your first full backup (since no log backup was taken) till now.

If you back up your log now, you'll be waiting much more time than you was waiting today while 300Gb of log space was zeroing out. To back up this log you'll need enough disk space, about 300Gb. After this backup completes, your log size will not change even if you'll try to shrink it because the active portion of the log will reside in the last VLF.

So if you decide to stick with full recovery model even after taking this enormous log backup you will still have a problem with your log size. You'll have to wait until the active portion of the log will move to the 1st VLF and it can require some time because your database is small enough, changes are small too, but your last VLFs are enormous. After shrinkig you'll have to decide how often will you take log backups and what is the appropriate log file size for your database.

So who and why chose FULL recovery model? Some HA technologies require it: log shipping, mirroring, AOAG. Or someone who cannot allow data lost. For example, if today your data file got lost, you could take your tail of the log backup (300Gb) and restore your database to the last minute of its life by restoring first your last night full backup and then restoring your log backup. Without it all that you could do was to restore your database from yesterday's backup, i.e. restored database will contain the data at the state of yesterday with all today's work lost.

What they are talking about in the comments? Point-in-time restore. Full recovery model permits you to restore your database to any point in time in the past (only in case you have unbroken log backup chain, of course). Do you really need it? Can it be a situation when you are told to bring your db to 2017-01-04 03:45:00? Or maybe it's enough to you to bring it back to 2017-01-04 00:00:00, the time of your full backup? If only latter is the case, you are good enough to stay in simple recovery model and continue to back up your db every night at 00:00:00

I just do not know how to fit in 1 page the materials that merit a book :) Try to start from BOL: Recovery Models and Transaction Log Management

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.