June 29, 2009 at 11:15 am
We've got 2 servers, each with copies of our databases. I keep the tables, views, SP's, etc schemas in sync between the test. This way we can do development against the test server and release when we're ready.
The test server runs in a VMware VM. Over the weekend VMware stopped working for us, and our system admin has been struggling with it ever since. (He's getting an error with msvcp80.dll.) Since our fiscal year comes to a close at the end of June, there are a lot of things which we need to work on in order to be prepared for the new fiscal year, and I prefer testing all of my SQL scripts against the test database first, before making any SQL Jobs to have them run on July 1. Anyway, now we don't have a test environment. Our sys admin has indicated that the problem he's dealing with may not be resolved soon, so I'm thinking of making a copy of one of two of our production databases onto the production server. I've been reading some articles on this website about that, and it looks as though SQL Server 2000 required you to put the database into single user mode in order to do a copy and/or move. We can't do that. However, I'm wondering if SQL Server 2005 is better than SQL 2000 was and can allow me to perform the copy of the database (with the Copy Database Wizard in SSMS, for example) without my having to put it into single user mode?
Kindest Regards, Rod Connect with me on LinkedIn.
June 29, 2009 at 11:29 am
Why not just restore the latest backup as a new database? It may not be current, ie up to the minute, but it would be close.
June 29, 2009 at 12:19 pm
Are you looking at running update scripts? Or, is this only going to be selects? If only selects, and you are running Enterprise Edition you can create a database snapshot instead.
But, I am guessing that you are actually going to be changing data - so I would recommend that you go with Lynn's suggestion and restore and create the copy from a backup.
Note: when performing the restore, you are going to supply a new name and use the MOVE option to create new data/log files. DO NOT USE the REPLACE option as that will replace your current copy of live and you really don't want to do that.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 29, 2009 at 1:06 pm
Lynn Pettis (6/29/2009)
Why not just restore the latest backup as a new database? It may not be current, ie up to the minute, but it would be close.
Just to make certain I don't screw things up, I've generated the SQL Script from the Database Restore command, and haven't run it yet. I'd like to show it to you to see if this will restore my production database to a new database (leaving the current production database alone) on our production server. Here is the SQL command:
RESTORE DATABASE [TestClientData] FROM DISK = N'C:\Data\SQL Server Backups\Full\ClientData_backup_200906260600.bak' WITH
FILE = 1,
MOVE N'amciclientData' TO N'C:\Data\MDFs\TestClientData_1.ClientData',
MOVE N'amcitranslog' TO N'C:\Data\LDFs\TestClientData_2.ClientData_Log',
NOUNLOAD, STATS = 10
GO
Kindest Regards, Rod Connect with me on LinkedIn.
June 29, 2009 at 1:13 pm
Rod at work (6/29/2009)
Lynn Pettis (6/29/2009)
Why not just restore the latest backup as a new database? It may not be current, ie up to the minute, but it would be close.Just to make certain I don't screw things up, I've generated the SQL Script from the Database Restore command, and haven't run it yet. I'd like to show it to you to see if this will restore my production database to a new database (leaving the current production database alone) on our production server. Here is the SQL command:
RESTORE DATABASE [TestClientData] FROM DISK = N'C:\Data\SQL Server Backups\Full\ClientData_backup_200906260600.bak' WITH
FILE = 1,
MOVE N'amciclientData' TO N'C:\Data\MDFs\TestClientData_1.ClientData',
MOVE N'amcitranslog' TO N'C:\Data\LDFs\TestClientData_2.ClientData_Log',
NOUNLOAD, STATS = 10
GO
More like:
RESTORE DATABASE [TestClientData] FROM DISK = N'C:\Data\SQL Server Backups\Full\ClientData_backup_200906260600.bak' WITH
FILE = 1,
MOVE N'amciclientData' TO N'C:\Data\MDFs\TestClientData.mdf',
MOVE N'amcitranslog' TO N'C:\Data\LDFs\TestClientData_Log.ldf',
NOUNLOAD, STATS = 10
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply