Hot answers tagged

14

The master database is special, different than other databases. It's a system database where SQL Server stores internal objects. The only time you'd normally restore it is if you're bringing back a server from the dead - you wouldn't usually want to restore master from one database to another. In your scenario, when you're just curious about the contents of ...


9

You can use the catalog view - sys.database_recovery_status SELECT DB_NAME(database_id) as DatabaseName, database_guid, family_guid FROM master.sys.database_recovery_status You can make your restore process more sophisticated by comparing the family_guid of the backup file and the database to be restored. Just dump the info of RESTORE headeronly into a ...


8

Restore the backup to a database with a new name. You get to keep the old and new and can move tables between them with a little work


8

There are a few possible things going on here, here's a non-exhaustive list: the execution plan cache is cleared by the log restore, so plans will need to be recompiled the first time. If your plans have long compile times, this could explain the difference. You didn't mention exactly how long the delay is on the first run compared to the subsequent runs ...


7

This process should not invalidate the synonym. As per the docs: The binding between a synonym and its base object is by name only. All existence, type, and permissions checking on the base object is deferred until run time. Therefore, the base object can be modified, dropped, or dropped and replaced by another object that has the same name as the ...


7

Well, reading the documentation for Resource Database (highlighting mine) The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every ...


6

From this page in the MS documentation: There are two options for configuration when you place the secondary database in standby mode: You can choose to have database users disconnected when transaction log backups are being restored. If you choose this option, users will be disconnected from the database each time the log shipping restore ...


6

It looks like pg_ctl wants a real terminal for output, which is not allocated by ssh when you simply ask it to run a command. According to the Postgres manual On Unix-like systems, by default, the server's standard output and standard error are sent to pg_ctl's standard output (not standard error). The standard output of pg_ctl should then be redirected ...


6

I'm not aware of a way to find out who changed a db owner without some additional logging in place such as Auditing, but you can definitely tell who restored a database by checking the restorehistory table in the msdb database: SELECT * FROM msdb.dbo.restorehistory If your coworker restored the database using impersonation they may be telling the truth ...


6

Try to enable "Instant File Initialization" feature if it is not enabled already. It speeds up the restore process and makes it significantly faster. What is Instant File Initialization(IFI)? SQL Server needs to initialize the pages before using them. Thus, it tries to fill the pages with zeros(zeroing the files). If IFI is enabled, it skips this part ...


5

Nothing works for me but this. mongorestore --gzip --archive=/path/to/file.gz --db db_name


5

No, it is not possible to only restore n number of rows from a given table. The restore process does not even know about the tables, let alone how many rows are in each table, as it works on the database page level. To accomplish your goal of only getting n number of rows from production to non-production without doing a full restore you'll probably want ...


5

There is no point in taking db into single user mode and then making it offline. background service like sql agent might try to connect to the db. If you want to get more details of what process is getting deadlock then you can enable TF 1222 (dbcc traceon (1222, -1) ) and then disable it (dbcc traceoff (1222, -1)) or use Event notification (link to my ...


5

It should always be possible to restore within the same version, irrespective of service packs and other updates. Having said that, it is also recommend that you keep up with service releases so in an ideal world this won't be an issue. One thing to note that throws some people off is that 2008 and 2008R2 are considered different versions (not one being a ...


5

Take a look at the command that is generating the backups (it could be a job, a maintenance plan, 3rd party tool, etc.). You can give the file any extension you like, including no extension. Windows Explorer might not know what on earth it is, but SQL Server will still be able to restore it just fine.


5

My advice would be to just restore the entire backup. In my current environment, someone accidentally screws up some table once every few months. It just happens. Of course, it always hits our biggest database. 200GB backup file, 1+TB when fully restored. It is just easier to restore a copy somewhere, find the specific table we need, and just update that ...


4

In my case, the reason was that I was using pg_restore from postgresql-contrib version 11.2 to restore a dump made by pg_dump 9.6 to a PostgreSQL cluster 9.6. After I downgraded my pg_restore back to 9.6, this schema "public" already exists error was gone, and the restoring process worked as before.


4

After restoring the full backup, do you restore the second differential backup and the last log backup? This is going to depend, but assuming: The source server is offline and the data files cannot be accessed The most up-to-date copy of the data is what is asked to be restored All backup files are successfully accessible Then you'd want to restore the 1:...


4

In this test I am checking the backups for a database called JunoReporting first I create a function to test the physical existence of the file USE MASTER GO create FUNCTION dbo.fn_FileExists(@path varchar(8000)) RETURNS BIT AS BEGIN DECLARE @result INT EXEC master.dbo.xp_fileexist @path, @result OUTPUT RETURN cast(@result as bit) END; GO ...


4

You absolutely can see a running history of database ownership changes, as long as the information hasn't rolled out of the default trace (how long that takes depends on how busy your instance is in terms of other things that are captured by the default trace). Let's say I created (or restored) a database called splut: CREATE DATABASE splut; GO Then I can ...


4

You cannot go back in time and get an incremental backup as of a certain time. If the database is in full recovery mode, you can give them the transaction log backups (if you have them) that go from the backup they restored to the time they want to restore to. However, if they have been using the database, they will have to restore the backup again and ...


3

Your command for full backup --Take full backup to begin new TLogChain backup database DbNameHereCorrupt to disk = @fullBackupPath with init, differential; does not make full backup. DIFFERENTIAL Used only with BACKUP DATABASE, specifies that the database or file backup should consist only of the portions of the database or file changed ...


3

Each and every restore operation relies (in some part) on the LSN that is stored together with the backup and internally in the msdb database. Covering The Basics From some Microsoft documentation: (emphasis mine) LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored. When a backup is restored, ...


3

Here's what I did ... this required a lot of trial and error, but I've got access to the database. Installed Oracle with it's home on an R: drive. Then opened an administrative command prompt, then: R:\>set ORACLE_HOME=r:\oracle\product\11.2.0\dbhome_1 R:\>set ORACLE_SID=SVP R:\>oradim -new -sid SVP -startmode manual -pfile r:\oracle\product\11.2....


3

Log Shipping utilises an executable called SqlLogShip.exe. As per the documentation, when it runs as part of the LSBackup job, it removes old backup files outside the defined retention period on the primary: The backup operation creates the log backup in the backup directory. The sqllogship application then cleans out any old backup files, based on ...


3

You should always produce a log backup before restoring a database. Call it a tail log backup if you wish. I call it "the last log backup before a restore". If you can't start your SQL server, meaning the ldf file is isolated, you hack-attach that ldf file into a running SQL Server and do that tail log backup there. Nothing complicated, really, just ...


3

The question you are referencing is a bit of a mess, I think you would be better off not referring to it at all. It just introduces misconceptions to then partially remove some of them. If you want to backup and restore the whole cluster, you should use "pg_dumpall", not "pg_dump". Using "pg_dumpall" without any of the restrictive options will emit the ...


3

DISCLAIMER Please be advised that these steps are by no way complete and do not guarantee a restore of your database and/or data. Please use them as a guideline in your specific situation and be aware that a multitude of unknown causes, could result in complete data loss. Step 1.) and Step 2.) can be used in any situation Further additional ...


3

Created the certificate from the .cer-file […] You created the certificate but you didn't include the import of the private key, so it makes the use of this certificate to decrypt the data impossible. why I have to transfer the certificates private key to my client. In this case, the certificate (which at its heart is just an asymmetric key) uses the ...


3

The error indicates mssql doesn't have permissions to read the backup file. You can give other users (users other than the file's existing owner or group) read access to the backup file with the chmod example below. This will allow the mssql daemon user to read the backup file for the restore. sudo chmod o+r /home/xxxx/DBBackups/xxxx.bak Regarding your ...


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