Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLsearcher
Starting Member
47 Posts |
 Posted - 2005-12-09 : 04:45:26
|
| Dear AllI replaced a database on Test with its counterpart on Production. The original database on Test was part of two maintenance plans. One that backs up the entire database and one that backs up the transaction log. The maintenace plan now says it cannot backup the transaction log of the new copy on Test (no big surprise). So I ran the first maintenance plan that does a complete backup. But the seconed maintenance plan (that backs up the transaction log) keeps telling me it cannot backup the transaction log. I think there is something very basic I overlook. And it will be embarrassing to me. So please be kind.Kind regards, SqlSearcher |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-12-09 : 08:09:31
|
| You'll need to check the recovery model. If it is not "FULL" then you cannot backup the transaction log. Also you may want to write you're own backup script because the maint plans never give any specific error messages when they fail, which makes them a big pain.Have fun,Daniel, MCP, A+SQL Server DBAwww.dallasteam.com |
 |
|
|
SQLsearcher
Starting Member
47 Posts |
Posted - 2005-12-09 : 08:34:19
|
| Dear SQLServerDBA_DanWe are both right on spot. I am embarrassed and the recovery model did cause the problem.Thanks a lot, SqlSearcher |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-10 : 00:40:31
|
You might want to look into why you have a Transaction Log backup on TEST but not on production  Kristen |
|
|
|
SQLsearcher
Starting Member
47 Posts |
Posted - 2005-12-12 : 02:31:06
|
| Dear KristenOn Production all our databases' recovery models are set to Full. There are maintenance plans that backup these T-logs. During copying from Production to Test (Enterprise Manager > All Tasks > Export Data > ... > Copy objects and data between SQL Server databases.), the recovery model of the (new)target database was set to simple in stead of full. Maybe it is because the model database is set to simple. Kind regards, SqlSearcher |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-12 : 04:25:29
|
| "Enterprise Manager > All Tasks > Export Data > ... > Copy objects"Ah, I see. I was assuming you had just done a BACKUP and RESTORE, which would have preserved the Recovery Model of the Source database. Why don't you use BACKUP + RESTORE?Kristen |
|
|
|
SQLsearcher
Starting Member
47 Posts |
Posted - 2005-12-12 : 04:34:14
|
| When I do a BackUp & Restore, user logins are also copied. This is bad when I copy from Test to Production. The other way it's not such a big deal. But I got used to this method. My old method was to script the source database, delete the target database, run the script and next import the data. Both ways give you a way to verify your referential integrity. During developing data might be inserted illegally (for testing purposes) and we might have forgotten to remove them.Kind regards, SqlSearcher |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-12 : 14:09:34
|
| Ah, I see [again!]Our RESTORE script itself generates a script of the Logins in the database - complete with code to "reinstate" the user on the target server (i.e. create a User Login if not found, and a Database User if required) and to, alternatively, DROP the user from the database [i.e. if no longer required], so my preference would be to use that route, but I can see where you are coming from!Kristen |
|
|
|
|
|
|
|
|
Backup T-logs fails for copied database