Recovery :: How To Perform Server Database Backup Retention Periods
Sep 22, 2015
For the best practice I issued full SQL Server database, differential and transaction log backups. I have setup a process to backup to local disks and then also copy the files to a centralized set of storage. On a weekly basis the centralized file system is backed up to a tape backup device. I know I can get data off of the tapes, but that process is time consuming, not well tested from my perspective and I am not in control of the overall process. Can you offer some recommendations from a SQL Server backup retention perspective?
View 6 Replies
ADVERTISEMENT
Sep 8, 2006
Hi SQL Replication Gurus:
I got some issues in my production environment, so please help me out. The following is the message I got from the replication monitor and I don't what to at this point.
Appreciate you help.
Yong
==========================================================================================
Command attempted:
{call sp_mergemetadataretentioncleanup(?, ?, ?)}
Error messages:
The merge process could not perform retention-based meta data cleanup in database 'TT'. (Source: Merge Replication Provider, Error number: -2147199467)
Get help: http://help/-2147199467
Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (Source: ply-db-svr1, Error number: 1205)
Get help: http://help/1205
View 8 Replies
View Related
Feb 13, 2007
hi seniors ,
I am running sql server 2005 under details
Microsoft SQL Server 2005 - 9.00.3033.00 (X64)
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit)
on Windows NT 5.2 (Build 3790: Service Pack 1)
When i attempt to take differentail backup i receieved following message
"Cannot perform a differential backup for database "abc",
because a current database backup does not exist.
Perform a full database backup by reissuing BACKUP DATABASE,
omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035)
BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)."
when i search this msg on net than i find that it is microsoft bug 863 so i found hotfixes
about this problem and tried to install but unable to install (On installation screen select feature i am unable to click on check box)
Can any one help me regarding to this issue .And more is.. full back of db "abc" exists and i never changed its location nor rename file name.
Regards
View 14 Replies
View Related
Jul 25, 2015
receive an error: "Cannot perform a differential backup for database ..., because a current database backup does not exist." Can't fully understand what to do with this.
View 2 Replies
View Related
Nov 16, 2015
I have a database that is just over 1.5GB and the Full backup that is 13GB not sure how this is since we have compression on for full backups and my other full backups are much smaller than there respective databases...Now my full backup is taken every Sunday night and the differentials are taken every 6 hours after the full backup. Now I have been thrown into this DBA role with little to no experience just what I have picked up and read. So my understanding of backups are limited but what I think I understand is that we take a full backup and the differential only captures what changes in the database so my question is why is my database 1.5GB but my differential is 15.4GB? I have others database that are on the same instance and don't seem to have this problem. I also just noticed that we do not rebuild the index before a full backup like we do on other instances...
View 13 Replies
View Related
Nov 12, 2007
I have just started in the scary world of SQL Server admin and am trying to unravel the mysteries of backups etc.
If I run 'BACKUP DATABASE xxx TO DISK = 'D:DB_Backupsxxx.bak' WITH RETAINDAYS = 7' each day, each db backup if appended to the same '.bak' file and the RETAINDAYS protects the backup from being deleted by SQL Server. OK so far. But does anyone understand what criteria is used to decide when to overwrite the older backups? My backup file is getting bigger everyday, with no sign of any of the old data being deleted! Do I have to wait for the entire disk to become full before they start to get overwritten? Or should I just not worry and trust that it will do it all correctly?
Any ideas would be much appreciated.
View 5 Replies
View Related
Jul 13, 2006
In sql2005 the database backup retention has been added in sql server properties in database setting.
In 2000 we had a comfortable option to set retention based on maintenance plan,files and also our space availabilty.It has helped the dba's a lot.But it has been removed in sql 2005.
Is that sql server setting is the only retention period setting or do we have to set in anyother tabs..
Thanks
View 6 Replies
View Related
Dec 19, 2006
Hi,
If we delete a Database accidentally (no backup exists), Is it possible to recover. How?
Thanks
ven--
View 4 Replies
View Related
May 29, 2015
My Database has many Dead lock issues,Will this Dead locks cause Differential database failure ?.
View 6 Replies
View Related
Nov 8, 2015
There is a lot of documention concerning data(base) backups, but I could not find information about how to backup a complete SQL Server instance (data + configuration) or the SQL server configuration (= everything, that is not user payload). you always re-configure your system after restoring, especially because you will forget some settings?I found following things necessary to backup configuration:
server-wide:
security: login, roles, ...
server objectsserver properties (e.g. collation, connection or memory settings; see SSMS "Server - Properties")
per database
database settings (SSMS - database - properties)scheme
1. Is there anything missing?In case of a severe system crash I would like to be able to recover the system using a full system image of the virtual machine. However I just create these images after significant changes (e.g. Windows service pack).The SQL server should be backuped independently from these system images.
2. Even if I used SQL Server maintenance plan, choose "backup all databases" and additionally backup resources DB manually I think, that some configuration is still missing, right? Where are the security configuration and server properties saved? In my system database there are some tables, but there is very little content inside, so that the data from the views INFORMATION_SCHEMA* and sys.* obviously is not saved there.SQL Server system objects, such as sys.objects, are physically persisted in theResource database, but they logically appear in the sys schema of every database. URL....What does that mean: Are all DB/table schemes, logins, ... saved in resource DB?
3. Does a usual SQL Server full database backup also contain all settings concerning this database (database properties, logins, ...)?
4. Is there at least a way to backup ONLY the configuration (server-wide and database) without data? The only tool I could find is DACPAC, that exports a database's scheme and some other configuration, but e.g. the database properties are not included: By default, the database created during the deployment will have the default settings from the CREATE DATABASE statement. The exception is that the database collation and compatibility level are set to the values from the source database. URL..
View 9 Replies
View Related
Mar 26, 2008
Hello all,
First off, I appreciate the time that those of you reading and responding to this request are offering. My quesiton is a theoretical and hopefully simple one, and yet I have been unable to find an answer to it on other searches or sources.
Here's the situation. I am working with SQL Server 2005 on a Windows Server 2003 machine. I have a series of databases, all of which are in Full recovery mode, using a backup device for the full database backups and a separate device for the log backups. The full backups are run every four days during non-business hours. The log backups are run every half hour.
Last week, one of my coworkers found that some rarely-used data was unavailable, and wanted to restore a database to a point in time where the data was available. He told me that point in time was some time back in November.
To accomplish this, I restored the database (in a separate database, as to not overwrite my production database) using the Point in Time Recovery option. I selected November from the "To a point in time" window (I should note that this window is always grey, never white like most active windows, it seems), and the full database backup and the subsequent logs all became available in the "Select the backup sets to restore" window.
I then tried a bevy of different options from the "Options" screen. However, every restore succeeds (ie: it doesn't error out), but seems to be bringing the database back to a current point in time. It's never actually going back to the point in time I specify.
My questions are as follows:
a) Is it possible to do a point in time recovery to a point in time BEFORE the last full database backup?
b) If so, what options would you recommend I use? (ie: "Overwrite the existing database", restore with recovery, etc etc).
I again appreciate any and all advice I receive, and I look forward to hearing from anyone and everyone on this topic. Thank you.
Ryan
View 4 Replies
View Related
Sep 17, 2015
Pages on a full recovery model database corrupted, need to ensure data loss is minimal for restore operation am thinking about restoring the latest full backup.
View 4 Replies
View Related
Nov 1, 2015
We have an issue with a 3 node SQL 2012 Always on availability group. Normal operation is node 1 (primary replica) with node 2 and node 3 as secondary replicas.After some patching, SQL wasn't running on node 1 hence the AG flipped over to node 2. This went unnoticed for some time and the transaction log for one of the AG databases became full on node 2 and node 3. (I think this is because it couldn't commit the transactions on node 1 so couldn't truncate it's t-log?) The DB is using synchronous replication btw.So I started SQL on node 1 and flipped the AG back to node 1 (with a data loss warning but I accepted this).Now the issue is that on node 2 and 3, the DB in question is stuck in a "Reverting / In Recovery" State. I've tried various commands such as ALTER DATABASE SET ONLINE, RESTORE DATABASE WITH RECOVERY etc but these fail stating unable to obtain a lock on the DB.
The weird thing is that on node 1 the state of the DB is "synchronised".how to resolve this issue on node 2 and 3? I've left them overnight (in case they were rolling back transactions, the DB is fairly large) but nothing seems to have happened. remove the DB from the AG in node 2 and 3 and add it back in again, ie recreate the replication?
View 2 Replies
View Related
Dec 15, 2000
1. I want some help regarding sql server 7.0.I had taken database backup (complete).And tried to restore in another machine.
It restored successfully. But it does not create any logins/users. But it has some objects owned by user A
If i create login as A and assigning default database, it is giving error and saying the user/role is already existing. Before restoring
also i created login and after restoring I tried to assign the default database, but failed.
I chaged the objects owner and tried again, still it is saying the user/role is existing.
Can you suggest the solution.
2. I had backup of a database of SQL server 6.5. If i tried to restore on SQL Server 6.5/7.0, it is giving error, and saying it is not valid file. Any solution
With regards
K.N.Rao
View 1 Replies
View Related
Jul 18, 2003
Is it possible we can take the DTS package backup as part of SQL Server Database backup?.
Is it possible to store the script only in the server folder.?
Thanks,
Ravi
View 3 Replies
View Related
Apr 22, 2004
Hi Guys,
Well. I'm new to SQL server and appreciate your help on a problem....
Is the database mirroring possible in SQL server 2000. as in Oracle.
ie. I've the data file and a old log file (dated 3-04-2004), and ofource the backup file of the same date.
Now if my hard disk is creashed yesterday, will I be able to restore the database with the help of the data file which is updated till yesterday.
I can fo this in oracle. I create another database and then swap it's control files and data file that of the old one( the one which I need to restore). then i change the password file and boom i get everything upto date.
Can i do some thing like this in SQL Server?
thanking in anticipation
santosh kamble
View 1 Replies
View Related
Apr 2, 2008
How can we check whether the backup is in consistent state or not.
View 14 Replies
View Related
May 17, 2008
Hi,
I am new to sql server2005.
How to take a backup,restore&recovery operation in
sql server 2005.
Question No 1:
If i drop my "customer" table how can i get it back?
In oracle we will get back the table with the help of
incomplete recovery.
Question No 2:
How to connect as a user?
How to give the grant privilege to the particular user?
View 2 Replies
View Related
Jul 23, 2005
Could someone please give me an idea of the differences between SQLServer and Oracle, backup and recovery options.Thanks
View 1 Replies
View Related
Oct 18, 2007
i am upgrading 2000 to 2005 databases
i upgrade using backup and recovery method to do this.
If 2000 database contains many databases i have to upgrade each database at one time using the bakup file
Is there a way to upgrade all my 2000 databases in one shot using backup and recovery.
please let me know
thanks
View 15 Replies
View Related
Nov 14, 2007
Hi all
Are there any steps, we have to follow while restoring Database.
Ex :I have Full backup (week end backup),every day I have Diff backup and every hour I have Trnbackup
If system has crahed how to restore Databse(I don't have any chanse to do any thing with system)
View 14 Replies
View Related
Oct 29, 2006
Can not attach database,
Error message;
The file you've specified is not a valid SQL Server database file.
My costumer HDD some files Deleted mybe elektric prblem -Elektric
go,com,go,come- then windows not opened.
insert corrupt hdd Another computer, then recovering (recover my files
software)
Recover mdf and ldf files. but I can not not attach mdf. file,
I am recovery .mdf file http://www.officerecovery.com/mssql/ some table ok.
but my importand table is not ok.
if I can recover another recovery software , my database correct recover or
same.?
I am using RecoverMyFiles.
Pleace help me.
Thanks.
View 6 Replies
View Related
Jan 21, 2014
I'm trying to create a cte to return a list of lots and every period between the first and last transaction date for each lot. I've gotten this far:
SELECTIntLotKey
,DATEPART(YYYY, StartDate)StartYear
,DATEPART(MM, StartDate)StartPeriod
,DATEPART(YYYY, EndDate)EndYear
,DATEPART(MM, EndDate)EndPeriod
[Code] ....
This gives me the following results:
IntLotKeyStartYearStartPeriodEndYearEndPeriod
271532013120135
28468201312201312
2846920131201312
2847020131201312
28472201312201312
593022013120131
593032013120131
Now what I need is something that looks like this:
LotKeyYearPeriod
2715320131
2715320132
2715320133
2715320134
2715320135
[Code] .....
Some lots may not have any transactions for some of the periods between the start and end dates but I need to report every period between the start and end period for each lot. I have a period table that I thought I could use but haven't come up with a way to get the results I'm after.
View 9 Replies
View Related
Aug 17, 2015
We have a requirement to build SQL environment which will give us local high availability and disaster recovery to second site. We have two sites- Site A & Site B. We are planning to have two nodes at Site A and 2 nodes at Site B. All four nodes will be part of same Windows failover cluster. We will build two SQL Cluster, InstanceA will be clustered between the nodes at Site A Server and InstanceB will be clustered between the nodes at Site B, we will enable Always On Between the InstanceA and InstanceB and will be primary owner where data will be written on InstanceA and will be replicated to InstaceB. URL....Now we want we will have instanceC on the Site B and data will be writen from the application available on Site B, will be replicated to the instance on the Site A as replica.
View 6 Replies
View Related
Apr 19, 2004
i've got a project that using MSDE and JAVA
but how can i backup and recovery the database by using Java code??
or any alternative method?
View 1 Replies
View Related
Apr 29, 2004
Hi,
I know SQLLiteSpeed is one best tool for MSSQL server Backup Recovery.
And also I wanted know that the top 5 best tools for MSSQL server Backup/Recovery.
View 9 Replies
View Related
Apr 1, 2008
every sunday 12 am fullback up.
for every 6 hours differential backup.
every 15 min t log back up.
If my server crashes at wednesday 3.30 pm
my restore order is fullbackup on sunday, last differential backup (wednesday 12pm) and the t logs from last full backup or t-log backups from latest differebntail backup
I have doubt after restoring the latest differentail backup we have to restore all t log backups from fullbackuop or all tlog backup from latest diff backup.
Which order we have to follow
View 7 Replies
View Related
Jul 23, 2005
Hi gurus,May be my question is funny to all of you guys but I don't know why wehave to keep the backup and recovery history of databases in msdb. Itry to read BOL & other documents but no clue.Thanks a bunch
View 3 Replies
View Related
Aug 25, 2015
I have small db and load is very less. I am set full backup at 9:00 pm once a day,and set transaction log backup to every 15 min.then i am taking the transaction log backup in 9:15,9:30,9:45....... My question is I lost my data between 9:15 to 9:30 . in those time i will do some transactions.Then how to recover my data even with out lasing single transactions.
View 4 Replies
View Related
Jun 12, 2015
I have a DB shown as recovery pending when running the following:
SELECT Name, state_desc
FROM sys.databases;
The DB was created by someone outside of our team using the Full Recovery model & I can see that no transaction log backups have been taken for this, causing the log to growth to a large size.
The MDF is only 5,120kb but the TRN has grown to 10,773,120kb
When I checked the Server I could see the data area had run out of space so I have freed up some space for this so now have 2.5gb available as a short term solution.
The MDF & LDF files are still visible & when checking the SQL log the DB is being reported as having a Full Transaction Log.
Essentially I want to change the Recovery Model from Full to Simple, Reduce the size of the transaction log & bring the DB back online. Luckily this DB is only used by a handful of users but I still need to get it up & running asap.
View 9 Replies
View Related
Dec 23, 2014
is bulk logged recovery model support point in time recovery
View 9 Replies
View Related
Aug 10, 2000
Hi Everybody:
We plan to do point-in-time recovery for certain databases. We plan to do Complete Database Backup every night and transaction log backup every two hours from 8:00 AM to 5:00 PM. I have following questions regarding the log backup.
1. There are two type of backup 'Append to media' or 'Overwrite'. If I choose 'Append' for log backup, is that mean I only need to restore database against last log backup file because all previous log backups have been accumulated there?
2. Can I automatically truncate log after the backup is done? How I can do it?
Thank you very much.
Joan
View 1 Replies
View Related
Jul 28, 2004
How much time does a 20 g tape backup take to recovery
I am on 6.5 sql and have compacq 15/30 gb DLT
How best to configure a standby sqk 2000 server
salim
__________________
View 8 Replies
View Related
