August 16, 2010 at 2:31 pm
i want to create a stored procedure that allows a user to run it with the name of the table they want to backup/save in case they need to restore it.
i have users that need to modify the data in a table but they sometimes get it wrong and need to restore the table the back.
so currently i have them run the
select * into table_save from table
but the indexes dont get copied over to the new table.
my users didnt know how to get the missing data back into the original table from table_save so they just deleted the original table and renamed the table_save to table. Without the indexes the application runs slow on some of the larger tables.
August 16, 2010 at 4:15 pm
You can try using database snapshots, once the database has been restored to a state. Create a database snapshot, then allow user to do their work. If issues restore from snapshot, it is very quick and painless.
If that is not an option; then script out the table in question.
After user drops the table re-create table with script and do import from staging table to work table.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
August 17, 2010 at 7:06 am
sorry for my ignorance but that is what i do not know how to do.
i currently do not know how to script the indexes so that i can recreate them from within a stored procedure/script.
August 17, 2010 at 7:20 am
As mohit suggested the database snapshot will help you to achive your target but, at database level i.e. Database Snapshot works for whole database and not for specific object of database. A snapshot database is consistent with the source database as of the moment of snapshot's creations. In your case this option would be the better option instead of taking the backup for specific table.
But, again if you are looking for table level backup as you have mentioned you can write the stored procedure to backup the table and later on create the indexes on it.
Abhijit - http://abhijitmore.wordpress.com
August 17, 2010 at 8:30 am
roy.tollison (8/16/2010)
i want to create a stored procedure that allows a user to run it with the name of the table they want to backup/save in case they need to restore it.i have users that need to modify the data in a table but they sometimes get it wrong and need to restore the table the back.
so currently i have them run the
select * into table_save from tablebut the indexes dont get copied over to the new table.
my users didnt know how to get the missing data back into the original table from table_save so they just deleted the original table and renamed the table_save to table. Without the indexes the application runs slow on some of the larger tables.
1. Allowing users to drop tables from database is very kind of you 😀 but it is not a good idea.
2. If you provided users with stored proc which allows them to copy data into new table, I would advise you to provide them with stored proc which can restore data from a given "back-up" table into required original one, so you will have no need in scripting indices...
3. Your last statement about indices is an eye-opener :hehe: If your users have permissions to drop tables, you may also teach them a bit about creating indices and other SQL Server things. So they can create and change database on fly as required and your work will be just making sure that they have enough rights to do what they want (you can even avoid this - just grant them with sa access)... 😀
August 17, 2010 at 8:52 am
Well i really appreciate all the info, but i am development for a company and am trying to help our support department out. Periodically a client will have an issue that our support dept needs to run a fix/update program that will change the data in a table or multiple tables. So our support dept wants to be able to copy/save one or multiple tables just in case what they are doing is not what the customer actually wants. sometimes the customer says they want it changed but decide afterwards that it really isnt what they wanted. So I showed support the simplest way to create a copy/save a table. But we currently use an ID Column with unique/non-unique indexes which vary from table to table. So I and a couple of others get called to help copy missing records back into the original based upon the unique index columns. Well some in support decided that they couldnt wait until we got involved, (meetings, project deadlines,...) so they just did the renaming of the original table and then changed the copy/saved table to the original name. So on the surface it all seemed ok to support but without our unique indexes data was being duplicated, which caused me a couple of hours of work to fix. So that is the reason for this post. between our support and clients they dont like having to wait for an entire backup, which at some of our client sites is a very long time due to poor sql servers.
I am creating a custom program(delphi) that will produce an interface between support and the sql data. currently it knows how to copy/save the table(s) but i am needing the indexes on the saved copies of the tables in case support needs to show the customers what the old data had in it that was changed/removed.
i am trying to figure out how to programmatically create the indexes on the new tables or even how to programmatically create a complete script of the table then i can parse thru it and get what i need.
August 17, 2010 at 9:28 am
OK i'm not sure why you cannot use the existing GUI tools in SSMS to script the table, including the index definitions; in 2008 you can also get the GUI to script out the data as insert statements as well.
if you gotta do it in TSQL, i created a procedure a while ago which scripts out a table definition (to my personally favorite format) and includes all the definitions... you could use that to get the table definition, but if you need to run that script to create a new table, you'd need to change it so that all the object names were unique...defaults, constraints and indexes...
is that what you are after?
usage is simply EXEC sp_GETDDL TableName
here is the procedure code to script via tsql
it produces results like this:
CREATE TABLE [dbo].[TALLYCALENDAR] (
[THEDATE] DATETIME NOT NULL,
[DAYOFWEEK] VARCHAR(50) NULL,
[ISHOLIDAY] BIT NULL
CONSTRAINT [DF__TallyCale__IsHol__023D5A04] DEFAULT ((0)),
[ISWORKHOLIDAY] BIT NULL
CONSTRAINT [DF__TallyCale__IsWor__03317E3D] DEFAULT ((0)),
[ISDAYLIGHTSAVINGS] BIT NULL
CONSTRAINT [DF__TallyCale__IsDay__0425A276] DEFAULT ((0)),
[HOLIDAYNAME] VARCHAR(100) NULL,
CONSTRAINT [PK__TallyCal__5CB7C64E00551192] PRIMARY KEY CLUSTERED (TheDate))
GO
CREATE INDEX [IX_TallyStuff] ON [TallyCalendar] (TheDate, DayOfWeek, HolidayName)
Lowell
August 17, 2010 at 9:30 am
Ok.
Then I would advise to use SMO:
August 17, 2010 at 10:21 am
Thanks for the extra info guys. i really appreciate it.
I am pretty sure that i have enough info to complete this project.
Again Thank you very much.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply