SQL Server Admin 2014 :: What Does Restore Log Do After The Log Is Restored
Jul 29, 2015
when execute the restore log command, in the messages window it shows how many seconds the restore takes, at the meantime, on the status bar, it also shows the seconds the command takes.
Two values are different and could be very different, please see below examples , restoring takes 1.8 seconds, but in total the command takes 4 seconds to complete, the other one is 8.1 seconds and 12 seconds.
What does SQL Server or Windows do after the restoring?
pic a:
pic b:
I did a xperf, I can see after the restoring is completed, sql server did garbage collect and log write, which just run very quickly, but storage is busy on reading the log file for nearly 2.2 seconds( 4-1.8), and 4 seconds ( 12-8.1) .
pic 1:
pic 2:
see pic 1 above, from 13 to 17, the restore operation is finished, but the storage jump to 100% active to do some reads, only reads no writes. zoom that period shows pic 2, it read 4096 (I don't know the unit size) for about 4 seconds, what does this do?
Data file, log file, backup file are no different drives, but all local drive, the interesting point is the read jumped after restoring, I tested it on different server, same result...
In Windows Server 2012. How do I do a System Restore to a previous restore point?I need to install the 64 bit and 32 bit Oracle Client Install for connections in SSIS and to create Oracle Linked Servers.
If you make a mistake it is not fun removing it. Sometimes it corrupts the machine and it is difficult to uninstall since there is not an Oracle Universal installer for Oracle 11g.If you install the 32 bit before the 64 you mess up the machine.how to create a restore point.
My sql databases in SQL Server 2014 has the status "suspend" as I saw in SQL Management Studio. I can't restore to serviceable condition sql databases through standard procedures. I need to restore .mdf file.
I have two databases like each other that one is the backup of another. Each DB have 2 filegroups. I want to replace one filegroup from one db to another. How do I do this? Or how do I backup and then restore?
is there a way to restore all file groups except one? example: Database A has 10 filegroups, but 1 of them is defunct, so i cant delete it and there's no backup for restore it.Can I create a new DB restoring the 9 good FGs from a database A's backup?
I have a windows 8 pc that I just got and installed sqlexpress 2014. My buddy haw windows 7 and installed sqlexpress on his pc. We create a db on his pc, did a backup, copied the backup to my pc. In ssms I right click on "database" > restore database. click device and the button to find my file. I navigate to the folder where the file shows in file explorer but the .bak file does not show in ssms to restore from. This is probably a windows thing but I have don't know what to look at.
What is the best method to restore a DBTest1 (with one .mdf and one .ldf) into DBTest2 (with one .mdf, multiple .ndf data files and with 4 filegroups associated with specific data files). I do not see how the one .mdf file (in DBTest1) can be separated into the other 4 filegroups (in DBTest2). This does not sounds like it is possible with Backup DBTest1/Restore to DBTEST2 or (Detach/Attach) because the underlying filegroup and file structure is different.
What method should be used to get the data and structure from DBTest1 (includes 1100 Tables and 550 GBs of Data) into DBTest2 (with 4 filegroups)? Is the following possible:
1) First, in DBTest2, execute a script to create tables/indexes on appropriate filegroups.
2) In DBTest2, use scripts to pull data from DBTest1 into DBTest2, for example INSERT INTO DBTest2.dbo.tables with SELECT FROM DBTest1.dbo.tables OR use SELECT/INTO DBTest2.dbo.tables FROM DBTest1.dbo.tables.
Or, is it possible to use the BULK INSERT or BULK COPY Options? Export/Import Wizard?
Does the Create Index step needs to be done after the data is loaded into DBTest2?
I have SQL 2014. When I try to restore a user database using SSMS GUI, the Restore Database Pop up box never pops up. This happens for any database on this server at any time. Sometimes I get the pop up, some times I dont get.
So I tried to click on Databases on Top and Restore Database, and then select the db that I need to restore from Drop down, then it shows "creating restore plan selecting backups" but it takes forever.
We have full backup and trn log backups every 30 mins. So is it trying to get all these backup files in the background causing this issue? If yes then how to overcome this?
I'm trying to figure out why this SQL Agent job keeps failing.
We used management studio, and connected as mydomainmyuser, and developed a script to take a backup file from a network share and restore it. It worked fine in SSMS under that login.
After we got it working , we created a SQL Agent job on the same server to run the script, and set the agent job to run under that account that we tested with.
This is the error message we got:
"Executed as user: mydomainmyuser. Create Database permission denied in database master'.
So, I gave that login the rights to Create Database and Create Any Database. Then the error message changed to:
"Executed as user: mydomainmyuser. User does not have permission to RESTORE database 'mydatabase'. [SQLSTATE 42000][ERROR 3013] RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. "
* I can't use SA for the job account, because the SA account doesn't have rights to see the network folder where the backup file sits, so it has to run under the domain account. * The user is a member of the dbcreator role - and the serveradmin and sysadmin roles * The user is a member of dbowner on the database I am trying to overwrite with the restore * I have given the user the rights CREATE DATABASE and CREATE ANY DATABASE
The only suspicious thing I found was that it appears the server was renamed at one time. When I looked at the login in management studio, I was not able to change some of the rights. On the Securables page, it shows the server name as "MyServer-New", but the server name is "MyServer". It is a replacement, and I suspect that when they did the replacement they named it "MyServer-New", set everything up, then renamed it.
I found this post listed below, and ran the script (shown below), and it showed that the server name was MyServer and the ServerInstanceName is MyServer-New
[URL] .....
SELECT HOST_NAME() AS 'host_name()', @@servername AS 'ServerNameInstanceName', SERVERPROPERTY('servername') AS 'ServerName', SERVERPROPERTY('machinename') AS 'Windows_Name', SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS 'NetBIOS_Name', SERVERPROPERTY('instanceName') AS 'InstanceName', SERVERPROPERTY('IsClustered') AS 'IsClustered'
I can't reset SQL until the next maintenance window to test changing the server name as outlined in the post.
Am I on the right track with the name change messing up permissions, or is there something else I need to check?
What is the best way to restore a database from a folder of backups (including full, diff and log backups) without using the backup history in msdb?
I have a restore process that restores all backups on a regular schedule in order to fully verify their integrity. To do this, I use the backup history in msdb on each server that I'm monitoring. I had a thought the other day that I would be in trouble if I lost msdb. Then my backup history would only be as good as the last backup of msdb.
What I'd like to do is read a folder of backup files and generate a restore script up to a specified time. Would I use RESTORE HEADERONLY to do this? If so, would I use PowerShell to traverse each file in the folder?
I have an issue to restore an encrypted backup via GUI.I can restore an encrypted backup on another instance using t-sql command, but cannot do the same using the GUI of sql server 2014. The message error is: No backupset selected to be restored.I have restored the master key from the instance where the encrypted backup was created on the instance where I want to restore the encrypted backup, then restored the certificate. I managed to open the master key. I have the following versions:
On the instance where the encrypted backup is taken: Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
On the instance where I'm trying to restore the bakcup via GUI: Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
Is there any way to restore a database of higher version to lower version.
E.g. I have created a database in sql server 2012, created some tables & procedures in that.I took Full backup of that database. Can I restore it to sql 2008r2 or any lower version.
I know direct restore is not possible, I have to use either import or export option or generating script,but i want to know is there any easy step to do so.
I have Full database backup upto previous day and transaction logfile of Today transaction. my database has crashed. I have restored previous day's Full backup. I have faced difficulty to restore today's transaction from today's transaction log. What are the steps to restore full database back and one day's transaction log file. Note: there is no differential database backup and transaction backup.
Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.
Hi I took a Full Backup on Production Server and Restored the Same Database on Testing Server, i am able to see all the Tables Stored Procedure and View except Logins, i am not able to see any logins, there are total 650 Logins under the Database how can i see the logins and how to restore the logins, while i am trying to create any logins it is saying Error Occured login is already existing in the Database..
This is more of a fyi than a question.After restoring a live db backup to our development server there wasan identically named login Melb02 on both the db server and in therestored db backup.However if you log in as Melb02 on the server you cannot access anyobjects owned by Melb02 on the restored db, without puttingMelb02.<object_name> in front. This would break our app so we neededa workarround.The mssql docs say that first check if the login is the owner, thendbo then deny access - something to that effect. So this wasconfusing.workarroundI think what happened was that the db restored from the live remoteserver had a different security id (sid) to the development serveralthough both users had the same name: Melb02. Thus if you log in asMelb02 you can't get access to Melb02 objects on the restored dbbecause of the different sid.we wrote a cursor that went though everything in sysobjects belongingto Melb02 and did a : sp_changeobjectowner to a new user login thatour app now happily uses.Jol.
I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.
I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.
I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).
So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.
Could someone please help me for SWL backup restoration and db by user
I restored SQL 7.0 database to SQL 2000. ( by creating empty db on SQL 2000 and restored from SQL 7 backup) -- restore ok.. I need to use same SQL user which is admin for DB on SQL 7, for SQL 2000 also. ( DB user is sql user not domain user) I put mixed authentication mode ( windows and SQL) in SQL 2000 enterprises manager security tab setting. I can see DB user is available in DB user list on restored DB but can not access DB when I try to access from query analyser I tried to create new login with same name as it was in SQL 7 and tried to give full admin access on SQL 2000 enterprise manager but I get error 21002:[ SQL DMO] User 'user' already exists.
Im running SQL 7.0 in production with no SQL service pack applied. I am trying to copy the database over to a test box running SQL 7.0 SP3. Both servers are NT 4.0 with SP6.1a. When I restore the database on the test box, I do not get the Users restored but I do get the Tables and other items. The SQL backup Im restoring from is a complete backup. Could the fact that the server Im restoring on is SQL 7.0 SP3 and it came from a pure SQL 7.0 server?
If I install an instance with Windows Only authentication, and then change it to Mixed Mode, if I enable the sa login, the password has already been set. What is the default? If it's generated, how secure is it? Is the password generated? What algorithm is used for that?
I am using a monitoring system where I can monitor a numeric SQL result assuming the result is one field and one row.I would like to do this to say monitor the free available space or percentage on say the Master database. DBCC SQLPERF gives me a few columns and results for all databases on the server.
In our environment applications are using a DNS name which points to the physical server ip address. Now we are planning to move to 2014. We are planning to have servers in different subnets so we will be having two ip adresses for listener. How we can point the DNS to the listener ips? If failover happens can the DNS point to the exact ip address of the listener where it's primary node?
"Process 0:0:0 (0x1e10) Worker 0x00000006B6D341A0 appears to be non-yielding on Scheduler 13. Thread creation time: 12906028806348. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 13%. System Idle 84%. Interval: 70189 ms."
Is it better to run the profiler or performan counter?
What are the filters we have to select in the profiler to monitor the Sql server
I have a SQL server box running 2014 reporting services. I have another server running IIS v8.
I would like to be able to connect to the IIS site and be given the SSRS report browser.
So externally if I browse to [URL], I am presented with the report server interface, the same as if I browse to http://xxx.xxx.xxx.xxx/reports internally.
What is the best approach for a read only copy of a database that is ~ 1TB. The primary database is fed nightly with an ETL process. We are currently trying to duplicate the ETL to read only server but that process is not going well. So we are looking at other options to let SQL make the copy.
The primary database is on a Win12R2 with SQL 12 or 14, a 2 node A/P failover cluster.
The read only copy will be on a Win12R2 with SQL 12 or 14. It is not a requirement to fail over to the read only copy if the primary should go down.
What would best the approach to accomplish the end result?
I have 10 databases which are configured as principal in mirroring I need to failover all the databases as part of failover , instead of writing query each database as parner failover, is an script which will generate the databases as principal to failover ?
