I'm trying to initialize a subscriber from a backup for a pull subscription. The publisher was started before the full backup was made. I followed the instructions @ Books Online topic "Initializing a Transactional Subscription Without a Snapshot" http://msdn2.microsoft.com/en-us/library/ms151705.aspx and How to: Initialize a Transactional Subscriber from a Backup (Replication Transact-SQL Programming) http://msdn2.microsoft.com/it-it/library/ms147834.aspx . I initialized the subscriber by restoring the db before I run the create subscriber scripts. All agents run successfully except for the following:
The network setup is that the publisher and the subscriber are in 2 different domains with the distribution database at the publisher. The subscriber agent is able to connect and run however the error on the subscriber side is:
"Agent message code 14080. The remote server "subscriber" does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers."
The warning on the publisher side is in the Replication Monitor where in the status it says Uninitialized Subscription.
Any ideas on why it says it isn't initialized? Is there a StoredProc that I have to execute to initialize from backup?
I have automated process, which synchronizes a transactional publication using initialize from backup approach. It drops subscriptions and puts them back again once the restore on the subscriber is completed.
Dropping the subscriptions causes a lot of blocking and deadlocking. I've decided to remove those steps, but it causes loss of data on the subscriber.
Is it a must to drop and re-create the subscriptions during such process? If not, how can I avoid the loss of data?
I have a table that i'm trying to pull the first transaction and last transaction per prodid. The first transaction, the ctrid should be 'TOPPEntry'.Â
sample raw data result prodid--------lineid------itemid---------ctrid----------createddate-- ================================================================= TOP02296228--Line15-----TTC3071-IR------TOPPEntry------2015-01-03 07:45:31.000--input TOP02296228--Line15-----TTC3071-IR------TOPCBFG--------2015-01-07 16:18:26.000--fg--done processing TOP02296229--Line15-----TTC3071-IR------TOPPEntry------2015-01-04 07:45:31.000--input TOP02296229--Line15-----TTC3071-IR------TOPLens--------2015-01-05 12:12:31.000--wip--wip means still in process TOP02296230--Line15-----TTC3071-IR------TOPPEntry------2015-01-05 08:45:31.000--input TOP02296230--Line15-----TTC3071-IR------TOPCBSCP-------2015-01-06 14:18:42.000--scrap--done processing
I created transactional replication on a database and setup pull subscriptions on each subscriber to run at a scheduled time once a day. The scheduled start time on each subscriber can differ. The transaction log on the publishing database will eventually consume all possible disk space. Is it possible (and safe) to shrink or truncate the transaction log file for the publishing database before all the subscribers completed running its daily pull subscription? If not, how can I manage disk space for the transaction log on the publishing database and ensure all transaction are replicated to the subscriber?
(Be forewarned... this is long, but I think you'll find the information interesting)
When it comes to SQL Server backup/restore/recovery, my approach is based on a two-part philosophy. I'd like to share this with you and get you opinion and feedback...
First, I approach recovery from the instance level, not the individual databases. While a SQL Server instance is made up of system- and user-databases, I make no distinctions between them in terms of recovery - the instance is the whole "database". I'll later explain why.
Second, I approach it so that it will ultimately allow me to do a "displace" recovery... not just "in-place" restore. In other words, be able to restore the database on the current host ("in-place"), such in a case of a media or other failures where the host is intact - which is usually the common, easy case. But above that, be able to recover the database onto another host, such in a case of a catastrophic failure of the original host.
At any rate, the two part approach equates to what I consider provisions for doing "displace SQL Server instance recovery" - the ability to restore the whole SQL Server Instance, including the original system-databases, onto another host.
I come from an Oracle background... in the world where there's only one "database instance"... no such thing as "individual" databases. And really, the term "database" in Microsoft equates to what Oracle calls a "tablespace". Oracle's "system tablespace" equates to Microsoft's "system databases"... and so forth. It's a common case of one vendor calling it, "TOE-MAY-TOE", and the other calling it, "TOE-MAH-TOE". And in Oracle... I'm not the habit of making distinction between these tablespaces - system or non-system - when it comes to backing up... not many are (I would think). Our backup appoach views backing up the WHOLE database instance... therefore if needed... we can restore the WHOLE database instance. I can't find any other logical way to approach database backup.
But as I've found... in the world of SQL Server, there's seems to be a disconnect between how user-databases should be backed up (and restored) from the the way system-databases should be backed up (and restored). Most people make distinction between these database when it comes to backup and recovery.
Let me it explain it this way...
In my shop, my backup strategy provides the capability to restore both system- and user-databases. Some people might be surprised, but all this can actually be done using only the built-in SQL Server backup and restore functions. What I've also found is SQL DBAs find it almost "un-natural" to restore system-databases. I've heard and read some say is this... "just restore the user-databases on the new server and "re-create" the objects (that were in the previous system database) into the new system databases on the new host". Some also suggests using scripts to make "re-creating" these objects easier.
I think that people approach it this way because restoring system database onto another server (a different host name, a different file directory structure, etc) is not as simple as restoring user-databases.
But it is doable... and doable using only the built-in SQL Server functions. My previous post on this matter can be found here (http://sqlforums.windowsitpro....22923&highlight_key=y)
I long time ago, I found a way to do this. And since then, my backup strategy has remained fairly the same... backup the whole sql server instance... and have the ability to restore the whole instance (if/when needed). And in the end, we not only have a great backup strategy, but a strategy for disaster recovery.
I agree that the approach to [simply] restore user-database, and then "re-creating" system-database objects is viable, but it's not "absolute". And here's why...
You'll always have to make provisions around when system-database objects are created/changed... for the life of the instance... such as logins, for example. I've seen some application create extended procedures (in the master database) and create jobs and plans (in msdb). You'll have to consistently ensure you update your "re-create scripts" to ensure you capture these objects and changes... through the life of the instance. You'll have to be that much more intimate with the applicatons in order to understand all the components it may be adding outside it's user-database.
There a huge potential risk that you'll miss something.
Bottom line... restoring the whole SQL Server instance avoids any of the above caveats, guaranteeing a "physical restore" [of the original instance] versus one where parts of it had to be "logically" recreated and/or potentially be missing.
I admit, I also use scripts to complete a full SQL Server instance recovery. For me, it is a set of batch and sql scripts, but I only use it to expedite the process. And more importantly... the scripts are created once for an instance and doesn't have to be updated... regardless of what occurs within the instance... regardless if new logins or extended procedures are created/updated (in the master)... regardless if new/updatess to jobs/maintenance plans/etc occur (in msdb)... and I don't even really know what goes on in model... but again... it doesn't matter... all of it gets restored.
The process... scripts or no scripts... is the same for all SQL Server instance I support... regardless of the idiosyncrasies of the application running on them. I have to make NO special provisions for any of them.
It's a solution where at the end of the recovery... I can tell the customer that whatever they originally lost has been restored (from the last backup)... without any doubt. Again it's a "physical restore", not one where parts of it had to be "logically" recreated.
Lastly... If you understand my philosophy... then you could probably understand why I don't consider Log Shipping and Replication to be a thorough disaster recovery solution. The reason being is that these solutions only provision for the recovery of user-databases, right? You cannot use log shipping or replication for system databases, right? And as with the other... you'll have to provision for re-creating system-database objects. Again, I admit log shipping and replication are viable solution, but again, with the idea of re-creating system database objects... it's not absolute... not for system recovery.
I'm looking for good arguments and feedbacks to really put this approach to the test. Thanks.
(Be forewarned... this is long, but I think you'll find the information interesting)
When it comes to SQL Server backup/restore/recovery, my approach is based on a two-part philosophy. I'd like to share this with you and get you opinion and feedback...
First, I approach recovery from the instance level, not the individual databases. While a SQL Server instance is made up of system- and user-databases, I make no distinctions between them in terms of recovery - the instance is the whole "database". I'll later explain why.
Second, I approach it so that it will ultimately allow me to do a "displace" recovery... not just "in-place" restore. In other words, be able to restore the database on the current host ("in-place"), such in a case of a media or other failures where the host is intact - which is usually the common, easy case. But above that, be able to recover the database onto another host, such in a case of a catastrophic failure of the original host.
At any rate, the two part approach equates to what I consider provisions for doing "displace SQL Server instance recovery" - the ability to restore the whole SQL Server Instance, including the original system-databases, onto another host.
I come from an Oracle background... in the world where there's only one "database instance"... no such thing as "individual" databases. And really, the term "database" in Microsoft equates to what Oracle calls a "tablespace". Oracle's "system tablespace" equates to Microsoft's "system databases"... and so forth. It's a common case of one vendor calling it, "TOE-MAY-TOE", and the other calling it, "TOE-MAH-TOE". And in Oracle... I'm not the habit of making distinction between these tablespaces - system or non-system - when it comes to backing up... not many are (I would think). Our backup appoach views backing up the WHOLE database instance... therefore if needed... we can restore the WHOLE database instance. I can't find any other logical way to approach database backup.
But as I've found... in the world of SQL Server, there's seems to be a disconnect between how user-databases should be backed up (and restored) from the the way system-databases should be backed up (and restored). Most people make distinction between these database when it comes to backup and recovery.
Let me it explain it this way...
In my shop, my backup strategy provides the capability to restore both system- and user-databases. Some people might be surprised, but all this can actually be done using only the built-in SQL Server backup and restore functions. What I've also found is SQL DBAs find it almost "un-natural" to restore system-databases. I've heard and read some say is this... "just restore the user-databases on the new server and "re-create" the objects (that were in the previous system database) into the new system databases on the new host". Some also suggests using scripts to make "re-creating" these objects easier.
I think that people approach it this way because restoring system database onto another server (a different host name, a different file directory structure, etc) is not as simple as restoring user-databases.
But it is doable... and doable using only the built-in SQL Server functions. My previous post on this matter can be found here (http://sqlforums.windowsitpro....22923&highlight_key=y)
I long time ago, I found a way to do this. And since then, my backup strategy has remained fairly the same... backup the whole sql server instance... and have the ability to restore the whole instance (if/when needed). And in the end, we not only have a great backup strategy, but a strategy for disaster recovery.
I agree that the approach to [simply] restore user-database, and then "re-creating" system-database objects is viable, but it's not "absolute". And here's why...
You'll always have to make provisions around when system-database objects are created/changed... for the life of the instance... such as logins, for example. I've seen some application create extended procedures (in the master database) and create jobs and plans (in msdb). You'll have to consistently ensure you update your "re-create scripts" to ensure you capture these objects and changes... through the life of the instance. You'll have to be that much more intimate with the applicatons in order to understand all the components it may be adding outside it's user-database.
There a huge potential risk that you'll miss something.
Bottom line... restoring the whole SQL Server instance avoids any of the above caveats, guaranteeing a "physical restore" [of the original instance] versus one where parts of it had to be "logically" recreated and/or potentially be missing.
I admit, I also use scripts to complete a full SQL Server instance recovery. For me, it is a set of batch and sql scripts, but I only use it to expedite the process. And more importantly... the scripts are created once for an instance and doesn't have to be updated... regardless of what occurs within the instance... regardless if new logins or extended procedures are created/updated (in the master)... regardless if new/updatess to jobs/maintenance plans/etc occur (in msdb)... and I don't even really know what goes on in model... but again... it doesn't matter... all of it gets restored.
The process... scripts or no scripts... is the same for all SQL Server instance I support... regardless of the idiosyncrasies of the application running on them. I have to make NO special provisions for any of them.
It's a solution where at the end of the recovery... I can tell the customer that whatever they originally lost has been restored (from the last backup)... without any doubt. Again it's a "physical restore", not one where parts of it had to be "logically" recreated.
Lastly... If you understand my philosophy... then you could probably understand why I don't consider Log Shipping and Replication to be a thorough disaster recovery solution. The reason being is that these solutions only provision for the recovery of user-databases, right? You cannot use log shipping or replication for system databases, right? And as with the other... you'll have to provision for re-creating system-database objects. Again, I admit log shipping and replication are viable solution, but again, with the idea of re-creating system database objects... it's not absolute... not for system recovery.
(Be forewarned... this is long, but I think you'll find the information interesting)
When it comes to SQL Server backup/restore/recovery, my approach is based on a two-part philosophy. I'd like to share this with you and get you opinion and feedback...
First, I approach recovery from the instance level, not the individual databases. While a SQL Server instance is made up of system- and user-databases, I make no distinctions between them in terms of recovery - the instance is the whole "database". I'll later explain why.
Second, I approach it so that it will ultimately allow me to do a "displace" recovery... not just "in-place" restore. In other words, be able to restore the database on the current host ("in-place"), such in a case of a media or other failures where the host is intact - which is usually the common, easy case. But above that, be able to recover the database onto another host, such in a case of a catastrophic failure of the original host.
At any rate, the two part approach equates to what I consider provisions for doing "displace SQL Server instance recovery" - the ability to restore the whole SQL Server Instance, including the original system-databases, onto another host.
I come from an Oracle background... in the world where there's only one "database instance"... no such thing as "individual" databases. And really, the term "database" in Microsoft equates to what Oracle calls a "tablespace". Oracle's "system tablespace" equates to Microsoft's "system databases"... and so forth. It's a common case of one vendor calling it, "TOE-MAY-TOE", and the other calling it, "TOE-MAH-TOE". And in Oracle... I'm not the habit of making distinction between these tablespaces - system or non-system - when it comes to backing up... not many are (I would think). Our backup appoach views backing up the WHOLE database instance... therefore if needed... we can restore the WHOLE database instance. I can't find any other logical way to approach database backup.
But as I've found... in the world of SQL Server, there's seems to be a disconnect between how user-databases should be backed up (and restored) from the the way system-databases should be backed up (and restored). Most people make distinction between these database when it comes to backup and recovery.
Let me it explain it this way...
In my shop, my backup strategy provides the capability to restore both system- and user-databases. Some people might be surprised, but all this can actually be done using only the built-in SQL Server backup and restore functions. What I've also found is SQL DBAs find it almost "un-natural" to restore system-databases. I've heard and read some say is this... "just restore the user-databases on the new server and "re-create" the objects (that were in the previous system database) into the new system databases on the new host". Some also suggests using scripts to make "re-creating" these objects easier.
I think that people approach it this way because restoring system database onto another server (a different host name, a different file directory structure, etc) is not as simple as restoring user-databases.
But it is doable... and doable using only the built-in SQL Server functions. My previous post on this matter can be found here (http://sqlforums.windowsitpro....22923&highlight_key=y)
I long time ago, I found a way to do this. And since then, my backup strategy has remained fairly the same... backup the whole sql server instance... and have the ability to restore the whole instance (if/when needed). And in the end, we not only have a great backup strategy, but a strategy for disaster recovery.
I agree that the approach to [simply] restore user-database, and then "re-creating" system-database objects is viable, but it's not "absolute". And here's why...
You'll always have to make provisions around when system-database objects are created/changed... for the life of the instance... such as logins, for example. I've seen some application create extended procedures (in the master database) and create jobs and plans (in msdb). You'll have to consistently ensure you update your "re-create scripts" to ensure you capture these objects and changes... through the life of the instance. You'll have to be that much more intimate with the applicatons in order to understand all the components it may be adding outside it's user-database.
There a huge potential risk that you'll miss something.
Bottom line... restoring the whole SQL Server instance avoids any of the above caveats, guaranteeing a "physical restore" [of the original instance] versus one where parts of it had to be "logically" recreated and/or potentially be missing.
I admit, I also use scripts to complete a full SQL Server instance recovery. For me, it is a set of batch and sql scripts, but I only use it to expedite the process. And more importantly... the scripts are created once for an instance and doesn't have to be updated... regardless of what occurs within the instance... regardless if new logins or extended procedures are created/updated (in the master)... regardless if new/updatess to jobs/maintenance plans/etc occur (in msdb)... and I don't even really know what goes on in model... but again... it doesn't matter... all of it gets restored.
The process... scripts or no scripts... is the same for all SQL Server instance I support... regardless of the idiosyncrasies of the application running on them. I have to make NO special provisions for any of them.
It's a solution where at the end of the recovery... I can tell the customer that whatever they originally lost has been restored (from the last backup)... without any doubt. Again it's a "physical restore", not one where parts of it had to be "logically" recreated.
Lastly... If you understand my philosophy... then you could probably understand why I don't consider Log Shipping and Replication to be a thorough disaster recovery solution. The reason being is that these solutions only provision for the recovery of user-databases, right? You cannot use log shipping or replication for system databases, right? And as with the other... you'll have to provision for re-creating system-database objects. Again, I admit log shipping and replication are viable solution, but again, with the idea of re-creating system database objects... it's not absolute... not for system recovery.
I'm looking to put my approach to the test... so I'm really looking for some good arguments and feedback. Thanks.
We take a full backup in the early morning and hourly transaction log back during the working hours for one database in the production server. The application team made certain changes to the design of the said database in their development server. The backup from the development server was restored to the production server during working hours. After the restoration should we take a full backup before next transactional logbackup? Would the transactional log backup with out a full backup after the restoration of a database be valid?
I just heard that for restore purpose, ths full backup and transaction log backup should be from one maintenance plan. Otherwise transaction log backup files cannot be restored after restoring full backup files.
Is it true? Can anyone offer official documents?
In my system, full and transaction backups are from one maintenance plan. Restores are doing fine. I am not sure that ideal is true or not.
I neglected to backup the transaction log as part of the process of backing up the database. Now i only have the backup file for the database and no transaction log backup. When i try to do a restore on the database, i get the error on a "tail log missing" message (which i'm assuming is that it's looking for the t-log backup?).
Is it possible to restore or even restore to a new database? I'm only looking to retreive data from 2 tables within the backup file.
I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:
Code Snippet
using (SqlCeEngine engine = new SqlCeEngine(connStr))
the sqlcesa30.dll cannot connect to SQL Server database.
In the sqlcesa30.log then I found following line:
Code Snippet
2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456
The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.
So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?
I have a full backup and transaction log backup strategy for my test server. My transaction log backup is failing as some user must have run some nonlogged operation. Is there any way to find out what is causing transaction log backup to fail?
I have a problem w/ my Transaction Log Backup. The backup files continues to grow and grow and grow. I have created a new Transaction Log backup through EM, but instead of appending each backup to the existing backup file, I overwrite the existing backup file. My gut feeling is that this is not right. If it isn't, then how can I continue to backup my TL's w/o having the file growing at an astronomical pace?
Is there a possibility to recover a database up to the point of failure ? I have a database with a primary datafile and a transaction logfile. I have backup of the database and transaction log backups upto an hour before failure. But I also want to recover one hour data which is in the transaction log. I have lost my primary datafile.
Is there a way to recover ?
How do I backup the transaction log before starting restore ?
Hi friends, I have set up the Transaction Log backup on one server he was running fine for few hour later he failed for two times and then after full backup it is running fine. Can any one of shed some light on this issue. Error: Cannot allow BACKUP LOG because file 'ZTVData' has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or differential database, backup. [SQLSTATE 42000] (Error 4213) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
I've set up 4 database maintenance plans for my databases. 1 for all system databases, and the other three for specific databases. they back up to a network drive on another computer.
The plans are set up to do complete backups every day and then transaction log backups every 15 minutes.
The complete backups work fine. They all create the backups on the network drive. The transaction log backups are only created for one of the databases though. All the others report "successful completion" but they don't actually create the backup file. I looked at the report created and it never has a step that backs up the transaction log. I compared the T-SQL code from the one transaction backup job that works to one that doesn't and all the commands are exactly the same (aside from different directories to save in to, job numbers, etc).
Does anyone have any idea what is going on? I've deleted the maintenance plans and recreated them to no avail. I've also created an on the side differential backup that works as well.
I am using ArcserveIT 2000 SQL Agent to backup my SQL Server 7.0 db. I perform a full backup (with archive on) every day. ArcserveIT backsup both database and transaction log files. 'Truncate log on chkpt' is set to False in the databases. If I don't use SQL server's backup command with trunc. log on chkpt to true, will the transaction log file ever get trimmed? Does a third party backup utility like ArcserveIT will do something similar to 'backup command' in SQL server 7. Anyone using ArcserveIT SQL Server 7.0 Agent, please comment. Thanks
Has anyone ever had an issue with a Transaction Log being backed up but not truncating? We’ve been having trouble with this and now have to truncate the log manually. Any information at this point will be useful. Thank you.
Hi, Can anybody help me in, When to take backup of transaction log and how to schedule it?
My database is in Full recovery mode with two log files. I am doing mass insertion, then i am getting the following error "The log file for database 'nags' is full. Back up the transaction log for the database to free up some log space." I have taken backup of transaction log. now also it is giving the same error.
Any suggestions how to handle to this situation, to prevent the above error.
What is the proper way to backup a transaction log for a database whose name contains a space. (I.E. DBname = Database Name). I am trying to execute a command, but it does not like the space in the database name. I have also tried wrapping the name in single quotes and parethesis to no avail.
We have a couple of databases in same server, and I set up the job on SQL server agent for backup transactions, a few dbs log backup successful, but a few are always failed, no error message be given, only can check the report --- "Backup can not be performed on database 'Agrdev'. This sub task is ignored ....," I checked all db property and set up , couldn't find out what's going on. Did anyone can help me?
Hi, I have error when i backup the transaction log of master and msdb database. I think there is an error because the model of this database is simple. can i modify the model and write full ?
I have another question, I have a db with 1 db file of 11Go, i have to create secondary files or not.
Hello!!I am relatively new to SQL and am having a problem. I have taken overa SQL 2000 db that did not have any backup plans scheduled for it. Icreated a maintenance plan that backs up the database every night andanother plan that backs up the system databases once a week. I ranboth these jobs successfully, but when I created a third plan tobackup the transaction log it fails with the following message:Executed as user: NT AUTHORITYSYSTEM. sqlmaint.exe failed. [SQLSTATE42000] (Error 22029). The step failed. I then executed the followingstatement: sp_grantlogin 'NT AuthoritySystem' which did not work.I still do not understand why I can't backup the log? The databasebacks up fine with no errors so it can't be a permission thing and Icreated the job with a maintenance plan so it has to be in fullrecovery mode. Right? How do I tell? Thank you for all yourresponses!!NS
Thanks a lot for all the replies. I'm trying to set up a job to do transaction log backup. I used the maintainence plan to create the job. the code is quite long and not very straight forward, I do not even see the BACKUP LOG .. command. Do people usually use maintainence plan for this purpose or they write their own script and then create the job?
Hi, I have one doubt about Transaction backup. I want to take backup of highly growing database. I will take full backup every 3 days, differential backup every 1 day and transaction backup every 6 hours.
Now my question is- When I take a transaction backup do it take backup of all transactions which are currently in progress and not committed in database? OR What are the chances of loosing transactions which are currently running on system while I take a backup?
We are experiencing problems with our transaction log backup. The follow error appears when we attempt the backup.
"Backup, CHECKALOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a datbase must be serialized. Reissue the statement after the current backup, CHECKALLOC or the file manipulation operation is completed."
We turned on "Truncate Log on Checkpoint" and created an online backup. Then we turned off "Truncate Log on Checkpoint" and manually saved the transaction log to tape. We thought all was well until the scheduled job to backup the transaction log to tape brought back the same error.
We have a job created by the maintenance job wizard that backs up the transaction logs for all of our databases on an hourly basis. At random intervals, one of the tranaction log backups will fail with the following message in the job history: sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
The next scheduled transaction log backup will run fine the next hour. The sqlmaint.exe is present and executable. There are no additional messages in the SQL Server error log or SQL Agent error log. Any ideas what causes this random failure?
