SQL Server Forums
Profile | Register | 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.
 New Topic  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
52325 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
2076 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
2076 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
2076 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000