Tag Info

Hot answers tagged

17

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 ...


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

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

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 ...


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) ...


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

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

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

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

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 ...


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

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 ...


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

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

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

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 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 ...


6

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 = ...


6

It sounds like the database is corrupt. Before you do anything further, make a copy of the files, and only work with copies. You might try the following, in case the problem is isolated to the log: CREATE DATABASE [GRMStemp] ON (FILENAME = 'c:\wherever\MDF_File.mdf') FOR ATTACH_REBUILD_LOG; If that fails, there is a forum post on MSDN by Paul Randal ...


6

This is really a question for the DBA site but yes, it will work. Compression is transparent when restoring, and the RESTORE command doesn't need any parameter to tell it that a backup is compressed. Of course you can easily test this yourself.


6

No, shouldn't be any issues; I have a VM right now with 2000, 2005, 2008, 2008 R2, 2012 and 2014 and it works just fine. I do suggest to observe the following: install the oldest version first, and get it completely patched before moving on to the next. use a very consistent and intuitive naming scheme for each instance (e.g. .\SQL2000, .\SQL2005, etc). ...


6

Short Answer: From when the backup was taken I actually wasn't sure of the answer, so I just made a database, put it in full recovery model, took a full backup, did some work (create a couple tables named after the time I created them) and then started restores. Restored the full and then attempted to apply the log backups. When I did that I had to ...


5

John has pointed you at the Flashback Query options useful from a developers point of view. If I read you correctly then you are probably looking for the Flashback Database and Restore Point functionality documented here which allows you to do precisely this. Flashback Database does have some requirements over and above the flashback query functionality (for ...


5

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 ...



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