Hot answers tagged backup
6
No, there is no way. Restores really is meant to recreate the original database that was backed up. There are tools that allow you to query directly a backup w/o actually restoring it (Idera's SQL Virtual Database) but those are horrendously slow.
6
I am noting the other answers here, but I don't believe any of them (at this moment) is a complete answer / solution to your problem. Yes, you can backup to a remote location, but that is not always the best way to do it. Backing up to a network location can be murderous to your bandwidth.
Transaction Log backups is very important and is the right way ...
6
Why doesn't the log file shrink after my backups? Is it because there are uncommitted transactions?
The actual NTFS log file doesn't "shrink" from a transaction log backup, but VLFs (Virtual Log Files) within the transaction log are marked for reuse (because they are now backed up and persisted on media) allowing the wrap-around of transaction log ...
5
What is the backup and restore method?
It is called Piece Meal Restore and unrestored filegroups can be restored at a later time.
Is it possible to restore only the lamb filegroup?
Yes it is possible to restore only the lamb filegroup.
Below will show you - how you can do it.
Create a database called "FGTest"
create database [FGTest] on primary (
name = ...
4
If you're using MySQL with InnoDB tables, then you could take incremental backups with Percona's xtrabackup, or talk to Oracle about enterprise licensing, which would net you MySQL Enterprise Backup.
Xtrabackup is a brilliant piece of software, but it behaves very differently from traditional MySQL backups using mysqldump, and it's worth spending some time ...
4
This is the difference between logins and users and how they relate to each other:
Logins - Instance level principals that allow an entity to connect to the SQL Server instance. They do not, by their nature, grant any access to databases on the instance. The exception to this is a login with sysadmin rights can use a database because they are sysadmin, ...
4
The primary issue you're dealing with is that you're backing up your logs once a day. The behavior of the engine is that log records (used space) within a log file will only be removed after a successful log backup. This space is reclaimed when a checkpoint occurs, but if your database is in Full/Bulk Logged recovery then log records will only be removed ...
3
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 ...
3
Unfortunately, yes, you'll need to reduce the size of your root LV.
(You could reduce the size of swap, too, but that's already a fairly small volume)
Even if you can use that 4MB, a snapshot of your root volume will become invalid quite quickly when LVM runs out of "free" disk space.
You get prompted with a text box asking how much of your disk to use ...
3
Two thoughts come to mind.
If you are concerned that this update may not properly affect the table the way you think, have you thought about putting the update inside a transaction.
You could do the update, query the data, and if it all looks okay, Commit the Transaction. If it fails, you could perform a Rollback.
Alternative
You may want to look at ...
2
You could use a third-party product such as Red-Gate's SQL Object Level Recovery, that'll allow for you to recover a single object from a native SQL Server backup.
If it's a one-off, you can get a 14 day free trial.
2
You could look into changing the database to contained database. Contained database user are authenticated by the database, not at instance level through login. It makes moving database to different instance simpler.
If not, you could backup the login information using sp_help_revlogin scripts provided at this Microsoft support KB. And execute the output ...
2
Read up here before you shrink your log file.
You dont have to change the recovery model ...
Update:
you can use Invoke-sqlcmd cmdlet.
eg.
$query = "DBCC SHRINKFILE(db1_log)"
Invoke-Sqlcmd -ServerInstance $instanceName -Query $query
EDIT: Below is an excerpt from Powershell V3 cookbook ...
Not all DBCC commands are wrapped in SMO methods. For other ...
2
It appears that no you can not backup a database in an offline state based on this comment from BOL. Offline and Emergency appear to be the exceptions.
Without the NO_TRUNCATE option, the database must be in the ONLINE state. If the database is in the SUSPENDED state, you might be able to create a backup by specifying NO_TRUNCATE. But if the database is in ...
2
Take a look at either Transaction Log Backups, or Differential Backups. Neither one is a "full" backup, which should mean that it doesn't affect your day operation as much.
2
Your best bet is to use Redgate's data compare to compare and insert data in the database your application is pointing to.
I have used this method in similiar situation and trust me ... this tool is a life saver.
You can dowload a 30 day full trial version and then purchase it if it suits your needs.
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
You'll have to manually query for the rows and copy then in using either T-SQL or SSIS. There's no way to take the two databases and have SQL Server just merge them into one database.
If you are using identity vales on tables you'll need to be careful of duplicate values and assign new values to the rows that you are inserting. If you have other tables ...
2
You cannot restore a DB2 database from AIX into Windows because of the endian. These are not compatible architectures.
For more information, check the InfoCenter: http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0005960.html
The only way to retrieve the data between these platforms is via db2look + db2move.
...
1
Can you define "corruption" and explain exactly what happened to the files?
If the data is really valuable (in which case you'll hopefully be investing a lot more in your backup and recovery to make sure that you can't lose your database and all your backups simultaneously), there are tools that may be able to salvage some of your data. Oracle Consulting ...
1
Sure enough, --single-transaction is supported from the MySQL command line.
mysqldump --single-transaction databasename
Will use all of the default options from --opt (except for --lock-tables, which is overridden by --single-transaction) as well as --single-transaction.
That really should be all there is to it.
I recommend briefly reading through all ...
1
You might want to run sp_refreshview, it is recommended to run this if the underlying tables change at all (this case seems to be a good candidate for that case).
[Edit]: I found the underlying cause. The parse trees of views are generated and cached
select * from sys.syscacheobjects
where objtype = 'View'
This is the output of the tokenizer, so an ...
1
The possible values in the State and State_Desc columns of sys.databases are:
0 = ONLINE
1 = RESTORING
2 = RECOVERING
3 = RECOVERY_PENDING
4 = SUSPECT
5 = EMERGENCY
6 = OFFLINE
7 = COPYING (Applies to Windows Azure SQL Database)
From your question, I am assuming that the backup job is called using a script, which queries and gets the value of state ...
1
Your backup solution for MySQL User Grants is fine.
I have been advocating doing that for years:
Mar 24, 2013 : MySQL export user with semi-colon ";" on the end
Dec 24, 2012 : Backup and restore "mysql" database
Jun 13, 2012 : Fastest way to move a database from one server to another
Apr 12, 2012 : Cannot GRANT privileges as root
Apr ...
1
An alternative is to use software that will allow for simultaneous backups to more than one location, such as Quest's Litespeed for SQL Server, or Red-Gate's SQL Backup. Both will allow for multiple copies to be created.
1
As the documentation shows, MIRROR TO is only available in the Enterprise editions of SQL Server 2005+.
If your end goal is only to have a redundant backup for safekeeping in a different location from the local disk, and they don't have to be the same backup, then you could just do them in sequence in a single batch.
BACKUP DATABASE [MGEC_STAGE] TO DISK = ...
1
@elijah,
SMO does have function to shrink file. The PowerShell script below shows the log.shrink method is used to shrink ONLY the log file. The shrink with default or truncateonly option work for me with full recovery model.
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$server = Read-Host 'Enter your SQL Server ...
1
I have a similar problem:
I have my own backups running as expected
The native Windows backup backs up to a VIRTUAL_DEVICE
Both appear as standard database backup entries in the SQL Server error log
I can not restore from the VIRTUAL_DEVICE. It is just a GUID. This worries me
What I'm trying is to disable the SQL Server VSS Writer service to see if ...
Only top voted, non community-wiki answers of a minimum length are eligible
