How to take a data snapshot from a SQL Server database, store it and later compare it to a reference snapshot
Following scenario: I'm currently migrating a database to use the SQL Server Hekaton engine (Hekaton is an in memory engine, the entire database is in main memory).
To get the most performance out of this, I will use native compiled stored procedures.
So, a lot of logic that was in C# code, will be moved to stored procedures.
Now I want to write integration tests for those procedures.
What I want to do:
- start with an empty database (or some predefined state)
- call some of the procedures
- verify manually that the database is now in the correct state
- if step 3 was successful, take a snapshot with all the data in the database
And later, I want to just run the test, take a snapshot, and compare it to the reference snapshot.
I could write a tool that just reads all the data and stores it in a file, but I think some tool (or a library I can use) should already exist, but didn't find anything so far.
Any suggestions (tools or libraries, C# would be preferred) ?
sql-server database tsql integration-testing edited Nov 14 '18 at 10:26
marc_s
577k12911141259
asked Nov 14 '18 at 9:17
MartinMartin
1
add a comment |
Following scenario: I'm currently migrating a database to use the SQL Server Hekaton engine (Hekaton is an in memory engine, the entire database is in main memory).
To get the most performance out of this, I will use native compiled stored procedures.
So, a lot of logic that was in C# code, will be moved to stored procedures.
Now I want to write integration tests for those procedures.
What I want to do:
- start with an empty database (or some predefined state)
- call some of the procedures
- verify manually that the database is now in the correct state
- if step 3 was successful, take a snapshot with all the data in the database
And later, I want to just run the test, take a snapshot, and compare it to the reference snapshot.
I could write a tool that just reads all the data and stores it in a file, but I think some tool (or a library I can use) should already exist, but didn't find anything so far.
Any suggestions (tools or libraries, C# would be preferred) ?
sql-server database tsql integration-testing edited Nov 14 '18 at 10:26
marc_s
577k12911141259
asked Nov 14 '18 at 9:17
MartinMartin
1
Why don't use ATTACH/DETACH of database file and simple copy them when needed?
– serge
Nov 14 '18 at 9:31
add a comment |
Following scenario: I'm currently migrating a database to use the SQL Server Hekaton engine (Hekaton is an in memory engine, the entire database is in main memory).
To get the most performance out of this, I will use native compiled stored procedures.
So, a lot of logic that was in C# code, will be moved to stored procedures.
Now I want to write integration tests for those procedures.
What I want to do:
- start with an empty database (or some predefined state)
- call some of the procedures
- verify manually that the database is now in the correct state
- if step 3 was successful, take a snapshot with all the data in the database
And later, I want to just run the test, take a snapshot, and compare it to the reference snapshot.
I could write a tool that just reads all the data and stores it in a file, but I think some tool (or a library I can use) should already exist, but didn't find anything so far.
Any suggestions (tools or libraries, C# would be preferred) ?
sql-server database tsql integration-testing edited Nov 14 '18 at 10:26
marc_s
577k12911141259
asked Nov 14 '18 at 9:17
MartinMartin
1
Following scenario: I'm currently migrating a database to use the SQL Server Hekaton engine (Hekaton is an in memory engine, the entire database is in main memory).
To get the most performance out of this, I will use native compiled stored procedures.
So, a lot of logic that was in C# code, will be moved to stored procedures.
Now I want to write integration tests for those procedures.
What I want to do:
- start with an empty database (or some predefined state)
- call some of the procedures
- verify manually that the database is now in the correct state
- if step 3 was successful, take a snapshot with all the data in the database
And later, I want to just run the test, take a snapshot, and compare it to the reference snapshot.
I could write a tool that just reads all the data and stores it in a file, but I think some tool (or a library I can use) should already exist, but didn't find anything so far.
Any suggestions (tools or libraries, C# would be preferred) ?
sql-server database tsql integration-testing sql-server database tsql integration-testing edited Nov 14 '18 at 10:26
marc_s
577k12911141259
asked Nov 14 '18 at 9:17
MartinMartin
1
edited Nov 14 '18 at 10:26
marc_s
577k12911141259
asked Nov 14 '18 at 9:17
MartinMartin
1
edited Nov 14 '18 at 10:26
marc_s
577k12911141259
edited Nov 14 '18 at 10:26
marc_s
577k12911141259
edited Nov 14 '18 at 10:26
marc_s
577k12911141259
577k12911141259
asked Nov 14 '18 at 9:17
MartinMartin
1
asked Nov 14 '18 at 9:17
MartinMartin
1
asked Nov 14 '18 at 9:17
MartinMartin
1
1
Why don't use ATTACH/DETACH of database file and simple copy them when needed?
– serge
Nov 14 '18 at 9:31
add a comment |
Why don't use ATTACH/DETACH of database file and simple copy them when needed?
– serge
Nov 14 '18 at 9:31
Why don't use ATTACH/DETACH of database file and simple copy them when needed?
– serge
Nov 14 '18 at 9:31
Why don't use ATTACH/DETACH of database file and simple copy them when needed?
– serge
Nov 14 '18 at 9:31
add a comment |
1 Answer
1
active
oldest
votes
Make a copy of your database (e.g. backup and restore as new database). There are various tools out there, for example Red Gate SQL Data Compare or ApexSQL Data Diff, which can compare the data for you.
edited Jan 3 at 22:52
David Atkinson
4,42721926
answered Nov 14 '18 at 9:56
Andrey NikolovAndrey Nikolov
4,0133821
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53296640%2fhow-to-take-a-data-snapshot-from-a-sql-server-database-store-it-and-later-compa%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Make a copy of your database (e.g. backup and restore as new database). There are various tools out there, for example Red Gate SQL Data Compare or ApexSQL Data Diff, which can compare the data for you.
edited Jan 3 at 22:52
David Atkinson
4,42721926
answered Nov 14 '18 at 9:56
Andrey NikolovAndrey Nikolov
4,0133821
add a comment |
Make a copy of your database (e.g. backup and restore as new database). There are various tools out there, for example Red Gate SQL Data Compare or ApexSQL Data Diff, which can compare the data for you.
edited Jan 3 at 22:52
David Atkinson
4,42721926
answered Nov 14 '18 at 9:56
Andrey NikolovAndrey Nikolov
4,0133821
add a comment |
Make a copy of your database (e.g. backup and restore as new database). There are various tools out there, for example Red Gate SQL Data Compare or ApexSQL Data Diff, which can compare the data for you.
edited Jan 3 at 22:52
David Atkinson
4,42721926
answered Nov 14 '18 at 9:56
Andrey NikolovAndrey Nikolov
4,0133821
Make a copy of your database (e.g. backup and restore as new database). There are various tools out there, for example Red Gate SQL Data Compare or ApexSQL Data Diff, which can compare the data for you.
edited Jan 3 at 22:52
David Atkinson
4,42721926
answered Nov 14 '18 at 9:56
Andrey NikolovAndrey Nikolov
4,0133821
edited Jan 3 at 22:52
David Atkinson
4,42721926
edited Jan 3 at 22:52
David Atkinson
4,42721926
edited Jan 3 at 22:52
David Atkinson
4,42721926
4,42721926
answered Nov 14 '18 at 9:56
Andrey NikolovAndrey Nikolov
4,0133821
answered Nov 14 '18 at 9:56
Andrey NikolovAndrey Nikolov
4,0133821
answered Nov 14 '18 at 9:56
Andrey NikolovAndrey Nikolov
4,0133821
4,0133821
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53296640%2fhow-to-take-a-data-snapshot-from-a-sql-server-database-store-it-and-later-compa%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Why don't use ATTACH/DETACH of database file and simple copy them when needed?
– serge
Nov 14 '18 at 9:31