| Author |
Topic |
|
MBeal
Posting Yak Master
110 Posts |
 Posted - 2004-08-12 : 17:18:22
|
| When setting up log shipping do I need to first back up and restore the Master and MSDB databases from the production server to the backup server?MBeal |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-12 : 17:29:15
|
| No. But in the case of a disaster, you need to keep the standby server up to date with logins, jobs, DTS pacakges, and everything else not stored in the user database being log shipped.Tara |
 |
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2004-08-12 : 17:32:44
|
| This is what I am trying to accomplish -- I read that you should back up Master and restore it because that would synchronize the SQL Server login ID's. What would you recommend? How would you recommend going about this? If I do restore the Master from the production server onto the backup server will I lose anything or will I create a problem on the backup server?MBeal |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-12 : 17:37:05
|
| I prefer not to restore master or any of the system databases unless it is on the same server where the backup occurred. Makes me nervous restoring it onto another server. It should work though as long as the config is the same.For the logins, Derrick uses this:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35143Tara |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2004-08-12 : 17:45:54
|
| Once again -- you guys come through!!! Thank you!MBeal |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2004-08-13 : 10:53:01
|
| Tara,I went to that link that you gave me with the stored procedure. It looks like it is altering a procedure called sp_hexadecimal. What if we don't have a stored procedure called this? Should I do a create statement first and then change it to Alter afterwards?MBeal |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-13 : 11:59:58
|
| Not sure. I don't use that one. I use one that I wrote called isp_Transfer_Logins. There are copies of it in the forums if you search on the name.Tara |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2004-08-13 : 13:11:55
|
| Tara,I have set up the log shipping, and my back up routines are working perfectly. Within the SQL Agent job, one of the steps is a copy command that sends it to the standby server. This command fails for the following reason:Executed as user: MBeal. G:\MSSQL7\BACKUP\HourlyBak.BAKInsufficient disk space 0 File(s) copied. Process Exit Code 4. The step failed.The backup file is approximately 4 GB in size. I have 19.9 GB of free space on the production server and 94.3 GB of free space on the standby server. How can I not have sufficient space?MBeal |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-13 : 13:21:55
|
| So this copy command isn't part of log shipping, right? The log shipping copy job is working fine for the tlogs? What is the command that it is running for this copy job?Tara |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2004-08-13 : 13:27:37
|
| I'm following some instructions for log shipping that I found on a website. It calls for 4 steps for the backup routine in SQL Agent 1) backup 2)clear transaction log 3) copy file to standby server (xcopy G:\MSSQL7\BACKUP\HourlyBak.bak \\Xena2\G$\SQL\Backup\/c ) 4)Execute the restore on the standby server. There are three steps for the log. The log copy works just fine but obviously the restore process fails because the database backup didn't restore before it.MBeal |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-13 : 13:32:37
|
| In log shipping, you don't ever restore the full backup except to initially create the standby database. After that, only tlogs are applied. We copy the .BAK file over in a separate job just in case we ever need it.So is this SQL Server 7.0 custom log shipping?Tara |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2004-08-13 : 13:38:07
|
| Ok, so maybe I am not understanding something. In my production database, I do 3 full backups each day with transaction backups running every 15 minutes between full backups. I planned on shipping the full backups over and restoring them, and then shipping the logs over as well. From what you just stated, it confuses me because it sounds like you restore one database from a backup and then continually send the logs. That scares me (probably because of my inexperience). Why wouldn't you want to restore it from a full complete backup?MBeal |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2004-08-13 : 13:39:14
|
| ps... the log shipping instructions did come from a sql 7 version but I figured that it would work with a little adjustments.MBeal |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-13 : 13:44:34
|
| The 3 full backups each day is okay but IMO is too much. The tlogs every 15 minutes is perfect for log shipping. You don't need to restore those full backups. Here's what is done:Create a full backup on the primary site, copy it over to standby, restore it with norecovery. So now the standby db is ready for tlogs. Now every 15 minutes, backup the tlog at the primary, copy it over, restore it with norecovery. Keep doing that until the disaster occurs. Once the disaster occurs, you restore the last tlog and use with recovery .You don't restore the full backup each time because it isn't needed. One full backup and the chain of tlogs is what is done for log shipping. Tlogs are not dependent on the full backups. They are dependent on the last tlog backup done.Tara |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2004-08-13 : 13:54:08
|
| Tara,Thank you for your detailed description. I really appreciate it. One last question. If I am backing up the server 3 times each day -- once the backup is finished it truncates the log. If this happens, isn't there the possibility that the log will not be in syncronization on the standby server? I just sense that the tlog restore will fail. Do I need to be concerned about this?MBeal |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-13 : 14:19:34
|
| Full backups do not truncate the log. Only tlog backups do this.Tara |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2004-08-13 : 14:25:35
|
| I'm trying it now. Thank you again for everything!MBeal |
|
|
|
|
Log Shipping