March 26, 2024 at 7:59 pm
Hello,
I am interested in creating multiple versions of an existing database. For example, lets say the "live" production database is called ACME, I want to create a few new copies on the same server called ACME_DEV, ACME_Test, and ACME_Test2. I attempted to do this by creating new databases with these names and then tried to RESTORE a production copy of "ACME".
However, I run into problems due to MDF and LDF files already existing. What is the best way to approach this? Is there a script anyone could recommend?
Many thanks in advance!
March 26, 2024 at 8:18 pm
My first step would be to read the docs on a restore. You can change the file locations for the MDF and LDF when you restore. link - https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-2016
Now, that being said, I personally don't do testing on production instances. Never have, never will. Too much risk that I could accidentally change LIVE data which would be unacceptable. I would spin up a new instance and put your "versions" (test, dev, etc) on the new instance. This reduces the chances of accidental LIVE data changes.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 26, 2024 at 8:25 pm
Hi, Many thanks I will study the link you send. This is actually all in a test environment. I agree with your prospective on keeping the separate instances. Thanks for the quick reply!
March 26, 2024 at 9:16 pm
Naturally you can't use the current file names, since they're already in use for the original db. Use "WITH MOVE" to "tell" SQL the new file names
RESTORE DATABASE ACME_DEV FROM DISK = '...' WITH <br /> MOVE '<logical_file_name_1>' TO 'x:\new\file\path\filename.mdf',<br /> MOVE '<logical_file_name_2>' TO 'x:\new\file\path\filename_log.ldf'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply