Solved

MS SQL DB settings and auto shrink question

Posted on 2004-08-18
5
1,363 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 250 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 250 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

749 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