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?
Can anyone suggest a quick and easy way to rename a database AND .MDF/.LDF file in SQL Server? I can not, for the life of me, figure out a way to do this. Help?
I have created a complete database with its tables, views, procedures, everything in SQL Server for a system. Now I am creating another system and I have realised that I can use almost the same fields I have and only do small changes, therefore I want to copy the existing one and rename it
I have been trying to copy it but I don't know how to copy exactly everything. Could you help me please?
Any help would be really appreciated! Thank you Gloria
I have created a complete database with its tables, views, procedures, everything in SQL Server for a system. Now I am creating another system and I have realised that I can use almost the same fields I have and only do small changes, therefore I want to copy the existing one and rename it
I have been trying to copy it but I don't know how to copy exactly everything. Could you help me please?
Any help would be really appreciated! Thank you Gloria
Hi all, I have a dev database on a dev machine that is the same name as a database on a production server.
I would like to attach the dev database to the production server but I can't because there would be two database with the same name. I tried just renaming my dev .mdf to another name but that doesn't work., I think I need to rename the database files but I don't know how.
Is there a way to rename the logical file for a database. For example, if I am moving a development database into production, I can use backup - but the backup takes the logical file names of the database and puts it into my production server. Now I have a production database with "dev_data1" for a logical file.....Can I change that name....?
This is a cross posting with: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33143
Is it possible to rename a database in SQL Server 2000? If not, is it possible to copy the tables and relationship diagram from one to another? The database is new, so there are no records to copy. I just want to rename it, because redoing the tables would take too much energy (<----Lazy). :)
If I run an ALTER DATABASE command that renames the Logical file name of a database, will I run into any unseen consequences in terms of day to day use of the database?
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?
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?
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.
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.
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!
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?
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
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?
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.
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
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.
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.
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'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.
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.
I need to rename my 2000 Server running an instance of SQL 2000. My question is this, will I have to reinstall SQL after the rename or will it come back up detecting the new server name. I am in a domain as a stand alone server.
I searched, but couldn't find anything to match what I am looking for...
Basically, is there any way to tell DTS to create a new table each time that the backup is run? I am scheduling the backup for 1 hour intervals for 5 days, but need the databases that are backed up to be unique, so i would end up wiht 120 of them total.
Is there any way to do this through DTS? Or am I hosed?
hi, I had a table and it has certain dependecies(like stored proc.). I rename the table and created anew one with same name.this new table did not inherited all the dependecies which it should. To say, I had a table TAB.I renamed it to TAB_old and made a new table with name TAB.When I go to SEM and click on 'Display Dependencies' table TAB is not showing anything whereas TAB_old is stil showing all the dependecies.I even ran Update stats and sp_updatestats. any idea what is this going on..... TIA
