SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Restoring from backup
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

PeteLeHoq
Starting Member

35 Posts

Posted - 09/28/2013 :  09:35:28  Show Profile  Reply with Quote
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

USA
35934 Posts

Posted - 09/28/2013 :  14:55:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

35 Posts

Posted - 09/30/2013 :  09:16:09  Show Profile  Reply with Quote
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

USA
35934 Posts

Posted - 09/30/2013 :  11:03:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000