September 3, 2012 at 6:39 am
Would it be possible to move a table to a new database, but still reference it on the old name?
We backup & restore a database on different environments alot and I would like to move one of the tables out of the backup as to save space. The table is currently 25Gb.
September 3, 2012 at 7:56 am
Take a look at synonyms, they should do what you need.
September 3, 2012 at 8:12 am
As long as you stay on the same SQLInstance, it should be OK.
Just:
1) create another DB ( enable ownership chaining )
2) create the table you need
3) grant the account being used the auth it needs for that table
4) in a single transaction, rename the current table and create a view or synonym that points to the new table in the new db
5) move over the data from the renamed table to the new table.
6) ( after double check ) drop the old (renamed ) table
Keep in mind, you'll need to take backups of the new db anyway! ( or if you don't care about the data, script it all every once in a while. )
An alternative could be to just move that table into its own filegroup and perform filegroup backups.
Also, with sql2008, enable backup compression. ( with our db it reduces the backup to about 20% of the original backup file size )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 3, 2012 at 10:50 pm
Thanks Johan, I will give it a try
September 4, 2012 at 3:53 am
Thank you all! Synonyms did the trick
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply