DB Engine :: Restore Database When It Is Accessed By Application?
Oct 5, 2015
Our application team make design changes of the database in their development server and asks me to restore the bak file from the development server into the production server with replace command. The database in the production server is connected to the application server and so when I try to restore the database error message comes that the database is in use.
So first I find out the sessions with the logins who is the user in the database with sys.dm_exec_sessions joined with sys.dm_exec_requests and then kill the sessions. And after that it is possible to restore. But many times when I kill the session and try to restore in between new sessions comes into effect which are generated from the web server and many times I have to resubmit the query in the dmv's and find the sessions and kill it again and again before restore can be done. Is there any correct method to restore an active database which is being accessed by the application from a web server with out stopping the web server?
What are the optimal values for this parameters? How it depends from queries characteristics?I create an application that insert some data in database. It'll work on different servers with different load and performance. I want to prevent timeout exceptions.
One of our database came to Restoring mode. I suddenly stop my SQL service and Copied only MDF files again Started SQL service ,unexpectedly i dropped the Database. Now i cant able to attach the database only with my MDF file.
I tried below Scripts. All scripts shows same error.
EXEC sp_attach_single_file_db @dbname='PhoenixPolice', @physname=N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf' GO CREATE DATABASE PhoenixPolice ON (NAME = N'PhoenixPolice', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf') FOR ATTACH_REBUILD_LOG GO CREATE DATABASE phoenixPolice ON (FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf') FOR ATTACH
All scripts shows below error
File activation failure. The physical file name "F:Microsoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAPhoenixPolice_1.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'PhoenixPolice'. CREATE DATABASE is aborted.
I am using SQL Server Management Studio 12 to try to restore a backup to a new database. I know I have done this before, but cannot seem to make it happen now. Her is what I have.
Backup file is "C:TempInProcessInspection_DataSQL_backup_2015_02_23_000012_4551393.bak" and it is for sure there.
This came from a customer of mine.I launch SQL Server Management Studio 12 and log in as SA. I right click on Databases and chose Restore Database. I choose Device then file then Add and try to browse the the named location.
When I get there, regardless if I choose All files or Bak, I see no files.I tried to upload an image of the "Upload Backup File" dialog, but MSDN would let it happen. When I get to the c:temp folder, no files appear in the window.
We have a production 2012 R2 SQL server that is not part of a domain. I have a database called CSSDC. I need to backup this database and make the BAK file available to a user on a different SQL server. Security is a concern. In order for this user to restore the database, they will have to be in the fixed server role sysadmins. I know I am going to have to use the Move with replace for the filegroups for the restore. Once the database is restored it will have to be altered to read-only. I would also like to remove the existing security and only have this new user access the read-only database.
I have a bunch of datbases I have created during the development process. I want to delete those no longer being used. How do I find when the last time a database was accessed?
I was having issue with one of my databases in SQL EXPRESS. It is offline this morning …it said “Database 'MyDB' cannot be opened due to inaccessible files or insufficient memory or disk space. When I checked the error log ..it only said “FCB::Open failed: Could not open file D:DatabasesMyDB_Data.mdf for file number 1. OS error: 32(failed to retrieve text for this error. Reason: 15100).
I did alter database offline and and online ..it works for me( I can access the database again but I need to find the cause of this issue )I checked the max memory setting is still by default .. not limited for SQL ..it could be the cause ?
I am hosting my database on a third-party Microsoft SQL server. The guest user is "Off". But, I am not sure if any one else is accessing/viewing/editing my database contents.
Can I know/log/record the users/logins who are viewing/accessing my database. Is there any script or any exisitng machnism which can be used to track these users.
Also, are there any things that I should take care with my database from outside users.
I am hosting my database on a third-party Microsoft SQL server. The guest user is "Off". But, I am not sure if any one else is accessing/viewing/editing my database contents.
Can I know/log/record the users/logins who are viewing/accessing my database. Is there any script or any exisitng machnism which can be used to track these users.
Also, are there any things that I should take care with my database from outside users.
Hi,I am hosting my database on a third-party Microsoft SQL server. The guest user is "Off". But, I am not sure if any one else is accessing/viewing/editing my database contents. Can I know/log/record the users/logins who are viewing/accessing my database. Is there any script or any exisitng machnism which can be used to track these users.Also, are there any things that I should take care with my database from outside users.Thanks-Sudhakar
The application server gets below error while the job is being run intermittently:
An error occurred while performing connection management
com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171) at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:319) at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:1839)
I have a plan of decommissioning of unused databases which are not been in use for quite some time. is there any simple way/t-sql to find out When was the database last accessed by each user/login in sqlserver.
Hi, I want to manage memory relational data in the memory(Insert, delete, return) and manipulate the data via SQL 2005 and CLR database objects. The gool is to manage a table that relatively has a lot of inserts and deletes which takes a lot of IO operations and i want to reduce it. the data itself is not permanant each row deleted from the table after 15-20 minutes. I looked at the extended stored procedure and other CLR enable objects and it seams that they all return data from the database itself. How can i return data that is reside in the memory? Thanks.
I'd like to build a simple search engine against Sql Server. Does .NET Framework provide some class to help in this task? From what I've heard there is an interface which implements it..
I want to create one bacpac file but it does fails, Need I delete all the descriptions before to begin? How odd! I don't understand why can we choose some scripting options such as we did on 'Generate Scripts' task..I get errors such as: dbo.table.field.Ms_Description is not supported when used as part of a data package
We have an issue where servers goes slow from morning 2 AM EST to 10 AM EST.
Using SP_WhoIsActive we somehow found that there is netbackup from Symantec which runs during that time phrase.
As per Symantec team there backup should get over by 5 AM EST, per their testing for almost 100 Dbs on the server( not big in size , all of them in total would be 60 GB).
Using SP_whoisactive we only see, start time of that virtual backup occurring on tape, for one database at time.
So is there a way we actually determine when does the above backup kicks and stops?Also, could SQL server be the problem in making these backups run slow or there is something else i need to monitor?
Hi there,I sincerely hope that someone out there can help. I have twoinstances of the SQL 2000 Desktop Engine running. One is on my localmachine for development and the other is on another machine on ournetwork which is the production environment. I have built an Access2003 front end application which connects to this database. Thisworks fine locally, as you would expect. I successfully installed thedatabase on the production machine and am able to connect to it viaAccess 2003 (using the Data Link Properties window) and from thirdparty database manager software (similar to Enterprise Manager). I amnot able to to connect to the database via my application.I am using the "sa" account with a strong password. This is myconnection string:strConnection = "Provider=sqloledb;DataSource=server02;UserId=sa;Password=strong;Initial Catalog=Test"The error I'm getting is:"Connection cannot be used to perform this operation. It is eitherclosed or invalid in this context."The connection string is the only thing that changes in my code when Iswitch from my local to my production database. Is there some reasonthat I can't use the "sa" account in this fashion that I'm not awareof? I'd rather not use integrated security for simplicity's sake asthis is a small, internal application. Also, I would have thoughtthat if that was the issue, I couldn't use "sa" at all, even locally.I'm going to post to the Access group as well but thought someone heremight have some advice to offer as well.Thanks,Barb
i have .bak file downloaded from internet , and i also have istalled sql server 2012.my problem that i can not restore this .bak file and get this error massage :
System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATASRO_VT_SHARD.mdf'. (Microsoft.SqlServer.SmoExtended) - .bak file version = 661 10 50 1600 = sql server 2008R - my sql version = Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
Is it possible to backup/restore sql server databases in a logical way like oracle(exp/imp, expdp,impdp, can export/import tables/users etc)?my case, I have a database which has many data files, in different file groups, I would like to put them together into one file and don't want to shred the database.
I have 5 TB of data in production and my development team requested to restore only last month data(<=500 MB ) in staging server and last 2 months data in pre production.
how to restore database backups with different recovery fork. I have 1-full backup 2-diff backups and 10-tran backups. My prod database in mirror, so after error, switched to mirror with "allow_data_loss" option. And now I have full and diff backup with one recovery fork GUID and other backups with another GUID.So the question is, how to restore all this backups if in middle of restoration will be different recovery fork.Tryed to restore log backups with new fork guid and got error:This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time.
1.) Taking a database out of an availability group, 2.) Setting the recovery to simple, 3.) Shrinking the log file, 4.) Setting the recovery mode back to full, 5.) Then backing it up.
I need to restore the file to my secondary server with replace and non recovery mode. I am having trouble performing that call? I have the code to reestablish the database to the availability group if I can get the restore feature working.
I have a database size of 9.8TB and I backup it to 30 backup devices. Each one has 110GB after backup compression.I tried to restore these files to standby server via 100MbE network but it always failed. My colleague told me this never happen before and I said yes because I have done this before a lot of times when backup devices are significantly smaller.He said the only way is to copy backup files locally and restore locally. But I am trying another method, I create more: 64(maximum) backup devices and try to restore via network again.SQL server version is Microsoft SQL Server 2012 (SP1)
- 11.0.3401.0 (X64) Jan 9 2014 13:22:15 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) .
I have seen this before. A 2000 restore fails, leaving the database thinking it is being restored but the restore job failed and errors when it is restarted. EM is clueless. I believe there is a proc to reset some flag. Can you share it with me???
My customer got a total hard drive failure.After sending it to drive recovery specialist we were able to recover the LDF log file (MyDB_0.LDF).But the MDF file was completely destroyed (MyDB.MDF).They have a good full backup from a month ago.
1) Installed SQL Server 2012 on a new PC 2) Created a new database of same name (MyDB) - with same MDF and LDF file names as original 3) Took the new database offline 4) deleted the MDF and LDF files of the new database 5) put "MyDB_0.LDF" in the place of the LDF file I just deleted 6) put the database back on-line 7) after hitting F5 to refresh databases - it shows "MyDB (Recovery Pending)" 8) tried to do Tail Log Backup with this command BACKUP LOG [MyDB] TO DISK = N'C:BACKUPMyDB_TailLog.bak' WITH NO_TRUNCATE
And I get this error...
Msg 3447, Level 16, State 1, Line 3 Could not activate or scan all of the log files for database 'MyDB'.
The sad thing is I know we can get this data back using ApexSQL-Log. I can see all the transactions since the last full backup in this program - so the log file is not damaged. But my client doesn't want to pay the $2000 fee for this software.There has to be a way to restore this data, without having to purchase a third party tool.
I have just finished configuring my first test mirrored environment (High safety mode). I setup the database engine service accounts on each of the servers with domainuser. I inherited a production mirrored environment set up by someone else. On the production servers the database engine service account is NT Authorityuser a local account. I am trying to practice installing Windows updates within a mirrored environment and I not sure how to proceed when the service account is NT Authority user account. should I change the service account to a domainuser?
Trying to keep out sysadmins & sa during/between database RESTORE
Configuration: WINNT Server Enterprise 4.0 w/SP5 SQL Server 7 Enterprise & SP1
2 SQL Servers: Production Server Standby server
I Backup (full backup) databases to disk on primary server (logical backup devices are physicaly located on a Standby server (dedicated gigabit NIC in each server for this process). Transaction logs are applied to the Standby server throughout the day.
Problem: How to keep out "sa" and sysadmins from a database while I'm restoring (or between restores) to a standby server? The database being restored cannot be in use during a restore. If a DBA forgets that this process is happening, the statement fails (RESTORE)for the database they happen to be in at the time of the restore.
Example restore statement: Standby Server - RESTORE DATABASE databasename FROM database_dd WITH DBO_ONLY, REPLACE, STANDBY = 'g:Mssql7FromPrimaryDatabaseName_undo.ldf'
I could restrict Domain sysadmin access and change sa password. I could also put the database in "Single user" mode, however this could become problem if my process disconnects and then someone else connects - then my process is locked out. What I'm really looking for is to lock out all activity for a database that is in "standby mode" except for RESTORE processes.
