|
Restore a SQL Server 2008 Database into SQL Server 2005
If you've tried to back up a database in SQL Server 2008 and then restore it into SQL Server 2005, you know that the database backups are not backward compatible. However, with SQL Server 2008 Management Studio, you can script data and schemas in SQL Server 2005 mode. This 10-Minute Solution takes you through the steps to back up the Northwind database on SQL Server 2008 and restore it to SQL Server 2005.
The Problem:
Transferring databases from SQL Server 2008 to SQL Server 2005 is not possible with standard backup and restore facilities.
The Solution:
Leverage the scripting wizard in SQL Server 2008 to script data as well as schemas in SQL Server 2005 compatibility mode.
Using the "Generate SQL Server Scripts" Wizard
The Northwind database is no longer shipped as part of the SQL Server installation, but you can download it from go.microsoft.com. The data is scripted as INSERT statements.
To create the scripts, you have to run the "Generate SQL Server Scripts" wizard, which you can run within SQL Server Management Studio (once Object Explorer is connected to the appropriate instance) by right clicking on the database and selecting "Tasks –> Generate Scripts."
Figure 1 shows the initial dialog to the wizard. Click "Next" and complete the following steps in the wizard to back up the Northwind database on SQL Server 2008 and restore it to SQL Server 2005
Figure 1. Initial Dialog to Generate SQL Server Scripts Wizard: To create your scripts, you have to run the "Generate scripts" wizard.
Click "Script all objects in the selected database" (see Figure 2), and then click "Next."
Figure 2. Select Database Dialog: Click the "Script all objects in the selected database" option.
Amend the script options: Specifically, set "Script for Server Version" to "SQL Server 2005" and set "Script Data" to "True" (see Figure 3). (SQL Server 2000 is also supported.) If you are putting the database on a new instance for the first time, make sure the "Script Database Create" option is set to "True." Click "Next" when you are happy with the options.
Figure 3. Choose Script Options Dialog: Set the "Script Data" option to "True."
Select "Script to file," select the file name, and choose "Single file" (see Figure 4). Click "Next" for a summary (see Figure 5).
Figure 4. Output Option Dialog: Select "Script to file," select the file name, and choose "Single file."
Figure 5. Script Wizard Summary: Review your selections for the wizard.
Now click on "Finish" to get progress messages while the script runs and completes (see Figure 6)
Figure 6. Generate Script Progress Dialog: Click "Finish" and you will get progress messages.
If the generation process fails, then you can use the "Report" option to see why.
When the scripting is completed, look for the following lines:
CREATE DATABASE [Northwind] ON PRIMARY
(NAME = N'Northwind', FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\northwnd.mdf' ,
SIZE = 3328KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
(NAME = N'Northwind_log', FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\northwnd.ldf' ,
SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
You will need to amend the paths to a valid path for the computer on which you are running. You also will need to comment out the following lines like this:
--EXEC sys.sp_db_vardecimal_storage_format N'Northwind', N'ON'
--GO
Once you have made these changes, you can run the script in SQL Server 2005 Management Studio to recreate the database in your development environment. You can now test data against SQL Server 2008 and SQL Server 2005.
The Inevitable Limitations
Of course, this technique is not without its limitations. Here are a few to bear in mind:
The data is insecure, as it is in clear readable text. So if you are using real data, you should delete the file created once you have loaded it into SQL Server 2005. You can regenerate the file from the SQL 2008 backup, if necessary.
If you have a database with a large amount of data, the script file, of course, will be huge.
SQL Server 2008 specifics in the source database will not be migrated.
|
|
|
|
|
Hi all,
Iam wrking in sql reporting services .In that i want to find the COUNT OF male and female in report .I have used an expression fielD to find count of male and female but it returnS both the count of male and female ..IE (TOTAL ROWS VALUE)
I have pasted my expression FIELD
=Count(Fields!GENDER.Value="MALE")
=Count(Fields!GENDER.Value="FEMALE")
PLS ANY ONE GIVE SOLUTION FOR THIS
|
|
|
|
|
could you use
=SUM(IIF(fields!GENDER.value = "MALE", 1, 0))
=SUM(IIF(fields!GENDER.value = "FEMALE", 1, 0))
|
|
|
|
|
Thanx a lot .it worked well
|
|
|
|
|
hello guys ! i need a report where i need data from two tables.here is the scenario.i have a table Ip-to-country which contains most ipnumbers ranges and th country which i appended a new field for iddcode.the second table is text_status tables which contains field like mobilenumber and other stuffs not needed for now.I want to do a report where it would be easier to have iddcode field in the text_status table which would be the iddcode of the mobilenumber.
what is chalenging to me is how to insert the corrresponding iddcode for the mobilenumber.
knowing that iddcode are from 1 digits to 4. ex 1 for us, 44 for uk, 359 for bulgaria and 1441 for bermuda.
as you can see bermuda and us start by one.so i'm confused about the way to go to know for which country a mobilenumber is for.thanks for reading this.
Ps: i even tried to substring the mobilenumber but at the end the problem is still the same.
Need some help please.thanks
eager to learn
|
|
|
|
|
Probably more than one way to go about something like this, and none of which are really very clean.
Basis of anything is, that if 1441 matches the start of the mobile number then it can't be 1 for U.S.
You could match these back in 4 iterations, starting with the 4 digit codes first, then exluding what you had matched when you do your 3 digit matches and so on.
You could code this on the fly, but would overcomplicate it really, could you add another field onto your text_status table, and update it with the iddcode where you get a match on all 4 digit iddcodes.
Then update the field with all matching 3 digit iddcodes where the field is still null, then 2 digit, then 1 digit.
|
|
|
|
|
thanks for your answer.it's makes me see other things that did appeared to me at first.i'll get back to you
eager to learn
|
|
|
|
|
To add to the answer from RGTuffin, you most propably also need to know the minimum lengths for a phone number. Consider following number:
1441232
If searching for bermuda (1441) this matches, so the result could be that you interpret this as a phone number from bermuda. This would result that the phone number is 232, but it's most certainly not true. Then again if you take out the prefix for U.S. (1) the phone number would be 441232.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi All,,,
I want To Get the max value of a string from a database,,, such as
I store a value A1-50,A2-50,B1-50,B2-50,,,
I want To Get the max value from these string, but i cann't do that.
Please Help Me.
Thanks All,,,,
|
|
|
|
|
i think this is what you meant.
select top 1 len{your string column} from <your table=""> order by len{your string column} desc
Or
select max{len{your string column}} from <your table=""></your></your>
hope it helps.
The name is Sandeep
|
|
|
|
|
Hi,,
I don't want the Len Of string,,
but i want the max value , like A9-50,A10-50, when get max, will return
A10-50
thanks all.
|
|
|
|
|
Actually the max value of A10-50 and A9-50 is A9-50 as you are doing string comparisons and A9 is larger than A1.
What you are trying to do is rather complex unless you can guarantee the format of the data as you need to reformat it for comparison. What you are trying to get is A10-50 and A09-50, then the comparison will work, so you have to split out the alpha at the start, reformat the first number and then reformat the last number (again, A1-9 is greater than A1-10).
It is possible, but not easy - then who said coding should be easy, thats what we get paid for 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
If you need to get the maximum length string, you could simply use a Select Max(Len(StringColumn)) expression.
If you have any custom criteria for getting the maximum sub-string from that comma separated list, I suggest you create a scalar valued function, e.g. GetMax(varchar (nn)). Inside that function you could parse that comma separated list into table rows (you can easily find such functions on the internet) and then apply your maximum criteria.
Hope that helps.
Regards,
Syed Mehroz Alam.
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
Hi,,
I don't want the Len Of string,,
but i want the max value , like A9-50,A10-50, when get max, will return
A10-50
thanks all.
|
|
|
|
|
There was a very similar question a couple of days ago, with a good suggestion to include another field in your table that just holds the 'numeric' part of your field, to be used in comparisons such as this.
|
|
|
|
|
If you stored your values in the same format
eg. A01-50, A02-50, A09-10, A10-50
Then a max on the column would bring back the A10-50 you wanted.
|
|
|
|
|
The answer Ashfield gave you is correct.
To give you some starting points for reformatting the values (Note, this is not the solution, just ideas you could use):
CREATE TABLE Test12 (
Column1 varchar(50)
)
--
insert into Test12 (Column1) values ('A1-50')
insert into Test12 (Column1) values ('A2-50')
insert into Test12 (Column1) values ('A10-50')
insert into Test12 (Column1) values ('A9-50')
--
SELECT Column1,
CHARINDEX('-', Column1),
SUBSTRING(Column1, 0, CHARINDEX('-', Column1)),
SUBSTRING(SUBSTRING(Column1, 0, CHARINDEX('-', Column1)), 2, 999),
REPLICATE('0', 2 - LEN(SUBSTRING(SUBSTRING(Column1, 0, CHARINDEX('-', Column1)), 2, 999)))
+ SUBSTRING(SUBSTRING(Column1, 0, CHARINDEX('-', Column1)), 2, 999)
FROM Test12
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi guys,
Back with another question 
Create Table TestTable (
TestTableId Int Not Null,
TestTableName Varchar(100),
Primary Key (TestTableId)
);
Now i want to alter the TestTableId column and change it to an Identity Key.
I heard that theres no direct syntax for this as such. But is this possible through SQL Server 2008?
The name is Sandeep
|
|
|
|
|
As far as I know it's not possible in SQL Server 2008 either.
Why not use temp table in the process, like:
- create temp table as you want the table to be
- set IDENTITY_INSERT ON
- copy the data to temp table using INSERT INTO ... SELECT ...
- set IDENTITY_INSERT OFF
- drop the original table
- rename the temp table to orginal table using sp_rename
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
|
The question is so large that it's impossible to answer it in whole. The .Net framework classes for accessing a database provide very much functionality and are quite flexible, so I take it you have good reasons to implement your own mechanism for some of these purposes.
What I would do in your case, I would write down to myself the (current and future) requirements for my custom classes. After that I would create (for example using Class Diagram in Visual Studio) a class model and test my concept against the requirements with different approaches. This would prove the concept to be correct (or you may discover weaknesses and want to change the concept).
Based on your description, few issues I would consider:
- why do I need to open the connections at start-up. If it's for speed, why don't I use connection pooling
- do I need to keep the connection open all the time
- do I need asynchronous database operations
- do different database operations require a common transaction
- what if I need to save several records using one call
- do I need set based operations (deleting multiple records at the same time etc)
Hope this helps you,
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi all,
Am i on the right forum for my questions? If not please lead me to the right one. Thanks a lot.
If Yes,
We are having trouble in creating right format for our Reports/Report Templates using Reporting Services. We used table element in rendering reports data. At first it satisfied our needs but apparently as data gets longer, the whole table we’re being pushed to the next page, leaving the first page blank.
I just want to ask if there’s other work around for this problem.
In our experience using List element is not applicable,because its keep together property is worst than the Table element. We already checked the page size and i think it's not the problem because the problem occurs when there are too many data on the table.
As in design, the whole page is composed of Header which is a sub report, then the whole table itself with groupings and all that stuff.
Can any one have some work around in mind? thanks for the help.
Can any one knows an email address of a particular person where i can ask these questions?
Thanks a lot! I'm not desperate for this one, but i do need an answer.
EWIN
|
|
|
|
|
With Crystal a similar problem occurs because of the report header and report footer. The solution I usually use is to repeat headers on each page and allow grids to be broken apart. This should work with SSRS but I haven't worked with it in a long time.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
Most of this sig is for Google, not ego.
|
|
|
|
|
Thanks for the reply. But there's no 'Allow Grid or Allow Table to be broken apart' property in SSRS.
|
|
|
|
|
It works fine for me even if I have lot of data rows that can not be accommodated in one page. Are you sure that you have unchecked options like "Insert a page break before/after this table" and "fit table on one page if possible" on the properties window (the one that pops up when you right click a table and select properties)?
Regards,
Syed Mehroz Alam
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
