Tag Info

Hot answers tagged

18

There is a do-not-ever-do-this-in-a-live-environment hack you can use where space is limited, by restoring the log file to a compressed folder. Attempt this by compressing an existing folder and restoring to it will result in an error, so you have to cheat with a symbolic link. Create a compressed folder D:\LogCompressed\ Create a symbolic link to the ...


11

Here is what I suggest: build a virtual machine running Windows, with enough disk space to hold the backup. Copy the backup file there. If you don't already have the ability to build virtual machines, you could do so with free products like Oracle VirtualBox. download and install the evaluation edition of SQL Server. Make sure you include both the database ...


10

You will need a previous Full Backup and any log backups taken since the last Full backup 1) Take a tail of the log backup BACKUP LOG OldDB TO DISK='C:\OldDB_Tail.trn' WITH NO_TRUNCATE 2) Find Transaction for Deleted Records (Operation will be LOP_DELETE_ROWS for DELETEs and LOP_SET_BITS & LOP_MODIFY_ROW for a TRUNCATE TABLE) SELECT * FROM ...


10

You could dump the database using pg_dump and then restore it on the new server using psql. Here's a couple of commands from the above link: Create the backup: pg_dump mydb > db.sql Copy db.sql to the new server (specific command depends on OS) Go to the new server createdb mydb -E UTF8 (you don't have to specify UTF8 encoding, but I always do) ...


10

Remember: you do not have a recovery plan until you tested your recovery plan... The theory is simple: on the disaster recovery machine you have prepared for such cases you restore the most recent full backup, then you apply the most recent diff backup (if any) and then all the hourly log backups taken after the most recent diff (or after the most recent ...


9

The perhaps easiest way is to do a full dump from the old server and pipe the result straight into the new server. Like this: pg_dump -h old_server_ip -p 5432 -U username dbname | psql -h localhost -p 5432 -U username dbname Do this as a superuser. By default the postgres user is a superuser, but you might've created others. UPDATE: In case you move data ...


8

Easily - just use RMAN to clone it. I think you mean in Oracle terminology, clone the database into a new instance on the same server. If you really do mean clone the schema into the same Oracle instance, then the easiest way is Datapump.


8

There is no way to shrink the backup as a part of the restore process. The restored database must look exactly like the source database with the only exception being that you can change the drive letters and folders around.


8

No backup = no restore. You restore from a backup you created earlier. It makes no difference what recovery model you have. Some myths busted from Paul Randall: BackupRestore


8

What you're trying to do would leave the database in a (transactionally) inconsistent state, hence it isn't possible. The Partial Database Availability whitepaper is a useful reference guide and includes an example of how to check whether a particular table or file is online. If your data access were through stored procedures, you could relatively easily ...


8

Have you tried the --disable-triggers option to pg_restore? Per the documentation: Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data reload. Please note that this only is valid for a --data-only restore and requires the --superuser=username option to be passed, as well.


8

Your database has multiple filegroups. You can't just use the simple UI dialog to say "restore database"... you need to use WITH MOVE to indicate which files should go where (even if they all are going to end up in the same location). We can't really tell you what your actual RESTORE command will look like without seeing the results of: RESTORE FILELISTONLY ...


8

That's because by not specifying the FILE parameter of RESTORE DATABASE, it is defaulting to 1 which will be the 1st backup set on that media. See the section on this page of Specifying a Backup Set. Instead of: restore database YourDb from disk = 'C:\yourpath\backupfile.bak'; go You will want to do: restore database YourDb from disk = ...


8

will I be able to do a restore to a point in time between Future full backup 1 and 2? Yes. What you're concerned about is the Log Chain. The log chain starts at a full backup. Therefore at future full backup 1 you will start a new log chain and with transaction log backups you will be able to restore to point-in-time. BOL reference on Working with ...


8

Not if you used any persisted Enterprise features. If you used any of such there will be an entry in sys.dm_db_persisted_sku_features and when trying to open/attach/restore this database on any edition lower than Enterprise you will gen an exception and the database won't open. The list possible of persisted SKU features are: Compression. Indicates that ...


8

When you take the backup of database, the last LSN on the source will be X. If any activity will occur (including, say, an automated checkpoint), the source LSN will progress forward to X+n. If there is any activity that occurred on the source and was not captured on in the backup it would leave an imprint in the source log, somewhere between LSN X and X+n. ...


8

The value for dbi_maxDbTimestamp is stored on the database boot page. (page 9 in the primary data file). This is not written to every time a timestamp value is allocated. Instead SQL Server reserves a few thousand at a time. For example if @@DBTS is 2000 and the dbi_maxDbTimestamp is also 2000 then SQL Server updates the value written in the boot page to ...


8

Some important factors to consider for backup are: how annoyed the forum users would be if X days of activity disappeared the probability of that happening how long will it take to recover the data if it's backed up how much does backup interrupt the normal operation of the site As for, "Am I making sure my data is protected enough ...?", I'll ask a ...


7

From the CREATE ROLE documentation: Note that roles are defined at the database cluster level, and so are valid in all databases in the cluster. Since pg_dump dumps a single database, you can't extract roles with that utility. The pg_dumpall --roles-only command you proposed will do the work - however you may need to filter its output so that only ...


7

You used NORECOVERY which leaves the database ready to receive diff/log restores You can remove it above, or simply run this RESTORE DATABASE NewDB WITH RECOVERY See MSDN RESTORE, look for "[ RECOVERY | NORECOVERY | STANDBY ]" header


7

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.


7

622 is an internal intermediate version, never released. How come you have an 622 version DB? The explanation is simple actually: an aborted upgrade. Look at the sequence Aaron posted: Converting database 'x' from version 611 to the current version 655. Database 'x' running the upgrade step from version 611 to version 621. Database 'x' running the upgrade ...


6

I believe that the reason your backup is 25 GB and the restored database is 100 GB is not because of your transaction log. What my guess is, your database files have 100 GB of allocated space and there's 25 GB of actual data in the database. There's a difference between allocated database file space and utilized data space. In this case, the former is 100 ...


6

There is no way to restore either a single table or a few rows in SQL server without a third party product. If the table for which you are trying to retrieve few rows is in a separate file group you can restore only that filegroup (assuming you have Enterprise edition). Else use a third party product like Quest Litespeed. I believe it has a feature that ...


6

Just from the words in your question I suspect the following: You most likely have innodb_file_per_table disabled. NOTE : The following info is based on innodb_file_per_table being disabled When insert data into InnoDB tables, everything and its grandmother lands in the system tablespace file, better known as ibdata1. What does ibdata1 actually contain? ...


6

The first point to consider that a clustered index is actual table data. So in that alone, I'd say yes. Your whole database should be backed up and able to restore (there are a few exceptions to that, like read only filegroups that are subject to no DML). The second point is that restoring a database does not rebuild or reorganize any indexes. So yes, ...


6

The backup size is simply used 8k pages. These 8k pages are part of the MDF. On restore, the MDF and LDF files may need recreated (if already existing and different in some way) or just created So backup file size isn't an indicator of space used on disk by the restored database. And this ignores backup compression too So, if the MDF is 100GB (with 2GB ...


6

Unfortunately not. SQL Server will always attempt to restore to exactly what is recorded in the backup file, unless you explicitly specify otherwise (with MOVE in this case). The REPLACE option only works if you're replacing files owned by the database you're restoring (which makes sense, You may have said you're fine killing DB_A by restoring over it, but ...


6

The main issue here is it looks like you're not doing any regular log backups. The first thing I would do is make sure you understand SQL Server's Recovery Models. Primarily, databases in FULL and BULKLOGGED need to have regular log backups taken. I would recommend that to clean up your log file, first take a log backup: BACKUP LOG foo TO ...


6

I just went through figuring out SQL backups, and had a very similar question. To restore to a point-in-time you need: The most recent Full backup from before your point-in-time (Optional) The most recent Differential backup after your Full backup and before your point-in-time All the Transaction Log backups since the Full Backup or Differential Backup ...



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