March 31, 2014 at 10:26 am
I need to copy the Production Database to QA and it's running 24/7. Before I do this, I just need to know if copying the Production Database will require any downtime or does it have any performance issues? What would be the best way to do it?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 31, 2014 at 10:52 am
Take the most recent full backup of the production database and restore it to the QA server. No impact on the production database.
March 31, 2014 at 10:52 am
do it by backup\restore. No down time required as its an online operation, I have never seen a backup cause a noticeable performance issue, especially if you route the backup to a different drive than the database.
---------------------------------------------------------------------
March 31, 2014 at 10:55 am
Also, if copying via explorer, pull it from the QA server rather than push from the prod server.
---------------------------------------------------------------------
March 31, 2014 at 11:09 am
Yes, restore the qa db from a production backup. Create a script that does that, including all the appropriate "WITH MOVE ... TO ..., MOVE ... TO ..." clauses.
But you have other things you'll also want to consider as part of this process.
If the db does not yet exist on qa, you'll want to pre-create a shell db to restore over if the prod log file is large. When SQL restores a backup, it must restore the log file to its full, original size. It's generally much faster to pre-allocate that space all at once rather than letting the log grow dynamically and thus incrementally.
Likewise, when you restore in the future, do it over the existing db if possible. If you can't, if you have to drop the qa db before restoring, then again build a shell db with pre-allocated log space before restoring.
Finally, determine any user and/or permissions that need removed, added or changed in qa vs prod. Create a script that does that. Be sure to include in the script any users that need the sid re-sync'd (i.e. sp_change_users_login). [Of course, if it's possible, it's much easier longer-term to just change the sid on qa to match what's on prod, then the restores will automatically re-sync those users.]
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 31, 2014 at 11:20 am
Thanks everybody for replying. Can I use import export utility? Just asking.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 31, 2014 at 11:23 am
ScottPletcher (3/31/2014)
Yes, restore the qa db from a production backup. Create a script that does that, including all the appropriate "WITH MOVE ... TO ..., MOVE ... TO ..." clauses.But you have other things you'll also want to consider as part of this process.
If the db does not yet exist on qa, you'll want to pre-create a shell db to restore over if the prod log file is large. When SQL restores a backup, it must restore the log file to its full, original size. It's generally much faster to pre-allocate that space all at once rather than letting the log grow dynamically and thus incrementally.
There is no DB in QA environment so I have to create one first. The DB is about 9 GB in prod so do I allocate 10 GB when I create a DB in QA and auto-extend on etc?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 31, 2014 at 11:25 am
New Born DBA (3/31/2014)
Thanks everybody for replying. Can I use import export utility? Just asking.
If all you want are the tables and the data, sure.
March 31, 2014 at 11:38 am
New Born DBA (3/31/2014)
ScottPletcher (3/31/2014)
Yes, restore the qa db from a production backup. Create a script that does that, including all the appropriate "WITH MOVE ... TO ..., MOVE ... TO ..." clauses.But you have other things you'll also want to consider as part of this process.
If the db does not yet exist on qa, you'll want to pre-create a shell db to restore over if the prod log file is large. When SQL restores a backup, it must restore the log file to its full, original size. It's generally much faster to pre-allocate that space all at once rather than letting the log grow dynamically and thus incrementally.
There is no DB in QA environment so I have to create one first. The DB is about 9 GB in prod so do I allocate 10 GB when I create a DB in QA and auto-extend on etc?
Assuming you have IFI on, only the log file will affect the speed of the restore. Although, depending on your disk drives, it might help performance significantly to pre-allocate the data file(s) all-at-once as well.
You definitely want to use a CREATE DATABASE command, something like below. To me, it's clearest if the logical and physical files names are the same as prod, but of course that's not a requirement.
--of course you may have fewer/more data files (I've shown primary and 1 secondary),
--and/or a smaller/large existing/initial log size, so adjust as needed.
CREATE DATABASE [db_name_to_be_restored]
ON PRIMARY ( NAME = [db_name_to_be_restored_data1], FILENAME = 'x:\full\path\to\file\db_name_to_be_restored_data1.mdf', SIZE = 2GB, FILEGROWTH = 100MB ),
( NAME = [db_name_to_be_restored_data2], FILENAME = 'x:\full\path\to\file\db_name_to_be_restored_data2.mdf', SIZE = 7GB, FILEGROWTH = 400MB )
LOG ON ( NAME = [db_name_to_be_restored_log], FILENAME = 'y:\full\path\to\file\db_name_to_be_restored_log.ldf', SIZE = 1GB, FILEGROWTH = 100MB )
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 31, 2014 at 11:58 am
New Born DBA (3/31/2014)
Thanks everybody for replying. Can I use import export utility? Just asking.
This will put more load on the production system than using the backup and restore process.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 31, 2014 at 12:50 pm
Grant Fritchey (3/31/2014)
New Born DBA (3/31/2014)
Thanks everybody for replying. Can I use import export utility? Just asking.This will put more load on the production system than using the backup and restore process.
One could also use replication. Or one could use an SSIS package to merge/update the data in QA.
But as Grant mentioned, do you really want the extra load on the prod box?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 31, 2014 at 1:04 pm
I did run into some problems and I am wondering if someone can tell me what to do.
I was unable to restore the DB because the DB we have in Prod is encrypted (TDE). So what would be step by step guide on how to restore the encrypted DB?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 31, 2014 at 1:21 pm
New Born DBA (3/31/2014)
I did run into some problems and I am wondering if someone can tell me what to do.I was unable to restore the DB because the DB we have in Prod is encrypted (TDE). So what would be step by step guide on how to restore the encrypted DB?
You need the certificate from prod used to encrypt the database on prod. With that certificate you can the access the database on QA.
March 31, 2014 at 1:34 pm
I found what I was looking for.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 31, 2014 at 1:55 pm
I've found the creation of a script and then restoring from a production backup to be very reliable.
ScottPletcher (3/31/2014)
Of course, if it's possible, it's much easier longer-term to just change the sid on qa to match what's on prod, then the restores will automatically re-sync those users.
How do you change the SID on the destination? I've never gotten it to work successfully, so I query for the mismatched SIDs and rebuild the users so the SIDs match the logins.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply