April 16, 2015 at 7:57 am
Hi,
I hope I'm in the right forum. I am transferring all the databases (and associated objects) from Sql Server 2005 (source) to Sql Server 2012 (target). Both servers are on separate boxes:-
I am using the following technique:
1. On the target box create a share on the like: C:\share
2. On the source save the databases to this share using the command:
BACKUP DATABASE dbName
TO DISK = '\\target\share\dbName.bak'
3. Once all the files are transferred across on the target box we then run
RESTORE FILELISTONLY
FROM DISK = 'c:\share\dbName.bak'
My questions are: is this sufficient to transfer and restore all associated object with the source DB? (like logs and contraints, etc...)
Am I missing anything important?
Comments/suggestions/ code snipets most welcomed,
Regards,
J.
April 16, 2015 at 8:09 am
You'll need SQL Server to be running under a domain account on the source server and for that account to have write permission on the share.
RESTORE FILELISTONLY won't restore the database: it'll only give details of the database files that are on the backup device.
If your target server doesn't have the same folder structure as the source, you'll need to use the results of RESTORE FILELISTONLY to build the MOVE clauses in your RESTORE statement.
You'll need to make sure that all users in the database are mapped to a login on the target server after you have restored the database. For Windows logins, you just need to make sure the login exists and it will be mapped automatically. For SQL logins, you'll need to create the login and then map to it in the new database like this (excuse any minor syntax errors here - I'm doing this from memory):
ALTER USER MyUser WITH LOGIN MyLogin
Don't forget to change the database compatibility, like this:
ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 110
There's other stuff to think about as well, for example, do the two servers have the same collation? If not, you may see collation conflict errors when attempting to JOIN or UNION to temp tables.
John
John
April 16, 2015 at 8:22 am
John Mitchell-245523 (4/16/2015)
Done. Thanks
RESTORE FILELISTONLY won't restore the database: it'll only give details of the database files that are on the backup device.
What should I use then? What is the best command to use?
If your target server doesn't have the same folder structure as the source, you'll need to use the results of RESTORE FILELISTONLY to build the MOVE clauses in your RESTORE statement.
J: I don't fully understand. How do I determine if the target has the same folder structure as the source in the first place?
If the target structure are the same - what is the best form of RESTORE command to use? Can you expand please?
You'll need to make sure that all users in the database are mapped to a login on the target server after you have restored the database. For Windows logins, you just need to make sure the login exists and it will be mapped automatically. For SQL logins, you'll need to create the login and then map to it in the new database like this (excuse any minor syntax errors here - I'm doing this from memory):
ALTER USER MyUser WITH LOGIN MyLogin
Don't forget to change the database compatibility, like this:
ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 110
J: Why? Is 110 the CM of Sql Server 2005?
There's other stuff to think about as well, for example, do the two servers have the same collation? If not, you may see collation conflict errors when attempting to JOIN or UNION to temp tables.
J: No sure. How do I determine that the 2 servers have the same collation?
April 16, 2015 at 8:35 am
Have a read through this[/url].
110 is the compatibility level for SQL Server 2012. You need to change to it to ensure you can use all the new features.
You can check the server collation either on the Properties page for the server through the GUI, or by running this:
SELECT SERVERPROPERTY('Collation')
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply