Tag Info

New answers tagged

0

From http://www.morgantechspace.com/2014/11/Powershell-script-to-Backup-and-Restore-SQL-Database.html # Set SQL Server instance name $sqlName= "localhost\SQLExpress" # Set new or existing databse name to restote backup $dbname= "MorganDB" # Set the existing backup file path $backupPath= "D:\SQLBackup\MorganDB.bak" #Load the required assemlies SMO and ...


0

Research recovery model. Default - simple - contains transactions in action. Full recovery records transactions since last log backup; in case you want to restore back to before the transaction ( corruption ) occurred [need to have a full backup first!] Simplistically: Two files: two states. MDF - truth. LDF intended truth. Backing up log commits the ...


0

The transaction log allows for two features: Performance. It allows for sequential writes to finish the 'commit' phase more quickly than if the database engine had to write to all the random pages necessary for the transaction(s). Changes are written to the log file sequentially (faster than random writes) and then flushed during the 'commit' phase, at ...


3

Whenever I make a change to a table, does it get written to both the database file AND the transaction log file? It is written to both, but it is written differently to each. The changes are made to the data pages in memory and are eventually flushed to disk via the checkpoint process. the changes are sent to a log buffer and hardened to disk at some ...


2

We had the same issue today. It turned out to be a permissions issue, as illustrated in some of the other answers. The difference is that the account we needed to add was NT SERVICE\MSSQLSERVER. I identified the account by comparing the permissions of the default SQL Backup folder permissions to the folder containing the backup file. Adding the service ...


0

Personally, I'd use SQL to do a local dump; with compression enabled [better]. Then I'd use Robocopy to copy the [smaller] file to the network location [faster]. This is pretty much what Robocopy is designed to do [stronger]. The backup should be striped across a number of files to allow maximum write speed possible: Backup database X to disk = ...


0

There are multiple ways of building out the report you want. The two more popular or ones I likely chose from are below. One option is to use Linked Servers from one central instance to keep it centralized. You use OPENQUERY or physically created linked servers for each server. This option would require a bit of setup on each instance but once done your ...


0

It is redundant, but i have put this on every server and had it send me an email. Most third party backup tools put a value in "Description", or you can tell based on the name or user name. select bs.name backup_name, backup_finish_date, bs.database_name, bs.backup_size , cast(bs.backup_size/1024/1024 as int) 'Backup Size MB', ...


0

No, there isn't a way to separate the two. You'll have to either hang on to the large file, or take a new full backup and start moving forwards from there.


2

No you cannot have two sets of backups otherwise you mess up the backup chain. Think of this occuring: Day one 1. Person A does a full backup Day two 2. Person B does a full backup 3. Then an hour later Person A does a diff backup That diff backup that Person A does will be based off the Full backup that Person B just did, not the Full backup that Person ...


3

Yes, it is possible, but it requires some agreement between both parties. If the "sysadmin" (it does not need to be a sysadmin by the way) wants his own backups, does he want all the transaction logs and differentials in addition to a full backup? If the "sysadmin" only needs full backups, he can do his own backups WITH COPY_ONLY and use them without ...


4

You can have sysadmins doing COPY_ONLY backups and DBA's doing regular FULL, DIFF and/or Transaction log backups. The key is COPY_ONLY backups for allowing ad-hoc backups. But they cannot be used for point in time recovery. Note that taking a random Transactional log backup (without using COPY_ONLY) will break the log chain and you wont be able to do a ...


1

The wal files are archived when they are no longer needed in the pg_xlog directory (usually when there are 16 segments and another one is needed - the one that is rolled over and reused is first archived). You can force the system to archive some files to verify manually if you want - to do this run the following: select ...


0

You should be able to dump the output from fn_dump_dblog into a table to sort if needed. I'd recommend you decrease your backup interval to maybe every hour or so to make it easier to analyze the data (plus it will improve your ability to recover and reduce the time each backup takes). I tested a 453mb log and it took 10 minutes to read all 6.7 million ...


0

I have achieved this without any SSIS. I used a linked server instead. It took me some time, but I still believe it is the best way of backing up SSAS databases. You can see how I did it on this link. I also delete old backup files, as you can see on this link.


0

Have you considered creating and running an extended event to monitor what queries are running to generate all those log records? The following post by Paul Randal touches upon two events to perhaps get started with. http://sqlperformance.com/2013/11/sql-performance/transaction-log-monitoring I haven't played with the transaction_log event or ...


0

We have a similar process in place, where we run a script to TRUNCATE TABLE backupTable; INSERT INTO backupTable SELECT * FROM mainTable WHERE datefield >= xxxx WHERE x is the time the backup ran the day before. Then we run MySQLDump against backupTable. Alternatively you could do something similar and set a 'backedup' flag in the main table, and ...


1

What you need is partial backups. innodb_file_per_table=ON is a prerequisite to make it work. In your case procedure would be like following: Take a partial backup from the production server innobackupex --databases="mydatabase" /path/to/backup On the destination server prepare the backup copy for export: innobackupex --apply-log --export ...


0

Short answer: you can't. Not with the file only, at least. Long answer: what you can do is query msdb.dbo.backupset on the source instance to extract this information. The backup start/end date of the previous T-LOG backup is approximately the beginning timestamp of the log records conteined in each .TRN file. The end of the log chain is the backup ...


1

SQL Server supports piecemeal restores. From that link: Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model. So you should be OK with the FULL recovery model. I'd suggest you partition your tables. The log tables especially should be amenable to partitioning by write ...


3

Maybe those files are not cataloged by RMAN? I would CATALOG them and then DELETE OBSOLETE once again.


0

Use command restore header only get backup file header information RESTORE HEADERONLY FROM DISK = 'E:\StackExchangebackupfile.trn' GO


0

It is a brave soul indeed that still uses an all-MyISAM database. Why are writes being quietly discarded instead of being queued, as they would be for a regular table lock? Look at the error message again [20-Nov-2014 01:41:56 UTC] Wordpress database error The MySQL server is running with the --read-only option so it cannot execute this statement ...



Top 50 recent answers are included