Sql-server – SQL Server 2016: cannot restore database from .bak file because of huge transaction log

restoresql serversql-server-2016transaction-log

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?

Best Answer

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

Related Solutions

Sql-server – Is it possible to restore sql-server bak and shrink the log at the same time

There is no way to shrink the backup as a part of the restore process. The restored database must look exactly like the source database with the only exception being that you can change the drive letters and folders around.

Sql-server – shrink the transaction log file on a mirror database

The DBCC SHRINKFILE command will be mirrored from the principal to the mirrored database. Here's some proof.

Create a sample database on the principal:

create database MirroredDb;
go

Create the same database from a backup with NORECOVERY:

restore database MirroredDb
from disk = '\\backupdir\MirroredDb.bak'
with norecovery;
go

Setup your mirroring session however which way you choose.

On the principal database look at the database file sizes:

use MirroredDb;
go

select
    name,
    size
from sys.database_files;

My result set looks like this following:

name            size
MirroredDb      392
MirroredDb_log  104

On the mirror database, create a snapshot and look at the same information:

create database MirroredDbss
on
(
    name = 'MirroredDb',
    filename = 'c:\sqlserver\MirroedDb.ss'
)as snapshot of MirroredDb;

use MirroredDbss;
go

select
    name,
    size
from sys.database_files;

My result set looks like the following:

name            size
MirroredDb      392
MirroredDb_log  104

Now grow the transaction log file on the principal database (I brought it to 1 GB):

alter database MirroredDb
modify file
(
    name = MirroredDb_log,
    size = 1GB
);
go

Looking at the principal database's transaction log size, we now see the adjusted size:

use MirroredDb;
go

select
    name,
    size
from sys.database_files;

My result set looks like the following:

name            size
MirroredDb      392
MirroredDb_log  131072

Create another snapshot on the mirrored database, and look at the transaction log file size there:

create database MirroredDbss2
on
(
    name = 'MirroredDb',
    filename = 'c:\sqlserver\MirroedDb2.ss'
)as snapshot of MirroredDb;

use MirroredDbss2;
go

select
    name,
    size
from sys.database_files;

My result set looks like the following:

name            size
MirroredDb      392
MirroredDb_log  131072

Now do the DBCC SHRINKFILE on the principal:

use MirroredDb;
go

dbcc shrinkfile('MirroredDb_log', 0);
go

select
    name,
    size
from sys.database_files;

My result set is the following:

name            size
MirroredDb      392
MirroredDb_log  104

Create a third and final snapshot on the mirrored database, and look at the size:

create database MirroredDbss3
on
(
    name = 'MirroredDb',
    filename = 'c:\sqlserver\MirroedDb3.ss'
)as snapshot of MirroredDb;

use MirroredDbss3;
go

select
    name,
    size
from sys.database_files;

And I get the following result set:

name            size
MirroredDb      392
MirroredDb_log  104

So as you can see here, the DBCC SHRINKFILE command is in fact mirrored to the mirror database.

Best Answer

Related Solutions

Sql-server – Is it possible to restore sql-server bak and shrink the log at the same time

Sql-server – shrink the transaction log file on a mirror database

Related Question