need script to insert records into test DB for testing

Question

need script to insert records into test DB for testing

sidewinder6x

Mr or Mrs. 500

Points: 535
More actions
June 15, 2010 at 10:02 am

#233746

I am a server administrator with some knowledge of SQL Server. I usually only support SQL configuration and performance and the maintenance of the underlying OS.
We are testing a backup strategy and I would like to set up a script that inserts records while we are doing the testing. Preferably one of the columns would be a date/time stamp so we can see what occurs during the backup and also once we try a restore, to determine if extra steps are needed to recover to a point in time.
Any help or links would be appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koncentrix Old Hand Points: 338 More actions · Answer 1

Hi,

I am not sure what you are trying to do. But, I assume that you want to test how backup can recover up to the point the SQL got corrupted or broke.

SQL has to files by default when you first created the database.

1. *mdf - where the data resides

2. *.ldf - transaction log - use for recovery

Everytime you do insert, delete, update it inserts that record to the transaction log before it writes to *.mdf file and back to transaction log and mark it complete.

In the event that SQL database fails in between the production hour. You can restore the database up to the point in time by restoring your lastest backup and the transaction log.

Here is the script for insert if you needed it.

INSERT INTO

(FieldName) VALUES (FieldValue)

I hope this helps.

Koncentrix

Joel

Koncentrix

sidewinder6x Mr or Mrs. 500 Points: 535 More actions · Answer 2

Thanks Koncentrix.

While I was aware of the INSERT statement, I wasn't sure if there was a better way to do this. Using your reply as a starting point, I looked up the INSERT option in Books Online and came up with this statement to be ran every 10 seconds by the SQL Agent.

Use TESTDB

INSERT INTO dbo.DT (DT)

VALUES (CURRENT_TIMESTAMP)

Koncentrix Old Hand Points: 338 More actions · Answer 3

That will work too.

or you can do this....

insert into TableName(FieldName) select current_timestamp

Koncentrix

LutzM SSC Guru Points: 107049 More actions · Answer 4

If you don't want to use SQL Agent you could use a *caugh* loop with a delay.

something like

DECLARE

@start DATETIME,

@duration_minutes INT,

@delay_seconds CHAR(8)

SET @start = GETDATE()

SET @duration_minutes = 1 -- procedure is running for 2hrs

SET @delay_seconds = '00:00:10' -- insert a value every 10 seconds

WHILE DATEADD(mi, @duration_minutes, @start) > GETDATE()

BEGIN

SELECT GETDATE()

/*

INSERT INTO dbo.DT (DT)

VALUES (CURRENT_TIMESTAMP)

*/

WAITFOR DELAY @delay_seconds

END

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sidewinder6x Mr or Mrs. 500 Points: 535 More actions · Answer 5

sidewinder6x

Mr or Mrs. 500

Points: 535

June 15, 2010 at 1:22 pm

#1180642

Imu92,

That is sweet code. Thank you.

LutzM SSC Guru Points: 107049 More actions · Answer 6

I just stumbled across the concept a few days ago when reading a thread related to the same issue. Unfortunately, neither can I find the thread nor the original author. I just remembered the code. But: It's not my fault!! 😉 😀

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]