I have the following use case:
- Create a database from the current used database.
- Use or switch to the created database.
- Return to the main database (ignore, delete, throw the created database).
How can I do that? I'm using .NET and SQL Server. Can I do it with:
- Transactionscope is a siutble solution for the problem?
- SQL Server Isolation Snapshot?
- Database Mirroring?
- Or backup the current and restore it with another name. Connect to the restored database after that drop it?
Any help would be greatly appreciated!
How to create a temp copy of the current used database
February 16th, 2015 4:56am
- Or backup the current and restore it with another name. Connect to the restored database after that drop it?
That will work in every Edition of SQL Server.
If you have Enterprise (or Developer) Edition, then you can use Database Snapshots (SQL Server), see Create a Database Snapshot (Transact-SQL) and How Database Snapshots Work
Free Windows Admin Tool Kit Click here and download it now
February 16th, 2015 5:15am
But the problem with Snapshots is read only I thought this use case is really known. Buckup and restore is slow with big database. is there any possible to use tempDb or any other magic solution if you have any idea maybe you support me also as consulting.
February 16th, 2015 5:47am
But the problem with Snapshots is read only
A database snapshot is not read-only. All changes are stored to the snapshot database while all unchange data will be read for the source database; see the links I posted.
Free Windows Admin Tool Kit Click here and download it now
February 16th, 2015 6:13am