Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is it possible to move table to different DB without changing connection strings? Expand / Collapse
Author
Message
Posted Monday, September 3, 2012 6:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 12:03 AM
Points: 6, Visits: 61
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.
Post #1353492
Posted Monday, September 3, 2012 7:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:52 AM
Points: 5,131, Visits: 4,919
Take a look at synonyms, they should do what you need.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1353531
Posted Monday, September 3, 2012 8:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:40 AM
Points: 7,001, Visits: 8,439
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #1353539
Posted Monday, September 3, 2012 10:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 12:03 AM
Points: 6, Visits: 61
Thanks Johan, I will give it a try
Post #1353704
Posted Tuesday, September 4, 2012 3:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 12:03 AM
Points: 6, Visits: 61
Thank you all! Synonyms did the trick
Post #1353790
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse