I have a question on how the retain parameter works for Backups. If I have a nightly full backup for a database and have retain = 10, does that mean on the 11th day the very first backup will be purged? Will the 11th backup attempt to take up some of the space that was occupied by that first backup? It seems that the 11th backup would be too large for the space that the first backup was taking up. I am fairly new to backups with SQL Server and want to ensure I am setting this up properly. Our goal is to keep backups for 10 days only.
I have two tables totally unrelated but give the same information, the difference is the duration. I need to create a stored procedure that will give the recent issue dates only, accept if they have already expired. I'm not exactly sure how to do that. We only want the employees to see the current issue date as long as the exclusion has not expired. Can anyone help please. What needs to happen is that the employees need to see only the exclusions that have expired which is based on the Expiration Date.
AS SELECT [dbo].[30 Day exclusion].[First Name], [dbo].[30 Day exclusion].[Last Name], [dbo].[30 Day exclusion].[Issue Date], [dbo].[Extended Exclusions].[First Name], [dbo].[Extended Exclusions].[Last Name], [dbo].[Extended Exclusions].[Issue Date] FROM [dbo].[30 Day exclusion] INNER JOIN [dbo].[Extended Exclusions] ON [dbo].[Extended Exclusions].[ID] = [dbo].[30 Day exclusion].[id] WHERE (@StartIssueDate is null or [Issue Date] >= @StartIssueDate) AND (@EndIssueDate is null or [Issue Date] <= @EndIssueDate) AND (@Enter_LastName is null or [Last Name] = @Enter_LastName) ORDER BY [Last Name]
Hi,SQL Server 2005 Standard (X64) edition.Have a database which has its log backed up every hour during thenormal business day and each evening a full backup is performed.After two days we would like the the log backups to be automaticallyoverwritten in the backup device [WebAdmin], is this possible?This is the command being used.BACKUP LOG [WebAdmin] TO [WebAdmin]WITH RETAINDAYS = 2,NOFORMAT,NOINIT,NAME = N'WebAdmin',SKIP,NOREWIND,NOUNLOADCurrently the backup device has log backups going back to 18/02/2007which is position number 1 in the device. Basically nothing is beingoverwritten.Thanks in advance.Dave
I have a table containing several date fields in which certain events happened, and I need to be able to find the expiration date for renewal. The expiration dates are at the end of the 6th, 12th, or 24th month after the event occurred, depending on the event.
Is there a way to return in a query the last day of month x following a date field? For instance, if the date '3/12/2006' is stored in the field for a 12-month expiration, I need to return '3/31/2007'.
This may seem like a stupid question but i am trying to get the hang of the new security model.
I have not really heard anything mentioned about certficate expiration date when it comes to creating certificates for keys or service broker endpoints etc.
We have created certificates for keys and service broker endpoints, now what exactly happens when the expiration date, by default 1 year i think is reached, will we no longer be able to decrypt encrypted data and will the service broker endpoints stop working etc ?
Or is this expiration date when the certificate can no longer be used to create security objects ? And all security objects already created with this certificate will always work ?
In other words is there ever danger that keys and endpoints or basically any object referrencing this certificate will just suddenly stop working one day, or will all objects work indefinately regardless of an certificate/objects expiration date ?
First question: I have sql server 2005 enterprise eval edition installed. is there a query, command or tool can tell me the exact expiration date? I understand it is 180 day but I still want some way to query the exact date.
Second question: does sql server log any warning message before eval expires?
Is there any way I can check the remaining time before I must upgrade an SQL Server 2000 Evaluation Edition? I thought I had made a note about which day it was. Can I rely upon the file date for uninstallation file in Programs/etc ?
I would like to filter records with in effective date and expiration date; If there is no record within that range, then check for grace period records ( effective date -30 days and expiration date + 90 days)
Below is the detailed script for sample data...
declare @tab table ( sno int identity, name varchar(100), EFFECTIVE_DATE date, EXPIRATION_DATE date) insert into @tab (name, EFFECTIVE_DATE , EXPIRATION_DATE ) SELECT 'chandu', GETDATE(), NULL union all SELECT 'chandu', '2014-02-11 00:00:00' , '2014-03-20 00:00:00' union all SELECT 'AAA', '2014-01-11 00:00:00' , '2014-05-11 00:00:00' union all
We are maintaining a database of drivers, and we are looking for a script or procedure that will automatically notify certain users of the list of drivers who are in need of a recheck or driver's license has expired. I am new to this , so any help you can give would be greatly appreciated
We need to use SQL Server Authentication for some reason and would like to enforce Password Policy with 90-day expiration period. I found "Change password" dialogue appears when I first logged in with the new user, but don't know
(1)what happens when the user failed to change the password before it's expired or (2)how a user can change his password in advance of the expiration date with no particular server-level permission.
I am having a pretty major problem with my SQL server. Everytime that I try to query the database I receive a timeout expired error. Even trying to open the query anylyzer, it presents me with that error. We have a we based ISP management solution and it cannot access the SQL either. This was an all the sudden problem and I have ran the performance monitor to try and look at the statistics. Everything seems like it is checking out. I even tried increasing the Timeout up to 20 seconds and it still will not function properly. Any suggestions?
hi, here i am using .mdf file as database for my project. i want this .mdf file should be expired or become read only after 365 days exactly. how can i achieve this?
One can never consent to creep,when one feels an impulse to soar RAMMOHAN
Is there a document that lists the expiration dates of the CTP releases? We've a test box still on CTP15 and I was wondering when the CTP will stop working.
Description: In the past I had an issue with the replication that suddenly became expired (no reason at all) and I had to re-do the whole thing from scratch. This is a transactional replication (SQL2.5K -> SQL2.5K) and the subscriber is used as warehouse where applications also inserting data (with neg. seed values for the PKs). This means applying the snapshot from the publisher is not applicable because there are data which will be lost from subscriber.
Question: Since the applying snapshot is not an option for reinitializing the subscription (initially was created without snapshot - from the backup and restore is not applicable at this stage) I need to find implementation way for two things:
1. I need subscription never get marked as expired (I did disable job "Expired subscription clean up" but still the replication can be marked as expired from the "Distribution clean up" job which I am not suppose to disable - this has different function as well - cleaning up the delivered transactions)
2. Safely dropping articles from the publication without applying snapshot to the subscriber (see reason above - num.1). If you force to drop the articles, does this mean that the subscription will be marked as expired after period of time? (This again will lead to problems as above).
Please, give me your opinion and/or best practices how to solve these problems. Other than that I am very happy with the improvements in the replication domain in SQL 2005.
Setup: SQL Server 2005 -> Merge pull subscription via HTTPS -> public retention period is 7 days
Question: If for some reason I don't synzhronize in 7 days (lets say the Internet is down for whatever reason), can I reinitialize the subscription on day 9 and still upload changes to the publisher or not? I'm guessing the latter based on this: http://technet.microsoft.com/en-us/library/ms152558.aspx
Is there a way to find out many days you have left on the trial period or is there a way to find out when it was created. I get the feeling it will just not work one of these days and I'll have to figure out something on the fly.
This has to be a simple question for most of you, so here goes.My passwords for sa and another login that I created keep resetting.How can I turn off password expiration in SQL Server 2000?Thanks!MB
We are getting the following error message sporadically:
Network error code 0x2746 occurred while establishing a connection; the connection has been closed. This may have been caused by client or server login timeout expiration. Time spent during login: total 6108 ms, enqueued 1 ms, network writes 1 ms, network reads 6108 ms, establishing SSL 6107 ms, negotiating SSPI 0 ms, validating login 0 ms. [CLIENT: #.#.#.#]
We are seeing this message because we enabled some DBCC trace flags to try and figure out why our clients are having their connections terminated to the SQL 2005 (x64) server we have in production. It appears that the establishment of SSL is taking a very long time according to the log entry. What is using SSL? I'm assuming it is trying to communicate with one of my domain controllers and I'd like to know what perfmon counters i should watch to see what's going on. Is it making an LDAPS connection or kerberos? Or native ntlm?
We've already updated the NIC drivers/firmware and disabled the TcpChimney setting mentioned in the KB articles, but nothing so far has had an effect. I'm now trying to figure out if my DC is overloaded but don't know what counters to look at.
I have had SQL2005 on a server for a very short period of time. Today I found that I am not able to execute an SSIS package in debug. It give me an error stating that the evaluation period has expired for data transformation services. I went to check the version by querying, but nothing happens when I click on the Server Management Studio link.
Has anyone seen this before or how long the trial version should last?
Does anyone know what version may have been installed on my server?
I am building a winforms .net 3.5 application which connects to a SQL Server 2005 database with SQL server authentication. Is it possible to access the SQL Server password policy and expiration through the .NET 3.5 framework? I would like add the following functionality to my login form:
Ensure passwords meet policy standard.
Prompt a user to change their password when it is due to expire in 5 days or less. Thanks in advance.
Numerous articles (e.g., http://www.microsoft.com/sql/techinfo/administration/2000/security/securingsqlserver.asp, even one that I wrote, http://www.dbazine.com/sql/sql-articles/cook12) state that to secure SQL Server, the SQL Server services should not run as Local System. That advice is useful only if making the change is not overly disruptive or is even allowable. My two most recent clients have absolutely rigid password expiration policies that require all account passwords to be changed at regular intervals. Realistically, that makes a compelling case for running as Local System.
here is our backup command and i like to add date in the backup file name. So daily it add date to the backup file name. Can any one let me know how I can do that.
C:BAKacct_backup_%date .bak
BACKUP DATABASE [acct] TO DISK =
N'C:BAKacct_backup.bak'
WITH NOFORMAT, INIT, NAME = N'acct-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
Hello all. I was just wondering how I could add the current date and time to the name of the .bak file. I perform a full backup daily and would like to keep a weeks worth of backups, but everytime I run the job, it just overites the previous one.
The command in the wizzard is:
BACKUP DATABASE [snl] TO DISK = N'C:Backups_for_FTPSNL_Full.bak' WITH NOINIT , NOUNLOAD , NAME = N'snl backup', SKIP , STATS = 10, DESCRIPTION = N'SNL Backup', NOFORMAT
Hi All,I have to a few backups restored from a set of backup files. Thebackup files have been removed from the drive because of some storageconstrains...For some data comparison reasons, I need to figure out the actual dateof these backup files that is used to restore these databases. Isthere any system table I can query to figure the actual backup timesof these databases...Any help will be much appreciated.. Thanks in advance.- Aravin
I need to have a script that will restore the backup file in a folder but the backup file will have the date appended to it and I won't know what that date is.
I have a select query that results all the dbs and their several backup dates×. Howover I only need the latest backup dates for each of the db.How can I write that in tsql?
We have no certified SQL DBAs. Mainly because 95% of our production data is on Oracle and DB2 databases.
One of our Oracle DBAs is trying to write scripts that can be run by the AS400 robot to export data and backup the databases. I (being the only person with any SQL expierance) have been asked to ensure that the scripts she wrote will not only work but not bring down the server.
Can anyone point me to the correct place (web, book, ect) to find out how this is to be done? In years past my SQL servers have been stand alone dedicated units. With db backups scheduled through the SQL Manager and tape backups handled by the sysadmin.
Here's my statement below. What I'm trying to get is joining the name column in master.sys.databases with a sub query for the database name, file location and backup start date from the MSDB database. The reason for this, if a new database has never been backed up, It should be returning as a NULL value, which is my goal. However, I'm getting multiple results for the backups.
select CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,a.name,File_Location=b.physical_device_name,backup_start_date=max(backup_start_date) from master.sys.databases a left join(select c.database_name,backup_start_date=max(backup_start_date),b.physical_device_name from msdb.dbo.backupmediafamily b join msdb.dbo.backupset c on c.media_set_id=c.backup_set_id where c.type='D'
I want to delete all backup files from a folder older than a specific date. But if I use the beklow query, I need to pass how many days of older backup files I need to delete whereas in my case, I dont know how many days/month/syears of old backup files are there in the backup folder.
I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.
The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).
Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?
