Solved

How do I tell what the collation is of a SQL Database from ONLY the .bak file

Posted on 2011-02-12
8
129 Views
Last Modified: 2016-05-29
I need to rebuild my SQL server from scratch.  I have a backup of the database but that is all.  Thoughts?
0
Comment
Question by:bdfirm
8 Comments
 
LVL 69

Accepted Solution

by:
Qlemo earned 334 total points
ID: 34879905
That is all you need. Just install MSSQL again, then perform a
   restore filelistonly from disk='Complete Path and File Name of .BAK';
to see which datafiles where included (e.g. log and data could have been in different locations), and then
   restore database NewDBName from disk='Path and file as above' with move 'LogicalFile1' to 'NewFileName1', 'LogicalFile2' to 'NewFileName2', ...
The "LogicalFileN" are those retrieved by the restore filelistonly.
If you are certain you have the same folder structure as with the original database available, you can omit the complete with clause.
0
 

Author Comment

by:bdfirm
ID: 34879940
When I install SQL 2005 I'm asked for a collation.  Shouldn't it match the collation of the .bak file?  I was wondering if I could find out the collation of the .bak BEFORE installing SQL.  
0
 
LVL 13

Assisted Solution

by:geek_vj
geek_vj earned 166 total points
ID: 34880001
>>When I install SQL 2005 I'm asked for a collation.  Shouldn't it match the collation of the .bak file?  I was wondering if I could find out the collation of the .bak BEFORE installing SQL.  

Unfortunately, there is no way to find out the collation of the server/database from .bak file. So the best way is to install SQL Server with the default collation first and restore the database. Post which, you can check the collation of the database that was restored. If the collation is not matching, then you can always change the server collation at a later point.
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34881640
hi THIS IS A QUESTION TO geek_vj

How Do You " change the server collation at a later point"
0
 

Expert Comment

by:EE_AutoDeleter
ID: 41623081
I've requested that this question be deleted for the following reason:

                           
No comment has been added to this question in more than 21 days, so it is now classified as abandoned and is now flagged for deletion.


If there is a valid solution, please OBJECT and indicate the comments that are, or would otherwise lead to, a solution.


Use the specific format https:#axxxxxxxx for comment ID(s).


Also, please don't object simply because the author did not respond to your comment. While we understand this is frustrating, unfortunately we cannot force the author to return to the question. Unless you feel you have presented a valid, verifiable solution we'll simply delete the question.


Experts-Exchange Auto Deleter
0
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 334 total points
ID: 41623079
The answer given in http:#a34879905 is correct.
One info is missing though: The DB collation does not need to match the server collation. The latter is used for anything regarding the server itself, that is database and login names, system object names (tables and views), aso. A case-sensitive collation requires you to write the database name with exact case hence.
However, the names and data used in tables inside of user databases are subject to the DB collation.
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 41623082
Objection: https:#a34879905 and the new comment https:#a41623079 should be accepted.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question