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


11

Does the backup process use only the memory that has been assigned to the SQL Server (instance)? The memory that you assign with MIN and MAX memory is just for Buffer Pool and for SQL Server 2012 and up, the memory manager has changed. When a backup starts it creates a series of buffers, allocated from the memory outside the buffer pool. The ...


10

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


6

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


6

Disclaimer : SQL Server 2005 is out of mainstream support. - extended support ends for sp4 on 04/12/2016 Migration always have a downtime. So depending on how much your downtime is, you can go with setting up either : Logshipping ( I have used this method and it reduces the downtime considerably) a. Set up logshipping from the source to destination ...


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


5

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'


5

In short - yes, there are standard operating procedures for doing all of this with Oracle. You should start by looking into RMAN (Recovery MANager). I have put together a high level overview of RMAN as well as an introduction to Oracle backups for SQL Server DBAs. I suggest watching both of those and then heading over to the Oracle Database Backup and ...


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

I want to know the best and the most convenient way to migrate the whole data includes: - Security and permission - Users and memberships - All of the databases and .. Best method to migrate data and all objects related to particular database is by using backup and restore method.Perhaps since you want to migrate whole database to new instance you can ...


4

Code gets backup history for all databases on a server and generates HTML report Step 1: DECLARE @Body VARCHAR(MAX), @TableHead VARCHAR(MAX), @TableTail VARCHAR(MAX) SET NoCount ON ; SET @TableTail = '</body></html>' ; SET @TableHead = '<html><head>' + '<style>' + 'td {border: ...


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


3

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


3

Well, you can keep using mirroring. "Deprecated" does not mean "doesn't work any more" - it just means that, at some point in the future, it won't be supported when you move to some version > 2012 (probably at least 3 versions later, maybe more given the backlash this announcement has caused). By then, I am sure they will have a replacement for mirroring ...


3

No there is no option or restriction which we can enforce so that a user can take copy_only backup when ever he tries to take any backup but what you can do is create a procedure which backups database with copy only option and grant execute on the procedure to various users and then remove backup database privilege from the user. BACKUP DATABASE and BACKUP ...


3

You could create a simple audit report which shows who is doing the non-COPY_ONLY backups, eg select user_name, min(backup_start_date) min_backup_start_date, max(backup_start_date) max_backup_start_date, count(*) records from msdb.dbo.backupset where is_copy_only = 0 group by user_name


3

You have corrupted your database by manually deleting files from within the data directory. Never delete files from within the data directory manually. Safely removing WAL If you want to remove WAL, either let the server do it at CHECKPOINT time, or use pg_archivecleanup. Note that the server will remove WAL is no longer needs automatically, unless: ...


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

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



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