New answers tagged backup
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 ...
0
So far by the given answers you are backing up your base table data, but not the table itself. The table has many other attributes, conducting a SELECT * INTO basically just get's you the cell values and column structure. Although not complete, some additional attributes to consider:
Partitioning Scheme/Functions
Indexes
Foreign Key Relationships
Object ...
0
The create table syntax posted won't work, afaik.
The easy way to back up a single table is:
SELECT * INTO [backup-table] FROM [source-table]
Then you can drop [backup-table] when you don't need it.
You could do this across to a diff db (that might be on a diff spindle set), etc.
0
To add to what @dmg said. Backing up a single table can be problematic. RI aside if for example your "large" table is 90% of the database then just backing up the table won't really help you much. If you are using SQL 2008 make sure that you are compressing your backups. You can get a fair compression percentage and may actually be able to take a full ...
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 = ...
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.
...
0
You could take a backup of the existing table alone into a new_table and update your table and if anything goes wrong you always have your backup table to revert back.
Ex:
-- SCRIPT TO BACKUP A TABLE
SELECT * INTO <NEW_TABLE> FROM CURRENT_TABLE
Taking Backup of the single table is much less then taking backup of complete database.
If everything ...
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 ...
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.
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.
0
right click on your database --> Tasks --> generate scripts -->next-->next-->
click on advanced button --> change the option "type Of Data to script" to "schema and data"
-->ok
-->next -->next
so now you have a script that creates database and its table and fills the data in it :) ;)
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.
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 ...
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 ...
0
It's possible to configure database replication (master/slave) and then create backup on slave server at any time.
0
But the link also recommends XtraBackup from Percona, which is faster, no locks on tables that are already in use and gains no time for restoration. For 500GB of data I prefer Percona XtraBackup to be quick and efficient unless you want to convert them to innodb_file_per_table model if it wasn't from the existing DB server. Below link explains on how to ...
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
Simple answer, no. The database being restored must match the source database.
this is not the fix you seek (waives hand dramatically)
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
@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 ...
0
Here is answer for my own question
SSMS uses "xp_fixeddrives" to list the drives in PC. Same is used by SSMS when clicking on button which is used to select backup path or browse directory.
The error is generated because "exec xp_fixeddrives" doesn't return any records when executed by a non-sysadmin account.When you run the same using user having sysadmin ...
0
Well you might have to go through another step or two: restore on another server, shrink, backup, copy and restore. If you restore in simple recovery mode (or switch to simple recovery mode which can be done with the db online), the log database will be (or can be shrunk to) as small as it can get. You can then switch to full recovery when the finance ...
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.
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 ...
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 ...
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.
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 ...
0
My standard backup regime for Oracle databases revolves around rman, flash recovery area and rsync. Basically as follows:
Configure your database in archive log mode, without it you will lose data. Not a question of if, but when.
Archived log files go to the flash recovery area.
Configure rman retention policy according to how far back in time you wish to ...
0
You can try some GUI clients like SQLyog. Where you can select the tables which you want to dump.
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 ...
0
For MySQL and PostgreSQL you can use the Snapshot Backups in order to obtain full or incremental backups:
Some file system implementations enable “snapshots” to be taken. These provide logical copies of the file system at a given point in time, without requiring a physical copy of the entire file system. (For example, the implementation may use ...
0
For Postgresql on MS Windows, the Postgresql wiki has an "Automated Backup on Windows" article that may be of use.
There is also "Instant PostgreSQL Backup and Restore How-to" available from Packt that provides a good overview of the various options available using the tools that come with Postgresql. There is nothing in the booklet that can't be found on ...
2
Postgres: The write ahead log(s), WAL; it's a complimentary component of your backup strategy. It's exactly what you want.
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 ...
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 ...
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 ...
0
If you are referring to the base/pgsql_tmp, then you should be fine. But I don't speak from experience of having done that myself. The only gotcha is that you have to make sure that the location is accessible when your PostgreSQL server starts up.
(Ref.: Book PostgreSQL 9.0 High Performance, page 93).
The book in question refers to creating a simlink of ...
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
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 = ...
0
If you did not take a log backup between the full yesterday and your restore today, then no, everything changed since yesterday is gone.
In a best case "accidently deleted data" scenario, the process is to take a log backup to capture the "tail"of the log, restore a full backup of the DB to an alternate location, restore log backups using STOPAT to get to ...
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 ...
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, ...
0
If you want to have a full backup without lock the live database, I will suggest that you start to replicate all information from master server into a slave, and take a full backup right from slave, this way you will hava a consistent backup from master without have to lock or put load on the master database at any moment.
You can look into MySQL ...
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 ...
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 ...
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
This is not a SQL Login permission issue. Rather that SQL Server Service Account can't access the location for the backup. To fix this you need to grant access in windows to that path to the account that the SQL Server Service executes under.
1
Easiest option, if you aren't relying on features specific to MyISAM or other storage engines: change your tables to the InnoDB storage engine, and use the --single-transaction option with mysqldump.
This tells mysqldump to perform all the work within a single transaction, causing InnoDB to give it a consistent point-in-time snapshot of the data, without ...
Top 50 recent answers are included
