Log Shipping For Training Database
May 12, 2008
We have a user Training database that we would like to keep updated from our Production database. Users would make different modifications to the Training and Production databases. It is the user updates on the secondary that bother me and I have not seen anything that indicates Log Shipping would fail if new random records are added to both the secondary and primary.
Would Log Shipping be appropriate for this nightly update of Training from Production?
Thanks
View 2 Replies
ADVERTISEMENT
Oct 4, 2006
Hi there,
I got this error when i tried to attach the adventureworks database and SSIS training database.
an exception occurred while executing a transact-sql statement or batch
(microsoft.sqlserver.connectioninfo)
could not find row in sysindexes for database ID 17, object ID 1, index ID 1. Run DBCC
checktable on sysindexes.
I join join a network domain for my computer. Previously it doesnt have this problem.
View 1 Replies
View Related
Oct 21, 2015
Our DBA is out for about 6 weeks. One of his regular jobs is to take a Database that is in our Prod environment and copy it to our Training environment.
I have backed up and restored to different servers before, however the Database name in Prod is different than in the Train environment in this case.
In other words I have a PROD database named DATABASE-XX that I want to copy to another SQL server and restore it to DATABASE-XX-TRAIN.
Is there anything special I need to do, other than backup on PROD, copy to TRAIN and restore to the corresponding DB in TRAIN?
View 11 Replies
View Related
Jun 11, 2014
I would like to do partitioning one of my client, but that primary database is participating in log shipping.
If I do partitioning on primary, automatically apply those changes on secondary db? if not, what is the way?
Note: I am adding new filegroups and ndf file in different drives on primary database.
View 2 Replies
View Related
Oct 2, 2006
Hi Folks
anyone could provide steps, links, on how to bring secondary online, with more than one database being log shipped ?
thx
View 1 Replies
View Related
Jun 30, 2007
In order to move from one data center to another, I am planning to use log shipping.
Lets say,
Current Data center - DC1
Current Server- S1
Current Database- DB1
New Data Center -DC2
New Server -S2
Now, on the New Server S2, I already restored DB1 as DB1 for QA testing. QA has to do some testing before we move from DC1 to DC2. In the mean time, I need to start log shipping from S1 to S2, so that we can fail-over to S2. Now since DB1 database is already present on S2, I can not use the same name. I will have to use some other name (say DB1_New).
I can not wait for QA to finish their testing. I have to start log shipping before that. When QA is done, I will delete the DB1.
So for this log shipping, -
Primary - S1- DB1
Secondary - S2 DB1_New
So when we fail-over, we will have the database name as DB1_New. But all the apps use the name DB1. So I will have to rename the database as DB1.
So want to know, will this renaming the database (DB1_New to DB1) cause any issues?
Anyone had similar experience?
Thanks
View 3 Replies
View Related
Oct 25, 2006
Hi
I'm looking at replicating our primary SQL server to a secondary offsite server (linked via 100Mb so effectively LAN speed). What are people's preference when deciding on a solution?
On the surface mirroring looks much better but having dug a little I've found it is recommended only 10 databases are mirrored per instance. That said, I've found a post from someone who is upto 58 databases mirrored. Are there similar limitations with log shipping?
Does anyone have any experience of mirroring and is using it in prefence to log shipping?
Our current recovery strategy is the classic restore the SQL dump from tape onto a rebuilt server so either method will be a vast improvement. None of our databases are mission critical that they need upto the second replication. 15 minute replication would be fine leading me to think that log shipping may be better given the possible limitations of mirroring
For recovery, I was considering amending the DNS records of the Database servers. Does anyone see any issues with this approach? I understand there is a automatic failure function if using mirror but this may require the application to be coded correctly?
Thanks in advance for any feedback
Robert
View 4 Replies
View Related
Oct 3, 2006
Hello All Database Mirroring Experts,
I'm interested in how Combining Log Shipping and Database Mirroring works when failover occurs.
From SQL BOL, it says:
"Topic: Database Mirroring and Log Shipping
...
To run in high-safety mode with automatic failover the mirroring
session is configured with an additional server instance known as the witness.
If the principal database is lost for any reason after the database is
synchronized and if the mirror server and witness can still communicate
with each other, automatic failover occurs. An automatic failover
causes mirror server to assume the principal role and bring its
database online as the principal database. For more information, see Automatic Failover
[ http://msdn2.microsoft.com/en-us/library/ms189590.aspx ] . If the log
shipping backup location is accessible to the new principal/primary
server, its backup jobs begin to ship log backups to that location. The
database mirroring synchronous mode guarantees that the log chain is
unaffected by a mirroring failover and that only valid log is restored.
The secondary servers continue to copy log backups without knowing that
a different server instance has become the primary server.
..."
Source: http://msdn2.microsoft.com/en-us/library/ms187016(d=printer).aspx
Could anyone tell me that how the database mirroring synchronous mode guarantees that the log chain is
unaffected by a mirroring failover and that only valid log is restored?
Let me elaborate the situation (if anything I said is incorrect, please correct me )
Here is the time line of the failover happens:
------- tn-1 ---------- tn ---------- tf -------- tn+1 ---------------> t
----------------> t: the time line.
tn: the moment that the log shipping backup job and copy job is done for the transaction log obtained between the time interval tn-1 and tn.
tf: the moment that mirroring failover occurs in the database mirroring session.
the time interval between each tn and tn-1 are constant, say h seconds, for all n are positive integers.
Here is the question that I want to ask:
In database mirroring synchronous mode, it guarantees that all the committed transaction from the moment tn to tf is copied to the mirror database. All the transaction log backup for log shipping are done on the original principal before the moment tf. After the mirroring failover occurs at the moment tf, how the log shipping mechanism guarantees that the transaction log between the interval tn and tn+1 that can be unaffected by a mirroring failover?
That's the point that I interested in.
Thanks a lot,
Terence
View 3 Replies
View Related
May 13, 2007
Hi
I could not able to find Forums in regards to 'Log Shipping' thats why posting this question in here. Appriciate if someone can provide me answers depends on their experience.
Can we switch database recovery model when log shipping is turned on ?
We want to switch from Full Recovery to Bulk Logged Recovery to make sure Bulk Insert operations during the after hours load process will have some performance gain.
Is there any possibility of loosing data ?
Thanks
View 1 Replies
View Related
Jun 26, 2007
Good day to the people who's reading this post!
I have some trouble with shipping my transaction logs to the secondary database be it on
another server or within the same server to another database instance.
Im using SQL Server 2005 workGroup editions with Service Pack 2.
Here are the problems that i encountered.
I do hope someone has bumped into such a problem and willing to help me out.
I tried on 2 separate servers (not domain environment) and also between 2 separate instances
(which is supposed to be simple!) on
our development server,but was unsuccessful.
Between 2 separate instances on the development server -
No error after configuring Log shipping though.
The configuring went through and it was a success.
Transaction logs was backup every minute and it got copied over to the other folder.
But SQL Agent not doing its last job which is supposed to restore to the secondary database on
another instance.No errors given out in SQL Agent error log files.Anywhere else im supposed to
look to see if there are errors given out?
Both instances SQL Agent has the same log on username and password with Administartive rights
So what went wrong?
Between the 2 servers-
The transaction logs was backup every 1min on the primary server
but it didn't got copied over to to the secondary database.
- Error message given was:Error in restoring database to the secondary
database.Network path given could not be found. Can't open the AxTest.bak file.
(i am very sure i have type the correct network path,even have shared it out and i think the firewall
is blocking incoming traffic since unlike our development server,
which allows us to access when we use Start>Run and type in the ip address,user name
and password,the primary server will only tell me no network path was found)
I also believe it's because the SQL Agent on the secondary database server wasn't given
permission to access the primary database folder.
I've shared out the drive and folder on the secondary server and
even have allowed SQL Agent to read,write and modify on both servers.
For the primary and secondary SQL Agent,
I configure their log on to be the same user account name and password
which have administrative rights.
So what went wrong?
Isit really true that both servers have to be in domain environment before you can configure log
shipping,mirroring and replication?
Hope someone help me out of this predicament.Thank you in advance!
View 6 Replies
View Related
Sep 5, 2015
I want to configure the logshipping to secondary database.
Already the primary database transaction log backup running every 15 minutes through (maintanance plan)
I want to skip the transaction log backup job in logshipping configuartion(disable the job)
want to use the existing transaction log backup and copy and restore in secondary using logshipping configuration
is it possible in sql 2008 r2 standard edition.
View 1 Replies
View Related
Oct 24, 2007
Dear,
Are possible build a solution with SQL Server 2005 Cluster and Database Mirroring? I have the following scenario:
1) Site A
SQL Server 2005 with 2 nodes and active/passive
2) Site B
SQL Server 2005 for Disater Recovery with Log Shipping.
Are possivel change Log Shipping by the Database Mirroring? If is possible, are better:
a) Synchronous(with or without witness)
b) Asynchronous
Thanks a lot! Sandro.
sgpcosta@hotmail.com
View 2 Replies
View Related
Nov 13, 2015
Is It Possible to Configure Log Shipping On a CDC Enabled Database?
View 4 Replies
View Related
Jun 14, 2006
Hi,
If I perform a point in time restore on a database that is currently a primary log shipping database, will the rollback be reflected on the secondary servers or are extra steps neccesary to accomplish this?
Thanks.
View 1 Replies
View Related
May 29, 2007
I have a 20 GB SS2005 database that I would like to be replicated to a dev server for testing purposes. I might have some issues with the firewall blocking ports. I haven't read up on the specifics of how log shipping works yet, but I assume that is the best approach. A daily transfer would suffice.
A few options I am considering:
1. use the built in stuff, but I'm not sure it will work with our security settings, and if the trans logs are large it might be strained.
2. writing a small custom app to zip up the log file then ftp them down to the dev server. This could run nightly.
3. some third party util, such as FolderShare could transfer the log files
Any comments or suggestions?
View 4 Replies
View Related
Jul 31, 2006
Hi,
I am testing with Log shipping, I have it setup and it is working just fine. The secondary database was restored with Standby mode and is readonly. I have no problem accessing and querying data from the secondary database but I noticed if changes are made to the primary and I am accessing the database when the restore step to the secondary runs it fails with the following error:
Message
2006-07-31 09:40:54.33 *** Error: Could not apply log backup file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupLogShipCI_REPLICATION_TEST_20060731131501.trn' to secondary database 'CI_REPLICATION_TEST'.(Microsoft.SqlServer.Management.LogShipping) ***
2006-07-31 09:40:54.33 *** Error: Exclusive access could not be obtained because the database is in use.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
This appears to be because you can not restore to a database that is in use? How do you get around this? Is it possible? How do I query data from the secondary db without worrying about causing log shipping to fail? Do I not worry about it because at somepoint when Exclusive access is obtainable all the logs will be rolled forward, but this could create a wide gap in sychronization between the primary and secondary db's, say if someone or something does not properly log off. I guess I could disconnect all users prior to applying tlogs but that kind of defeats the purpse of having it in read only mode so that data can be accessed? Couldn't really depend on it as a stable source of data for running data extracts? I suppose it could all just come down to timing everything in the appropriate sequence? Anyway.....
To sum up:
Is there anyway to access the standby server without causing the restore process to fail?
Both the Primary and Secondary are SQL 2K5.
Thanks!
View 9 Replies
View Related
Aug 8, 2006
Up to now we have gotten by without having any local DR copies of servers (if a sql server goes down we are usually able to get it back in less than 3 hours). But I want more now. I want to trim the "down" window to no more than 5 or 10 minutes. (Immedate failover would be nice but is not an essential requirement. The essential requirement is to loose no data!)
I have a spec of knowledge in these areas:
SQL 2005 Clustering (requires approved hardware, quorum disk, etc. involved)
SQL 2005 Replicaiton
SQL 2005 Log Shipping.
SQL 2005 Database mirroring. ( needs three servers)
Which approach do you think is the most straightforward, sparing of hardware, yet reliable way to get us back up and running after a sql server failure.
TIA,
barkingdog
View 8 Replies
View Related
Mar 20, 2015
We have log shipping for databases from production to back office (BO) environments for users to run reports. In preparation for moving a back office environment to a different server, we want to setup an addition log shipping environment, let’s call it BO2, same as the BO. One of the challenges we had was the amount of time it would take to get the full backup from production to BO2 due to the size of the database. It would take days just to transfer the full backup.
Solution:Let’s just say the database being logged shipped is DB123. Instead of using the full backup of DB123 from production, we use the mdf and ldf of DB123 from current log shipped database on the BO to setup the additional log shipping on BO2.
Steps:1.Setup the directory structure for the undo file (tuf) on the BO2 server same as the BO server.
Note: The undo can be moved to different drive after the log shipping is setup.
2.Stop SQL services on the BO server.
3.Copy the undo file (tuf), mdf, and ldf files of the log shipped database, DB123, from server BO to server BO2.
Note: Keep these files as main copies because they will be used multiple times. Do NOT copy files to where you’ll put the new database files.
4.After the files are copied, start SQL services on BO.
5.On the BO2 server, create an empty database DB123 with database name, file_id, logical names, physical names exactly the same as the ones on the BO server.
Note: Make the size of mdf and ldf small so it doesn’t take long to create the database.
6.Stop SQL Services on the BO2 server.
7.Overwrite the new mdf and ldf files of the empty database DB123 with the mdf and ldf files that were copied over from BO.
8.Start SQL services on BO2.
Note: If the file_id, logical names and physical names are not matching up, the database will be in suspect mode.
9.Do a backup of DB123 with COPY_ONLY.
10.Drop DB123 on BO2 after backup is complete.
11.Use the full backup from previous step to setup log shipping of DB123 on BO2.
Note: Below was the LiteSpeed syntax that was used
exec master.dbo.xp_restore_database @database = N'DB123' ,
@filename = N'F:SQL BackupsDB123_FULL.slsfull',
@filenumber = 1,@with = N'STATS = 10',
@with = N'STANDBY = N''C: MSSQLDataROLLBACK_UNDO_DB123.trn''',
@affinity = 0,@logging = 0
12.Stop SQL services on BO2.
13.Again, overwrite the mdf, ldf and undo file (tuf) of DB123 on server BO2 with mdf, ldf, and undo file (tuf) from BO.
14.Start SQL services on BO2.
15.Replay transaction log from PRD
Note: You can change the location of the undo file by copy the current undo file to the new location and point to new location in “STANDBY” parameter. DO NOT do cut and paste of undo file but do copy and paste.
It works on Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64) . I have not tried it on SQL Server 2012 yet.
In steps that stop/start SQL services, you can bring the database offline/online.
In a way, this is doing detach/attach a log shipped database.
View 0 Replies
View Related
Nov 8, 2006
I have an error 14421 (database has restore threshold for 45 minutes and has not restored in more than "so many" minutes)firing for a database that no longer exists. How can I remove this alert? I have other log shipping plans that are running and so I can't delete the Log shipping alert job.
View 4 Replies
View Related
Apr 19, 2006
Dear All
Please I need an urgent help
After i finished all Transaction Log Shipping Configuration.
I tried to use the database in the secondary database but i couldn't access it
i saw it in SQL Managment Studio as (Restoring......)
i tired to make a database snapshot from it , i had a message
Msg 1822, Level 16, State 1, Line 1
The database must be online to have a database snapshot.
Please urgently
View 1 Replies
View Related
Jun 8, 2006
Hi,
I 'm sure I am missing something obvious, hopefully someone could point it out. After a failover log shipping, I want to fail back to my inital Primary server database; however, my database is marked as loading. How can I mark it as normal?
I did the failover as follow:
I did a failover log shipping from the 2 server Sv1 (Primary) and Sv2 (Secondary) by doing the following
1) Stop the primary database by using sp_change_primary_role (Sv1)
2) Change the 2nd server to primary server by running sp_change_secondary_role (Sv2)
3) Change the monitor role by running sp-change_monitor_role (Sv2)
4) Resolve the log ins - (Sv2)
5) Now I want to fail back - I copy the TRN files to Sv1 - use SQL Ent to restore the database at point in time. The task is done; however, the database is still mark as loading. I could not use sp_dboption.
I appreciate any suggestion.
Thanks in advance
View 5 Replies
View Related
Mar 17, 2014
I've configured log shipping to use for DR purposes. I'm concerned that the physical location of the secondary is mis-reported by SQL Server Management Studio.
Viewing the secondary location (with Studio DB_name Properties Files) shows the path of the primary DB (I expected it to show the path of the secondary).
This SQL command shows the correct/actual paths of both primary and secondary DB's when run on their host servers.
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files
Is this just cosmetic?
Here is an Example of how the Studio shows the incorrect path for the secondary.
Example:
(Primary) servername=prodSrv, DBname=aquaDB, Actual_Path=G:aquaDB, SQL-studio-Properties-Path=G:AquaDB, sys.master_files Path=G:AquaDB,
Log shipped to
(Secondary / Read Only) servername=DRSrv, DBname=aquaDB, Actual Path=F:aquaDR, SQL-studio-Properties-Path=G:AquaDB(WRONG), sys.master_files Path=F:aquaDR
View 2 Replies
View Related
Jun 18, 2015
I received an alert from one of my two secondary servers (all servers are running 2012 SP1):
File 'E:SQLMS SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAMyDatabaseName_DateTime.tuf' is not a valid undo file for database 'MyDatabaseName (database ID 8). Verify the file path, and specify the correct file.
The detail in the job step shows this additional information:
*** Error: Could not apply log backup file 'MyDatabaseName_DateTime.trn' to secondary database 'MyDatabaseName'.(Microsoft.SqlServer.Management.LogShipping) ***
*** Error: Table error: Page (0:0). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Table error: Page (0:0). Test ((m_type >= DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 0.
Table error: Page (0:0). Test (m_freeData >= PageHeaderOverhead () && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192.
Starting a few minutes later, the Agent Job named LSRestore_MyServerName_MyDatabaseName fails every time it runs. The generated log backup, copy, and restore jobs run every 15 minutes.
I fixed the immediate problem by running a copy-only full backup on the primary, deleting the database on the secondary and restoring the new backup on the secondary with NORECOVERY. The restore job now succeeds and all seems fine. The secondaries only exists for DR purposes - no one runs reports against them or uses them at all. I had a similar problem last weekend on a different database that is also replicated between the same servers. I've been here for over a year, and these are the first instances of this problem that I've seen. However, I've now seen it twice in a week on the same server.
View 0 Replies
View Related
May 12, 2006
Hi all,
my collegue is leaving, his masters in computer science has ensured he has solved most problems we've encountered iver two years...
now im told i need at least a few training sessions under my belt to take over parts of his job.
Basically i need sql server traing and vb but am confused as where to start, a lot of the courses ive looked at assume you have knowledge in certain areas, my problem is i have loads of gaps as ive learnt on the job so need some recommendations- are any of the MS courses geared to people like me or do you need a foundation in the subject....ive read stuff like SAMS teachyourself SQL and found it just doesn't go deep enough....too many examples are just skimmed over....it also doesn't teach you the kind of things i learn here....
im happy to go on a course which repeats stuff i already know whilst teaching me the fundamentals but can only seem to find course geared to specific job roles which require prior knowledge- so where did you guys start? im in the UK btw
greg
View 12 Replies
View Related
Sep 15, 2006
I am new to the SQL enviroment and I am looking for a good course to learn more. I have been in the computer industry for a long time but not exposed to this program. I am looking for some training videos/online/books in order to learn more not just to pass a test for certification. I thought I would check some forums and see if anyone could offer up any suggestions of companies/authors for a person that has computer knowledge but knows nothing about SQL.
Thanks in advance for any suggestions.
View 4 Replies
View Related
Sep 10, 1999
Hi. I am just starting a new position as a dba for SQLSERVER. The first thing
I need to do is develop my training plan. I have begun reading SAMS Teach
yourself SQL Server 7.0 in 21 days. I would like to know what you (experienced)
dba's feel are the best training methods or resources. ie. CBT's, instructor led
classroom training, etc. Any feedback you can give me will be greatly appreciated.
thx,
deb
View 1 Replies
View Related
Feb 23, 2006
has anyone tried the cd/learning from sql usa
http://www.sqlusa.com/order2005/.
Interested in any input
View 1 Replies
View Related
Jun 10, 1999
Hi,
I am newbie on MS-SqlServer (6.5) I am looking for useful scripts for
learning Transact-SQL .
Is anyone known a good site for downloading some tools ?
Thanks,
Herve
View 1 Replies
View Related
Aug 8, 2006
I have the opportunity to take some MS SQL Server 2000 training courses for my Marketing Database Analayst job. The companies I have come accross so far are Learning Tree, ONLC Training Centers, and Hands On Technology Transfer, Inc. (HOTT). Has anyone had any experience with these companies?
The course I may be taking are
- Querying Microsoft SQL Server 2000 with Transact-SQL
- Populating a Data Warehouse with Microsoft SQL Server 2000 Data Transformation Services
- Administering a Microsoft SQL Server 2000 Database
Any input is greatly appreciated,
Marc
View 1 Replies
View Related
Apr 13, 2004
Hi Gurus ....
Planning for a MDX Training for me and my development team ...
The following has been proposed for a training course spread over three days ...
I need to know whether this will be enough or should some more things be added on to the same (anything that has been missed)...
I have the option of removing and adding from the list.
Module 1: OLAP Review
OLAP Databases
OLAP Definitions
Warehouse Data Flow
Module 2: MDX Foundations
The Role of MDX
MDX Member Names
Using the Calculated Member Builder
Working with Calculated Members
Module 3: Using MDX Expressions
Displaying Member Information
Displaying Family Tree Relatives
Working with Member Properties
Using Conditional Expressions
Module 4: Retrieving Values from a Cube
Understanding Multidimensional Coordinates
Retrieving a Value from a Cube
Percent of Total Calculations
Growth Calculations
Module 5: Creating Simple MDX Queries
Understanding MDX Query Statements
Creating Simple MDX Query Statements
Module 6: Creating and Manipulating Sets
Using Set Creation Functions
Using Set Manipulation Functions
Using Subquery Set Functions
Working with Dimension Interactions
Module 7: Using Aggregation Functions
Understanding Aggregation Functions
Using Time Set Functions
Calculating a Trend
Module 8: Case Study - Implementing Budget Allocations
Allocating Values from a Grand Total
Allocating Values from a Subtotal
Allocating Values Across Multiple Dimensions
Module 9: Using Calculated Cells
Respose needed urgently since I have to get back to the training team by tomorrow morning
View 8 Replies
View Related
Mar 26, 2006
Hi there.
I am pretty new to SQL, I am really keen to trained and certified as a dba.
I have been working with access and vba for the last few years and I am really keen to get trained and qualified using SQL.
Can anyone suggest any good companies that can take me from little knowledge of SQL into a certified dba?
View 3 Replies
View Related
May 8, 2007
Hi there,
I'll be going through a training couse for SQL Server next month. The class I'm signed up for is M2780: Maintaining a Microsoft® SQL Server™ 2005. My company uses SQL Server 2000 now and unless I give good reasons to upgrade to 2005, we won't be upgrading. I have very little DBA experience, but since I was told SQL Server is my responsibility, I figured it would make sense to go through some type of training for it. My questions are: Is there enough the same between the two that it makes sense for me to take this class? Are there good business reasons I can give for upgrading? If we don't upgrade is it worth taking a class for 2000 vs 2005?
Thanks for any input.
Marcie
View 2 Replies
View Related
Jan 11, 2008
Stumbled onto a good URL:
(I have checked out a few and they are pretty well done)
http://www.asp.net/learn/sql-videos/
JasonL @msft
http://blogs.msdn.com/usisvde/
View 5 Replies
View Related
