Database Back Up and

[kishor_bangera]

Hi any budy done successfully Database Back Up and Restore on new server

[Liam_Finn1]

Are you trying to move an ev database from one server to another?

Will the new server have the same server name as the old or are you adding an additional sql server to your environment 

[kishor_bangera]

Yes i am using same server name and Database Back Up and Restore option.

and i am not trying to move an ev database from one server to another?

i dont know how it will done

Message Edited by kishor bangera on 11-21-2008 05:38 AM

[Liam_Finn1]

I recommend not doing a simple backup and restore.

I recommend that you detatch the database from SQL then copy the Database file and log file to the new server.

Here are the steps I would do

NEW SERVER

Install OS

Update service packs/ hotfixes and security updates for OS 

 Disable all the EV services on your EV servers (This is needed because without this you will not be able to detach the database)

OLD SERVER

Detatch database from SQL

Copy database files and transation log files from old server to new

Power off old server

Rename new server to the same name as old server

Install SQL on new server (do not do this before renaming the server only after the rename)

Update service packs/ hotfixes and security updates for SQL 

Attach the database which you copied from the old server 

Starting with your DIrectory server Restart the Ev services

These are only outline steps but it is what I have done when moving SQL to new hardware

Hope it helps 

[mhotek]

I know all of the EV documentation and many of the EV recommended procedures say to detach databases and then attach them.  I'm a SQL Server DBA and this is a practice that is strongly discouraged.

There is absolutely no difference within the database if you restored a backup or you attached a database.  The database does not know.  The data will not be any different between the two methods.

However, a backup is MUCH safer.  When you backup a database, the original is still attached to the SQL Server and available.  If something is wrong with the media, you have a disk/controller error, or you have an error while copying the file, you can wind up with a corrupted file.  If the file got corrupted between the time the backup was taken and when you went to restore it to another machine, you can always go back to the original SQL Server and just take another backup.

Once you detach a database, you are finished.  You have exactly one alternative and it has to go off perfectly.  When the database is detached, the only option you have left on the table is to be able to successfully attach it to a SQL Server instance.  If anything at all goes wrong, the database will not attach.  If the database does not attach, you are now stuck going to your backups.  If for some reason, you don't have good or current backups, you have just lost 100% of the data in the database that you detached.

The detach process is not fool-proof.  I've been through MANY instances where someone detached a database and then was never able to attach it.

While the EV documents and best practices might say to detach and then attach, I would VERY strongly discourage doing this.

[Liam_Finn1]

mhotec

I see your point but our DBA's recommend detaching overtaking a backup.

Also since in this case the database is being moved to a newserver and any IT admin with any common sense has at least Raid 5 if not thepreferred raid 10 on a SQL data disk the chances of loosing media in the sourceis slim

Also in the instructions i said for the old server to bepowered off and not erased the original database files are still on the oldserver. All one would need to do is power the server up again and recopy thefiles as needed if the did happen to get damaged during the copy to the newhardware

Taken into account that the hard disk on both source anddestination are in a raid config and that the chance of disk failure is slim.Plus that the original data will remain on the old server until everything isfully operational on the new server there is very very minimal risk.

Also since the server needs to be the same name as the olderone. The first server needs to be taken offline first so no matter what thedatabase on the old server will be unavailable weather it be detached or on ampowered down server.

[mhotek]

If your DBAs recommend detaching instead of backing up, then they don't follow the accepted management practices of the rest of the SQL Server community and they also don't following the recommended best practices by Microsoft either.  It doesn't matter whether you have RAID or not, the simple act of detaching a database irrevocably modifies the contents of the database header as well as the contents of the system databases.  Your RAID configuration could be perfectly fine, same goes for every piece of your hardware, and you can still wind up with a corrupted database.  A database that became corrupted only because it was detached.  You will find many companies that have in fact banned the use of a detach/attach, because it leaves you with no alternatives at all.  Just like you don't jump out of an airplane with a single parachute, you shouldn't leave yourself with a single alternative coupled with a hope that everything will be alright.

When you detach a database, SQL Server has to checkpoint the database.  The checkpoint process flushes the written buffers to the data files, marks the transaction log, closes the database, and then removes the entry in the master database.  If any of these operations fails or even partially finishes, you are left with a set of database files, unlinked from your database server, in an inconsistent state, which will never be able to be reattached or even accessed.  The corruption would still occur regardless of what happened at a hardware level.

