?
Solved

MS SQL DB settings and auto shrink question

Posted on 2004-08-18
5
Medium Priority
?
1,369 Views
Last Modified: 2008-01-09
1) What is auto shrink in MS SQL , should i check it ?

2) I have schduele a backup DB in my MS SQL to do backup every 3 hours , it seems it has only generated one file , should i be needed to restore the back up , how can go about doing it ?

3) migrating DB , if i want to change server , when i import data from my old MS SQL server to my new server , the tables and rows are imported , but the table attributes is always short fall or never did went over , how can i make sure the imports of the data and all its attribute are imported as well ?

please advise

thank you

regards

0
Comment
Question by:weeee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 11

Expert Comment

by:ram2098
ID: 11828895
1. Shrink the database is only an administrative option and it is not recommened to check this by default.

2. Backups are needed to recreate your database in case of database corruptions/losing data etc...So, continue to take the backups as per the schedule..but, need to restore only when needed.

3. Not sure how you are doing the migration..if you are using Enterpirse Manager ==> Import/Export option, you can set this as below..

Select option "Copy Objects and data between SQL Server databases" and uncheck "Use default options".

Select "Options" button and modify the options based on the attributes you require.

Hope this helps .. Ram
0
 
LVL 12

Expert Comment

by:patrikt
ID: 11828929
1) Auto shrink means that SQL will free unused space in your database files if it is possible. When that shrink will be done depends on your backup strategy and db operations.
In general if your database is of type "Constant grow" you don't need that.
If there is big logged operation from time to time in your db it should save you time and free disk space automaticaly.

2) You have to specify backup type you are using: Complete, Diferential or Transaction log?
All restore strategy depends on backup strategy. Describe when and what backups you are doing.

3) Simpliest way to move database is detach/attach. You will preserve all except logins. Logins are server wide and are stored in marer db. You have to create logins.

Patrik
0
 

Author Comment

by:weeee
ID: 11829395
Hi patrikt and all ,

My restore strategy is straightforward , retrieve all data on DB up to point when the last successfully backup is done

cheers

0
 
LVL 11

Assisted Solution

by:ram2098
ram2098 earned 750 total points
ID: 11829505
Since you are saying it is one file..I think you would have chosen the "Append" option when taking the backup.

So, when you try to "Restore" Thru Enterprise Manager..

Right click on database ==> All Tasks ==> Restore database => and select the file.

It shows you all the valid backup files on this. (From the first full backup to last differential backup)..Again depends on how you did the backup...like PatrickT said.

You can restore select any of the individual/ full backups from the list.

Is this clear?
0
 
LVL 12

Accepted Solution

by:
patrikt earned 750 total points
ID: 11829570
Explanation:

1) You have to have one comlete database backup to start from. No way if you don't have that, but doeson't meter how old it is.
Say that it is time T1 and backup BF1.
You are able restore DB to T1 with this.

2) May be you have some diferential backups. Say that you have last differential backup from T2>T1. Say it is BD2.
You are able restore DB to T2 with BF1 plus BD2.

3) May be you have some transaction log backups T3..T5>T2. Say this is BL3-BL5.
You are able restore DB to any point in time between T2 and T5. You have to use BF1+BD2+BL1+(BL2+BL3+BL4+BL5).


Is it clear?

All this you can see in EM as ram2098 writes.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question