Hot answers tagged

11

On a technical level: is this a good practice or not? I would say not. If the database is experiencing no activity at all during the business off hours, then taking the backups is very low overhead. On the other hand, if the database is experiencing any activity during business off hours then not taking the backups is a serious problem. My stance is ...


10

The short answer is yes. As usual, after full backup follows differential backups, that completely depend on the last full backup. It means that if the database failure occurs there will be no chance to apply scheduled differential backups because they will be with another LSN. The solution is to use COPY_ONLY option.


7

The new additional full database backup will be affected on the scheduled backups. Let's consider the following example: Assume that the full database backup starts at 12:00 once a day, a differential backup starts every three hours and transaction log backup makes every hour. Then after scheduled full backup was made at 12.00 you decided to make the extra ...


7

You probably misunderstood the recovery model concept. From BOL Doc A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available Recovery model controls the logging and there by how much data you can recover. Only ...


6

Recently I have faced with the same issue. I have googled for a while and found that this is a problem in the Microsoft products. I wrote the article according to this error message, so you can find more information there. So, to solve this issue and restore your database to point-in-time use T-SQL commands: Restore your last full backup RESTORE DATABASE ...


6

You've addressed a boatload of items in your question. Thanks for being so thorough! Just a couple of things I notice off hand: How various hardware / load factors influence what should be done. Are you running a 24x7 instance? What is the load around the clock? I notice you have backup compression disabled; is that by design for the test, or ...


5

To solve this issue, you need to know how full and bulk-logged recovery model work. So, let' start from Full recovery model: In the full recovery model, full database backup does not truncate committed transactions until you make a transaction log backup. It allows creating full, differential and transaction log backups thus making point-in-time recovery ...


5

The number one reason is operator error. The 'whops, I dropped the wrong table' moment. Next are catastrophic hardware failures (lost, unrecoverable, disk). Non-disk failures are actually less problematic because the data can usually be retrieved by attaching the (still working) disks to a different host. Disk I/O errors (corruption of media at rest) used ...


4

I would say that there isn't necessarily a Best Practice, this is more of a matter of personal preference or your businesses operating procedures. I've had separate folders per database, then sub folders for FULL, DIFF, LOG at some jobs, others everything goes to a single folder and the backup name is some combination of database name, backup type, and ...


4

I think it is important to make transaction log backups, but everything depends on the individual case. For example: Your database grows slowly and changes occur very rarely, so, in this case, you can use simple recovery model, and backup plan like you described. You know that simple recovery model allows full and differential database backups ...


3

Yes, it will affect on the scheduled backups (LSN chain). If you need to make one additional full backup. You need to use COPY_ONLY option. This option will make the additional full backup that does not alter the differential chain. This way you do not affect the LSN chain at all. The LSN’s of any differential backups taken after the new ad hoc backup ...


3

If I make full/differential/log backups to disk under full recovery model it is important to specify MEDIANAME? NO, it is not important Is it true that MEDIANAME is only for tape backups? If not, when and why should I specify MEDIANAME in the backup statement? No it is not just for tape backups, you can take local disk backup and specify ...


3

Provided that you have alerts set up on the jobs, Log Shipping is a feature that is available on all editions, doesn't require a whole lot of effort to set up, and I believe fulfills all of your requirements. I would avoid any wizards that you might stumble upon in Management Studio. Essentially you need three jobs for the following tasks: Initialize the ...


3

Seeing as we take regular full db backups at midnight, would it be safe for me to temporarily put the database into Simple Recovery Mode (after one of these backups has run), shrink the log file to reclaim (virtually all of) the space and then put it back in to Full Recovery with the backup strategy mentioned above? Yes, it would be safe ...


3

Just an F.Y.I that the transaction log for that database contains all transactions since the last transaction log backup, or the last time it was switched from simple recovery mode. Execute the following to get the definitive reason as to why SQL Server can not truncate the log and subsequently why the log is growing. SELECT d.Name ...


3

