ITBeginner

Yesterday I attempt to backup and restore my database I use sql server 2005 version developer . I want to copy database “DataTest” to new database name “MyDB” I try two ways first I backup “DataTest” then restore to “MyDB” but when restore sql server have error . Second restore database “DataTest” to “MyDb” and but not work I attempt about 4 hours I google then found answer then I can restore.

How I solve problem ?

When I backup sql server backup two files are:

C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataDataTest.mdf

C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataDataTest_log.ldf

C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataMyDB.mdf C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataMyDB_log.ldf

CREATE TABLE Students
/* A table named Students
with Primary Key as Id
will be created */
(
Id INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Address VARCHAR(40) NOT NULL,
/*Only the phone number accepts null value */
Phone INT
)

SELECT @qry = CAST(@qry1 AS VARCHAR(MAX)) + CAST(@qry2 AS VARCHAR(MAX))

– ————————————————————–

– You would expect the length of @qry to be 10,000 at this point

– But that is not true. The length will be 8000.

– ————————————————————–

SELECT

LEN(@qry) AS sqlLength,

LEN(@qry1) AS sql1Length ,

LEN(@qry2) AS sql2Length

/*

sqlLength            sql1Length  sql2Length

——————– ———– ———–

10000                5000        5000

*/

 For SQL Server 2005 or higher

Build the function for return table to the calling statement.

CREATE FUNCTION SearchModule(
)
RETURNS @AppendText TABLE (
modTest varchar(500)
) As
BEGIN
INSERT INto @AppendText (modTest)
SELECT m.HEADER FROM MODULE_INFORMATION m
WHERE m.PROJECT_ID = 1
RETURN
END


Additional Articles from SimpleTalkEvery day, out in the various discussion boards devoted to Microsoft SQL Server, the same types of questions come up again and again: Why is this query running slow? Is my index getting used? Why isn’t my index getting used? In order to arrive at the answer you have to ask the same return question in each case: have you looked at the execution plan? We are very pleased to be allowed to publish the first chapter of Grant Fritchey’s excellent new book on execution plans.More »