MSSQL Server Database Backup Restore method

Tutorial on MSSQL Server database backup Restore method

Full backup: Backs up all selected folders and does not rely on the archive properties of files to determine which files to back up. (During the backup process, any existing tags are cleared, each file is marked as backed up, in other words, the archive attribute is cleared.) A full backup is also called a full backup.

Differential backup: A differential backup is for full backups: All files that have changed since the last full backup. (During a differential backup, only those selected files and folders that are marked are backed up.) It does not clear the tag, that is, after the backup is not marked as a backed-up file, in other words, the archive attribute is not cleared.

Incremental backup: An incremental backup is for the last backup (regardless of which backup): After the previous backup, all the changed files. (During an incremental backup, only marked selected files and folders are backed up, which clears the tag, that is, when you back up the tag file, in other words, clear the archive attribute.) ）

Transaction log backup: A full database backup and last differential backup (if any) that was performed before a specific transaction log backup. Any transaction log backups performed after a full database backup or a differential backup that was performed before a specific transaction log backup (if you restored a differential backup). If you set the recovery mode to "simple", you will not be able to use the "transaction log" backup. SQL Server 2000 and SQL Server 2005: Create transaction log backups, you must use full recovery or bulk-logged recovery models.

Partial backup: A backup created by specifying read_write_filegroups is called a partial backup. In the simple recovery model, only file group backups are allowed for read-only filegroups. Restored data backup type: Database backup, partial backup, or file backup. For a database or partial backup, the log backup sequence must start at the end of the database backup or partial backup. For a set of file backups, the sequence of log backups must be continued from the beginning of the entire set of file backups.

File backup: "File Backup" contains all the data in one or more files (or filegroups).

Log chain: A sequential sequence of log backups is called a "log chain." The log chain starts with a full backup of the database. Typically, a new log chain is started only when the database is backed up for the first time, or after the recovery mode is switched from the simple recovery model to the full or bulk-logged recovery model. Unless you choose to overwrite an existing backup set when you create a full database backup, the existing log chain will remain unchanged. When the log chain remains unchanged, the database can be restored from any full database backup in the media set, and then all subsequent log backups prior to the corresponding recovery point are restored. The recovery point can be the end of the last log backup, or it can be a specific recovery point in any log backup.

Example of a backup scenario: A site performs a full database backup at the night of Sunday. A transaction log backup set is made every 4 hours during the day and the first day of backup is rewritten with the day's backup. Differential backups are made every night. If a data disk for a database fails at Thursday 9:12, the site can:

1 Back up the current transaction log, (failure has occurred, how to back up the current transaction log?) ）

2 Restore database backup starting from Sunday night;

3 Restore the differential backup starting from Wednesday night, roll the database forward to this moment;

4 Restore the transaction log backups from 4 to 8 in the morning to roll the database forward to 8 in the morning;

5 The log backup after the failed restore. This will cause the database to roll forward to the moment the failure occurred.

To secure SQL Server data, database administrators should back up the database on a regular basis, with different backup database backup strategies in different situations, maintaining data security on the one hand, and keeping SQL Server running smoothly on the other. Try your best to reduce the damage to your customers due to data corruption.
Concept
1) Backup work plan
The stability of computer hardware and software has not yet reached a very stable state, the data in the computer is still lost or destroyed. The data that exists in the computer is unsafe, and even with fault-tolerant devices, the database is not guaranteed to be 100% secure. The cost of computer hardware can be high, but the computer data is more valuable, do a good job of data backup, can ensure that in all kinds of accidents, can still maintain the integrity of the database. Administrators must take the time to back up the database, but SQL Server also provides a number of automation capabilities.
2) Backup mode
Database-Complete (total)
is to back up the contents of the entire database. If you back up the entire contents of the database, you need a lot of storage space to store the backup data, but the advantage is that when you restore the database, you can simply restore the entire database from a database backup to SQL SERVER. It is the basis for other backups and must be backed up in this way before making other backups.
Database--Differences (differential)
Only data that has changed since the last full database backup is backed up, so the backup time and backup space required are less than the "full" approach, so this approach is best suited as a regular backup method.
Transaction log (Transaction log)
Backs up only the contents of the transaction log file. Because the transaction log file only records the changes we made to the database after the previous database backup or transaction log backup, that is, only the database changes over a certain period of time, you must do a "full" database backup before making the transaction record backup
Files and Filegroups:
If the contents of a database are distributed across multiple files or filegroups, and the database is already very large, a full database backup will have time and storage problems. You can then use this method to back up some files or filegroups of the database. Because only a portion of the file or filegroup is backed up at a time, several different backups are required to complete the backup of the entire database. The advantage of backing up this way is that when the corrupted data is just a file or filegroup in the database, it is only possible to restore bad files or filegroup backups.
3) The principle of the Backup program formulation
Planning the backup strategy for your database requires that you consider the various variables that you have backed up, and that you must take account of the restore job. When making a backup, consider the following points.
(1) How much time and during those periods can be backed up
(2) How often does the database change?
(3) Database size
(4) When the database problem, users are willing to wait how long to restore the database completed
(5) What kind of backup method should be used, and how to match
(6) What kind of storage media to use

4) Backup Scheme
(1) Full database backup with transaction log backup
For database data is larger, but daily changes in the content of small, you can consider this approach. A full database backup can be done on a regular basis, such as a full database backup weekly or monthly. A transaction log backup is then made in a shorter period, such as daily or two days. In this way, the backup work burden is relatively light, when it occurs, you can restore the data to the most recent state.
(2) Full database backup with differential type backup
This method changes the transaction log backup in the previous way to a differential backup. The advantage is that when you do a restore, you can restore it with the previous day's differential backup only after you've restored the most recent database backup, not as if you were using a transactional record backup. The disadvantage is that in the days after the comparison, the time and space needed to make a differential backup increase.
(3) The comprehensive use of three kinds of backup methods
Because transaction log backups and differential backups have their advantages and disadvantages, we can combine the two methods. For data information is very important to backup every 1 or 2 hours, the possible loss is only 1, 2 hours of data.
(4) When the database is too large, there are difficult to back up the problem, you can use file or filegroup backup, you can consider the database separately in different files and filegroups, and then the file and file groups of the entire database to be divided several times. In addition, if only some of the tables in the database are often changed and the other part is rarely changed, you can also consider saving them in different files and filegroups, and then backing up their contents with different backup frequencies. The disadvantage of backing up data in this way is that you have to work several times to back up your data.
(5) Use of standby server
In this way is to prepare a server, first in the original server to do a full backup, and then put the backup on the backup server to do the restore, so that the data on both sides of the synchronization, the original database can be done on a regular basis transaction log backup, the transaction log to the backup server to restore. When there is a problem with the original server, you can use the backup server to connect to the Network service.

Note: The system database backup and restore is a lot of users in the definition of database backup scheme is easy to ignore, although the system database does not save business data, but it saved a lot of SQL Server settings, if not back up the system database, then the server failure caused system damage, Reconfiguring all SQL Server settings will be a nightmare demo
Scenario One database backup (common scenarios)
In the SQL Server Backup window, you can set the current backup processing to the role of timed automatic processing by scheduling settings. The specific steps are as follows:
1. Expand a server group.
2. Expand a server (SQL Server instance).
3. Expand Databases, right-click the database you want to back up, and select "All Tasks"-〉 the "Back Up Database" command in the pop-up menu.
4. In the SQL Server Backup window that is subsequently opened, set the options for the backup. See previous backup scenarios
Click the General tab, and in the schedule item, select the Schedule check box, and then click the ... button after the Schedule check box. In the Edit Schedule dialog box that appears later, set the schedule for scheduling.
5. When the setup is complete, click OK, SQL Server will perform the backup operation and automatically establish the corresponding job in the SQL Server Agent-〉 job.
6. In the SQL Server Agent-〉 Job window, you can modify or delete the backup schedule defined in the Database Backup window as you would a normal job.
Demonstrate
Scheme II Data conversion method (DTS)
The specific solution is:
1. The primary server and the backup server are loaded with identical operating system and database software, as well as the corresponding management software;
2. Select the tables, views, stored procedures, etc. that need to be updated regularly, and choose all database updates if necessary;
3. Use the Data Transformation Services Import/Export Wizard provided by SQL Server (for example, SQL Server2000) to copy a Microsoft SQL Server object from one instance of SQL Server to another using the replication SQL Server Object task SQL Server;
4. Edit recurring Job scheduling to determine the frequency and specific time of occurrence, in general, due to the large amount of data, do not advocate many times every day, as far as possible in the network free time to occur.
The advantages of this scheme are:
1. Low cost, high reliability, simple operation and easy maintenance;
2. System conversion time is short, the backup system replaces the primary server in just a few minutes. Even if there is an accident, the backup system will soon return to normal, only a small amount of data loss, re-enter it;
3. Local and off-site backups are made using the evening network idle time, without increasing the daily network traffic, will not increase the network burden.
Note Select Transport objects, the same version of SQL Server can be backed up to a high version of SQL Server from each other, or from a low version, such as:
The instance of SQL Server 7.0 is backed up to the instance of SQL Server 2000.
An instance of SQL Server 2000 is backed up to another instance of SQL Server 2000.
Steps to Backup:
1. Open Enterprise Manager, select the SQL Server database, click the right mouse button, select All Tasks, and click Import data or Export data. If you are working on the primary server, select the export data, and if you are working on the backup server, select the import data.
2. After the first step, bring up the Data Transformation Services Import/Export Wizard interface, click Next, go to the DTS Import/Export Wizard selection Data source interface, select "Purpose", select "Make Table Copy or query", select the object to copy. Weak
When making a selection, first select the data source based on the specific client, and if it is an OLE DB client, select "OLE DB program provided by Microsoft for SQL Server."
Then select the server that provides the data source, and then choose how you want to enter the database by using Windows Authentication or using SQL Server authentication. If you select Use SQL Server authentication, you need to fill out a user who is approved by SQL Server and has rights to modify the data, generally optionally "SA" and its password. Finally, select the database you want to replicate.
3. In Make table replication and query, select Copy objects and data between SQL Server databases, and then click Next. Select specific replication objects and corresponding options, and so on.
4. Customize the timing and frequency of replication by saving schedules and replication packages.
5. After the completion of all settings, the prompts will list the source, purpose, manner adopted, database and other options, confirmed by you.
6. If you want to modify the custom time after the setup, in Enterprise Manager, through the server, database, management, SQL Server Agent, job, and then select the specific job, the interface to modify.
At this point, you can in your own small and medium enterprise environment, easy to achieve multiple database server automatic data backup between the time.
Demonstrate
Scheme three database Maintenance Plan +xcopy command (recommended)
1. Expand a server group
2. Expand a server instance (SQL Server instance).
3. Expand Management, right-click Database Maintenance plan, and select New Maintenance Plan from the shortcut menu that pops up.
4. In the subsequent database Maintenance plan-(local) dialog box, click Next to skip the Welcome page and, in the next select Database step, define the database to back up.
5. Click the Next button to skip steps unrelated to the database backup. In the Specify database backup schedule step, select Back up the database as part of the Maintenance Plan check box, select the backup device by clicking the tape or disk radio button, and click the Change button to the right of the schedule option. You can set the schedule for the maintenance plan in the Edit Recurring Job Schedule dialog box that appears later.
6. Click the Next button, and if the backup device chooses a disk device in step (5), it goes to the "Specify Backup disk directory" Step, setting the contents of this step, as appropriate.
7. Click the Next button to enter the "Specify Transaction log backup Schedule" step and, if a transaction log backup is required, select the "Back up the transaction log as part of the Maintenance plan" check box. Additional setup steps for backing up the transaction log reference (5) ~ (6).
8. Set up additional steps as needed, finally go to the "Completing the Database Maintenance Plan Wizard" step, set the "Schedule Name", and click the "Finish" button to complete the definition of the database maintenance plan.
9. After completing the steps (1) ~ (8), click Database Maintenance plan to see a defined database maintenance plan in the details window to the right of the Enterprise Manager console, while expanding the SQL Server Agent-〉 job, which is available in the detailed window on the right side of the job. You can see a backup exit job that is automatically established by the maintenance plan.
10. Through the Organic maintenance plan, select the "Properties" command, you can modify the defined maintenance plan.
11. By right-clicking the maintenance plan, selecting the Delete command, you can delete the defined maintenance plan, and the related jobs in the SQL Server Agent-〉 job are also deleted automatically.
Create a new batch file Copy.bat the specific script is as follows:
@echo off
Title is transferring the database backup files to the remote backup server!
echo Hint: Start verifying that there is a backup file on the server!
echo, please wait!
If exist F:dbback*.bak (goto Process) Else goto Unfound
:P rocess
echo Hint: A backup file was found on the server!
Echo-------------------------------------------------------------------------------
Echo Tip: Start deleting the original files on the remote backup server!

REM NOTE: The following IP address is the IP address of the remote backup server!
Del 192.168.244.24dbback/q/s/f
del 192.168.244.24dbback*.*/q/s/F
if errorlevel 0 goto success
: Success
echo Hint: The original file was deleted successfully!
Echo-------------------------------------------------------------------------------
echo Hint: Start copying database backup files to remote backup server 192.168.244.24dbback!
echo, please wait!
REM Note: Please change the location where 192.168.244.1DBback is stored for the server backup files. 192.168.244.24DBback for the location of the backup server!
xcopy 192.168.244.1DBback 192.168.244.24dbback/y/F
REM Note: When the xcopy command ends, the return of ERRORLEVEL to 4 indicates that the disk control is not sufficient. When the xcopy command returns 0 o'clock the description completes the operation successfully!
if errorlevel 4 goto lowmemory
if errorlevel 0 goto showmessage
: lowmemory
Echo WARNING: Please see if disk space is low!

: Unfound
Echo WARNING: The backup file cannot be found on the server and the batch task will end. The server backup does not appear to be working properly, please confirm that the server is generating the backup files properly!
Goto exit

: ShowMessage
echo Hint: Batch task complete!
echo Hint: Successfully replicate database backup to remote server 192.168.244.24dbback!
Echo Press any key to end exit!
Echo--------------------------------------------------------------------------------
: Exit
Pause
Echo successfully replicated Personnelm database backup to remote server 192.168.244.241!
Pause

Use Windows Task Scheduler to schedule batch tasks
1. Click Start-〉 Program-〉 attachment-〉 System Tools-〉 Task Scheduler
2. Double-click New Task Scheduler, pop-up Task Scheduler Wizard, and click Next. Select Browse in the Select Task form. We select the Copy.bat file to schedule execution.
3. In the next pop-up form, schedule a name for our task and choose the frequency of execution. Here we choose "Every day"
4. Enter the password for this computer in the password confirmation form. Click Next to select Finish. The scheduled task is complete. (If necessary, click to select the "Open Advanced properties for this task when click Complete" check box to make advanced settings for this task!) ）

Scenario Four Synchronous Backup server (script)
/* This section refers to the Jiangjian script * *
/*--Description:
The following code demonstrates how to use the log Restore feature to feed data changes in the primary database to the standby database in a timely manner
The data for the standby database can be used for queries at any time, but cannot be updated (the standby database is read-only).
--*/
--First, create a demo database (primary database)
CREATE DATABASE Db_test
On
(NAME = Db_test_data,
FILENAME = ' c:db_test.mdf ')
LOG on
(NAME = Db_test_log,
FILENAME = ' c:db_test.ldf ')
Go
--Backing up the database
BACKUP DATABASE db_test to disk= ' C:test_data.bak ' with FORMAT
Go
--Restore the database to an alternate database (demonstrating the synchronization between the primary and this standby database)
RESTORE DATABASE Db_test_bak from disk= ' C:test_data.bak '
With replace,standby= ' C:db_test_bak.ldf '
, move ' db_test_data ' to ' c:db_test_data.mdf '
, move ' db_test_log ' to ' c:db_test_log.ldf '
Go
--Start the SQL Agent service
EXEC Master.. xp_cmdshell ' net start SQLServerAgent ', no_output
Go
--Create a job that synchronizes between the primary server data trainer and the standby server database
DECLARE @jogid uniqueidentifier
EXEC msdb.. Sp_add_job
@job_id = @jogid OUTPUT,
@job_name = N ' data synchronization '
--Create synchronous processing steps
EXEC msdb.. Sp_add_jobstep
@job_id = @jogid,
@step_name = N ' Data sync ',
@subsystem = ' TSQL ',
@command = N ' '
--Log backups in the primary database
BACKUP LOG db_test to disk= "C:test_log.bak" with FORMAT
--Log backups of the primary database in the standby database (latest changes in the application master database)
--The restore job of the primary database backup and the standby database should be established on both the primary and standby servers, and the backup files should be placed in a shared directory accessible to both the primary server and the standby.
RESTORE LOG Db_test_bak from disk= ' C:test_log.bak ' with standby= ' c:test_log.ldf ',
@retry_attempts = 5,
@retry_interval = 5
--Create a schedule (run once per minute)
EXEC msdb.. Sp_add_jobschedule
@job_id = @jogid,
@name = N ' time schedule ',
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type =0x4,
@freq_subday_interval = 1,
@freq_recurrence_factor =1
--Add target server
EXEC Msdb.dbo.sp_add_jobserver
@job_id = @jogid,
@server_name = N ' (local) '
Go

--The synchronization relationship between the primary database and the standby database is set to complete through the above processing
--Start testing to see if synchronization is possible
--Create a test table in the primary database
CREATE TABLE Db_test.dbo.TB_test (ID int)
Go
--Wait for 1分钟30秒 (because the synchronization time interval is set to 1 minutes, so you have to delay to see the effect)
WAITFOR DELAY ' 00:01:30 '
Go
--Check the standby database to see if the synchronization is successful
SELECT * from Db_test_bak.dbo.TB_test
/*--results:
Id
-----------
(The number of rows affected is 0 rows)
--*/
--Test success
Go

--finally delete all the tests
DROP DATABASE Db_test,db_test_bak
EXEC msdb.. Sp_delete_job @job_name =n ' data synchronization '
Go
/*===========================================================*/
/*--Server Downtime Processing instructions
Database synchronization established in this manner, when the primary database is unavailable (for example, primary database corruption or downtime maintenance)
You can use the following two methods to make an alternate database available.
--*/
--1. If the primary database is corrupted and the most recent log cannot be backed up, you can use the following statement to make the standby database read-write (missing all data since the most recent log restore).
RESTORE LOG Db_test_bak with RECOVERY
--2. If the primary database can back up the most recent logs, you can use the following statement.
--Back up the latest transaction log for the primary database first
BACKUP LOG db_test to disk= ' C:test_log.bak ' with FORMAT
--Restore the most recent transaction log in the standby database and make the standby database readable and writable (upgrade to the primary database)
RESTORE LOG Db_test_bak from disk= ' C:test_log.bak '

Scenario Five replication subscriptions
Before we begin our study, we should understand the following concepts:
Replication (Replication) is the replication of data from somewhere to one or more destinations, because of the increasing demand for decentralized applications of the database, SQL Sever also provides replication capabilities.
The replication capabilities of SQL Server basically use the structure of Publisher (publisher) and Subscriber (Subscriber), which contains several different components.
(1) Publisher (publisher): In replication, the publisher is the SQL Server responsible for publishing the content of the database to other servers (typically we treat the primary server as a publisher, a metaphor for the image of the publisher as if it were the author of a book)
(2) Subscriber (Subscriber): A subscriber is a SQL Server that is published by a publisher, typically with only one publisher, but a subscriber can have more than one server. (Figurative subscription service is like a reader, there are many readers who buy the same books.) ）
(3) Distributor (distributor): In addition to the two basic publishers and subscribers, another role that SQL SERVER can play throughout the replication structure is the Distributor. The Distributor is the server responsible for publishing the databases published by the publisher and transferring them to each subscriber. In a simple replication structure, the Publisher and distributor typically use the same SQL Server. Set as SQL Server for the Distributor, there will be a system database named distribution that holds replication information.
(4) Publish Item (Article): The data published by the publisher is called a publishing item, the publication item is the replication unit for replication, the publication item can be a database object such as a table or stored procedure, or a few columns or some data in a table.
(5) Publish (Publish): A specific set of publishing item combinations is called a publication, which is the data that SQL Server replicates when replicating. When Subscribers subscribe to the publisher for data, they are published as a unit, not as a publishing item. That is, subscribers can only receive all article in the entire publication. Instead of selecting only a few of the release item class subscriptions, each publication has only content from a single database, but we can set up multiple publications for the database.
(6) To accommodate different replication requirements, SQL Server provides two different ways to subscribe (Push) and request (Pull). The difference is as follows:
(a) Send subscriptions (push subscription also known as push subscriptions): the so-called send subscriptions are sent by the Publisher to the Subscriber, that is, the publisher controls the replication operation, as long as the replication time is specified, regardless of whether the subscriber requests it, The publisher transmits data to the Subscriber.
(b) Pull subscriptions (Pull Subscription): Pull subscriptions and send subscriptions instead, subscribers are asked to request subscriptions to the publisher based on their scheduled schedule, usually in the case of more subscribers, or when the latest data content is not required to be fetched immediately.

Steps:
The steps to implement replication are described below. (Take snapshot replication for example)
Preparatory work:
1. Publisher, Subscriber creates a Windows user with the same name and sets the same password as a valid access user for the publication snapshot folder
--My computer.
--Control Panel
--management tools
--Computer Management
--Users and groups
--Right key user
--New user
--Establish a user who is logged on to Windows in the group of Administrators
2. At the publisher, create a new shared directory as the repository for the published snapshot files:
My Computer--d: Create a new directory, named: bakpub
--Right key to this new directory
--Properties--shared
--Select "Share this Folder"
--Set specific user rights by Newlay by "permissions" to ensure that the user created in the first step has all permissions on the folder
-Determine

3. Set up the startup user for the SQL Agent (SQLServerAgent) service (Publish/subscriber does this setting)
Start--Program--management tools--Service
--Right Key SQLServerAgent
--Attribute--Login--Select "This account"
--Enter or select the Windows logon user name created in the first step
--Enter the user's password in "password"
4. Set SQL Server Authentication mode to resolve permissions issues when connecting (publish/subscriber does this setting)
Enterprise Manager
--Right key SQL instance--Properties
--Security--authentication
--Select SQL Server and Windows
-Determine
5. Registering with each other at the Publisher and Subscriber
Enterprise Manager
--Right-click SQL Server Group
--New SQL Server registration ...
--Next--Available servers, enter the name of the remote server you want to register--add
--Next--Connect using, select Second SQL Server Authentication
--Next--Enter user name and password
--Next--Select a SQL Server group, or you can create a new group
--The next step--complete
6. Register a server alias for a computer name that can only be used for IP
(at the end of the connection configuration, for example, at the Subscriber, the server name is entered in the IP of the publisher)
Start--Program--microsoft SQL server--Client Network Utility
--Alias--add
--Network Library Select "TCP/IP"-Server alias input SQL Server name
--Connection parameter--Enter SQL Server IP address in server name
--If you modify the SQL port, deselect "Dynamic decision port" and enter the corresponding port number
==============================================================================
Official start:
1. Configure the Publisher
A. Select the specified server node
B. Select the Publish, subscribe to and distribute commands from the Copy submenu of the Tools Drop-down menu
C. The system pops up a dialog point [next] and then looks at the prompt to operate
--until "Specify Snapshot folder"
--Enter the directory created in the preparation work in the snapshot folder: < server name > Bakpub
One [next] straight operation to completion.
D. The system adds a replication Monitor to the server's tree structure when the publishing server is set up
Also generate a distribution database (distribution)

---------------------------------------------------------------------------
2. Create a Publication
A. Select the specified server
B. Choose the Create and manage publications command from the Copy submenu of the Tools menu. The system will now eject
A dialog box
C. Select the database to which you want to create the publication, and then click Create Publication
D. Clicking Next in the Prompt dialog box for the Create Publication Wizard will pop up a dialog box. Dialogue
The contents of the box are three types of replication. We now choose the first one is also the default snapshot publication (Two other
Everyone can go and see Help)
E. Click Next to specify the type of database server that can subscribe to this publication, which SQL Server allows
Data replication between different databases, such as Oracle or access. But here we choose to run
database server for SQL Server 2000
F. Click Next to select the objects (such as tables, views, stored procedures, typically tables) to be published.
G. Then [next] until the operation is complete. When you finish creating your publication, the database that creates it becomes
A shared database.

---------------------------------------------------------------------------
3. Design Subscriptions
A. Select the specified subscriber
b. Select [Pull subscriptions] from the Tools drop-down menu [Copy] submenu
C. Click Next until you are prompted to check the running state of the SQL Server Agent service and perform
The prerequisite for a copy operation is that the SQL Server Agent service must already be started.
D. Click Finish to complete the subscription operation.

Associated Users

--Lists orphaned users of the current database
exec sp_change_users_login ' the "

--You can automatically add a login with the same name that corresponds to the user name to syslogins
exec sp_change_users_login ' auto_fix ', ' username '

--Maps the user name to the specified login name.
exec sp_change_users_login ' update_one ', ' username ', ' sign-in name ' shrink log

ALTER DATABASE MyDB SET recovery simple
DBCC SHRINKFILE (mydb_log,10)--shrinks mydb_log to 10m to change owner to dbo

exec sp_msforeachtable "Sp_changeobjectowner '", ' dbo ' "fix the problem that SQL cannot connect after modifying the computer name

Sp_drops Tutorial erver ' oldSERVERSQL2005 '
Go
sp_addserver ' SERVERSQL2005 ', local scheduled backup database

DECLARE @t nvarchar (100)
DECLARE @db nvarchar (20)
SET @db = ' MyDB '
SET @t= ' e:mssqlbackup ' + @db + ' _ ' +replace (CONVERT (nvarchar, getdate ()), ': ', ' _ '), ', ' _ ') + '. Bak '
BACKUP DATABASE @db to DISK = @t with Noinit, Nounload, noskip, STATS = ten, Noformat
Go