Hot answers tagged

7

It can be specified in TSQL but the documentation states COPY_ONLY has no effect when specified with the DIFFERENTIAL option. There's no need for copy only differential backups because you can just take a normal differential backup. Taking a differential backup doesn't break anything as each such backup is cumulative and contains all changes since the ...


4

Restore Headeronly From Disk = '...' There's only one SQL Server backup format. If this doesn't return anything useful then it's not a SQL Server backup file and may be something else (I can only imagine what; perhaps you'd have to open it in a text editor and see what the first few characters are). It will return a BackupType column: 1 = Database 2 = ...


4

A simple way to identify the backup is by running restore headeronly command. Like RESTORE HEADERONLY FROM DISK = 'D:\Backup\backupfil.bak' ; Look at the output and look at the column backup type. Below are types and there corresponding backups Backup type: 1 = Database 2 = Transaction log 4 = File 5 = Differential database 6 = Differential file 7 = ...


4

SQL Server can only see network drives that are mapped in the Windows Profile of the SQL Server service account. So you'd have to log on as that account (not your own) and map the drive, for SQL Server to be able to use it for backups. Backups run using the credentials of the SQL Server service account, not yours. As suggested in the comment above, try using ...


4

I've set up option 3, Master Server, a number of times, but never for scheduling backups. I'm able to schedule jobs for performance data collection, pushing out administrative changes/script updates via SQLCMD, and run tests against multiple versions of SQL Server on multiple instances to test for any unexpected changes made after SP/CU application (among ...


4

Mostly because a snapshot isn't really a backup. As explained in How database snapshots work The snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of ...


3

Its easy using T-SQL. I would suggest not to use Maiantenance plan. Instead go with a much flexible and widely adapted TSQL based solution - SQL Server Backup by Ola Hallengren EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES, -DB1,DB2', -- All user databases, except Db1, DB2 @Directory = 'C:\Backup', @BackupType = 'FULL', @Verify = 'Y', @Compress ...


3

My problem is what is the use of differential backup every 6 hours if we are taking a transaction log backup every 15 minutes? Differential backup is used to decrease the Recovery time object(RTO), it gives you facility of restoring backups quickly. Differential backup includes all the changes made since last full backup, or full backup which started ...


3

You cannot use backups from before the switch to ARCHIVELOG mode to restore and recover the database to a point in time after the switch. Looking at this quote it makes sense. If you enable archivelog on a database where it was not enabled, then any backup that you have before archive log was enabled can't be used to do a point in time recover, since ...


3

It's not really a requirement. The database itself will work perfectly without backup after switching between ARCHIVE / NOARCHIVE mode. But if something ever goes wrong (or database needs to be copied/cloned to other machine), you'll need a backup. You may or may not use incremental backups feature ; if you do want to use them you must take 0-level ...


3

I recommend using Ola Hallengren's backup solution. The 6th question down on Ola's FAQ page walks you through automating the stored procedures with batch files. The steps given in the FAQ can be used for any stored procedure not just his. Below is the example provided from the FAQ site. You can modify that to run your custom stored procedure. sqlcmd -E -S ...


3

You can use replace into flag (--replace) and where condition (--where) while also omitting the create statement (-t): mysqldump -t --replace --where="id between 901 and 1000" my_db1 table my_table


3

First of all, you can use a pipe mongodump -h sourceHost -d yourDatabase … | mongorestore -h targetHost -d yourDatabase … This reduces the time, as each document read will more or less instantly be restored on targetHost. However, this has the disadvantage that you might run into problems if the procedure is aborted for some reason, for example of a ...


3

Download the Google Drive desktop application. When backing up SQL Server, set the destination to the "My Drive" folder created by Google Drive (something like C:\Users\<user>\Google Drive by default) Once a backup is placed in that folder, it will automatically start to sync to Google Drive. Also worth nothing, as per Shawn Melton's comment: You ...


2

Backups from a normal user are a nightmare. If you're using PostgreSQL on Linux, I'd recommend relying on sudo (and the /etc/sudoers file) to accomplish this, so that a given specific user can sudo -u postgres pg_dumpall <params> with specific parameters, so that's all they can do as user postgres (and therefore have no write access to the database).


2

+1 for sensibly wanting to get the backups on a separate drive. However, if the new cluster volume is on the same SAN, you're not really accomplishing much. It would likely be much better to run backups to a file share on a totally different system. As a workaround, you can easily add an "Execute T-SQL Statement" task to your Maintenance Plan to manually ...


2

No - the MyISAM architecture prohibits hot backups - it uses a locking mechanism. You might be interested to my response in a related thread. Take a mysqldump of your database, change the bit where it says ENGINE=MyISAM to ENGINE=InnoDB (InnoDB uses a MVCC architecture) and then restore - XtraBackup will then perform hot backups. It's relatively easy to use ...


2

Here's a shell script that can do what you want: SCHEMA="myschema" DB="mydb" psql -Atc "select tablename from pg_tables where schemaname='$SCHEMA'" $DB |\ while read TBL; do psql -c "COPY $SCHEMA.$TBL TO STDOUT WITH CSV" $DB > $TBL.csv done Make sure you set the DB and SCHEMA variables to your particular database and schema. The wrapping psql ...


2

The use of the differential Backup is simple. Instead of restoring a huge amount of log backups in a row, you restore a full, a diff and a small amount of transaction log backups instead. Yes you are correct, you don't need the differential Backup, logically. But your restore will be faster using full + diff + log backup compared to a full restore + ...


2

Their cannot be a comparison drawn as Snapshot is "photo copy" of data file. The difference lies in amount of activity both does and consistency of product arising out of both the operations. Full backup "Is Always" more reliable than snapshot backup Full backup includes all committed and uncommitted transaction when full backup has finished. Read more ...


2

It sounds like you've done your homework to a large degree. Clearly you're aware of the log chain implications for COPY_ONLY backups. My advice to you would be to ensure you have a clearly though-out and tested restore plan. Without having tested your disaster recovery plan, you can never be certain it will work, and therefore it's not a plan. If you ...


1

A solution for a database set to recovery mode SIMPLE is having the FILESTREAM data in a read-only file group (which isn't your ideal option), and then backing up only the read/write file groups with DIFFERENTIAL like this: BACKUP DATABASE [name] READ_WRITE_FILEGROUPS TO DISK = '' WITH DIFFERENTIAL, COMPRESSION; It will get any data that has changed in ...


1

The problem this caused was that the log file (which also gets backed up) is now unnecessarily large because it includes the FILESTREAM data. I'm not sure if you mean the log file itself is too large or that the log file backups become too large. If it's the former then how often were you backing the log up? Depending on the application design you may ...


1

I would check the Management > Maintenance Plans folder, in the Object Explorer pane of SSMS. The other place would be under SQL Server Agent > Jobs, so I'm curious what you meant by, "I was able to find the full backup option on the SQL Agent." If there is a job titled "Full Backup" under the Jobs folder, then that's probably what you're looking for.


1

This seems to me like the opposite of a problem... Regardless of requirements, I just feel better knowing every database is being backed up. Unless these two databases are quite large and can fill up a significant amount of disk space with backups, what negatives are there to backing up those files?


1

When we put the tablespace in backup mode Oracle copies whole changed data blocks into the redo stream in order to make the backup safe from fractured block. Copying whole data block instead of only the change may degrade the performance. Oracle strongly recommends to put the tablespace in backup mode only when required. What Happens When A ...


1

The final reason for this effect was that the Backupexec process responsible to take regular log backups was still present and running (so IT monitoring did not alert anything), bunt it was kind of internally crashed, so it did not do it's job properly. In order to address this kind of event I decided to add the log file events and it's metadata details to ...



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