Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -----------------------------------------------------------------
- -- Restore database from file
- -----------------------------------------------------------------
- use master
- go
- declare @backupFileName varchar(100), @restoreDirectory varchar(100),
- @databaseDataFilename varchar(100), @databaseLogFilename varchar(100),
- @databaseDataFile varchar(100), @databaseLogFile varchar(100),
- @databaseName varchar(100), @execSql nvarchar(1000)
- -- Set the name of the database to restore
- set @databaseName = 'myDatabase'
- -- Set the path to the directory containing the database backup
- set @restoreDirectory = 'aboslute_path_to_restore_directory' -- such as 'c:\temp\'
- -- Create the backup file name based on the restore directory, the database name and today's date
- set @backupFileName = @restoreDirectory + @databaseName + '-' + replace(convert(varchar, getdate(), 110), '-', '.') + '.bak'
- -- Get the data file and its path
- select @databaseDataFile = rtrim([Name]),
- @databaseDataFilename = rtrim([Filename])
- from master.dbo.sysaltfiles as files
- inner join
- master.dbo.sysfilegroups as groups
- on
- files.groupID = groups.groupID
- where DBID = (
- select dbid
- from master.dbo.sysdatabases
- where [Name] = @databaseName
- )
- -- Get the log file and its path
- select @databaseLogFile = rtrim([Name]),
- @databaseLogFilename = rtrim([Filename])
- from master.dbo.sysaltfiles as files
- where DBID = (
- select dbid
- from master.dbo.sysdatabases
- where [Name] = @databaseName
- )
- and
- groupID = 0
- print 'Killing active connections to the "' + @databaseName + '" database'
- -- Create the sql to kill the active database connections
- set @execSql = ''
- select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
- from master.dbo.sysprocesses
- where db_name(dbid) = @databaseName
- and
- DBID <> 0
- and
- spid <> @@spid
- exec (@execSql)
- print 'Restoring "' + @databaseName + '" database from "' + @backupFileName + '" with '
- print ' data file "' + @databaseDataFile + '" located at "' + @databaseDataFilename + '"'
- print ' log file "' + @databaseLogFile + '" located at "' + @databaseLogFilename + '"'
- set @execSql = '
- restore database [' + @databaseName + ']
- from disk = ''' + @backupFileName + '''
- with
- file = 1,
- move ''' + @databaseDataFile + ''' to ' + '''' + @databaseDataFilename + ''',
- move ''' + @databaseLogFile + ''' to ' + '''' + @databaseLogFilename + ''',
- norewind,
- nounload,
- replace'
- exec sp_executesql @execSql
- exec('use ' + @databaseName)
- go
- -- If needed, restore the database user associated with the database
- /*
- exec sp_revokedbaccess 'myDBUser'
- go
- exec sp_grantdbaccess 'myDBUser', 'myDBUser'
- go
- exec sp_addrolemember 'db_owner', 'myDBUser'
- go
- use master
- go
- */
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement