Mssqlserver database backup restoration method

Mssqlserver backup restoration method full backup: backs up all selected folders and does not rely on the file archive attribute to determine which files to back up. (During the backup process, any existing tags are cleared, and each file is marked as backed up. In other words, the archive attribute is cleared ). Full backup is also called full backup. Differential backup: Differential backup is for full backup: All files that change after the last full backup are backed up. (During differential backup, only the marked files and folders are backed up. It does not clear the mark, mssql server backup restoration method

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

Differential backup: Differential backup is for full backup: All files that change after the last full backup are backed up. (During differential backup, only the marked files and folders are backed up. It does not clear the mark, that is, it is not marked as a backup file after the backup, in other words, it does not clear the archive attribute ).

Incremental Backup: the Incremental backup is for the last backup (no matter what type of backup): All files that have changed since the last backup. (During the Incremental backup process, only the marked files and folders are backed up. The Marked files are cleared, that is, the files are marked after the backup. In other words, the archive attributes are cleared .)

Transaction Log backup: full database backup and last differential backup performed before a specific transaction log backup (if any ). All transaction log backups performed after the full database backup or differential backups performed before the specific transaction log backup (if you have restored the differential backup ). If you set the recovery mode to simple, you will not be able to use transaction log backup. SQL Server 2000 and SQL Server 2005: Create a transaction log backup. You must use a full recovery or large-capacity log recovery model.

Partial backup: the backup created by specifying READ_WRITE_FILEGROUPS is called "partial backup ". In simple recovery mode, only file group backup can be performed on read-only file groups. Type of restored data backup: database backup, partial backup, or file backup. For database backup or partial backup, the log backup sequence must start from the end of the database backup or partial backup. For a group of file backups, the log backup sequence must start from the beginning of the entire group of file backups.

File backup: "file backup" contains all data in one or more files (or file groups.

Log chain: continuous log backup sequence is called "log chain ". The log chain starts from the full backup of the database. Generally, a new log chain starts only when the database is backed up for the first time, or when the recovery mode is switched from the simple recovery mode to the full recovery mode or the large-capacity log recovery mode. The existing log chain remains unchanged unless you select to overwrite the existing backup set when creating a full database backup. When the log chain remains unchanged, you can back up and restore the database from any complete database in the media set, and then restore all subsequent log backups before the corresponding recovery point. The recovery point can be the end of the last log backup or a specific recovery point in any log backup.

Example of a backup solution: a site backs up a complete database on Sunday evening. Create a transaction log backup set every four hours during the day and rewrite the backup of the previous day with the backup of the current day. Differential backup is performed every night. If a data disk in the database fails at AM on Thursday, the site can:

1) back up the current transaction log. (If a fault has occurred, how can I back up the current transaction log ?)

2) restore the database backup from Sunday night;

3) restore the differential backup from Wednesday night and roll the database to this point;

4) restore the transaction log backup from AM to roll the database forward to am;

5) restore the log backup after the fault. This will cause the database to roll forward to the moment when a fault occurs.

To ensure the security of SQL Server data, the database administrator should regularly back up the database and adopt different backup database backup policies under different circumstances to maintain data security, on the other hand, the SQL Server can run smoothly. Do your best to reduce the loss caused to customers due to data damage.
Concept
1) backup work plan
The stability of computer software and hardware has not yet reached a very stable state, and data in the computer is still lost or destroyed. The data stored in the computer is insecure. Even if a fault-tolerant device is used, the database security cannot be guaranteed by 100%. The cost of computer hardware may be high, but computer data is more precious. Backing up data can ensure the integrity of the database in the case of various accidents. The Administrator must spend time backing up the database, but SQL SERVER also provides many automated functions.
2) backup mode
Database-total)
Is to back up the content of the entire database. If you back up the entire database, you need a lot of storage space to store the backup data, but the advantage is that when you restore the database, you only need to restore the entire database from a database backup to SQL SERVER. It is the basis of other backups, and must be backed up in this way before other backups are performed.
Database-difference (Differential)
Only backup of the changed data after the last "full" database backup is performed. Therefore, the backup time and backup space are less than the "full" method, therefore, this method is most suitable for regular backup.
Transaction Log)
Only the content of the transaction record file is backed up. Because the transaction log file only records the changes made to the database after the previous database backup or transaction log backup, that is to say, only the database changes in a certain period of time are recorded. Therefore, a "complete" database backup must be performed before the transaction record backup.
File and file group:
If the database content is stored in multiple files or file groups, and the database is already very large, it may take time and storage space for a complete database backup. This method can be used to back up some files or file groups of the database. Because only part of the files or file groups are backed up at a time, different backups must be performed several times to complete the backup of the entire database. The advantage of using this method is that when the destroyed data is only a file or file group in the database, you only need to restore the bad file or file group backup.
3) Principles of backup solution formulation
To plan the database backup policy, you must consider the backup variables and the restoration job. When performing a backup, consider the following.
(1) How long is there and during which time periods can be backed up?
(2) How often are database changes?
(3) database size
(4) when there is a problem with the database, the user is willing to wait for the time to restore the database.
(5) backup mode and matching
(6) storage media to be used

4) backup solution
(1) full database backup and transaction log backup
This method can be used if the database data is large but the daily changes are small. Complete database backup can be performed on a regular basis, for example, weekly or monthly "full" database backup. Then, a transaction log is backed up every day or every two days for a short period of time. In this way, the backup workload is relatively low, and data can be restored to the most recent status when the backup occurs.
(2) full database backup and differential backup
This method is to change the transaction log backup method in the previous method to differential backup. The advantage is that, during restoration, you only need to restore the last database backup, and then use the differential backup of the previous day to restore it. Unlike the transaction record backup, it takes one day to restore. The disadvantage is that in the days after comparison, the time and space required for each differential backup increase.
(3) Comprehensive use of the three backup methods
Because of the advantages and disadvantages of transaction record backup and differential backup, we can combine the two methods. Data is very important. Data is backed up every one or two hours. The possible loss is only one or two hours.
(4) When the database is too large and difficult to back up, you can use the file or file group backup method. You can consider saving the database separately in different files and file groups, then, the entire database is backed up in the form of files and file groups. In addition, if only some tables in the database change frequently and the other part changes little, you can save the two parts in different files and file groups, then, we use different backup frequencies to back up its content. The disadvantage of using this method to back up data is that data can be backed up only several times.
(5) Use a backup server
In this way, another server is prepared. A full backup is performed on the original server, and then the backup is put on the backup server for restoration to synchronize data on both sides, in the future, you can regularly back up the transaction logs of the original database and store the transaction logs on the backup server for restoration. When a problem occurs on the original server, you can use the backup server to connect to the network to provide services.

Note: The backup and restoration of System databases are easily ignored by many users in defining database backup solutions. Although business data is not stored in the system database, many SQL Server settings are saved, if you do not back up the system database, reconfiguration of all SQL Server settings will be a nightmare DEMO when a Server failure causes system damage.
Solution 1 Database Backup (common solution)
In the SQL Server backup window, you can schedule settings to set the current backup processing to timed automatic processing. The procedure is as follows:
1. Expand a server group.
2. Expand a Server (SQL Server instance ).
3. Expand database, right-click the database to be backed up, and select all tasks> backup database in the pop-up menu.
4. In the subsequent SQL Server backup window, set backup options. See the previous backup solution
Click the General tab, select the scheduling check box in the scheduling item, and click... next to the scheduling check box 【...] Button. In the edit scheduling dialog box that appears later, set the time scheduling schedule.
5. After the configuration is complete, click OK. SQL Server performs the backup operation and automatically creates the corresponding job in SQL Server proxy> job.
6. In the SQL Server proxy> job window, you can modify or delete the backup plan defined in the database backup window as if you were dealing with common jobs.
Demo
Solution 2 Data Conversion Method (DTS)
The specific solution is:
1. The master server and backup server are loaded with identical operating system and database software, as well as corresponding management software;
2. Select tables, views, and stored procedures to be updated on a regular basis. If necessary, select all databases for updates;
3. use the data conversion service import/export Wizard provided by SQL Server (taking SQL Server2000 as an example) to copy an SQL Server Object task, copy the Microsoft SQL Server Object from one SQL Server instance to another SQL Server;
4. edit repeated job scheduling to determine the frequency and time of occurrence. Generally, due to the large amount of data, it is not recommended to occur multiple times a day.
The advantages of this solution are:
1. low cost, high reliability, simple operation, and easy maintenance;
2. The system conversion time is short. It takes only a few minutes for the backup system to replace the master server. Even in the event of an accident, the backup system will soon return to normal, with only a small amount of data lost and re-input;
3. Local and remote backup are performed at night when the network is idle, without increasing the daily network traffic and without increasing the network burden.
Note that if you select a transfer object, SQL Server of the same version can be backed up from the lower version to the higher version, for example:
SQL Server 7.0 instances are backed up to SQL Server 2000 instances.
An SQL Server 2000 instance is backed up to another SQL Server 2000 instance.
Backup procedure:
1. Open Enterprise Manager, select SQLServer Server database, right-click, select "all tasks", and click "import data" or "export data ". If you operate on the master server, select export data. If you operate on the backup server, select import data.
2. after step 1, call the "Data Conversion service import/export wizard" interface and click "Next" to go to the "data source selection interface of the" DTS import/export wizard "and select" purpose ", select "create table copy or query" and select the object to be copied. Weak
When selecting a data source, first select the data source based on the specific client. If it is an ole db client, select "OLEDB program provided by Microsoft for SQL Server ".
Select the Server that provides the data source, and then select the database access method. You can select "use Windows Authentication" or "use SQL Server Authentication ". If you select "use SQL Server Authentication", you must enter the user that SQL Server recognizes and has the right to modify the data. Generally, you can select "SA" and its password. Finally, select the database to be copied.
3. In "create table copy and query", select "copy objects and data between SQL Server databases" and click Next. Select a specific copy object and related options ..
4. Save the scheduling and copy packages to customize the replication time and frequency.
5. After all the settings are complete, you will be prompted to list the source, purpose, method used, database, and other options, which will be confirmed by you.
6. if you want to modify the custom time after setting, in the Enterprise Manager, select a specific job through the server, database, management, SQL server proxy, and job, and call up the interface to modify the time.
Now, you can easily implement regular and automatic data backup between multiple database servers in your own small and medium-sized enterprise environment.
Demo
Solution 3 database maintenance plan + Xcopy command (recommended)
1. Expand a server group
2. Expand a Server instance (SQL Server instance ).
3. Expand manage, right-click Database Maintenance Plan, and select create maintenance plan from the shortcut menu.
4. in the subsequent "database maintenance plan-(Local)" dialog box, click "Next" to skip the welcome page. In the next "select database" step, define the database to be backed up.
5. click the next button to skip steps unrelated to database backup. In the "specify database backup plan" step, select the "Back up database as part of the maintenance plan" check box; click tape or disk to select a backup device. Click Change on the right of the scheduling option, you can set the maintenance schedule in the edit recurring Job Scheduling dialog box that appears later.
6. click Next. If the backup device is a disk device in step (5), the backup disk directory is specified, set the content in this step.
7. Click "Next" to go to the "specify transaction log backup plan" step. If you need to back up transaction logs, select the "Back up transaction logs as part of the maintenance plan" check box. For more information about how to back up transaction logs, see (5 )~ (6 ).
8. set other steps as needed. Finally, go to the "completing database maintenance plan wizard" step, set the "Plan Name", and click "finish" to define the database maintenance plan.
9. Complete steps (1 )~ (8) Click database maintenance plan. In the Details window on the right of the Enterprise Manager Console, you can see the defined database maintenance plan, expand SQL Server proxy> job. In the Details window on the right of job, you can see the backup job automatically created in the maintenance plan.
10. Select the attribute command through the organic maintenance plan to modify the defined maintenance plan.
11. right-click the maintenance plan and select the DELETE command to delete the defined maintenance plan. The related jobs in SQL Server proxy> job are also automatically deleted.
The specific script for creating a batch file Copy. bat is as follows:
@ Echo off
Title is transferring the database backup file to the remote backup server!
Echo prompt: Start to verify whether there is a backup file on the server!
Echo, please wait!
If exist F: DBback *. bak (goto Process) else goto UnFound
: Process
Echo prompt: the backup file is found on the server!
Echo -------------------------------------------------------------------------------
Echo prompt: Start to delete the original file on the remote backup server!

Rem Note: The following IP addresses are the IP addresses 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 prompt: the original file is deleted successfully!
Echo -------------------------------------------------------------------------------
Echo prompt: Start copying the database backup file to the remote backup server 192.168.244.24DBback!
Echo, please wait!
Rem remarks: Change 192.168.244.1DBback to the location where the backup files are stored on the server. 192.168.244.24DBback is the location on the backup server!
Xcopy 192.168.244.1DBback 192.168.244.24DBback/y/f
Rem remarks: When the Xcopy command ends, the returned Errorlevel is 4, indicating that the disk control is insufficient. If the Xcopy command returns 0, the operation is successfully completed!
If errorlevel 4 goto lowmemory
If errorlevel 0 goto showmessage
: Lowmemory
Echo WARNING: Check whether the disk space is insufficient!

: UnFound
Echo WARNING: the backup file cannot be found on the server, and the batch task is about to end. The backup on the server does not seem to work properly. Check whether the backup file is generated on the server!
Goto exit

: Showmessage
Echo prompt: the batch task is completed!
Echo prompt: the database is successfully copied and backed up to the remote server 192.168.244.24DBback!
Echo press any key to end and quit!
Echo --------------------------------------------------------------------------------
: Exit
Pause
Echo successfully copied the PersonnelM database to the remote server 192.168.244.241!
Pause

Use Windows Task Scheduler to schedule Batch Tasks
1. Click Start> program> attachment> System Tools> Task Plan"
2. Double-click "Create task plan". In the "task plan wizard" displayed, click "Next ". In the select Task form, select Browse ". Select the Copy. bat file to be scheduled.
3. In the following pop-up form, name the task scheduler and select the execution frequency. Here we select "Daily"
4. Enter the password of the local machine in the Password confirmation form. Click "Next" and select "finish ". Now the task plan is set. (If necessary, you can click the "open Advanced properties of this task when you click Finish" check box to enable advanced settings for this task !)

Solution 4 synchronous Backup Server (SCRIPT)
/* This part refers to the script created by the producer */
/* -- Description:
The following code demonstrates how to use the log restoration function to timely report data changes in the primary database to the standby database.
The data of the standby database can be used for query at any time, but cannot be updated (the standby database is read-only ).
--*/
-- First, create a database for demonstration (master 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
-- Back up the database
Backup database Db_test to disk = 'C: test_data.bak 'WITH FORMAT
GO
-- Restore the database to a backup database (demonstrate synchronization between the master database and the backup 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 jobs for synchronization between master server data training and slave server database
DECLARE @ jogid uniqueidentifier
EXEC msdb .. sp_add_job
@ Job_id = @ jogid OUTPUT,
@ Job_name = n' Data Synchronization Process'
-- Create synchronization process steps
EXEC msdb .. sp_add_jobstep
@ Job_id = @ jogid,
@ Step_name = n' data synchronization ',
@ Subsystem = 'tsql ',
@ Command = n''
-- Log backup in the primary database
Backup log Db_test to disk = "c: test_log.bak" WITH FORMAT
-- Restore the log backup of the master database in the backup database (the latest changes in the application master database)
-- In practice, the restoration jobs of the backup and backup databases of the primary database should be created on the primary server and the backup server respectively, and the backup files should be stored in the shared directory that can be accessed by both the primary server and the backup server.
Restore log Db_test_bak from disk = 'C: test_log.bak 'with standby = 'C: test_log.ldf ',
@ Retry_attempts = 5,
@ Retry_interval = 5
-- Create Scheduling (executed every minute)
EXEC msdb .. sp_add_jobschedule
@ Job_id = @ jogid,
@ Name = n' schedule ',
@ Freq_type = 4,
@ Freq_interval = 1,
@ Freq_subday_type = 0x4,
@ Freq_subday_interval = 1,
@ Freq_recurrence_factor = 1
-- Add the target server
EXEC msdb. dbo. sp_add_jobserver
@ Job_id = @ jogid,
@ Server_name = n' (local )'
GO

-- Through the above processing, the synchronization relationship between the master database and the slave database has been set.
-- Test whether synchronization can be implemented.
-- Create a test table in the primary database
Create table Db_test.dbo.TB_test (ID int)
GO
-- Wait 1 minute 30 seconds (because the synchronization interval is set to 1 minute, so the latency is required to see the effect)
Waitfor delay '00: 01: 30'
GO
-- Query the slave database to see if the synchronization is successful.
SELECT * FROM Db_test_bak.dbo.TB_test
/* -- Result:
ID
-----------
(The number of affected rows is 0)
--*/
-- Test successful
GO

-- Delete all tests
Drop database Db_test, Db_test_bak
EXEC msdb .. sp_delete_job @ job_name = n' Data Synchronization Process'
GO
/* ===================================================== ===================================== */
/* -- Server downtime handling instructions
This method is used to synchronize databases. When the primary database is unavailable (for example, the primary database is damaged or shut down for repair)
You can use either of the following methods to make the standby database available.
--*/
-- 1. If the primary database is damaged and the latest logs cannot be backed up, you can directly use the following statement to make the standby database readable and writable (loss of all data after the last log restoration ).
Restore log Db_test_bak WITH RECOVERY
-- 2. If the primary database can back up the latest logs, you can use the following statement.
-- First back up the latest transaction logs of the primary database
Backup log Db_test to disk = ''c: test_log.bak ''WITH FORMAT
-- Restore the latest transaction logs in the slave database and make the slave database readable and writable (upgraded to the master database)
Restore log Db_test_bak from disk = 'C: test_log.bak'

Solution 5 copy and subscribe
Before learning, we should first understand the following concepts:
Replication refers to copying data from one location to one or more destinations. Due to the increasing demand for distributed database applications, SQL Server also provides the Replication function.
The replication function of SQL Server basically uses the structure of "Publisher" and "Subscriber", which contains several different components.
(1) Publisher: in replication, the Publishing Server is the SQL Server responsible for publishing database content to other servers (generally we regard the master Server as the Publishing Server, the image is a metaphor. The Publishing Server is like the author of a book)
(2) Subscriber: the Subscriber is the SQL SERVER that publishes data from the published SERVER. Generally, there is only one Publishing SERVER, but the Subscriber can have multiple. (The image is similar to the subscription service. There are many readers who buy the same book .)
(3) distribution SERVER (Distributor): apart from the distribution SERVER and subscription SERVER, the other role that SQL SERVER can play in the entire replication structure is the distribution SERVER. The distribution server is responsible for publishing and transmitting the database published by the publishing server to the servers of each subscription server. In a simple replication structure, the publisher and distributor usually use the same SQL SERVER. If it is set as the distribution SERVER's SQL SERVER, a system database named distribution will store the replication information.
(4) Publishing item (Article): The data published by the publishing server is called a publishing item, and the publishing item is the replication unit during replication, A published item can be a database object such as a table or stored procedure, or a certain number of columns or items in a knowledge table.
(5) Publish: A combination of Specific release items is called Publish, that is, the data that is copied by SQL SERVER during replication. When the subscription server subscribes to data from the Publishing Server, it refers to publishing, rather than Publishing items. That is, the subscription server can only receive all articles in the entire release. Instead of selecting just a few release item subscriptions, each release only has content from a single database, but we can set multiple releases for the database.
(6) to meet different replication requirements, SQL SERVER provides two different subscription methods: Push and Pull. The differences are as follows:
(A) Push subscribe (also called forced Subscription): The Publish and subscribe is an operation that the Publishing Server actively transmits to the Subscription server, that is, the replication operation is controlled by the publishing server, as long as the replication time is reached, no matter whether the subscription server makes any request, the publisher will send the data to the subscription server.
(B) Pull Subscribe: The request Subscription is opposite to the Subscription sent by the subscriber. The Subscriber needs to subscribe to and publish to the Publishing Server Based on the schedule, this subscription method is usually used when the number of subscription servers is large or the latest data content does not need to be retrieved in real time.

Steps:
This section describes how to copy data. (Take snapshot copy as an example)
Preparations:
1. On the Publishing Server, the subscription server creates a windows user with the same name, and sets the same password as the valid user who accesses the snapshot folder.
-- My computer
-- Control Panel
-- Management Tools
-- Computer Management
-- Users and groups
-- Right-click the user
-- Create a user
-- Create a windows login user affiliated to the administrator Group
2. Create a new shared directory on the Publishing Server as the directory for storing the published snapshot files. perform the following operations:
My computer -- D: create a directory named BakPUB
-- Right-click the newly created directory
-- Property -- share
-- Select "share this folder"
-- Use the "permission" button to set specific user permissions to ensure that the user created in step 1 has all permissions on the folder
-- OK

3. Set the startup user of the SQL proxy (SQLSERVERAGENT) Service (this setting is done by the publishing/subscription server)
Start -- program -- management tool -- service
-- Right-click SQLSERVERAGENT
-- Property -- login -- select "this account"
-- Enter or select the windows logon user name created in step 1.
-- Enter the user's password in "password"
4. Set the SQL Server Authentication Mode to solve the permission issue during connection (this setting is done by the publishing/subscription servers)
Enterprise Manager
-- Right-click an SQL instance -- Properties
-- Security -- Authentication
-- Select "SQL Server and Windows"
-- OK
5. Register with each other on the Publishing Server and subscription Server
Enterprise Manager
-- Right-click the SQL Server group
-- Create an SQL Server registration...
-- Next -- available server, enter the name of the remote server you want to register -- add
-- Next -- connect to use, select the second "SQL Server Authentication"
-- Next -- enter the user name and password
-- Next -- select an SQL Server group or create a new group.
-- Next -- complete
6. Register a server alias for an IP address-only computer name.
(Configure on the connection end. For example, if the server is configured on the subscription server, enter the IP address of the Publishing Server in the server name)
Start -- program -- Microsoft SQL Server -- client network utility
-- Alias -- add
-- Select "TCP/IP" for the network library -- enter the SQL server name as the server alias
-- Connection parameter -- enter the SQL Server IP address in the server name
-- If you have modified the SQL port, deselect "dynamically determine port" and enter the corresponding port number.
========================================================== ==============================================
Official start:
1. Configure the Publishing Server
A. Select the specified [server] Node
B. Select the [Publish, subscribe server, and distribute] command from the [copy] submenu of [Tool ].
C. In the dialog box that appears, click [next] and follow the prompts.
-- Until "specified snapshot folder"
-- Enter the directory created during preparation in the "snapshot folder: <服务器名> BakPUB
1. [next] The operation is complete.
D. After setting the Publishing Server, the system will add a replication monitor to the tree structure of the server.
A distribution database (distribution) is also generated)

---------------------------------------------------------------------------
2. Create a release
A. Select the specified server
B. Select the [create and manage release] command from the [copy] sub-menu in the [tools] menu. At this time, the system will pop up
A dialog box
C. Select the database for which you want to create a release, and click [Create release]
D. Click [next] in the prompt dialog box of [Create release wizard]. A dialog box is displayed. Dialog
The content in the dialog box is the three types of replication. Now we select the first one, that is, the default snapshot release (the other two
You can check the help)
E. Click [next] system requirements to specify the type of database server that can subscribe to the release. SQLSERVER allows
Data is replicated between different databases, such as ORACLE or ACCESS. But here we choose to run
"SQL SERVER 2000" Database SERVER
F. Click [next] and select the objects to be published (such as tables, views, stored procedures, and tables)
G. Then [next] until the operation is completed. After the publication is created, the database for creating the publication becomes
A shared database.

---------------------------------------------------------------------------
3. Design subscription
A. Select the specified subscription Server
B. Select [subscribe request] from the [tools] drop-down menu in the [copy] submenu
C. Follow the prompts and click [next] until the system prompts you to check the running status of the SQL SERVER proxy service.
The precondition for the replication operation is that the SQL SERVER proxy service must be started.
D. Click [finish] to complete the subscription operation.

Associated user

-- List isolated users of the current database
Exec sp_change_users_login 'report'

-- Automatically add the login with the same name as the user name to syslogins
Exec sp_change_users_login 'Auto _ fix', 'username'

-- Map the user name to the specified Login Name.
Exec sp_change_users_login 'Update _ one', 'username', and 'username' shrink logs

Alter database mydb set recovery simple
Dbcc shrinkfile (mydb_log, 10) -- contract mydb_log to 10 m and change the owner to DBO.

Exec sp_msforeachtable "sp_changeobjectowner '? ', 'Dbo' "fixed the problem that SQL cannot be connected after the computer name was modified.

Sp_droerver 'oldserversql2005'
Go
Sp_addserver 'serversql200', local timed backup database

DECLARE @ t nvarchar (100)
DECLARE @ db nvarchar (20)
SET @ db = 'mydb'
SET @ t = 'e: MSSQLBACKUP '+ @ db +' _ '+ replace (CONVERT (nvarchar (16), getdate ()),':', '_'), '', '_') + '. bak'
Backup database @ db to disk = @ t with noinit, NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT
Go