0

I originally posted this question but it got removed for being a duplicate. I will try to be more clear in my question here, since I run into different problems when trying the solutions suggested.

I have set up a database using the tempdb and now I would like to duplicate this data to store as a more permanent database. However, all options I have found online do not allow me to do this. For example, the following link shows step by step how to duplicate/restore a database but I do not have the same options using the tempdb.

For example, my database setup looks like the following. I can right click on the model database as follows, and I have the option to restore the database.

enter image description here

However, when I do the same to the tempdb I don't have this same option.

enter image description here

So my question is, how can I make a backup of the tempdb to a more permanent database?

EDIT:

When I store the data into a new table, the tables obtain strange names, this does not occur when storing in the tempdb (i.e. some names are stored correctly, others obtain random character/number strings).

enter image description here

user113156
  • 6,761
  • 5
  • 35
  • 81
  • So what do you have in tempDB you need to keep permanently? What do you do currently on a server restart? – Stu Jun 14 '21 at 11:54
  • I just made the mistake of storing the data in the `tempdb` to begin with. I just want copy the whole db to another database and keep everything exactly as it is. – user113156 Jun 14 '21 at 12:07
  • 1
    Surely you have your creation scripts still, so just rerun them on the correct database. – Thom A Jun 14 '21 at 13:12

1 Answers1

0

This is too long for a comment.

It doesn't make sense to backup and restore the temporary database. By definition, temporary tables are temporary. They go away when the server goes down.

If you have tables that you want to backup and restore, then you don't want them to be temporary tables. You probably need to fix your application so the use of temporary tables is appropriate.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for your reply. Yes, I do realise saving in the `tempdb` was not the best solution now. However, I when I try to download, process and store the tables into a new database, the table names obtain random characters and it doesn't process correctly. The only option which seems to work for me is to store it in the `tempdb` and now I am left with having to save the `tempdb` as another db... - this was my first SQL db I set up so mistakes were made. – user113156 Jun 14 '21 at 11:28
  • @user113156 . . . That cannot be the "only option". Anywhere you define a temporary table, you can define a *real* table, put it into a database, and that database can be backed up and restored. – Gordon Linoff Jun 14 '21 at 12:20
  • 3
    *"This is too long for a comment. It doesn't make sense to backup and restore the temporary database. By definition, temporary tables are temporary. They go away when the server goes down. If you have tables that you want to backup and restore, then you don't want them to be temporary tables. You probably need to fix your application so the use of temporary tables is appropriate."* Seems to fit in a comment fine. – Thom A Jun 14 '21 at 13:10