Hot answers tagged

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


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


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

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.


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


3

Without using third party software, your best bet is to restore the backups one by one and running your query on the restored databases. If you're looking for the last backup that contains those records, you can be smart about which databases you recover, so you don't have to recover all of them. Example, restore the oldest backup, if it contains the ...


3

MariaDB [theDBName]> SOURCE C:\Users\myUserName\theDBName_data.dump Either of the following should be correct: MariaDB [theDBName]> SOURCE C:\\Users\\myUserName\\theDBName_data.dump MariaDB [theDBName]> SOURCE C:/Users/myUserName/theDBName_data.dump


3

Do the restore using T-SQL with the RESTORE DATABASE ... statement - that should provide a more meaningful error. In brief, restore database works like this: RESTORE DATABASE [database_name] FROM DISK = N'\Path\to\the\backup\file.bak' WITH RECOVERY , MOVE N'file name' TO N'new\path\for\this\file.mdf' . . . I wrote a blog post with code that automates ...


2

but I can't find any examples online of how to use verifyonly with a destination to test. How can I do this? You can use this syntax: restore verifyonly from disk = 'V:\SQL_backup\myBak.bak' with move 'myDb' to 'C:\myDb.mdf', -- put here your destination move 'myDb_log' to 'C:\myDb_log.ldf' -- put here your destination In case of insufficient ...


2

I've been able to export data from an Azure Managed Instance (MI) to a SQL Server 2012 system successfully without the need of Visual Studio. Using SQL Server Management Studio (SSMS) right click on the database in you want to copy choose Tasks > Export Data-tier Application. Choose where to save the .bacpac file that is created. A .bacpac file is a ...


2

Also from the Database Backup and Recovery User's Guide. The total number and size of backup sets depends mostly on an internal RMAN algorithm. However, you can influence RMAN behavior with the MAXSETSIZE parameter in the CONFIGURE or BACKUP command. By limiting the size of the backup set, the parameter indirectly limits the number of files in the ...


2

If you have the mdf file, you have your data. Re-install the SQL express edition, attach the database file take a backup and restore it to your developer edition, see Restore database according to editions if you have issues with the restore. If you don't have the mdf file and you don't have a backup, it is gone, start over. When you installed developer ...


1

I'd just like to know why it didn't save as .bak file. In other servers that I've worked on it's always .bak This might have happened: Declare @BackupPath varchar (256), @DBName varchar (256), @DateTime varchar (25) --- With Extension (Other Server) set @BackupPath = '\\SharedLocation\' + @DBName + '_Full_' + @Datetime + '.bak'; --- ...


1

Using backup database plus archivelog rman will make several steps: log switch (creates new archivelog) backup of archivelogs backup of datafiles log switch (creates new archivelog) backup of archivelogs for the time of db backup backup of controlfile and spfile; So here rman will create at least 4 backupsets: two - with archivelogs, one - with datafiles, ...


1

Bacup-restore from a MI is not supported. The backpac extraction fails when it finds few dependencies or unsupported features.This is because SSMS as it verifies your extraction. Try using SSDT (Visual STudio) and you can skip verify extraction (this is by default unchecked) : Open SQL Server Object Explorer Connect to your MI. Right click the database and ...


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