You can specify SKIP options in your backup command. For example: BACKUP DATABASE SKIP INACCESSIBLE; or take the datafile offline, and: BACKUP DATABASE SKIP OFFLINE; skipSpec


3

Short answer: add ", INIT" to the end of your WITH statement to re-initialize the backup file on every backup. Per Microsoft: INIT Specifies that all backup sets should be overwritten...NOINIT is the default. https://msdn.microsoft.com/en-us/library/ms186865.aspx I modified your code below to include the new change: use My_database go checkpoint go use ...


2

To address your first question, if you take a single basebackup of your database and just keep archiving away the WAL segments, the time to restore that database will grow longer and longer as the amount of WAL segments increases. That is not really an ideal situation to be in if you have downtime and need to bring a working database back up. Most people ...


2

I just want to avoid to spare disk space if possible. I'm searching for a way to "shrink on the fly" and then create the bak.. but seems to be impossible Well, general answer here will be NO, there is no easy/simple way to get this done seeing the amount of free space , which is 50GB compared to the database (200GB) whose MDF is 80 GB. Shrinking ...


2

This restriction of 'instant file initialization' (aka. SetFileValidData) is documented: Note The file cannot be a network file, or be compressed, sparse, or transacted.


2

The short answer is - after you made some changes to a table all this information are recorded into transaction log immediately, but all changes that you made are recorded into data file during the checkpoint. After the transaction log backup, all virtual log files marked to be reused.


2

Data Domains use their own deduplication and compression features, which don't always play well with SQL Server's own compression. That's the main point. Whether this is problematic or not depends on many factors. If you're not compressing your backups with SQL Server, the Data Domain will be able to deduplicate the data more efficiently, but more data ...


2

It's not generally a good idea to ask a question IN ALL CAPS, BECAUSE PEOPLE THINK YOU ARE YELLING. Hence, I edited your question. You also should try to show that you've attempted to do something before just coming here to ask people to spend their time looking something up when it seems like you haven't spent any of your time doing just that. Anyway, ...


2

23068672 bytes = 22 MB, not 20 GB. If it were really 20 GB the only thing I could think that would make it that big would be reusing the same file for multiple backups and appending each backup to it.


2

What are the performance drawbacks when running an Index Defrag during a Full Database Backup? Any database operation will generate extra log that the full backup needs to capture (the Full backup contains, at least, all log denerated during the backup). However, defrag is fairly small log overhead. I would not be worried. Is there a way to ...


2

Instead of making zip file of the backup use COMPRESSION option of BACKUP command. It will compress backup and you can use it directly for restore (skipping unzip step). It is supported from SQL 2008 onward (I see you have SQL 2012) and the compression is very good (compared to compression ratio / speed). See BACKUP command for more details. You can check ...


2

According to a grep in the sources, this error ERROR: compressed data is corrupt happens in case of a decompression failure of a LZ-compressed TOAST'ed value. See http://doxygen.postgresql.org/tuptoaster_8c.html#abcb4cc32d19cd5f89e27aeb7e7369fa8 At the row-level storage, large values are stored as pointers to tables in the pg_toast schema containing ...


2

Which SP of SQL Server 2012 did you use? This is a known issue with SQL Server 2012 SP2. The information for "Restore to:" is empty, which was supposed to show the timelines. To work around this issue: Use SQL 2014 SSMS Use T-SQL


1

When you use Delete Database from modern versions of Management Studio, it asks whether you want to delete the backup history for this database in MSDB. The reason that a deleted database is showing in your query is simply because backup history exists for it - in MSDB. I adjusted your query by joining backupset to sysdatabases, so it excludes databases ...


1

From MediaName Property: The MediaName and MediaDescription properties are written to a tape medium when the medium is initialized. From Backup Object: For example, the MediaName and MediaDescription properties provide, primarily, data used to ensure media availability for tape devices and are applicable when the backup operation defined will ...



Only top voted, non community-wiki answers of a minimum length are eligible