New answers tagged

0

In general, always run your T-log backups in serial; many of my instances have a couple of dozen databases, and several that are very active, and the transaction log backups only take a few seconds total; up to half a minute or so when it's particularly busy. Running backups in parallel only really would be beneficial if all of the following conditions are ...


0

Should I set up one job that uses ALL_DATABASES? or is it better to set up one job for each database and kick them all off in parallel? I would suggest to setup one job that would backup the transaction logs (serially). This would also make sure backup does not heavily utilities the I/O because you are running backup for database one at a time. What ...


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 ...


3

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 ...


1

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


0

I dont remember. Is there any way to find out. When you detach database you dont delete it you just remove it from SQL Server instance. The mdf and ldf files would be present at location where it was before, if you have not deleted the files. Detaching a database removes it from the instance of SQL Server but leaves the database intact within its data ...


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 ...


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 ...


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 ...


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 ...


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

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 ...


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 ...


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 ...


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 ...


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 ...


0

Its a weird quirk of the Postgres clients that the ones that are remote or in scripts typically connect to the server using its address and are then authenticated by password but when you connect from the same machine via the command line client then it tries to use peer authentication and you get that error. I found from this answer that you can fix it by ...


0

After one week investigation on Sql Server and having taken offline 3 more databases, it seems that I finally fixed the problem by rebuilding indexes on msdb system database. I took a look at fragmentation of indexes and most of them were over 50% average. Rebuilding indexes, sql job for integrity and full backup system databases came back to normal time ...


1

While the specifics will depend on the database system itself this could be handled with pretty much any of the RDBMS platforms out there. Permissions will pretty much handle what you want. The process that puts the data in would need to have insert permissions and all other access would be select only. As far as tracking the modifications - most of the ...


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 ...


0

You can look into tailing the oplogs of the shards yourself, thereby sending the data to a cluster in a remote data center incrementally. Similarly you can look into tools like mongoriver from Stripe or mongo-connector from MongoDB labs if you don't want to do all the work yourself from scratch (but then are relying on a third party keeping that tool up to ...


0

How long does the backup take? How much does it "lock up" the Slave? What time of day is the backup done? What time of day are analytics performed? The answers to those questions will tell you "Will the backup interfere too much with the analytics queries?" The answer to that is the answer to your original question.


0

It is a good idea to use a slave for backups, especially if backups cause problems with operations on the master. Reading from the slave shouldn't present any issues - a lot of people do it as a way to lighten the load on the master. Check that the slave is set to read-only and be aware that long-running SELECTs may cause replication lag and possibly ...


0

Not a proper slave because you are not using mysql replication but anyway it's very common to have an slave or more than one to reduce the load in the master server. Make really sure that its only used for read queries.


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

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 ...


0

Those changes will have no affect on the amount of time system backups take to run. As a first step I would change the job schedules so that the Integrity checks and System backups are not running at the same time. Also make sure that there are no other jobs running at the same time as either of these. If you don't have monitoring in place, jump on the ...


0

We use Ola Hallengren's backup scripts to perform the backups. We have created a custom TSQL script that simply runs on a development server once a week which scans the backups and restores the latest Full / Diff and TLog (Whatever is needed really to restore to the most recent backup). Very simple approach but it works for us in making sure the backups ...


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 ...


0

We make use of Ola Hallengren's Backup scripts to do your daily backups to our network storage and we're having 3 main regions: Americas, Asia and emea. Emea having around 1.000 databases, Americas and Asia having 200 databases each. Based on this MSSQLTips article, I created a stored procedure taking 3 parameters: Database name, server name and backup path. ...


0

"displayed incorrectly" -- That does not necessarily mean that the text is stored incorrectly. How many steps do you have here? You have data in a WP database? Please provide SHOW CREATE TABLE for one of the tables so we can verify whether it is latin1 or utf8. Also, do SELECT col, HEX(col) to see what you have in the table for €. For utf8, the hex ...


0

It somewhat begs the question of why not use rman for your backups instead of user managed backups. rman has been the robust solution since at least 9i, and here we are with 12c being mature.


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 ...


0

There is no way of pausing a running defrag on an index. Having said that, you can always pause BETWEEN defrags as you probably have more than one object to defrag. If you are using the wizard for handling your defrags, you are quite out of luck, however. I always find it better to use dedicated scripts (like Ola Hallengren's scripts) to defragment. The ...


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.


0

Unable to complete login process due to delay in opening server connection The error above happened when you try to do something that take time higher than the original timeout. you can try -l option to increase the timeout for the command. example: sqlcmd.exe -U myuser -P mydb -l200 \\fill other needed info


1

You can try to address the problem by increasing the timeout periods. Increase the standard settings on your server for remote login timeout (s) and remote query timeout (s). Or in your connection string you could include "Connect Timeout=120;General Timeout=120;" to increase the time allowed for the connections.


0

Yes, it can. As mentioned in Making User-Managed Backups of Online Read/Write Tablespaces copies of the datafile blocks modified are written to th redo logs. But checkpoints are no problem.


1

Unless you are actively archiving those LVM snapshots and copying them off the servers, having two live nodes doing snapshotting is not overkill. The alternative would be having a single node that would hold all of your snapshots, meaning you would not be able to restore to a previous state if anything happened to that machine. One important thing to ...


0

You can use script from microsoft gallery How To Restore Multiple Databases In SQL Server


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 ...


1

why it show most of the automatically backup taken in virtual device with "NT AUTHORITY\SYSTEM" user name & by default it shows the backup with virtual device & their some virtual location. This is because some tool or windows server native backup feature is taking a Snapshot backup of SQL Server. Such backups normally use NT ...


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, ...


1

This is possible using piecemeal restore. The source database's objects and filegroups have to be organised in a way that supports this. The RESTORE requires additional specific keywords. While the concept may take a little bit of getting used to, the amount of scripting to implement is no more than that for other solutions suggested.


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

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

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


1

Its a blunder to restore a BIG database on SQL Server with no instant file initialization enabled. The amount of time taken to restore with no IFI is MUCH larger than amount of time taken to restore with IFI enabled. When IFI is enabled what it does is this permission keeps SQL Server from "zeroing out" new space when you create or expand a data file. This ...


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 ...


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.



Top 50 recent answers are included