While there might be minimal risk, there isn't any reason to take a risk at all.  While your DBAs might not have encountered a database that was corrupted during a detach and so might think that the risk is acceptable, I have seen many database that did get corrupted when they were attached.  Nothing is gained with a detach/attach over doing a backup/restore.  There is no benefit that is gained by detaching/attaching.  I'm fairly certain that you wouldn't want to have the conversation with the CEO/CTO/CIO or anyone else within an organization and try to explain to them that you lost the EV databases, because you detached them when every single SQL Server best practice recommends against doing that.  I've been called into companies to recover systems where databases got corrupted because they were detached.  I've been asked what the database best practices are.  I've had to explain the best practices and why they exist.  I've watched several of those conversations occur and I know that you don't ever want to be on the receiving end of one.

Your method says to detach the databases at which point you have to hope that everything went fine and that you will be able to attach the database to the new server.  If it doesn't attach to the new server, you have to hope that it will at least reattach to the old server.  If it got corrupted during the detach process, it won't ever attach to any SQL Server.

Using the best practices for SQL Server database migration, you take a backup of the existing database, shut down the old server, bring up the new server, restore the database, reconnect EV, and be up and running.  If the backup file was corrupted during the transfer, you could just make another copy.  If the backup file got corrupted during the write to disk, you could bring the old server and make another backup.  You always have an alternative.  You always have a good copy to go back to.  You always retain an intact database that SQL Server can access.  You don't ever cut yourself off to a single option, but you still get the migration done and on the other side, EV doesn't care that you restored the database since it has no capacity to ever tell.

[Liam_Finn1]

once again you make valid points

BUT :)

We know Microsoft best practices change as much as the direction of the wind changes. I have had MS change their ideal way of doing things so much that i listen to them but never use them as a golden rule because next week they change

We have also had all our processes validated by Microsoft as we work very close with them on our projects due to the size of our operations.

So ok we both have our points and we agree to disagree

As an FYI i have never seen a database fail to attach after being detached in all my years in IT. And also i did a detach and reattach for 76 EV databases in one day when i was moving them to a new cluster and it went 100% sucessful

Also backups are a must in every environment so you are never putting every egg in one basket.

We backup to tape and to file. We even truncate our transation logs every 15 mins which is highly recommended for any SQL environment so unless you dont do regular backups and dont do regular truncation of your logs to disk and dont do the DR steps then yes i agree you are SOL but anyone who does basic DR always have a way back 

Message Edited by Scanner001 on 11-21-2008 11:01 AM

[kishor_bangera]

But i am installed my server Symantec Endpoint Protection Manager and Console with the embedded database ...

Message Edited by kishor bangera on 11-23-2008 05:59 AM

[mhotek]

For almost 10 years, I made a living doing emergency disaster recovery of SQL Server databases based on "I've never had that happen".  I hope that you never have one abort while detaching and wind up corrupted.  I also hope that if one does, you aren't anywhere near it.  The person that blows up a critical, enterprise database by doing something that is very strongly discouraged rarely survives to have a job when someone else is called in to fix the problem.  I've sat in the conference room when the person who blew up the database by detaching it was fired.  I've had to submit testimony in several lawsuits where someone blew up a database by detaching it, causing a major, costly outage.

I'm not a random SQL Server DBA.  Just plug my alias into a search engine.  I've had to fix problems with several thousand databases that had detach failures amongst the millions of databases I've worked with in my nearly 20 year SQL Server career.  Just because you haven't seen a failure in the handful of databases you've had in your environment, doesn't mean it doesn't happen. 

I'm one of the people who created many of the best practices for SQL Server disaster recovery.  I also created many of the best practices in how to avoid creating disasters in the first place, based on having to fix problems when people took risks for absolutely no reason.  I also don't see best practices, particularly those related to backing up and restoring, changing that much.  Many of the best practices that I created 2 decades ago for managing SQL Server, in particular backup and recovery practices, are the same practices in use throughout the world today and they haven't appreciably changed.

As for the second part of this, a DBA caught truncating a transaction log in a production environment that I, or many other DBAs that I know, manage had better have a VERY good reason for doing so or they will very quickly find themselves working in a position where they don't have admin access to a database.  Backing up a transaction log and truncating a transaction log are VERY different things and are accomplished by using different commands.  Backup the transaction log, absolutely.  Truncate a transaction log? NEVER.  Truncating a transaction log is such as incredibly bad idea that after nearly 10 years of pushing by thousands of DBAs, the command to truncate a transaction log no longer exists in SQL Server 2008.