The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Hot answers tagged

117

This will list all "most recent" restores for each database on your server: WITH LastRestores AS ( SELECT DatabaseName = [d].[name] , [d].[create_date] , [d].[compatibility_level] , [d].[collation_name] , r.*, RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC) FROM master.sys.databases d LEFT OUTER JOIN ...


64

I wrote the free (and open source) sp_Blitz for this exact reason. People kept handing me SQL Servers and going, "You're the DBA, you manage this thing." I needed something that could quickly analyze stuff like: Databases that hadn't been backed up or checked for corruption Unsupported builds of SQL Server Dangerous trace flags and database settings Bad ...


57

You can let mysqldump create the dump in such a way that it does not create or select the database. EXAMPLE : You are dumping the database db1 and loading it into database db2 This will put in the CREATE DATABASE and the USE commands in the dump mysqldump -u... -p... --routines --triggers --databases db1 > /root/db1.sql This will not put in the ...


37

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


36

In order to restore a contained database into a different instance of sql server, in this case my local server, the "Enable Contained Databases" property must be set to True. You can do this from management studio: Right-Click on the server instance, select Properties Select Advanced page, set under Containment the property value to True Proceed to ...


33

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


27

No, this is not possible.SQL Server 2017 backups cannot be restored by any earlier version of SQL Server ref Also, regarding detatching and reattaching per the docs: After being attached to SQL Server 2017, the database is available immediately and is automatically upgraded. This prevents the database from being used with an older version of the ...


26

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.


23

Repro Open up SSMS Type in the following into a new query window use <YourDatabase>; go Go over to Object Explorer (SSMS) and right-click on <YourDatabase> -> Tasks -> Take Offline Open up a second new query window and type the following: use <YourDatabase>; go You will be prompted with the following message: Msg 952, Level 16, ...


22

You are allowed to save more than one backup in a backup file (i.e. device). The FILE clause lets you access a particular backup operation when there are multiple to choose from within the .bak file. For more information on the various options of the RESTORE command, please see the following MSDN documentation for RESTORE Arguments. If you look under the ...


21

No, there is no workaround to upgrading a database directly from 2000 to 2012. Since you don't have a ton of data, you can do all kinds of things to move the data (but not the database as a whole), including: Import/Export wizard SSIS BCP Manual queries using a linked server from 2012 or an application However these will not necessarily bring over other ...


21

This is simple and quick to test. Take that 2TB backup file, copy it to Azure using AzCopy. Provision a SQL Server using the "Free License: SQL Server 2017 Developer on Windows Server 2016" image configured with 12-15 TB of SSD storage. Remote desktop to the SQL VM and restore the backup directly from Blob Storage, or copy it to a local disk and then ...


20

Straight backup and restore is obviously out. I also wouldn't consider replication of any kind. Database mirroring is relatively simple to set up, but requires real-time connectivity between the two servers, setting up of partners and endpoints, etc. Availability Groups could be an option, but on top of the networking complications you also have to have ...


19

You'd have use the Import/Export wizards in SSMS to migrate everything There is no "downgrade" possible using backup/restore or detach/attach


19

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


19

Please try the below query, REPAIR TABLE nagios_servicechecks; Try the above query in terminal or check the below link to repair table or databases via phpmyadmin http://php.about.com/od/mysqladministration/qt/repair_mysql.htm


18

The reference to --binary-mode (introduced in MySQL 5.6.3) is probably a distraction. It doesn't sound like you're dealing with a mysqldump output file, there. Try the file utility. shell> file dumpfile.sql dumpfile.sql: ASCII text If you don't get the ASCII text response, you're dealing with either something that isn't a dump file from mysqldump at ...


18

Configure pgAdmin->Paths->Binary paths and set "PostgreSQL Binary Path" as shown in the screenshot. Depending on your OS and installation details, the binaries may be located elsewhere. Try `which psql` from the command line on linux/Unix systems. A Windows example: PostgreSQL Binary Path: "C:\Program Files\PostgreSQL\9.6\bin"


16

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.


16

The script that can be used is: declare @DB sysname = 'MyDB'; select * from msdb.dbo.restorehistory where destination_database_name = @DB;


16

I recently migrated 15tb across 6 databases using mirroring. Very simple and worked perfectly with just a couple seconds of failover time. Edits: I had two new virtualized SQL Servers. The databases were coming from 3 servers that they had just plain outgrown, and were impacting performance on the smaller databases hosted on them. The process was very ...


16

You have to use the mysql client to reload mysql -u root -p -Ddatabasename < /home/databasename_bkup.sql Another way to reload would be mysql -u root -p -Ddatabasename then from the MySQL prompt, do this mysql> source /home/databasename_bkup.sql If you would like the mysqldump to drop and recreate the database for you, create the dump like ...


16

Not uncommon during a whole-DB restore because that's an exceptionally huge operation. If you see this during normal operation, consider raising your setting for checkpoint_segments permanently, just like the error message hints. You might go to the trouble of setting checkpoint_segments higher just before the restore and then lower it again. This is even ...


16

This is "normal" behaviour in SSMS if you check the box "Take tail-log backup before restore" and the box "Leave source database in the restoring state" It may not be intuitive and it's probably not the best decision ever to check those boxes by default but you have to un-check the box "leave source database in restoring state" to avoid that behaviour: So ...


16

The standard procedure would be to: Obtain the page IDs that have to be restored. Start a page restore with a full database. Apply the most recent differential backup. Apply subsequent log backups. Create new log backup. Restore the new lob backup. After the new log backup has been applied, the page restore is completed and the pages are then usable. ...


15

Listen to your adviser. By restoring a backup, you are essentially replacing the database schema and data. You will need to turn synchronization off, remove the DB from HA and perform the restore on the primary and replica, leaving the replica version in a restoring state by using WITH NORECOVERY. Once your backup is in place, put the DB back into HA and ...


15

The error is harmless but to get rid of it, I think you need to break this restore into two commands, as in: dropdb -U postgres mydb && \ pg_restore --create --dbname=postgres --username=postgres pg_backup.dump The --clean option in pg_restore doesn't look like much but actually raises non-trivial problems. For versions up to 9.1 The combination ...


15

This information is not tracked in MSDB, so there's no way to find it there (unless the restore operation is in its own step in a job, in which case you could get it from msdb.dbo.sysjobhistory). If you can't rely on job history, and it was recent enough, you can approximate it from the default trace, with the assumption that the system does something to ...


15

You don't need to mess with RECOVERY and NORECOVERY here, all you need is the STANDBY option. Here's a quick demo on how to use it. Create a database, set it to simple recovery, and create a table. Insert data, take some diffs. Fun, right? USE master; /*Create a dummy database*/ CREATE DATABASE DiffRestoreTest /*We simple now*/ ALTER DATABASE ...


14

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


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