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


7

Since this logic is buried within the code for the SSIS package behind maintenance plans, I think you will only get guesses unless the author happens to swing by here. Here is my educated guess based on a few experiments: For SQL Server 2008 and above, the 7 digits are the sub-second portion of the datetime2(7) output of SELECT SYSDATETIME(); at the ...


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

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

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


5

Restoring a full + differential backup restores you to the point in time that the differential backup started. If you just took the differential this second and nothing has happened in the database since it started, then yes, that is the latest state. But it's quite unlikely that you will have a disaster the minute you take a differential backup and that ...


5

This will be tough to do with the built-in schedule UI, unless you want to create 12 different schedules, but you can easily build this into your job step. Assuming it runs every night: IF DAY(GETDATE()) <= 8 OR DAY(GETDATE()) >= 28 BEGIN -- do backups END On other days (9th -> 27th), the job will still run, but everything inside the IF ...


4

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


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

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

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


4

Concurring with Aaron's assessment of ask the developers. I used the wizard to create a full backup plan, exported that to my machine and then opened it up in Visual Studio/BIDS/SSDT-BI. You'll see the Backup Database Task looking something like below When you click that View T-SQL button, it shows the command it will execute. Running a Profiler session, ...


4

Is your SQL Server instance existing in your local system (laptop/desktop) or in some other system (server?). If it's on server then go and look for C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup on the server


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

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

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

On the first "use" command after login, MySQL scans database, tables and columns name for auto completion. If you have many db, tables it could take a while. To avoid that, launch your client with the -A option (or --no-auto-rehash) mysql -uroot -p -A You could also add the disable_auto_rehash variable in your my.cnf (in the [mysql] section) if you want ...


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

You should use Ola's backup solution. Brad talks about it in more details on simple talk. The solution is very simple to implement and its tested by many organizations. Value Description FULL Full backup DIFF Differential backup LOG Transaction log backup



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