Tag Info

Hot answers tagged

11

I turned to my copy of SQL Server 2008 Internals and the DMV sys.database_recovery_status was pointed out to find the first LSN of the next log backup. Which going by BOL the column last_log_backup_lsn provides you with: Log sequence number of the most recent log backup. This is the end LSN of the previous log backup and the starting LSN of the next log ...


9

I've been doing some testing of different methods for compressing and storing MS SQL Backups (using MS SQL 2008 R2 Enterprise edition), and I'm wondering what the most effective compression algorithm is for long term storage of those backups, outside of SQL's internal compression algorithms. Since you are using SQL 2008 R2 Enterprise edition, you ...


7

No, you cannot rely on system or NTFS backups as an appropriate disaster recovery strategy for SQL Server. If all your current solution is doing is backing up the database files (i.e. mdf, ldf, ndf) and if you needed to revert to those "backups", then you could potentially run into a horrific situation where SQL Server can't use those database files. You ...


6

In terms of backup compression, I did (a couple of years ago) make a comparison of the backup compression options provided by Red Gate's SQL Backup, Quests's LiteSpeed for SQL Server, and Idera's SQLSafe, benchmarking the three products. The differences in a typical backup at maximum compression was about a 5% spread between the three for time taken, and a ...


6

Something like the following should do it. WITH LSN_CTE AS ( SELECT TOP 1 LEFT( LogRecords.[Current LSN], 8 ) AS Part1, SUBSTRING( LogRecords.[Current LSN], 10, 8 ) AS Part2, RIGHT( LogRecords.[Current LSN], 4 ) AS Part3 FROM sys.fn_dblog(NULL,NULL) AS LogRecords ORDER BY [Current LSN] ) SELECT CAST( CAST( CONVERT( ...


5

Unfortunately, it's not possible to take an online backup of a DB2 database if it's in circular logging mode, which is the default for DB2 databases when they are created. You can check whether your database is using circular logging by issuing: ./db2 get db cfg for dbemp | grep LOGARCH If both LOGARCHMETH options are switched off... First log archive ...


5

No, do not use WITH INIT for transaction log backups to the same file. If you do that, you may as well not take them in between full/diff backups. Typically you will backup the log to a unique file each time. Each one will have some form of a timestamp in the name and have a .trn extension rather than .bak. This is mostly for management purposes - it makes ...


5

Remote file access occurs over SMB. Remote backups are just another form of remote file access. See File and Sharing Protocol Stack. If you use SMB over TCP directly (ie. disable NetBIOS) then the port used is 445, both UDP and TCP. If you use NetBIOS then it requires 137 both UDP and TCP, 138 UDP and 139 TCP. See Directly Hosting SMB over TCP/IP: ...


4

Unless you are running all of your databases in simple recovery mode, you are eventually going to run out of transaction log space. Read some good write-ups about transaction log management. As transaction log management is fundamental a topic of managing an Sql Server, I'd recommend you to pick up a book about the subject too. For example, Microsoft Press ...


4

Thanks for posting. so at most we would lose an hours worth of work. If your transaction logs are on a different disk array than your data file, you perform a 'tail of the log backup'in case of a data array failure. This would ensure minimal data loss so you wouldn't even lose that hour. Can I use WITH INIT on the transaction log backup to force ...


4

Yes, there is, though you got to use RESTORE command anyway. Instead of RESTORE DATABASE, use RESTORE FILELISTONLY to get a detailed view of files in the backup. The Size column tells the file size in bytes. RESTORE FILELISTONLY FROM DISK = N'v:\MyBackup.bak'


3

If something is taking a snapshot behind the scenes that breaks SQL differential backups causing a full to need to be taken before a new differential. You can either stop whatever is taking the snapshot or ensure you're taking a full backup afterwards before you try to do a differential. If you use Ola Hallengren's Maintenance Solution that can ...


3

Rebuilding an index needs enough space to create the new index. A simplified rule of thumb seems to be that you need about 120% of the space used by the original index. This may be in the database or in tempdb, depending on whether SORT_IN_TEMPDB is ON or OFF. If possible, have SORT_IN_TEMPDB = ON reduced some of the logging that is done. If you rebuild ...


3

Depending on your requirements and what other backups you have done since then, you could use (from http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmreprt.htm#BRADV89594): REPORT NEED BACKUP RECOVERY WINDOW OF n DAYS Displays objects requiring backup to satisfy a recovery window-based retention policy. REPORT NEED BACKUP REDUNDANCY n Displays ...


3

Depending on your database size, checkdb can take hours to finish. It is heavy on the disk subsystem as well incurring lots of I/O's. Check out Aaron's excellent article on : Minimizing the impact of DBCC CHECKDB : DOs and DON'Ts Backups are also I/O intensive operation. I would suggest to look for a maintenance window - when activity is low or minimal on ...


2

Look at the duration of the job, it's zero seconds. The job is completing successfully but the job must be configured incorrectly. It won't actually be doing any backups in milliseconds, so look at the package to make sure it's set up properly. Have you checked the right database? And is this a secondary node in an availability group as per the warning ...


2

The msdb database keeps history of where, when, who, how big etc. of backups. I have often needed to know where a backup went or who did it or when was the last backup. This is set to return the last two days of backup history. You can put a specific database in the value, if you leave it blank it will return for all databases. This query works for 2005+. ...


2

Online backups require that the database be enabled for rollforward recovery. However, this is not the default when you create a database. In order to do this, you need to set the LOGARCHMETH1 database configuration parameter. Once you have done this, you'll need to take one offline backup (i.e., no users can be connected). Once you've completed these ...


2

If root has no password you could just do this: mysql -u$USER database_name < backup.sql If you want to code so that root can be given a password later, do this in the script USER="root" PASS="" if [ "${PASS}" == "" ] then PASSWORD="" else PASSWORD="-p${PASS}" if mysql -u${USER} ${PASSWORD} database_name < backup.sql Give it a Try !!! ...


2

You're correct. If you want PITR capability, you need to keep all archives between when the base backup was taken and when you want to be able to recover to. For this reason it's often a good idea to do a fresh base backup (say) weekly, and rotate them out, so you keep a base backup plus a week of archives. PgBarman should be able to help you with this. ...


2

I'm not sure if I fully understand your question. First you say you want to find the last restore made to a certain database. You can find all relevant information (databases, files, lsns, times) on these restores through SELECT * FROM [msdb].[dbo].[restorehistory] r INNER JOIN [msdb].[dbo].[backupset] b ON b.backup_set_id = r.backup_set_id But I'm not ...


2

Hello you need to change max_locks_per_transaction in the postgresql.conf file typically located in /var/lib/pgsql/data or wherever you installed the data directory for PostgreSQL. Which was already answered here You will have to restart the PostgreSQL Service. The answer above also talks about having to possibly increase shared memory. Here is a link to ...


2

The tablespace content type (Datatype in your second example) is defined in sqlutil.h: /* table space contents */ #define SQLB_TBS_ANY 0x0 /* All permanent data. Regular table */ /* space. */ #define SQLB_TBS_LARGE 0x20 ...


2

You can't use differentials as stated in the other answer, but you CAN use database snapshots. They require Enterprise Edition, but they function exactly like what you're looking for. You take the snapshot, start your deployment, do user acceptance testing, and then either delete the snapshot or roll back to it. Be aware that "snapshots" is an overloaded ...


2

Just tell me that you don't have databases on the system drive. :) Seriously, what they said. If your databases aren't in simple recovery, they must have transaction log backups or the log will grow until it eventually consumes the disk. (Which is why I said "not the system disk." One of my lesser colleagues once installed software, including SQL ...


2

I am doing remote backup by backing-up locally and then copying the bak files to remote storage file share. I do not think it is much different if to backup directly. Anyway I've checked it with WireShark: having shown that TCP port 445 was used. Though, depending on Windows version (it was not even clear if your backup will be stored on a device ...


2

Turns out we needed to remove the full text catalogs from 2005 then take the backup. We then recreated the catalogs in 2012 and all is good. Following a single full backup, the t-log maintenance plan runs fine.


2

When you take a backup all information including the file path is included as part of the backup. As you found out if you just run restore database it will try to restore to the exact same location. when you include with replace, the backup overwrites the existing .mdf and .ldf files. I normally also have the data and log path directives to specify where ...


2

Write the backups to a different drive (or array, set of spindles, controller, whatever), or to the network. To back up to the network you'll need acceptable bandwidth, and run SQL Server as a domain account. Edit: With your new information, I'm guessing you have poor-performing I/O on the server. As a stopgap measure, try to get the backups to complete ...


2

Operating system error 2 is a standard Windows operating system error--file not found. Check the permissions on the folder and make sure that the account that owns the agent job has access to the folder and is able to traverse the path to the folder where the backup is trying to write. Unfortunately, this is a Windows error message and not a SQL error. ...



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