Backup and Restoring from several different sources
-
Hello SQLers!!!
I have a question that may seem silly, but I'm still trying to learn the capabilities of SQL Server. If I set up a database of the same name on different machines (each have their own set of data) and I want to amalgamate them on a central database .... can this be done by doing differential backups on the various machines and "restoring" them on the central database? If this way just won't work, what is the best (and easiest) way to achieve this? One point to note is that we want to have a somewhat "smart" database that would know if there are two exact entries from different sources.
Thank you very much.
-
Hi Juan,
You can't achieve 'merging' of data on a central database with backups. Have a look at Replication in BOL (SQL Server Books Online)
Cheers,
Angela
-
Hi I want to restore the backup of another newly created database.Whlie doing so the logical dat and logical log file of new datbase cannot be the same of the newly created database
-
Actually I have created a new database.Not it has not user table.I have back up of another database of same server.Now I want to restore data in my newly created database from my backup of another database.While doing so it doesnot not allow me to keep the logical file names to my new database file name
-
Chetan,
Just restore your database as normal. Then use:
ALTER DATABASE yourdatabasename
MODIFY FILE (NAME = logical_file_name, NEWNAME = new_logical_name)
To change the logical filename for .mdf, .ndf, .ldf to whatever you want it to be.
Cheers,
Angela
-
While you can use the MODIFY FILE it would be easier to do the restore while moving the database at the same time. IE
From BOL)This example uses both the BACKUP and RESTORE statements to make a copy of the Northwind database. The MOVE statement causes the data and log file to be restored to the specified locations. The RESTORE FILELISTONLY statement is used to determine the number and names of the files in the database being restored. The new copy of the database is named TestDB. For more information, see RESTORE FILELISTONLY.
BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
GO
BTW: With this method you don't even have to have the TestDB created before doing the restore!
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Gary,
AFAIK and I may be wrong on this, but you cannot rename the logical filename of a .mdf, .ndf or .ldf file while restoring a database. The example you have given renames the actual database and physical filenames, not the logical filenames. In your example, The database is restored as TestDB but still has the logical filenames of Northwind and Northwind_log.
If you re-read Chetan's posts, he was wanting to change the logical file names to reflect his new database name.
-
Angela, You're right. Although I've never much worried about changing the logical filenames. I just always create the copied db on the fly and change the physical filename. In fact I have to do just that here in a few minutes.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Thank you .....
I will try this. This seems to be the "simplest" solution.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply