Is it possible to move table to different DB without changing connection strings?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Is it possible to move table to different DB...

Is it possible to move table to different DB without changing connection strings?

Rate Topic

Display Mode

Topic Options

Author

Message

chammann

Posted Monday, September 03, 2012 6:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 10:37 PM
Points: 6, Visits: 49

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

anthony.green

Posted Monday, September 03, 2012 7:56 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

ALZDBA

Posted Monday, September 03, 2012 8:12 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:17 AM
Points: 6,862, Visits: 8,049

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

     Jul 13

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

- 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" BigGrin