Restore data from backup database to production database. 1

[EM1107]

Good day everyone.

Here is my situation. I have a user that deleted data from my production database and they would like to have the data back in the database. In order to do that I have to retrieve the data from a database backup but the database backup is on a different server and it is not connected to the same domain. What I have to do is to extract the data from the table and insert it back in the production database using the insert command. I have then created a select command that return the information as an insert command.

Here is an example.

I have a table CALLED TABLE1 with col1, col2, col3.
My co11,col2 are int and col3 is varchar(10)

I created my select statement like this.

SELECT 'INSERT INTO TABLE1 (COL1, COL2, COL3)
VALUES (' + CAST (COL1 AS VARCHAR (6)) +', ' + CAST (COL2 AS VARCHAR (6)) +', ''' + COL3 +'''' + ');'
FROM TABLE1
WHERE COL1 = 15434

My query return as follow.
INSERT INTO TABLE1 )COL1, COL2, COL3) VALUES (15434, 1, 'TEST')

From that point on I can use the query to insert back in my production database. Where I have an issue is if one of the column is null then the query return null. I would like to know if anyone would have a solution to turn the null column to something else so the result does not return null value.

Thanks in advance and let me know if you require more detail.

 

[simian336]

Why not just say

insert into table1 (COL1, COL2, COL3)
select COL1, COL2, "Test"
from backup_table
WHERE COL1 = 15434

Simi

 

[gmmastros]

Try something like this.

Declare @Temp Table(Col1 Int, Col2 Int, Col3 VarChar(10))

Insert Into @Temp Values(Null, Null, Null)
Insert Into @Temp Values(1, Null, 'x')
Insert Into @Temp Values(2, Null, 'Y')
Insert Into @Temp Values(2, Null, 'Da''ta')

Select 'Insert Into TableX(Col1, Col2, Col3) Values(' 
       + Coalesce(Cast(Col1 As VarChar(6)), 'NULL') 
       + ',' + Coalesce(Cast(Col2 As VarChar(6)), 'NULL') 
       + ',' + Coalesce('''' + Replace(Col3, '''','''''') + '''', 'NULL') 
       + ')'
From   @Temp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[EM1107]

You are correct gmmastrosI could use variable but I was trying not to use them as the table as about 100 column and that would involve a lot of typing just for maybe only one column or two columns been null. I was trying to get something more simplified.

simian336 this would have been simple if I could link the two database server together, but they are on different network and there is no link between the networks.

 

[gmmastros]

I'm sorry. You're right. I didn't consider how much typing this would cause you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

The alternative solution would be to define each column in SQL Server to have a default value. Then you can omit that column in your INSERT statement.

PluralSight Learning Library

 

[JoeAtWork]

You are correct gmmastrosI could use variable but I was trying not to use them as the table as about 100 column and that would involve a lot of typing just for maybe only one column or two columns been null.

If only one or two columns might be Null, then you only need to add the Coalesce to only one or two columns, correct?

Anyways, I would copy the MDF/LDF files of the backup database to the production server, attach the database, and then you can run Simian's statement. After that, just delete the backup database from the production server.