September 11, 2010 at 7:41 pm
Hello All...
I have a database XYZ (Full Recovery Model,300 users in it size aroung 134800 MB,) on Server 1(dev)
I need to back it up to some location(z drive/disk)
Then
I need to restore to same Server1(dev) with different name XYZ_ABC
All I know is to do in GUI and writing Basic Scripts ,but my lead needs Scripts .
Steps: 1.Doing an backup to disk z
2.Restoring it
but I am concerend about renaming the DB and restoring it on same server1(dev) ....
Can u guys help me on giving the Step -Step -Procedure to do ....What will be the steps and Scripts ??
and after restoring do i need to assign permissions to alla existing 300 users ?
(may be this one depends on my lead)
Thanks
September 11, 2010 at 7:57 pm
Here's a shortcut to learn how to script those backups.
Through the gui, make the selections you need. Reassign the name, file names, and file locations as necessary. Then click on the script button at the top of the window. This will allow you to script it out to a new query window and see what is going on with the script.
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
September 11, 2010 at 9:34 pm
Given that you are restoring onto the same server/instance with just a differenct db name, all the logins (and permissions) will still work on the new db without any change. If you restore onto a different server/instance, you may need to script out the logins and restore onto the new server/instance as well.
September 12, 2010 at 8:15 am
CirquedeSQLeil (9/11/2010)
Here's a shortcut to learn how to script those backups.Through the gui, make the selections you need. Reassign the name, file names, and file locations as necessary. Then click on the script button at the top of the window. This will allow you to script it out to a new query window and see what is going on with the script.
I was going to suggest this also. You can use the script function in other areas as well. Most functions you do through the GUI (table changes, logins ...) can be scripted so you can see the script, save it for later use and as a log of what was done.
September 13, 2010 at 7:29 am
I got the script ....Thanks all ....
But do i need to restore .ndf files also???
Because i saw 6 ndf files when i try to restore ,,
MOVE N'XYZ_Data1' TO N'A:\MSSQL\User\XYZDBData1\XYZ_Mock_2.ndf',
MOVE N'XYZ_Data2' TO N'A:\MSSQL\User\XYZDBData2\XYZ_Mock_3.ndf',
MOVE N'XYZ_Data3' TO N'A:\MSSQL\User\XYZDBData2\XYZ_Mock_4.ndf',
MOVE N'XYZ_Index1' TO N'A:\MSSQL\User\XYZDBOthers\XYZ_Mock_5.ndf',
MOVE N'XYZ_Text1' TO N'A:\MSSQL\User\XYZDBOthers\XYZ_Mock_6.ndf',
MOVE N'XYz_Index2' TO N'A:\MSSQL\User\XYZDBOthers\XYZ_Mock_7.ndf'
Thanks
September 13, 2010 at 8:20 am
You need to restore the .mdf, all .ndf and .ldf files. .ndf are secondary data files.
September 13, 2010 at 8:34 am
John is correct. you need to restore/move, all the files that are returned from the RESTORE FILELISTONLY command.
September 13, 2010 at 12:10 pm
Yes - all files.
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
June 20, 2018 at 11:27 am
I know this is an old Post. It is pretty much what I am trying to do. I used the backup via the GUI interface.
I backed up database A to a separate location. Once I have done as a file ABC.mdf
I used the GUI interface to do a Restore operation changing the device to ABC.mdf
changed name to database B
on the files tab Restore AS pointed to directory on same server bothe the data and log file
Database A files are in the original location and database B are in the new location
My Problem is that it appears that both database A and database B are insinc with each other.
One is the production envirnment and the other is the Development area.
Any possible ways to determine that they are not insinc
I have never seen this before.
Thank You
I would appreciate any feedback
June 20, 2018 at 11:52 am
bdkdavid - Wednesday, June 20, 2018 11:27 AMI know this is an old Post. It is pretty much what I am trying to do. I used the backup via the GUI interface.
I backed up database A to a separate location. Once I have done as a file ABC.mdf
I used the GUI interface to do a Restore operation changing the device to ABC.mdf
changed name to database B
on the files tab Restore AS pointed to directory on same server bothe the data and log file
Database A files are in the original location and database B are in the new locationMy Problem is that it appears that both database A and database B are insinc with each other.
One is the production envirnment and the other is the Development area.
Any possible ways to determine that they are not insinc
I have never seen this before.Thank You
I would appreciate any feedback
As a test, you can create an object like a test table or stored procedure in one of the databases and see if it shows up in the other database.
Development and production on the same server isn't really a good idea. I realize a lot of times that's a company decision and they won't buy more hardware. I'd try to get anything for a different development server - even if it was an older PC. Anything.
Sue
June 20, 2018 at 12:14 pm
Hello Sue,
Thank You for the reply.
I created a table in the Development environment. It appears to not be replicating.
The record count in the main table is increasing. Since I am the only one using it.
It should not be the case.
It is one to one with the the production environment.
Thank You.
David
June 20, 2018 at 2:22 pm
bdkdavid - Wednesday, June 20, 2018 12:14 PMHello Sue,Thank You for the reply.
I created a table in the Development environment. It appears to not be replicating.
The record count in the main table is increasing. Since I am the only one using it.
It should not be the case.
It is one to one with the the production environment.
Thank You.
David
So they aren't synchronizing which is correct.
But for your record count on that table, if you have permissions, run a trace to see what else or who else is inserting records into that main table.
Sue
June 20, 2018 at 3:28 pm
HI Sue
It does not appear to be insync that is good. I do not have the rights to do much of anything here.
I created the DEV environment for me to some development and testing.
There shouldn't be anyone using it. I am worried that I in some matter sync the database.
The records are not in sync so I would have to attempt to figure it out somehow.
June 20, 2018 at 4:55 pm
bdkdavid - Wednesday, June 20, 2018 3:28 PMHI SueIt does not appear to be insync that is good. I do not have the rights to do much of anything here.
I created the DEV environment for me to some development and testing.
There shouldn't be anyone using it. I am worried that I in some matter sync the database.
The records are not in sync so I would have to attempt to figure it out somehow.
If the database is restored to a new name and the files for the database are different then it nothing else should be using it even accidentally. You probably want to involve whoever is the administrator and get some help from them to make sure no applications are hitting your own database.
Does the company have an MSDN subscription? They may be able to get you a copy of SQL Server 2005 developer edition that you could install on your own laptop or PC.
2005 is old so it's not available for the free download and more current versions don't support putting databases in SQL Server 2005 compatibility mode. If you could have your own separate environment then you wouldn't have to worry about this issue. And you'd have somewhere to safely work on the development.
Sue
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply