SQL Server Admin 2014 :: Get Last Full Backup Times
Oct 27, 2015
I've got the below and have several variation and still cant seem to find a perfect way to query the server to bring back that last full backup per db. I'm shopwing mutilple records in the backup set db w/ type = 'D'. I look online and type D = Database. Which i assumed it meant full database backup. Apparently not. Try running the below on one of your full databases. Then check to see if the date is actually the last backup date.
DECLARE @db_name VARCHAR(100)
SELECT @db_name = DB_NAME()
-- Get Backup History for required database
SELECT TOP ( 30 ) s.database_name,
m.physical_device_name,
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.
If data is modified (by an insert, update, or delete) while the backup is running, will the backup contain those changes or will it be added to the database afterwards?
I am trying to create a logon trigger. As I am testing this, I discovered that each time I do a connection, I get 19 rows, inserted into my audit table. I ran profiler, and I see it is going through the logon trigger multiple times, for a single connection. So, what am I doing wrong? The code is fairly simplistic, and the profiler doesn't give a clue, as to what is going on. When I look at the output, I see the spid for the first couple of connections are different, then a spid, that is different from those 2 is in the next 17 rows. But, when I do an sp_who2, that spid does not exist.
This issue was noticed on a 2012 version, that I was first testing on, then had the same issue on a 2008 R2. I am currently testing on a 2014 version, that is doing the same thing. Is the logon trigger itself, firing, and causing this?
I also tried using the After Logon option, and got the same issue.
Here is the code:
CREATE TRIGGER LogonAuditTrigger ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN DECLARE @Body NVARCHAR(2000),
Message: Executed as user: NT AUTHORITYSYSTEM. The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases [SQLSTATE 42000] (Error 9002). The step failed in my sql server agent job i have the above error, this type of errors i got some of multiple jobs.
We are running SQL 2014 SP1. We are using defined Full text indexes on several tables in the database. However, on one specific set of servers, a certain search will not return any data. This exact same search works on another set of servers built identically. The first responses I'm sure will be stop list, but I have dropped and recreated the FTI multiple times with different stop lists or no stop list at all and get the same results.
The specific word being searched on is YUM. If I change the value to YUMk, it actually returns, and if I change the data to TUM it returns, but YUM does not. This exact query is working on multiple other systems, so it seems to be something environmental, but I haven't been able to pinpoint it.
I was trying to configure maintenance plan to take nightly full database backup and Log backup. I was trying to configure it like in attached file. Any links that i can follow and configure as in attached file.
1- I have created a backup device 2- I have created a maintenance plan full backup and run it - with overwrite option on the backup device 3- I have created a maintenance plan differential backup and run it with append option on the same file of backup device 4- I have created a maintenance plan log backup with append option on the same backup device
When I made restore database from the backup device i found three files the full and differential and log backup
5- I ran again the differential backup maintenance plan ( suppose to be ran everyday night )
when i made restore database I found only two backups the full and the last differential !!!
what I want to do is to take a full backup every week , append differential backup everyday and append log backup every hour
when I ran the last differential backup it erased the first differential and log backups, why is this happening and how to apply this scenario and keep the all differential backups on the same backup device .
I cannot see the file created in the directory. The account under which sql server the agen job run have full privileges on it and is sysadmin.Then i run the Command in ssms
BACKUP DATABASE [F1SB] TO DISK = N'F:SqlBackup2014<server>F1SBFULLIGS-DB01_F1SB_FULL_20150510_214455.bak' WITH NO_CHECKSUM, COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = [serverCertificate])
and I get this error message:
Msg 3013, Level 16, State 1, Line 13 BACKUP DATABASE is terminating abnormally.
I need backup script to take all the database backups and we have the maintenance plan but our database character size is 98 and when we are taking the backups through maintenance plan while storing the backup history information it is adding the date and timezone information and exceeding the length to 128 so it is not writing the information on MSDB.
So we want to take the backup using the script and it has to create sub folder for each database. Also if any of the database fails it should continue with others.
With all the new functionality, can 2014 now restore a single table from a standard backup without using any third party tools? I have looked, but can't see this listed as a feature (though that doesn't mean it's not there, maybe I've just missed it).
I am planning to take one full backup and Transactional Log backups for every month ..as i will be making the changes in database only once in a month .
And I am aware of that in case of disaster i need to restore database with all the Transactional Log backups . My Plan is to have Transactional log backups for 5 Years and after 5 years i would be taking a full backup .
So should I need to take any other precautions or concerns with this approach.?
we currently use Backup Scripts from Ola Hallengren, It Says Full (non copy-only) and differential backups are performed on the primary replica. Full(Copy-only) backups and transaction log backups are performed on the preferred replica.
we currently do FULL(COPY_ONLY) Backup everyday and LOG Backups for every 15 min, is there any performance benefit on running the FULL (non copy-only) on the preferred replica .
I would like to know what happens if i shrink the database with truncate only option and do a full backup or transaction log backup ? are the full backup or transaction log backup valid? I know that the performance of the database is bad if i shrink the database. What happens to full backup or transaction log backups?
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'm performing SQL Server native backups and Comvault Backups are breaking the backup chain?I can work around them if they only perfume 1 backup per day.We have a 3rd party hosting our servers so I do not have much control as to what they do.I have concerns about being able to get my databases Comvault being restored.
I've recently started working with a public sector organisation who have 4 clustered sql instances that has 80% of it's db mirrored.
Looking at the transaction log - it seems that a transaction log backup is a good idea as the log is 4x larger than the data file.But I'm not allowed access to the physical server to check onto which drive I can create the trn. No RDP, no vmware - let's be honest I'm not even allowed to launch cmd line Also the Server Manager informs me "We will need to carefully look at database backups if you guys want to start doing these backups on box, as that will break our off box backup routine (it will screw the transaction chain)."
I don't understand how backing up the transaction log could break the "transaction chain"?
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: )
I've a SQL server 2014 running on one of our server. We're in the process of implementing security steps for our databases. I've encrypted a column in one of the table in the database on the server. The issue is when I restore the backup on my local SQL server and run a query to decrypt the column data it gives me null values. On the other end when I decrypt the column data on the main server it works fine. I found a thread on this forum which states to do the following when restoring the encrypted database on different server.
USE [master]; GO OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongPassword'; ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY; GO
select File_Name , CONVERT(nvarchar,DECRYPTBYKEY(File_Name)) from [test].[dbo].[Orders_Customer]
I did tried the encryption on server "A" for database "AdventureWorks2012". Then I tried to restore to server "B". There was the certificate issue, and I thought "of course : it's encrypted ! Let's deactivate it". So here I go "ALTER DATABASE AdventureWorks2012 SET ENCYRPTION OFF".I look at sys.databases : not encrypted.I backup using no encryption, I verify using msdb.dbo.backupset : not encrypted.
I move my backup to my other server where encryption was never configured (so no certificate, nothing...), and I have the error : Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xFA130E58C999C4919B8975999C83A75A403B11D8'. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
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.
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?
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.
