Yes, this is bad because it can lead to your log shipped database getting out of sync and will probably result in Error 4305; error 4305 states that the log in a backup set is too late to apply, which means your log shipping jobs will fail until you can synchronize the databases. You should schedule your transaction log backup job to stop before your full backup job begins and then to restart once it has completed. See the Microsoft TechNet article on setting up Log Shipping.

When you perform a a full database backup SQL Server stores the ending log sequence number (LSN), which becomes the starting LSN for the next transaction log backup. So, if you are performing a transaction log backup while your full database backup is executing, and that transaction log backup is successful, once it is shipped and applied to the subscriber it will have a different starting LSN than the ending LSN of the full backup. This is not allowed and SQL Server will throw error 4305.

At the last user group that I attended, a question was raised adbout restoring databases involved in replication and maintaining the replication settings.  I mentioned that the RESTORE command has a KEEP_REPLICATION option that can be used in this scenario but thought I’d go into a little more detail here.

 Typically when you restore a SQL Server database, replication settings are removed during the restore process.  This allows you to do a database recovery to a dev/test/DR environment and not have to worry about replication settings causing issues in a non replicated environment.  In many scenario’s however you may need to restore a replicated database and maintain these replication settings so that you can re-initialize replication after the restore.  The KEEP_REPLICATION option allows replication settings to be maintained after a database is recovered.  Syntax is below:

 RESTORE DATABASE northwind FROM DISK=’d:\backups\northwind.bak’ WITH KEEP_REPLICATION

For more information, Microsoft has an MSDN article that goes through the entire Backup and Recovery strategy for databases involved in replication.  There is quite a bit more work here than you may initially think, so be prepared.

http://msdn.microsoft.com/en-us/library/ms151152.aspx

If anyone has any real-world scenarios around backing up and restoring replicated databases and some of the challenges that you’ve faced, I’d love to hear them in the comments section.

Question continued…1) When I issue the create file command, will it be log shipped (I think it will)? 2) Will it fail when the drive specification is not replicated on the secondary servers (I think it will)? 3) What will that impact of that be and how can I work around it? 4) How will log-shipping continue when the secondary datafile is not created (or not created the same) as the primary server? And finally 5) What are your recommendations for doing this with minimal interruption.

The situation you’ve encountered is certainly not rare. At 50,000 feet: yes, you can add a file, yes it can be on different paths on different servers, yes the process will be logged, and yes doing so will throw an error. There’s a fifth yes though, and that’s, “Yes, there’s a simple workaround.”

While the process isn’t as “seamless” as Microsoft says it is at the beginning of the article this Microsoft KB article provides the details you’ll need to add a new database file. To sum up what you’re going to see in the article, you’re going to add the new file, SQL Server’s going to throw an error, then you’re going to manually restore the transaction log backup using WITH MOVE. Once you’ve done that, everything really will be seamless.

Q: I developed a clinical progam in SQl server and vb which I’m using it for the last 5 years.At times there is some delay in executing the search commands esp the name and address text boxes.I overcome this problem by backing up the database or transferring it to another computer.Would like to know a permanent remedy for this problem

Kevin Kline says: The problem is that you are not updating index statistics regularly. (You do have indexes on the tables in your database, right?) Index statistics become stale over time and are ove no value when stale. There are two ways to refresh index statistics. The very slow and resource intensive way is to drop and recreate the indexes. The fast and less resource intensive way is to run the UPDATE STATISTICS command against each table in the database.

For my production databases, I create a job that runs every night. The job simply executes UPDATE STATISTICS against all of the user databases in the database. I also create a weekly job that runs during the weekends, when few if any users are on the system, that drops and recreates the clustered index of each table to restore the table fill factor to my preferred value of 75%. I recommend that you do the same.

Q: I have a database DB1 with 250 tables and second database DB2 with 25 tables which are also in DB1 . Now i want to copy data of only 25 same tables from DB1 to DB2 . What is the best way to do this ?? This is done almost daily, so we can’t use import export wizard. I created a secondary filegroup and added tables in that group. Now will take the backup using secondary Filegroup. Is this the right way to solve the problem?

Kevin Kline says: No, I don’t believe your approach is the best solution.

The import/export wizard is the best way to go and is very easy to schedule. The import/export wizard is actually just a tiny subset of the features available in SQL Server Integration Services (SSIS) of SQL 2005 and Data Transformation Services (DTS) in SQL 2000.

