New answers tagged

25

No, this is not possible.SQL Server 2017 backups cannot be restored by any earlier version of SQL Server ref Also, regarding detatching and reattaching per the docs: After being attached to SQL Server 2017, the database is available immediately and is automatically upgraded. This prevents the database from being used with an older version of the ...


1

No need to use another tool, you could just: ./mongo -udbadm admin --port 27100 -p --quiet --eval "rs.status().members.find((m) => m.stateStr === "SECONDARY").name" to get: example-1.domain:27200 Note that the find method will return the first element found.


2

Absolutely, that is an option. Do the full backup, and then log backups. At the receiving end, restore all but the last using NORECOVERY. Automating regular log backups and then restoring at the other end is what we call "log shipping", and there's a feature to do that using Agent jobs in SQL Server. Might, or might now, be relevant for you depending on ...


3

From your example, you are trying to insert the results of your query into a table variable and then trying to use that table variable in the sp_send_mail procedure call. That won't work. sp_send_mail needs to reference a real table and not a temp table either.


1

The Log file of database is fragmented too much and there are too many Virtual Log Files. This would force recovery to take longer time. Run dbcc loginfo(db_name) on database and tell me how many rows it returns. Please see How a Log file Can affect database restore time The SQL Server service account does not have "Perform Volume Maintenance Task" privilege ...


1

As long as you state that you did restore before and it took much less time I would quess that some of the following could be the reason: some other process on the reporting server taking a lot of resources (in- and outside of SQL Server), that put high load on disks; slow network connection between backups and reporting server if backups are located on a ...


0

If Azure is anything like PostgreSQL, an online physical backup won't interfere with pg_dump and vice versa (except that both will generate I/O load). pg_dump uses a REPEATABLE READ transaction which guarantees a consistent backup.


0

To answer the two specific questions in your post: Can I change the database property to simple restore and shrink the log file? You can switch to the simple recovery model and shrink the log. This might workaround the problem for a little while if it moves the data off of the faulty area. Or it might cause more problems (if there are more "bad" areas ...


2

I started by extracting the SELECT query into a separate SSMS window. I replaced double-ticks (quotes) with single-ticks and added into dbo.TableForEmailingLogBackups that would allow me to create a real table to hold all of the results. Note: I had to change a part of your main query from WHERE sysdb.name = DB_NAME() to AND sysdb.name = DB_NAME() to ...


4

A possibility would be to check for DB_Name = 'Tempdb', and if it's tempdb just end the script. This is a quick test I did, and it seems to works : EXEC sp_msforeachdb 'USE [?] IF DB_Name() = ''Tempdb''BEGIN RETURN END SELECT db_Name()' That will run the code on each db, but if it's tempdb it wont execute a thing, cause it'll return. It's also known that ...


0

You need them all. Or at least any that contain the "point in time" that you need, plus all that come after that file. When one binlog gets "full" (according to a setting), it stops being written to, and another one starts. FLUSH LOGS also stops the writing to one binlog and starts another. Presumably, your full backlog does such. That becomes the point ...


1

So I tried to restore only filegroup of filestream to other database, but I get an error message As Laughing Vergil originally mentioned in a comment, your question is answered in this great Stack Overflow post: Restore filegroup to different database The gist of the answer is that you always have to start by restoring the database with the PRIMARY ...


1

Deleting files on NAS should not be done by sql server. You should rather use powershell to do it. You can use my function Clean-NASOldFiles which uses robocopy to scan files extremely fast and will then delete based on your input.


1

It's really important to keep the conditional-execution comments. But if you absolutely know that the MySQL version that will load the dump is greater or equal to the one that creates it, you can remove the "comment" part with this: sed -r s'#/\*![0-9]{5} ?([^*]*)\*/#\1#'g It will convert lines such as /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; to SET ...


7

A database is required to have a full backup before it can be added to an availability group, and the method of creating the database has no impact on this requirement. You have to do a full backup on a database prior to adding it to an availability group, whether you: Create a new database Restore it from a full backup (including any combination of ...


8

Copy only backups are generally used for situations where you need to restore the data for an unusual reason without disrupting the current backup chain (assuming there is one). Typical reasons are restores to dev boxes to test code or look at an irregularity in the data. You get the idea. As a side note, taking backups from AG secondaries is kind of a ...


0

I found: --max-allowed-packet=1G --net-buffer-length=32704 ...makes it work where it didn't (reliably) previously, despite net read/write timeout changes, TCP keepalives etc. The max_allowed_packet settings alone didn't make it work, so may not be required if net_buffer_length is used. - ralph-bolton Modifying max-allowed-packet and net-buffer-length ...


4

This is a common issue when you back up to the same file over and over again. Without WITH INIT, the new backup gets appended to the file, it doesn't overwrite it. So in this example: BACKUP DATABASE model TO DISK = 'c:\temp\model.bak' WITH COMPRESSION; GO USE model; GO CREATE TABLE dbo.newtable(id int); GO BACKUP DATABASE model TO DISK = 'c:\temp\model.bak'...


3

When you do a database restoration, all your current data as well as objects - tables, views, functions, procedure anything which is inside database is lost and you get all the objects of the database whose(from) full back up was restored. In case, you were not aware of this - check if you had any scheduled job running on reporting server which was taking ...


6

There are no absolute recommendations we can give you with the information you've given. Things this depends on: Your current backup settings. In SQL 2008 R2 you can compress backups to save on backup size. You should also consider upgrading to SQL Server 2012 (which seems to be supported on NAV 2013). Server performance (CPU/IO): How long does it take the ...


1

That's what you get by following tutorials. It is better to understand the process (as explained in the documentation), then problems like this won't happen. You have to place recovery.conf in the data directory. The data directory is the directory where postgresql.conf is, unless the data_directory parameter in postgresql.conf says differently.


1

Keeping compressed archives of binlogs is a very common way of achieving point in time recovery for your database, assuming you have a full backup from which you can roll forward. Just copying away the binary logs with a remote transfer procedure (e.g. scp) would be fine- they can just be applied as is; however, it may create inconveniences if the binary ...


0

mysql supports incremental backups via the binary (transaction) logs, assuming you are using transaction-safe tables (i.e. InnoDB, not myISAM) which is the default these days. This is covered in the official documentation.


1

What you doing is perfectly fine as long as you issue ALTER DATABASE BEGIN BACKUP; before you archive the data files and ALTER DATABASE END BACKUP; afterwards. It is normal for tar to complain if files are modified while it processes them, and that is no problem. Archive recovery will repair the damage. With GNU tar, you can distinguish that from the ...


6

I just don't get why the private key is needed during the backup operation. Short Answer That's how it's currently implemented. Longer Answer I didn't look at all the dependencies to see if it would be required (logically it wouldn't be needed to encrypt the data, just decrypt as you've already stated) for all of the other calls, but it is checked and ...


2

According to Tail-Log Backups (SQL Server) - (highlighting mine) If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an error for an online database, you must use the ... WITH NORECOVERY option of the BACKUP Transact-SQL statement. If a database is offline and ...


5

There are many possible options, but as databases get larger and full backups take longer, you will likely have to incorporate differential backups, if you haven't already: Creating a differential backups can be very fast compared to creating a full backup. A differential backup records only the data that has changed since the full backup upon the ...


9

A couple of potential solutions: Going from full-only to a weekly full backup and nightly differential can be an easy solution. There are a number of performance-related parameters that you can tweak in Ola's scripts, you might be able to tweak these to get the performance that you want: BlockSize Specify the physical blocksize in bytes. The BlockSize ...


15

There are ways to tune backups by messing with different knobs like MAXTRANSFERSIZE or BUFFERCOUNT, or striping the file (which you've noted you're already doing). The problem is that touching those knobs may still result in hitting the limits of your network and/or storage, and them not having any real impact on backup time. Your first job should be to ...


10

The alternative you mentioned seems to be the best choice. What you can do is a 2 step process : Take native sql server backups with compression using Ola's backup solution locally. Use Robocopy to do the transfers to a network share. This is decoupled and can run as a Windows scheduled task. This way, your backups are local and they will be fast. You ...


0

In MySQL 8.0.17 and later, you can use the redo log archival feature to avoid this issue. References: https://dev.mysql.com/doc/mysql-enterprise-backup/en/meb-redo-log-archiving.html https://dev.mysql.com/doc/refman/en/innodb-redo-log.html#innodb-redo-log-archiving With the redo log archival enabled, InnoDB will copy the redo log events during the backup ...


0

No. Copying to Secondary will not help Yes. Primary will replicate the data to Secondary. Follow the steps below Re-creating the replica set is not an option Steps: You can restore it on the Secondary node first Next, Switch your Secondary with restored data to be the new Primary. Now the Primary has latest data Bring down the Secondary, erase its data and ...


2

does it mean that in case of the db crash last 10 seconds of transactions can be lost? If the database has a "soft" crash, like power failure, it will go through autorecovery upon startup, and will recover all transactions (other than those possibly lost to synchronous_commit = off) using the log files it found in the pg_wal or pg_xlog directory. ...


4

You cannot go back in time and get an incremental backup as of a certain time. If the database is in full recovery mode, you can give them the transaction log backups (if you have them) that go from the backup they restored to the time they want to restore to. However, if they have been using the database, they will have to restore the backup again and ...


1

Hmm, good question. I suggest that you rename it and fill it with zeros at the end, for example using dd if=/dev/zero of=000000010000000C00000080 oflag=append conv=notrunc bs=1024 count=16416 This could be part of your restore_command.


0

I had a same problem, I fixed it by changing the tmpdir path. You can change it to whatever you like, just set the permission for that folder correctly ("write" for all or just for the mysql user).


Top 50 recent answers are included