1

I truncated the wrong table, but I have a backup made an hour before the incident.

I am trying to restore but at 30% I get the error:

Restore of database 'msdb1740' failed.
(Microsoft.SqlServer.Management.RelationalEngineTasks)

System.Data.SqlClient.SqlError:
Write on "C:\...\Backup\msdb1740_LogBackup_2016-03-19_09-50-09.bak"
failed: 112 (There is not enough space on the disk.)
(Microsoft.SqlServer.SmoExtended)

I have freed up some extra disk space but had no luck. I have transferred the .bak file to another computer and tried it there but that gives me a permissions error.

How can I restore this database, or even better, can I restore just one table in the database?

migrated from stackoverflow.com Mar 19 '16 at 11:06

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

1

It is not possible to restore a single table from an earlier point in time directly into an existing database.

You can restore all or part of the database as a new database, then copy the required data across. Or, you can restore using the REPLACE option, but the whole database will be reverted to the earlier point in time, not just the table you are concerned about.

If all your data is stored in the PRIMARY filegroup (the default), you have no choice but to restore the whole thing. If you have multiple filegroups, you may be able to restore just some of the filegroups, reducing the space needed. The details depend on the database recovery model as well as how any filegroups are arranged, see:

You can determine in advance how much space will be needed for the restore by running the following command and inspecting the output:

The paths to the files that will be written are all from the perspective of the SQL Server instance, not the user performing the restore. You will need to ensure that enough space is available for all the files that will be restored. Note that the size of the file may be larger than the amount of data stored in the file, and larger than the size of the backup file. SQL Server only writes the data actually stored to a backup, which may also be compressed, but a restore must create the full file size.

The process of creating the data files will be much faster if Instant File Initialization is enabled on the restore target.

Backup and restore is a large topic, well covered in the documentation, see:

The T-SQL RESTORE command offers more flexibility and control than using the SQL Server Management Studio UI.

If you have differential and/or transaction log backups as well as a full database backup, you may be able to apply one or more of these to get closer to the point in time when the accidental truncation occurred.

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.