Assuming you’re running SQL Server 2000, you should create a simple daily job to move the data over that can be set in a schedule using DTS. You can get started with DTS by reading the content on this URL: http://support.microsoft.com/kb/222073/en-us  

Q:  How can I convert from MySQL to MSSQL server 2005 ?

Iain Kick says:   I would take a look at this white paper by microsoft on converting from MYSQL to SQL Server 2000 it is a very similiar process for 2005.

Technorati Tags:
SQL Server 2005, MySQL, database migration
Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |

Q:  Hi I have one sql server 2000 containing 10 databases, around 100 jobs, the os being windows 2000 server. I have to migrate to a new server having os as windows 2003 please give a plan for the migration with the least downtime so that everything on the current server is moved on to the new server.

Dave Swanson says:   This is a tricky process that requires a comprehensive plan including everything from hardware configuration, complete backup of each database (user and system), DNS/IP configuration, application changes (possibly) and migration of your windows file system. While I understand your drive to decrease downtime I feel it is much more important to verify the migration is done correctly (dot those i’s, cross those t’s).

Edgewood Solutions created a very useful template for migration that is a great place to start.

Not to promote *too much :)*, if you want to complete the backup and recovery part of the migration, try taking a look at LiteSpeed for SQL Server

Technorati tags:
SQL+Server migration migration+plan Edgewood+Solutions backup+and+restore

Q:   How can I link a SQL Server database to MS Access using link tables in MS Access?

Bryan Oliver says:   Using access to query SQL Server Data - check it out:

Create the database manually (on SQL Server 2005), then right-click the DB (on SQL Server 2005) and choose Task|Import Data, drill into your Access DB and import the tables. Once imported you can create a Database diagram (on SQL Server 2005) since all constraints will now be handled by SQL server. (make sure you key all of the tables)

Then create an ODBC file DSN for this database connection (to SQL Server 2005), then fire up the Access database and right-click in the ‘Tables’ window, choose Link Tables, choose ODBC Databases() and then choose the DSN you created (to SQL Server 2005 database) and finally all of the tables you need to link.

You’ll want to rename the old tables to “tableName_old” then rename the linked tables removing the “dbo_” from the table names.

Linked server

You can add a Access database to a SQL Server database. Following steps are -

(1) Open EM.
(2) Goto the Server to which you want to add it as linked server.
(3) Then goto security > Linked Servers section from console tree.
(4) Right click on the Client area. Then New Linked Server.
(5) Give a name and Specify Microsoft Jet 4.0 as Provider string.
(6) Prvide the location of the MDB file.
(7) Click OK.

– OR –
Issue this statement in QA of SQL Server-

EXEC sp_addlinkedserver @server = ‘DBName’, @provider = ‘Microsoft.Jet.OLEDB.4.0′, @srvproduct = ‘OLE DB Provider for Jet’, @datasrc = ‘C:\MSOffice\Access\Samples\Northwind.mdb’

You have added it as linked server now. Then, use full qualified name to issue your statements.

Q:  What is log shipping?

Bryan Oliver says:  Essentially, log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. But this is not all. The key feature of log shipping is that is will automatically backup transaction logs throughout the day (for whatever interval you specify) and automatically restore them on the standby server. This in effect keeps the two SQL Servers in “synch”. Should the production server fail, all you have to do is point the users to the new server, and you are all set. Well, its not really that easy, but it comes close if you put enough effort into your log shipping setup.

Microsoft support Log shipping only in the enterprise edition
Quest supports Log shipping in all versions of Lite Speed

Technorati Tags:
Log Shipping, SQL Server, transaction logs, standby server, Microsoft, LiteSpeed for SQL Server
Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |

Q:  What are the things we should adhere while migrating from SQL server 2000 to SQL server 2005? Please list the procedure for the migration process.

James Delve says:  There are good resources available on the web that cover this topic.  Microsoft offers an option for you on their SQL Server solutions page.  Also, you can download the SQL Server Upgrade Adviser here.  
Decide on which upgrade method below to use and migrate an existing SQL Server 2000 database to SQL Server 2005:

Upgrade through Backup/Restore
Upgrade through Attach/Detach
Upgrade using Copy Database Wizard
Upgrade through an in-place install/upgrade

Technorati Tags:
SQL Server Upgrade Advisor, SQL Server, upgrade, backup/restore, database wizard
Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |