Backup/recovery Larger Databases...
Apr 10, 2008
Greetings all and thanks for reading this post.
Here is my situation... I have 2 fairly large databases. Full backups are 83gb & 63gb. I am in the process of moving these database to a new data center. I've taken full backups of these databases and shipped them to the new center. I have been taking transaction log backups (larger db every 24 hrs smaller db every 15 min ... from log shipping).
I want to restore these databases in the new data center. I've gone ahead and restored the dbs in the new location.
Question final cutover.. can I just apply the transaction logs to the databases on final cut-over or do I have to restore the database backup first then apply the transaction logs?
Is there an other way to do this that I'm missing?
Thanks.
Kurt
View 3 Replies
ADVERTISEMENT
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
Sep 19, 2015
We have 3 replica AG setup. 2 replicas are in sync/automatic failover, the other(DR Server, different subnet) in asynchronous/manual mode…All these replicas were on sql server 2012, Recently we upgraded DR server to 2014. Since then we have a problem, the AG databases in 2014 instance went into ‘Synchronizing/ in recovery’ state…The SQL server error log has message, the recovery couldn’t start for the database ‘XYZ’…We tried to create a new database and add it to AG , it works for fine for other two 2012 replicas, but on 2014 we see the same issue
View 3 Replies
View Related
Oct 9, 2007
Hello!
I make a full backup of many databases (about 45).
How can I recovery all databases at once? With a script?
I use SQL Server 2005.
Thanks
ezinsi
View 4 Replies
View Related
Mar 20, 2007
I need to display the recovery mode of ALL databases on a server into a table. (Just like the sp_helpdb prcedure extended with the recoverymodel of each Database).
Can anyone give me a hint on this or provide me with a little tsql code snipplet to realize this?
Thanks alot in advance
View 4 Replies
View Related
Sep 16, 2015
I have log shipping enabled on databases(primary and secondary) and works fine. I need to implement TDE on the database. I have experience on implementing TDE on databases which are not used for log-shipping.steps needed to setup TDE which are involved with log-shipping.
View 2 Replies
View Related
Mar 6, 2006
Hi
What are the pros and cons of putting the system databases in full recovery mode
I am asked by my manager to keep everything simple and uniform.We have all the user databases in Full recovery mode and he wants all the system databases as well to be full recovery mode.
I would like to get the points where in i can argue that, there is no point in having the system databases in full recovery mode.Unfortunately i dont see anything related to this in BOL or internet.
Plz help
Thanks
Sreenath
View 3 Replies
View Related
Jul 23, 2005
Hi All,I have a 2MB database set to SIMPLE recovery. This database is usedonly to generate new keys to web users. It has two columns - UID andLASTDATE. The UID column is only updated when users log in or log out(INSERT & DELETE). The LASTDATE column is updated every time theyswitch between pages.Please don't ask for the reason why we do it this way, it just IS.The maximum amount of rows in this table at any given time is about2000. There is a non-clustered index on the UID column.My problem is that all throughout the day I have constant blocks by theWRITELOG waittype. I know my disk I/O is very slow (will be upgradedsoon) but this is the only database on which these blocks occur. Whatis writing to the transaction log if I have the DB set for SIMPLErecovery?Thanks,Josh
View 4 Replies
View Related
Feb 8, 2008
When I create a database, is there a way to make sure that the default recovery model is Full instead of Simple?
View 1 Replies
View Related
Sep 15, 2006
Hello,
I would like to restore SQL Server Express and its databases from a tape backup to the same server. This is a disaster recovery senario.
I backed up the Master, Model, MSDB and my own test database using SQLCMD scripts. I have no problem restoring these using task manager on the server before the disaster recovery.
However, in my real disaster recovery testing, When the server is restored by tape drive (HP one button disaster recovery), I try to run my SQLCMD restore scripts in task manager and I cannot connect to the sql server. Also I cannot connect with Management studio. I have recieved the following error in event viewer.
----------------------------------------------------------------
Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: (2)
Event ID: 3411
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
Configuration block version 0 is not a valid version number. SQL Server is exiting. Restore the master database or reinstall.
Data:
0000: 53 0d 00 00 15 00 00 00 S.......
0008: 16 00 00 00 43 00 4f 00 ....C.O.
0010: 50 00 4c 00 45 00 59 00 P.L.E.Y.
0018: 4e 00 45 00 57 00 53 00 N.E.W.S.
0020: 5c 00 53 00 51 00 4c 00 .S.Q.L.
0028: 45 00 58 00 50 00 52 00 E.X.P.R.
0030: 45 00 53 00 53 00 00 00 E.S.S...
0038: 00 00 00 00 ....
Event Type: Warning
Event Source: SQLBrowser
Event Category: None
Event ID: 3
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The configuration of the AdminConnectionTCP protocol in the SQL instance SQLEXPRESS is not valid.
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The SQL Server (SQLEXPRESS) service terminated with service-specific error 3411.
C:Program FilesMicrosoft SQL Server90ToolsBinn>sqlcmd -S.SQLExpr
COPLEYNEWSDATABASEscriptsMASTERFULLRESTORE.sql"
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred whi
shing a connection to the server. When connecting to SQL Server 2005,
re may be caused by the fact that under the default settings SQL Serve
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
----------------------------------------------------------------------
My question is, what is the correct procedure to follow when I want to do a disaster recovery and restore SQL Server Express from tape backup using the Simple Backup method and scripts.
Is it always required to reinstall sql server express from the original program file or is it possible to reinstall from back up tape.
I know my backup and restore scripts work because I tested them on the server before I do the disaster recovery and rebuild that server from tape.
This is some kind of issue with SQL Server Express being restored by tape backup.
Any suggestions, thanks.
View 8 Replies
View Related
May 1, 2015
SQL Server 2012: Out of all the databases in the instance we have a requirement in which we need to maintain a high availability for the databases around (128). Our team believed log shipping will apt for this requirement but not sure on it's limitations, how many databases are allowed or supports this log shipping from a single instance.
View 7 Replies
View Related
May 13, 2015
I believe to configure maximum number of replicas it is required to have 5 nodes (1 Primary & 4 Secondary replicas). But how many databases can be included in one availability group, it think it is 32767. When I am referring to below URL it is mentioned something like "An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. " What does this 'sets' means, is that maximum limit for only eight databases or maximum groups should be eight?
[URL] ....
View 3 Replies
View Related
Aug 27, 2015
We have several read-only nodes in our AlwaysOn cluster, which are set to use Synchronous-commit mode, which ensures that the logs are updated on the read-only nodes before any update statements complete. Even with this option, if we query a read-only node before the logs have been processed, we can read old data. I would like to know a strategy to ensure that a read-only query will definitely return up to date information. I had an idea that if I just used a different transaction type, like Serializable, that it might block the read-only query from actually getting the data until after the log file was processed, but I have not tried it, yet.
I would like to move more queries to the read-only nodes, in an effort to offload CPU utilization from the primary node.
View 2 Replies
View Related
Nov 20, 2007
Hi all
What is best Disaster/Recovery methode to securing the system databases.
Master database allows only Full backup
MSDB allows Full ,Diff
View 5 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
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
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
Jun 22, 2015
How to find last login date/time for user DML acitivity on databases on Instance?
Is there any way we can find our the last login date/time for databases?
Note: 1. We can find if the SQL Trace is running and store.This is not good solution
2. Audit logins off/on is also not good solution.
3. Using DMV's also not good option, if reboot sql server instance then historical values can not see.
View 5 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
Oct 11, 2011
I have an old sharepoint db (SQL Server 2000 Standard edition). What I need to try to recover is the data in log backup files from the docs table. I need to see if there is a way to get any data in the log backups from columns dirname, leafname and content. The column content is an image type which old documents (word, excel...). I don't have a full log chain since the last full restore so any data I can get out of the logs is better than nothing. I really just need to get the image files stored in the column content and place them in a shared directory for the users and let them see what they can use.
View 7 Replies
View Related
Aug 22, 2007
Hi,
We have remote offices running SQL 2000, which are on a small bandwidth pipeline, so we implemented an incremental backup schema where they back up their LOGs overnight and send it to our main office. After an initial full backup, they just keep sending **incremental** LOG backups.
In the main office we restore the full backup of each remote office and from then on do a restore of each night's LOG file (with standby option), so that we keep in sync with them. The issue we have now is that we cannot get regular full backups from the remote (and sometimes independent) offices on a regular basis - maybe only once a *quarter*.
If we use differential backups, the size of each one grows each day, until we get a full backup. Incremental backups thru LOGs seemed to be the only way to go. *HOWEVER*, we now have multiple LOG files sitting on our server in the main office and since the databases that we restored to is in warm-standby and read-only mode, we cannot make a backup of them in-house, without breaking the LOG restore chain.
That also means that we would not be able to easily re-create the environment locally, should something go wrong - we would have to apply all of the LOG restores for (potentially) the whole quarter!
I even tried to create two restored databases - one where I keep it in standby state and ready to receive more incremental LOG backups and another where I would take it out of standby mode (and into recovery mode). At that point I am then able to make a full backup locally of this second database. *BUT*, when I then try to restore the next day's LOG backup to this second database, it fails with an error
- Msg 4330 - Level 16, State 4, Line 1
- The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.
How do I do incremental backups and restores, while maintaining the ability to make a new full backup of the restored database, before the restore sequence is done?
Help !
View 1 Replies
View Related
