New answers tagged

4

Do I restore each transactional log file as "standby" all the time and just leave them as "standby" mode? Either will work, but it is faster to apply all the logs except the last one using NORECOVERY. The last one applied uses STANDBY, making the database available for read-only access. SQL Server has to do extra work to make the ...


1

For RDS: exec msdb.dbo.rds_finish_restore @db_name='database_name';


0

The steps that worked for me I found here. Create a backup of the database you want to clone first, then restore it as a new database after renaming the current one: -- Run this script in SQL Server Management Studio: USE [MyDatabase]; -- Changing Physical names and paths ALTER DATABASE MyDatabase MODIFY FILE (NAME = 'MyDatabase', FILENAME = 'C:\Program ...


0

The copy-backup.sh can be found on the repo Then following the commands on the documentation will produce xtrabackup.stream file.


2

Jonathan Kehayias' article Database Version vs Database Compatibility Level brings some interesting info related to that error message: When a database is opened for the first time on a newer version of SQL Server, the database is automatically upgraded by the database engine. If you were to make a backup of a SQL Server 2000 database named ExampleDB and ...


3

The easiest options are to either upgrade the source instance to the same version as the target (or otherwise ensure the database engine versions are compatible), or perform a restore from a full backup every day to refresh. For example, say the source was SQL Server 2008 and the target was SQL Server 2012. Upgrading the source installation from SQL Server ...


0

Try sp_dbpermissions <db_name>. It will give both grant and revoke scripts. you can download the script by Kenneth Fisher from his blog.


6

It's more likely that the log backup occurs after some processes that generate a lot of log records, like index maintenance or any ETL-ish tasks. You may find some benefit in increasing the frequency of your log backups to every 5-15 minutes so that internal truncation occurs more frequently, since that will likely make the backup size smaller.


3

This is the expected behavior. When you restore using "With no recovery", it tell SQL to leave the DB in the "recovery" state (allowing you to restore subsequent backup). If you want to make the database available, you can run a Restore database [dbname] with RECOVERY; Note that running this command will bring the DB online and you will ...


1

It is not normal for a newly created database to be recovering status. Try executing the command: RESTORE DATABASE ADMINISTRATEUR WITH RECOVERY; ...from a query window in the context of the master database.


3

When you shutdown/restart your computer/server, the service for the SQL Server Express instance, will be triggered to stop too. While the service is shutting down the SQL Server Database Engine will try to shut down the databases gracefully, in order to ensure they can be restarted in a timely fashion when the system comes back up. If the system was forced ...


3

If you need a method to restore the database but do not have an Intel/AMD-based system available, then a virtual machine on Amazon, DigitalOcean, or elsewhere may give you what you need. If you’ve never had an Amazon Web Services account, you can even do all of this for “free”. The process might work like this: Sign up for an account with a VPS provider ...


5

The error is saying that there’s a chance that Oracle will end up updating the same row twice because there is no unique constraint on _file_*date*_BAK.filecod. The simple solution is to create the unique index on your logical backup table: Create unique index _file_*date*_bak_uix on _file_*date*_BAK (filecod); And then running the update statement exactly ...


0

Execute this before doing restore (DBNAME replace with real one): DECLARE @execSql VARCHAR(1000) = ''; SELECT @execSql = @execSql + 'kill ' + CONVERT(CHAR(10), spid) + ' ' FROM master.dbo.sysprocesses WHERE DB_NAME(dbid) = 'DBNAME' AND DBID <> 0 AND spid <> @@spid; EXEC (@execSql)


0

I need to analyze the client's data and for that requirement client has provided us only ibdata1 file ... Completely useless. You asked them for a book so they've taken a copy of the book, cut out all of the words and sent them to you in a great, big, cardboard box. If you're very, very lucky, you might be able to put it all back together into something ...


1

If your client is unable to give you a proper database dump, then whatever task they're asking of you will take far longer and cost much more than anyone should be willing to tolerate. Is there a way I can restore the database with only ibdata1 file? ⇢ Yes, but not without a remarkable amount of work. People who are not trying to save a multi-billion dollar ...


1

There is a fairly simple way that I use for a similar task: Restore a backup under a different name. For example, WellProductionInjection_new_data Right-click on WellProductionInjection_new_data -> Task -> Generate Script In the advanced options specify Script USE DATABASE = False Types of data to script = Data Only Save the script to a .sql file ...


2

You can kill all connections this way: USE [master]; DECLARE @kill varchar(8000) = ''; SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';' FROM sys.dm_exec_sessions WHERE database_id = db_id('MyDB') EXEC(@kill); Then you pass to single user mkode: ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK AFTER 60 --this will give your ...


4

SQL server doesn't have any built-in capacity to merge multiple backups into a single database, you'll have to restore each backup with a different database name (like WellProductionInjection_2019 and WellProductionInjection_2020), then do the work yourself to combine them however you need. Once the databases are restored, you can combine them using a ...


Top 50 recent answers are included