Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Moving database faster, by dropping a large table.
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KrisLewis
Starting Member

USA
7 Posts

Posted - 04/16/2013 :  10:40:43  Show Profile  Reply with Quote
I am trying to backup a very large database from a client and move it to my server. How can I remove a large table, and just backup the rest of the tables all at once. I currently am backing up the whole database to a mock database. I drop the large table than backup that database again. After this I transfer the new database to my server. This takes a very long time. Is there a faster way of doing this. Thanks for any advice.

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 04/16/2013 :  10:58:19  Show Profile  Reply with Quote
are both set of tables in different file groups?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/16/2013 :  11:01:31  Show Profile  Visit russell's Homepage  Reply with Quote
And what do you mean backing up to a mock database? Are you using native SQL Server backup? Are you using with compression?
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts

Posted - 04/16/2013 :  11:28:54  Show Profile  Visit jackv's Homepage  Reply with Quote
if one of the tables is read only ( for example , the large table) - you can place it in its own file group -. That way , you'll only need to backup and restore the other tables - which are sitting on a separate file group

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts

Posted - 04/16/2013 :  11:30:06  Show Profile  Visit jackv's Homepage  Reply with Quote
btw , there are a number of methods to transfer quicker. have you checked a non-buffered copy or SAN copy (if you're on SAN)

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

KrisLewis
Starting Member

USA
7 Posts

Posted - 04/16/2013 :  11:32:28  Show Profile  Reply with Quote
Hello Jack,
Can you explain or refer me to a website of how to properly setup a file group. And also how to only backup certain filegroups. I have tried setting this up before but had trouble doing so. I think this is the approach I am going for.

quote:
Originally posted by jackv

if one of the tables is read only ( for example , the large table) - you can place it in its own file group -. That way , you'll only need to backup and restore the other tables - which are sitting on a separate file group

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Go to Top of Page

KrisLewis
Starting Member

USA
7 Posts

Posted - 04/16/2013 :  11:34:19  Show Profile  Reply with Quote
Hello Visakh

I am currently trying to create file groups. I have been having problems setting them up. I think Jack also gave me some advice on using filegroups.

quote:
Originally posted by visakh16

are both set of tables in different file groups?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts

Posted - 04/16/2013 :  11:36:36  Show Profile  Visit jackv's Homepage  Reply with Quote
Here are some links explaining the cocepts with examples:
http://www.sqlserver-dba.com/2011/03/sql-server-files-and-filegroups-improving-database-performance.html
http://msdn.microsoft.com/en-us/library/ms187087(v=sql.105).aspx

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

KrisLewis
Starting Member

USA
7 Posts

Posted - 04/16/2013 :  11:37:00  Show Profile  Reply with Quote
Hello Russell,

I am currently for example creating a new database with a similar name to the one I am backing up and calling it (backuptable)test. So when I have the backup, I drop the large table I have. But now I will be looking into how to setup a proper file group, as Jack and Visakh have mentioned to try.

quote:
Originally posted by russell

And what do you mean backing up to a mock database? Are you using native SQL Server backup? Are you using with compression?


Edited by - KrisLewis on 04/16/2013 11:51:54
Go to Top of Page
  Previous Topic Topic Next 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.1 seconds. Powered By: Snitz Forums 2000