Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Restoring from backup

Author  Topic 

PeteLeHoq
Starting Member

37 Posts

Posted - 2013-09-28 : 09:35:28
I've got a backup of a live db, and when I restore it to my local setup, using sql manager, tasks, restore - It restores OK 100%, but I notice that each table has lost it's primary key, so when I test the application I get "column does not allow nulls".

Is there a quick script which will re-create each table (there's about 30 tables) and add in the primary key?

I realise the data will be dropped from each table when I do this.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-28 : 14:55:35
If you performed an actual restore, then it's an exact copy of the source database. If the source database does contain the primary keys and you are missing them, then something was done wrong with the backup or the restore (in other words, not really a backup or a restore).

Data will not be dropped when you add a primary key.


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

PeteLeHoq
Starting Member

37 Posts

Posted - 2013-09-30 : 09:16:09
Thanks, looks like it was a data export to xls, then imported to a new db, then a backup taken of that db!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-30 : 11:03:29
That's the most unusual method I've heard of to date. Yeah you are going to be missing all sorts of things with that method.

Instead, backup the source database and then restore it to your local setup. You'll have an exact copy.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -