sqlserver2008备份还原-脚本实现-清晰易懂-备份源数据库

记录sqlserver2008的一次备份还原

注意：备份的路径是源数据库所在的主机路径（也就是说源服务路径）

备份：

DECLARE @name NVARCHAR(128) -- database name
DECLARE @path NVARCHAR(256) -- backup path
DECLARE @fileName NVARCHAR(256) -- backup file name
DECLARE @sql NVARCHAR(300) -- dynamic SQL

-- Set backup path (replace with your local path)
SET @path = 'D:\SQLBackups\'

-- Cursor to get all databases
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') -- 排除system databases

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    -- Construct file name and SQL command
    SET @fileName = @path + @name + '.bak'
    SET @sql = 'BACKUP DATABASE [' + @name + '] TO DISK = N''' + @fileName + ''' WITH INIT, NAME = N''' + @name + ' Full Backup'''
    
    -- Execute the backup command
    EXEC (@sql)
    
    FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

拿到 .bak 备份文件之后，放到目录中，并在脚本中指定路径

还原：

DECLARE @BackupDir NVARCHAR(MAX) = N'D:\SQLBackups\';
DECLARE @BackupFile NVARCHAR(MAX);
DECLARE @DatabaseName NVARCHAR(MAX);

-- Create a cursor to iterate over each backup file
DECLARE BackupCursor CURSOR FOR
SELECT [name] FROM sys.master_files WHERE [type] = 0 AND [physical_name] LIKE @BackupDir + '%.bak';

OPEN BackupCursor;
FETCH NEXT FROM BackupCursor INTO @BackupFile;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Extract database name from the backup file name
    SET @DatabaseName = REPLACE(REPLACE(@BackupFile, @BackupDir, ''), '.bak', '');

    -- Restore database
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = N'USE master; RESTORE DATABASE [' + @DatabaseName + '] FROM DISK = N''' + @BackupDir + @BackupFile + ''' WITH REPLACE;';
    
    EXEC sp_executesql @SQL;

    -- Fetch next file
    FETCH NEXT FROM BackupCursor INTO @BackupFile;
END

CLOSE BackupCursor;
DEALLOCATE BackupCursor;