October 27, 2009 at 5:29 am
Brandie Tarvin (10/27/2009)
The transaction log file is "optional" in the sense that you can attach a DB without it and SQL Server will create a new one.
Usually. Not always.
However, as Gail has previously said, it is a BAD Idea to delete your transaction log file without cause. What happens to all those uncommitted transactions you'll have in your logfile if you do that? You lose that data.
Actually, if there are uncommitted transactions at time of the DB being detached/SQL being shut down, the DB is considered not to have cleanly shut down. IF a DB hasn't been shut down cleanly, is detached and the log deleted it will not reattach.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2009 at 5:33 am
Gail,
Do you have documentation (or links) you can point to that refer to this? Or is this just a "general knowledge" thing that everybody knows but isn't actually documented?
I ask because I've never seen documentation that actually states that as a fact. If it is a fact, I need to read up on it and educate myself better.
October 27, 2009 at 5:45 am
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
Casually mentioned here as the cause for RECOVERY_PENDING state
What you have to do if you get your DB into that state, and the consequences
As for documentation, I doubt there's a msdn article that tells you what will happen if you do something that you're never supposed to do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2009 at 9:22 pm
As said earlier, before going for migration, we need to take all the backups of user and system databases.
Assuming we are having the 300 GB of database which would take 3 hrs/more to take the full backup.
In such cases, do we need to go for only Full backups or any other alternate options are there?
One more thing, before migration of user database, do we need to keep them READ ONLY mode?
November 4, 2009 at 12:23 pm
GilaMonster (10/27/2009)
As for documentation, I doubt there's a msdn article that tells you what will happen if you do something that you're never supposed to do.
I'm not looking for "what will happen" so much as I am "how it all works," if that makes sense. Thanks for the links, Gail. I will research and see if I can find anything else.
My knowledge of the SQL engine is woefully basic level. I really need to correct that.
November 4, 2009 at 12:28 pm
mahesh.vsp (10/31/2009)
Assuming we are having the 300 GB of database which would take 3 hrs/more to take the full backup.In such cases, do we need to go for only Full backups or any other alternate options are there?
You're going to have to go with FULL backups regardless of whatever other backup option you use. Differentials require a FULL in place before you can do those. And even if you're just detaching & reattaching the database, doing this without a backup & copy of the files is a Bad Idea.
You can do it without the backup, if you want. I just don't recommend it.
The only other option I can think of is a database copy, but I've never heard of those working well. Every time I've tried, it's failed because it tries to copy over dependant objects before their "parent" object. Maybe our DB design is just that horribly convoluted.
One more thing, before migration of user database, do we need to keep them READ ONLY mode?
We didn't do anything with READ ONLY mode. We literally took down the entire system over a weekend and did our move. Of course, our shop isn't quite 24/7 yet. And, as I've said before, we did all the hard work (code / pkg conversions, login & job setups, etc) before hand. So when everything went down, it was just a matter of backing up the DBs, restoring them over, and enabling everything.
November 4, 2009 at 7:25 pm
Brandie Tarvin (11/4/2009)
I'm not looking for "what will happen" so much as I am "how it all works," if that makes sense.
Will see what I can find you, won't be this week though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 5, 2009 at 5:34 am
Thanks, Gail. I'm patient and any new links you have would be greatly appreciated. FYI, looking at your very first link above, I found my answer.
The transaction log has a number of uses within SQL.
...
5. During restart-recovery to ensure that transactions that had committed when the service stopped but whose changes had not been written to the data file are replayed and to ensure that transactions that hadn’t completed are rolled back.
My issue is a misunderstanding of what "committed" means. I thought it meant "not yet written to the data file." Obviously I'm wrong about that. Whoops!
But my point to the OP still stands. Not bringing the Transaction Log along will destroy those transactions that didn't make it to the data file yet (which is what I originally meant to say). So, it's a bad idea to let the attach re-create your transaction log.
I highly recommend you read the links Gail posted for me. They are VERY informative.
November 6, 2009 at 4:32 am
Thank You!
February 22, 2010 at 12:53 pm
Hello, This is Praveen, Iam a tester and please can you give me some information while migrating Database from SQL Server 2000 to SQL Server 2008 what we need to test.
February 22, 2010 at 2:46 pm
mahesh.vsp (11/6/2009)
Thank You!
I know you have got the answers for questions already, Are doing this install on cluster? Or Stand alone? Sorry i did not read all the replies 🙂
EnjoY!
February 22, 2010 at 2:58 pm
Is that required to upgrade SQL 2000 first to SQL Server 2005 and then to SQL Server 2008?
Can't we do the migration directly from sql 2000 to sql 2008?
If not, any valid reasons!!!
No, you can upgrade from 2000 to 2008.
EnjoY!
February 22, 2010 at 3:31 pm
Assuming standalone install, if it is cluster install let me know few more additional steps straight forward. Most of them had already mentioned how to do migration and check list i thought this may be helpful to you.
Here is what we do when migrating to new version.
Have Server guys Disable IIS on Application Servers
Set All user databases to RESTRICTED_USER
Verify no user connections
Disable SQL Agent
Script out DB_Mail profiles
Script out SQL Jobs
Script out Users/Logins
Script out DB_Mail profiles
Save out All DTS packages
Save out All SSIS packages
Script out, Linked Server connections, Operators, Alerts, Proxies.
Full Backup of all DB's (including System databases)
Remove(Script out) All replication, publisher, subscriber and distributor
Shut down the SQL 2000 box (If you are installing new SQL 2008 on different hardware)
Copy Install files to new server.
Make sure SQL Server Domain account is in domain admin group.
Start SQL 2008 Ent install
Apply latest SP
Restore only user databases (Don't restore System databases)
Run add user connections
Run link_users script
Install DB_Mail profiles from script.
Run script to add link servers
Test link servers connections
Install Operators, Alerts, Proxies from script
Install DTS packages
Install SSIS packages
Install jobs from script
Test at least one job
Run publisher replication script
Run subscriber replication script - If this server is also a subscriber.
Re-initialize subscribers.
Run row counts on both publisher and subscriber.
Enable IIS on on application servers.
Time for testing.
EnjoY!
February 24, 2010 at 5:56 am
patwarimk (2/22/2010)
Hello, This is Praveen, Iam a tester and please can you give me some information while migrating Database from SQL Server 2000 to SQL Server 2008 what we need to test.
Short answer? Everything.
Every job, every package, every application that connects. Every email, every login, every alert.
Just everything.
Since every setup is different, I can't give you a complete list. You'll have to get that list from your DBAs.
May 11, 2010 at 11:15 pm
Hi,
I would like to know, how do DBA's plan for Rollback plan for migration.
Assuming for the first time am going for SQL 2000 to SQL 2008 migration. I have taken all the sql 2000 system and app backups. Run the upgrade advisor. Assuming that we have fixed all the issues which will not support in SQL 2008 and whatever which is breaking the Application.
1. Take full backups of all users and system databases of 2000.
2. Run the upgrade advisor on each 2000 server which has to be eventually migrated to 2005.
3. backup-restore method /dettach - attach method of all application databases.
4. Fixing of Orphan users
5. Script jobs from 2000 server and excute the script on 2005 server.
6. Setting up linked servers
7. migrate the dts packages
8. Perform unit testing at each level
9. Replication setup
10. Change the compatibility levels to 90
11. rebuild all indexes
12. Run dbcc checkdb, pagetorn....,DBCC UpdateUsage,update stats commands (health checks)
and so on...
Suppose, we have tested everything and we planned to GO LIVE!. Till that time , we will keep the SQL 2000 instances/databases in READ_ONLY mode and point our Application to the new SQL 2008 instances.
Assuming that we have migrated to SQL 2008 and customers started using the Application. Assuming, a scenario wherein after 5 days something has broke the Application. In such a scenario how to have a Rollback plan for safe side.As per my knowlege we cannot restore the SQL 2008 backups on to SQL 2000 instances nor we can apply the SQL 2008 Txn'l backup on SQL 2008.
How to recover or make the customer in safer zone without loosing the 5 days data??
How does a DBA can plan or prepared for such a scenario?????
This was a question normally asked by the management, and if that is the case how can a DBA can come up with a solution?
I would like to know how do they normally plan for that?
Thanks in Advance
Viewing 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply